3 Replies Latest reply: May 15, 2014 9:54 AM by Grant Perkins RSS

    Slow Filter with advanced trapping

    Nick Osdale-Popa

      Here is my report layout:

       

      [font="courier"] RUN DATE 11/21/07  15:54:31                          COINS PAYROLL MODULE                                               PAGE   246    

      OUR COMPANY NAME                                       EMPLOYEE LISTING                                                                               

      BY EMPLOYEE NUMBER                        USER PR SECURITY LEVEL:          9                                                                               

      12345     LASTNAME, FIRSTNAME M.             SS: 666-66-6666    HIRE DATE: 10/01/2003 UNION:                 LABOR CATEGORY:          

                 123 MAIN STREET                    MINORITY:     A    TERM DATE:                                                                               

      TRADE: PR                EEO SKILL LEVEL:         

                                                    GENDER:       O    ACTIVE:           Y   SKILL:                 CERTIFIED JOB:      N    

      ANY CITY            , US  92704              MARITAL STAT: E    PAY FREQ:         B   JOB:                   PAY COUNT:         23    

                                                    SUPER: OFF         HOUR/SALRY:       H   PHS:   GC              NORMAL HOURS:    0.00    

      FIRST NAME:  FIRSTNAME                       BORN:  04/01/1808  AUTO GEN:         N   CC:    PRJE            LAST PAID: 09/09/2009    

      MIDDLE NAME: M.                              SORT GROUP:        TYPE:            NU   VETERAN: N             LAYOFF DT:               

      LAST/SUFFIX: LASTNAME                        LOCATION:          CENTER:           1   BANK:    BP            REHIRE DT:               

      JOB TITLE:   PROJECT ENGINEER                DIVISION:     1    LICENSE: CADL 0XA125EX                       ADJ SERV:                

      FOREIGN ADD: N                               VIETNAM VET: N     EQ STATUS:            EQUIP #:               JOB CAT:                 

      PHONE #:     123/456-7890                    COUNTY CODE:       UTIL EXCL NW:        0.00                    DISABLED VETERAN:   N    

      PROJECT MGR:                                 SECURITY LV: 6     UTIL INCL NW:        0.00                    RETIREMENT:         Y    

      ALLOW ENTRY OF TIME CARDS WHEN EMPLOYEE IS INACTIVE: N                                                                               

      I-9 EXP DATE:                      I-9:         Y     BENEFIT TRACKING TYPE:                       DEFERRED COMP:      Y    

                 UNPSTD RATE REGULAR:       0.00    USE STD LAB J/S: N TERM REASON CODE:                            JOB SORT VALID:          

                            OVERTIME:       0.00    USE STD LAB S/M: N BATCH SORT/MERGE CODE:                       BUSINESS LOC:    0000    

                          DOUBLETIME:       0.00    POSITION:          1 TIME UNION AMOUNT:         0.00            1 TIME UN FINISHED: N                                                                               

      -


      FED------      -


      STATE-----       -


      LOCALITY
                                        -
      MISC
      --


            

      1. EXEMPTIONS:  1       HOME STATE:       US       HOME LOCAL:                                                W/C STATE:         US     

      EXEMPT:        N       WORK STATE:       US       WORK LOCAL:                                                W/C CODE:       4227      

      EX AMT:     0.00       TIMECARD RULE:     E       TIMECARD RULE:          E                                  W/C TC RULE:        E     

      EX PER:     0.00       # EXEMPT:          1       # EXEMPT:               1                                  W/C EXEMPT:         N     

      FILING STATUS:         # PERSON EX:       0       FILING STATUS:                                             G/L EXEMPT:         N     

      FICA EXEMPT:   N       FILING STATUS:             EXEMPT:                 N                                  SUTA STATE:        US     

      FUTA EXEMPT:   N       EXEMPT:            N       EX AMT:              0.00                                  SUTA TC RULE:       E     

      EIC ELIGIBLE:  N       EX EXEMPT:      0.00       EX PER:              0.00                                  SUTA EXEMPT:        N     

      EIC BY SPOUSE: N       EX AMT:         0.00       TAX DIST METH:          1                                  DBL STATE:         US     

                             EX PER:         0.00                                                                  DBL TC RULE:        E     

                             TAX DIST METHOD:   1                                                                  DBL EXEMPT:         N                                                                               

      -


      DIRECT DEPOSIT -


                                            

                                     ACCOUNT#            PRENOTE   BANK               ROUTING                                                                               

      0112233445          N         D1               556677889    CHECKING                                     /font[/quote]The only items I need from the list are:

      Emp No (12345)

      Name (LASTNAME, FIRSTNAME M.)

      And if they have Direct Deposit: (- DIRECT DEPOSIT -)

       

      A 1-line trap using the dashes in the SSN number and grabbing the Employee Number and Name.  Then I'm grabbing a 7-character field lined up with the word DEPOSIT and using the advanced item:

      preceding string: - direct (space at the end)[/b]

       

      This correctly traps a field for those employees that have a Direct Deposit.

       

      This report has 3832 records in it.

       

      Filtering takes a couple of minutes with that advanced field as opposed to practically instantaneously with not having it.

       

      My original filter was only checking for a non-blank item in the Direct Deposit.

       

      Here's the model I'm using if you want to test it yourself.

      [font="courier"]<model version="XMLMOD03"><location>D:MacrosPayrollSuperModels</location><author>nosdalepopa</author><description></description><settings><input><date_pattern>

      M/D/Y</date_pattern><first_year_of_century>50</first_year_of_century><active_patterns>D,DT,T</active_patterns><decimal_char>.</decimal_char><leading_chars>0</leading_chars>

      <ignore_form_feeds>false</ignore_form_feeds><ignore_ctrl_chars>false</ignore_ctrl_chars><input_encoding>ANSI</input_encoding><input_ascii>false</input_ascii>

      <case_sensitive_traps>false</case_sensitive_traps><alt_trap_chars>false</alt_trap_chars><force_break max_page_lines="100"/></input><grid suppress_zero="false"

      null_as="(Null)"><table header_lines="1" data_lines="1"/></grid><comment_position>top</comment_position><views><report><font face="Courier New" size="8"/></report><table>

      <font face="Courier New" size="8"/></table></views><export><dbf_field_naming>false</dbf_field_naming><database><summary names_from_fields="false"/></database><text

      names_row="true" spacing="true" pad_with_zeros="false" character_set="ASCII" eof="false"><delimited character="," quoted="true" names_row_quotes="false"

      format_dates_with_dashes="false"/></text><excel apply_formatting="true" enable_advanced="false"/></export></settings><chart_appearance hatch_patterns="false"><color

      number="1">16744576</color><color number="2">6299776</color><color number="3">16711935</color><color number="4">14680224</color><color number="5">8421440</color><color

      number="6">8388863</color><color number="7">8421504</color><color number="8">16733525</color><color number="9">8388736</color><color number="10">12648641</color><color

      number="11">7566335</color><color number="12">16728192</color><color number="13">32768</color><color number="14">4210816</color><color number="15">255</color><color

      number="16">16760831</color><color number="17">16777215</color><color number="18">14803425</color><color number="19">9934743</color><title><font face="Verdana" size="12"/>

      </title><label><font face="Verdana" size="10"/></label><values><font face="Verdana" size="8"/></values></chart_appearance><external_models><external_model

      expose_functions="true" function_link_type=""><name>Default</name><path>C:Documents and SettingsAll UsersDocumentsMonarchModelsUserDefinedFunctions.xmod</path>

      </external_model></external_models><templates><template><name>Detail</name><level_type>detail</level_type><number>11</number><is_float>false</is_float><is_multicolumn>

      false</is_multicolumn><trap_line>1</trap_line><mask>                                              SS     -  -</mask><sample><line> 00000     NAME, LAST                        

      SS: 555-55-5555    HIRE DATE: 07/27/1999 UNION: SOCALPT         LABOR CATEGORY:  PL</line></sample></template></templates><fields><table_field style="report"><name>

      Employee</name><size>34</size><type>C</type><sequence>2</sequence><display_length>34</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/>

      <input><level>11</level><length>34</length><line>1</line><offset>12</offset></input></table_field><table_field style="report"><name>EmpNum</name><size>9</size><type>C</type>

      <sequence>1</sequence><display_length>9</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>9</length><line>

      1</line><offset>2</offset></input></table_field><table_field style="report"><name>Deposit</name><size>7</size><type>C</type><sequence>3</sequence><display_length>

      7</display_length><alignment>L</alignment><verify left="true" right="true" type="true"/><input><level>11</level><length>7</length><line>1</line><offset>69</offset>

      <line_advanced><start type="preceding" string="- direct "/><stop type="lines" count="1"/></line_advanced></input></table_field></fields><filters><filter type="formula_based">

      <name>NameTest</name><short_name>nametest</short_name><filter_expression>if(substr(employee,3,1)="f","Y","")<>""</filter_expression></filter></filters><audit></audit>

      </model>[/font][/quote]You'll need to take the linebreaks out to make it work correctly. (I hope)

       

      This model also uses a test filter on the employee name for comparison.

       

      Any clues as to lag in filtering?

        • Slow Filter with advanced trapping
          Grant Perkins

          Hi Nick,

           

          I may have missed it - raw html is not the easiest read in the world - but I can't see your 'deposit' filter in the model file code.

           

          Apart from that I will add that I have never, as far as I can recall, noticed an extended delay when filtering with anything less than a lot of records, a lot being, typically, around 100,000 or more. But even then 2 minutes seems a long time. (OK, one or two exceptions when the PC has had a problem with the number and the nature of tasks running but otherwise nothing.)

           

          Interesting.

           

           

          Grant

          • Slow Filter with advanced trapping
            Nick Osdale-Popa

            The model I posted does not have the "Deposit Filter", it has a test filter on Employee Name.

             

            I was trying to determine if the filter lag was due to the filter being on the advanced field or if any filter had the lag. It does occur on any filter.

            • Slow Filter with advanced trapping
              Grant Perkins

              Hi Nick,

               

              Ah, I thought you had intended to include both filters in the model. My mistake.

               

              If you are filtering in the table I can't think of any logical reason why it should always take so much longer. Small variations might be expected but the differences you mention are more than small.

               

              I'll seek some time to find and try something equivalent in terms of number of records and experiment with it.

               

              Grant