Login
Register
Search
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Forums
Integration / Customization
S3 Customization/Development
database index filter routines
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Who's On?
Membership:
Latest:
Chris Radcliffe
Past 24 Hours:
0
Prev. 24 Hours:
0
Overall:
5187
People Online:
Visitors:
266
Members:
0
Total:
266
Online Now:
New Topics
S3 Customization/Development
Cobol - Extract Current Time
4/24/2024 7:21 PM
How do you extract just the Current System Time in
Lawson Landmark
LPL INSTR Functions
4/5/2024 8:32 PM
I'm writing a simple report using the Create R
Infor SCM
Translating 856 to get the ~ REF^CN^ field
4/3/2024 8:24 PM
We are trying to get the tracking number which is
IPA/ProcessFlow
Sample XML file create Flow
4/3/2024 3:43 PM
Hello everyone, I am new to creating XML files
Lawson S3 HR/Payroll/Benefits
bn105 error message
3/26/2024 6:40 PM
I need to change some of the set ups in our Life I
IPA/ProcessFlow
IPA executing Job
3/13/2024 7:08 PM
New to the IPA world and was wondering, can an IPA
Lawson S3 HR/Payroll/Benefits
Life Age Reduction on benefits plans
3/12/2024 7:15 PM
For our optional life we have an age based coverag
Lawson S3 HR/Payroll/Benefits
BN53.1 Add-In
3/7/2024 3:31 PM
We are migrating to Solstice. They require a
Lawson Business Intelligence/Reporting/Crystal
Domain Name Change
3/5/2024 7:45 PM
Our domain name needs to change and was hoping I c
S3 Customization/Development
Cobol calling Shell Script
2/29/2024 1:27 PM
Has anyone created or modified a Lawson Cobol prog
Top Forum Posters
Name
Points
Greg Moeller
4184
David Williams
3349
JonA
3288
Kat V
2984
Woozy
1973
Jimmy Chiu
1883
Kwane McNeal
1437
Ragu Raghavan
1351
Roger French
1311
mark.cook
1244
Forums
Unanswered
Active Topics
Most Liked
Most Replies
Search Forums
Search
Advanced Search
Topics
Posts
Prev
Next
Forums
S3 Customization/Development
database index filter routines
Sort:
Oldest First
Most Recent First
You are not authorized to post a reply.
Author
Messages
Chesca
Veteran Member
Posts: 490
10/28/2013 5:33 PM
I am trying to use filters and using sample code in the doc for developers API. But I don't quite understand how to use WS-DB-BEG-RNG or it's purpse. Does it matter if I use company or employee number?
Woozy
Veteran Member
Posts: 709
10/28/2013 6:49 PM
You pass in the index-fieldname of the "lowest" index field you want to start the filter with. Below is an example of using the filter to return all HRDEPBEN records for a specific company/employee filtered by the plantype/plancode where the hdb-start-date = ben-start-date.
Note that WS-DB-BEG-RNG is set to HDBSET4-EMPLOYEE because we want this limited by the company/employee based on the index, but then filtered from there:
I hope this helps!
Kelly
* Identify the fields to filter by
STRING "((HDB-PLAN-TYPE = ?) AND "
"(HDB-PLAN-CODE = ?) AND "
"(HDB-EMP-START = ?))" DELIMITED BY SIZE
INTO FILTER-STRING.
* Establish the filter
PERFORM 890-CREATE-FILTER.
* Populate the Plan Type to filter (for the first "?")
MOVE BEN-PLAN-TYPE TO ALPHANUM-FILTER-VALUE.
PERFORM 890-SET-ALPHANUM-FILTER-VALUE.
* Populate the Plan Code to filter (for the second "?")
MOVE BEN-PLAN-CODE TO ALPHANUM-FILTER-VALUE.
PERFORM 890-SET-ALPHANUM-FILTER-VALUE.
*Populate the Start Date to filter by (for the third "?")
MOVE BEN-START-DATE TO DATETIME-FILTER-VALUE.
*Populate the index fields
MOVE BEN-COMPANY TO DB-COMPANY.
MOVE BEN-EMPLOYEE TO DB-EMPLOYEE.
* Identify Employee as the lowest index field to use to start the filter
MOVE HDBSET4-EMPLOYEE TO WS-DB-BEG-RNG.
* Find the first record
PERFORM 850-FILTER-BEGRNG-HDBSET4.
*Cycle though the records until no more are found.
IF (HRDEPBEN-FOUND)
* Process the record
PERFORM 1070-WHATEVER
THRU 1070-END
* Get the next record
PERFORM 860-FIND-NXTRNG-HDBSET4.
UNTIL (HRDEPBEN-NOTFOUND)
END-IF.
Chesca
Veteran Member
Posts: 490
10/28/2013 7:11 PM
Got it, thank you. I want to get all closed transactions for a specific employee and plan.
**Create Filter closed trans and tran type 21
***
MOVE SPACES TO FILTER-STRING.
STRING
"(ETT-STATUS =?) AND "
"(ETT-PLAN-NAME =?) AND "
"(ETT-TYPE =?) AND "
"(ETT-DATE >=?) "
DELIMITED BY SIZE
INTO FILTER-STRING.
PERFORM 890-CREATE-FILTER.
MOVE 9 TO NUMERIC-FILTER-VALUE.
PERFORM 890-SET-NUMERIC-FILTER-VALUE.
MOVE WS-ETT-PLAN-NAME TO ALPHANUM-FILTER-VALUE.
PERFORM 890-SET-ALPHANUM-FILTER-VALUE.
MOVE "ME" TO ALPHANUM-FILTER-VALUE.
PERFORM 890-SET-ALPHANUM-FILTER-VALUE.
MOVE 20121002 TO DATETIME-FILTER-VALUE.
PERFORM 890-SET-DATETIME-FILTER-VALUE.
MOVE ETTSET1-EMPLOYEE TO WS-DB-BEG-RNG.
Woozy
Veteran Member
Posts: 709
10/28/2013 7:23 PM
Don't forget - if you are going to set WS-DB-BEG-RNG to ETTSET1-EMPLOYEE, then before that you will need to populate DB-COMPANY and DB-EMPLOYEE with the proper values. The idea behind the filter is that it uses as much of an index as it can, and then filters the results of the index query based on the filter fields.
jaherb
Veteran Member
Posts: 164
10/29/2013 3:35 PM
Index Filters are great tools.... just takes a little getting use to, but once you understand them, they will become your friend.
What the WS-DB-BEG-RNG does is hold the lowest field of the index you are using, as Kelly stated. The value that actually goes into this field is actually a numeric value tellig the system the lowest field you are using in the index. Since you are using Comany & Employee the value that will go into the WS-DB-BEG-RNG would be the numeric value of 2, since the employee value is the second field of the index.
You are not authorized to post a reply.