Filter API - LawsonGuru.com - LawsonGuru.com Forums

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3288
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1348
Roger French	1311
mark.cook	1244

ARaja

Advanced Member

Posts: 26

12/24/2008 7:20 AM

Can i use the field in the index as filter criteria as below?

STRING "((GLT-ACCT-PERIOD >= ?) AND"    DELIMITED BY SIZE
                 "(GLT-ACCT-PERIOD <= ?) AND"    DELIMITED BY SIZE
                "(GLT-FISCAL-YEAR = ?))"       DELIMITED BY SIZE
                                                               INTO FILTER-STRING.
    PERFORM 890-CREATE-FILTER.
    MOVE PRM-FR-ACCT-PERIOD           TO NUMERIC-FILTER-VALUE.
    PERFORM 890-SET-NUMERIC-FILTER-VALUE.
    MOVE PRM-TO-ACCT-PERIOD           TO NUMERIC-FILTER-VALUE.
    PERFORM 890-SET-NUMERIC-FILTER-VALUE.
    MOVE PRM-FISCAL-YEAR              TO NUMERIC-FILTER-VALUE.
    PERFORM 890-SET-NUMERIC-FILTER-VALUE.
    PERFORM 850-FILTER-NLT-GLTSET3.

Thanks in advance for your suggestions.

Pramod Chandra

Basic Member

Posts: 12

1/19/2009 6:50 AM

Yes, You can, above filter looks right if the database fields are numeric
Thanks
Pramod
9836078822

ARaja

Advanced Member

Posts: 26

1/19/2009 9:30 AM

Pramod thanks for ur response.

The filter criteria looks correct. But i had a question like whether we can use the fields in the database index as a selection criteria in the filter.

Thanks,
Raja

Pramod Chandra

Basic Member

Posts: 12

1/19/2009 9:40 AM

call me 09836078822 if you are in india
what do u mean "using an index in filter "? offsource use of filter is on index always

ARaja

Advanced Member

Posts: 26

1/19/2009 11:29 AM

Acct_period and fiscal_year are part of the index (gltset3) of gltrans table. whether i can include this field as a selection criteria in my filter api?

Pramod Chandra

Basic Member

Posts: 12

1/19/2009 12:01 PM

Man Can you just let me know what are all the fields in GLTSET3

John Henley

Senior Member

Posts: 3348

1/19/2009 3:03 PM

Raja, filters are typically used when desired fields for filtering are not part of the index. The advantage is that Lawson passes to the database in form of a WHERE clause, and the database handles the filtering. Prior to having the FILTER APIs, filtering was done within the COBOL program, which required all the data to pass from the database to the program. So, the reduced traffic is the performance gain of the FILTER API.

As for using indexed fields with FILTER, it would be redundant if you are requesting index fields "from the top down", but if you wanted to request certain records which would leave "holes" in your index (say account across all companies), then FILTER would help with performance.

Is your question really about which fields to fill for which index?

Pramod Chandra

Basic Member

Posts: 12

1/19/2009 3:42 PM

No that wasnt my question
My question was what are the fields in GLTSET3

John Henley

Senior Member

Posts: 3348

1/19/2009 4:25 PM

Go here and drill into desired version: https://www.lawsonguru.co.../datadictionary.aspx

ridjayc

Veteran Member

Posts: 191

1/19/2009 4:38 PM

Here is the script that creates the index. This is on a SQL server and also Apps 8.1.ESP5. On your system it could be different. You should be able to get this same data by just going into Lawson Microsoft Addins and looking at the index there. Also dbdef should have this information.

GO
/****** Object: Index [GLTSET3] Script Date: 01/19/2009 10:18:09 ******/
CREATE NONCLUSTERED INDEX [GLTSET3] ON [dbo].[GLTRANS]
(
[GLTSET3_SS_SW] ASC,
[TO_COMPANY] ASC,
[ACCOUNT] ASC,
[SUB_ACCOUNT] ASC,
[ACCT_UNIT] ASC,
[FISCAL_YEAR] ASC,
[ACCT_PERIOD] ASC,
[SYSTEM] ASC,
[CONTROL_GROUP] ASC,
[JE_TYPE] ASC,
[JE_SEQUENCE] ASC,
[LINE_NBR] ASC,
[COMPANY] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

ARaja

Advanced Member

Posts: 26

1/20/2009 3:54 AM

John,

Thanks for ur clear response about filters.