Prev
Next
Forums Integration / Customization S3 Customization/Development

Filter API

 10 Replies
 0 Subscribed to this topic
 17 Subscribed to this forum
Sort:
Author
Messages
ARaja
Advanced Member
Posts: 26
Advanced Member
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
    Basic Member
    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
      Advanced Member
      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
        Basic Member
        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
          Advanced Member
          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
            Basic Member
            1/19/2009 12:01 PM
              Man Can you just let me know what are all the fields in GLTSET3
              John Henley
              Posts: 3353
              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?
                Thanks for using the LawsonGuru.com forums!
                John
                Pramod Chandra
                Basic Member
                Posts: 12
                Basic Member
                1/19/2009 3:42 PM
                  No that wasnt my question
                  My question was what are the fields in GLTSET3
                  John Henley
                  Posts: 3353
                  1/19/2009 4:25 PM
                    Go here and drill into desired version: https://www.lawsonguru.co.../datadictionary.aspx
                    Thanks for using the LawsonGuru.com forums!
                    John
                    ridjayc
                    Veteran Member
                    Posts: 191
                    Veteran Member
                    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
                      Advanced Member
                      1/20/2009 3:54 AM

                        John,

                        Thanks for ur clear response about filters.