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

    Trapping Issue

    Nimbuz _

      I would like to capture home department, employee number, employee name, ssn, deduction code, ytd amount, report date, report time, report title, and report short name. The problem with my model is rather difficult to explain in proper words. Perhaps, the problem lies on setting of append template with fields of employee number, employee name, and ssn and detail template with fields of deduction code and ytd amount. Please advise from scratch if possible. I am using Monarch 4.05 at work and Monarch 8 Pro at home. Here's the sample....

       

      [font="courier"]Date: 03/18/2005                                     COMPANY NAME                                                   Page  : 14         

      Time: 1505                               Employee Summary Deduction Register For 03/18/2005                         Report: HPRSD211   

                                                         Calendar Year: 2005  Month: 12                                                                               

      Home Department: 6045                                                                               

      Employee Nbr.                            Sched        Date      Last                                                                   

      Employee Name              Pay  Ded      Amount     of Last    Amount        MTD         QTD          YTD       Maximum     Arrears    

      SSN                        Ind  Code     Flat/%     Activity    Taken       Amount      Amount       Amount      Amount     Balance    

      -


                                                                                       

      2100002295                  Y    42      100.00 % 07/01/2006  1,918.47    3,854.42   11,800.37    50,034.20        0.00        0.00    

      ABADILLA,EMELYN M                52      300.00   07/01/2006    300.00      600.00    1,600.00     4,400.00        0.00        0.00    

      111-99-8888                      63D       2.00 % 07/01/2006     32.50       65.00      195.00       845.00        0.00        0.00    

                                       71B      16.52   12/31/2005     16.52       33.04       99.12       429.52        0.00        0.00    

                                       93H      36.55   01/28/2006     36.55       73.10      219.30       945.01        0.00        0.00                                                                               

      2100003693                  Y    63F       2.00 % 07/01/2006     32.50       32.50      162.50       812.50        0.00        0.00    

      ALLADO,AGNES                     87A     100.00   07/01/2006    100.00      100.00      500.00     2,500.00        0.00        0.00    

      222-33-4444                      93B      33.00   02/25/2006     33.00       33.00      132.00       132.00        0.00        0.00                                                                               

      2100003248                  Y    42      100.00 % 07/01/2006  1,667.78    3,292.66    9,968.74    36,165.38        0.00        0.00                                                                               

      Date: 03/18/2005                                     COMPANY NAME                                                   Page  : 15         

      Time: 1505                               Employee Summary Deduction Register For 03/18/2005                         Report: HPRSD211   

                                                         Calendar Year: 2005  Month: 12                                                                               

      Home Department: 6045                                                                               

      Employee Nbr.                            Sched        Date      Last                                                                   

      Employee Name              Pay  Ded      Amount     of Last    Amount        MTD         QTD          YTD       Maximum     Arrears    

      SSN                        Ind  Code     Flat/%     Activity    Taken       Amount      Amount       Amount      Amount     Balance    

      -


                                                                                       

      2100003248                  Y    63F       2.00 % 07/01/2006     32.50       65.00      195.00       682.50        0.00        0.00    

      HERNANDEZ,EVA                    72C       5.96   07/01/2006      5.96       11.92       35.76       154.96        0.00        0.00    

      666-88-8855                      87A     100.00   07/01/2006    100.00      200.00      600.00     2,100.00        0.00        0.00    

                                       93B      33.00   12/31/2005     33.00       66.00      198.00       693.00        0.00        0.00                                                                               

      2100004476                  Y    42      100.00 % 06/03/2006    770.43      853.74    1,772.18     5,592.88        0.00        0.00    

      HOLMES,DOROTHY                                                                               

      444-99-5555                                                                               

      2100004337                  Y    44      100.00 % 07/01/2006  2,210.93    4,341.39   12,523.48    55,815.83        0.00        0.00    

      RAMOS,BERNADETTE                 63F       2.00 % 07/01/2006     32.50       65.00      195.00       780.00        0.00        0.00                                                                               

      Date: 03/18/2005                                     COMPANY NAME                                                   Page  : 16         

      Time: 1505                               Employee Summary Deduction Register For 03/18/2005                         Report: HPRSD211   

                                                         Calendar Year: 2005  Month: 12                                                                               

      Home Department: 6045                                                                               

      Employee Nbr.                            Sched        Date      Last                                                                   

      Employee Name              Pay  Ded      Amount     of Last    Amount        MTD         QTD          YTD       Maximum     Arrears    

      SSN                        Ind  Code     Flat/%     Activity    Taken       Amount      Amount       Amount      Amount     Balance    

      -


                                                                                       

      2100004337                  Y    63G      37.50   01/28/2006      0.00        0.00        0.00        75.00        0.00        0.00    

      RAMOS,BERNADETTE                 80        3.94   07/01/2006      3.94        7.88       23.64        94.56        0.00        0.00    

      199-00-7777                      93B      33.00   12/31/2005     33.00       66.00      198.00       759.00        0.00        0.00                                                                               

      2100004372                  Y    42      100.00 % 10/22/2005  1,495.43        0.00    2,990.87    29,054.91        0.00        0.00    

      REYES,KAREN                      93B      33.00   04/23/2005     33.00        0.00        0.00       198.00        0.00        0.00    

      888-44-3333                                                                               

      1100004297                  Y    44      100.00 % 07/01/2006    936.01    1,713.77    2,547.99     2,547.99        0.00        0.00    

       

      /font[/quote]

        • Trapping Issue
          Nimbuz _

          I would like to capture home department, employee number, employee name, ssn, deduction code, ytd amount, report date, report time, report title, and report short name. The problem with my model is rather difficult to explain in proper words. Perhaps, the problem lies on setting of append template with fields of employee number, employee name, and ssn and detail template with fields of deduction code and ytd amount. Please advise from scratch if possible. I am using Monarch 4.05 at work and Monarch 8 Pro at home. Here's the sample....

           

          [font="courier"]Date: 03/18/2005                                     COMPANY NAME                                                   Page  : 14         

          Time: 1505                               Employee Summary Deduction Register For 03/18/2005                         Report: HPRSD211   

                                                             Calendar Year: 2005  Month: 12                                                                               

          Home Department: 6045                                                                               

          Employee Nbr.                            Sched        Date      Last                                                                   

          Employee Name              Pay  Ded      Amount     of Last    Amount        MTD         QTD          YTD       Maximum     Arrears    

          SSN                        Ind  Code     Flat/%     Activity    Taken       Amount      Amount       Amount      Amount     Balance    

          -


                                                                                           

          2100002295                  Y    42      100.00 % 07/01/2006  1,918.47    3,854.42   11,800.37    50,034.20        0.00        0.00    

          ABADILLA,EMELYN M                52      300.00   07/01/2006    300.00      600.00    1,600.00     4,400.00        0.00        0.00    

          111-99-8888                      63D       2.00 % 07/01/2006     32.50       65.00      195.00       845.00        0.00        0.00    

                                           71B      16.52   12/31/2005     16.52       33.04       99.12       429.52        0.00        0.00    

                                           93H      36.55   01/28/2006     36.55       73.10      219.30       945.01        0.00        0.00                                                                               

          2100003693                  Y    63F       2.00 % 07/01/2006     32.50       32.50      162.50       812.50        0.00        0.00    

          ALLADO,AGNES                     87A     100.00   07/01/2006    100.00      100.00      500.00     2,500.00        0.00        0.00    

          222-33-4444                      93B      33.00   02/25/2006     33.00       33.00      132.00       132.00        0.00        0.00                                                                               

          2100003248                  Y    42      100.00 % 07/01/2006  1,667.78    3,292.66    9,968.74    36,165.38        0.00        0.00                                                                               

          Date: 03/18/2005                                     COMPANY NAME                                                   Page  : 15         

          Time: 1505                               Employee Summary Deduction Register For 03/18/2005                         Report: HPRSD211   

                                                             Calendar Year: 2005  Month: 12                                                                               

          Home Department: 6045                                                                               

          Employee Nbr.                            Sched        Date      Last                                                                   

          Employee Name              Pay  Ded      Amount     of Last    Amount        MTD         QTD          YTD       Maximum     Arrears    

          SSN                        Ind  Code     Flat/%     Activity    Taken       Amount      Amount       Amount      Amount     Balance    

          -


                                                                                           

          2100003248                  Y    63F       2.00 % 07/01/2006     32.50       65.00      195.00       682.50        0.00        0.00    

          HERNANDEZ,EVA                    72C       5.96   07/01/2006      5.96       11.92       35.76       154.96        0.00        0.00    

          666-88-8855                      87A     100.00   07/01/2006    100.00      200.00      600.00     2,100.00        0.00        0.00    

                                           93B      33.00   12/31/2005     33.00       66.00      198.00       693.00        0.00        0.00                                                                               

          2100004476                  Y    42      100.00 % 06/03/2006    770.43      853.74    1,772.18     5,592.88        0.00        0.00    

          HOLMES,DOROTHY                                                                               

          444-99-5555                                                                               

          2100004337                  Y    44      100.00 % 07/01/2006  2,210.93    4,341.39   12,523.48    55,815.83        0.00        0.00    

          RAMOS,BERNADETTE                 63F       2.00 % 07/01/2006     32.50       65.00      195.00       780.00        0.00        0.00                                                                               

          Date: 03/18/2005                                     COMPANY NAME                                                   Page  : 16         

          Time: 1505                               Employee Summary Deduction Register For 03/18/2005                         Report: HPRSD211   

                                                             Calendar Year: 2005  Month: 12                                                                               

          Home Department: 6045                                                                               

          Employee Nbr.                            Sched        Date      Last                                                                   

          Employee Name              Pay  Ded      Amount     of Last    Amount        MTD         QTD          YTD       Maximum     Arrears    

          SSN                        Ind  Code     Flat/%     Activity    Taken       Amount      Amount       Amount      Amount     Balance    

          -


                                                                                           

          2100004337                  Y    63G      37.50   01/28/2006      0.00        0.00        0.00        75.00        0.00        0.00    

          RAMOS,BERNADETTE                 80        3.94   07/01/2006      3.94        7.88       23.64        94.56        0.00        0.00    

          199-00-7777                      93B      33.00   12/31/2005     33.00       66.00      198.00       759.00        0.00        0.00                                                                               

          2100004372                  Y    42      100.00 % 10/22/2005  1,495.43        0.00    2,990.87    29,054.91        0.00        0.00    

          REYES,KAREN                      93B      33.00   04/23/2005     33.00        0.00        0.00       198.00        0.00        0.00    

          888-44-3333                                                                               

          1100004297                  Y    44      100.00 % 07/01/2006    936.01    1,713.77    2,547.99     2,547.99        0.00        0.00    

           

          /font[/quote]

          • Trapping Issue
            Grant Perkins

            Hi Nimbuz,

             

            Is your problem related to the way the report repeats one or two lines of what you want to capture in the append template if the employee record goes over a page break?

             

            Looking at the report the detail you want should be quite simple to extract but the employee number append would be very likely to cause problems because of the repeat lines and the fact that they are not ALL available both sides of the page break.

             

            Does it matter to you if the suggested solution would only work in V8? I ask this because there are a lot of differences between V4 and V8 and the models are not backwards compatible as I am sure you are aware already. (Also it is some time since I used V4 so I don't really remember what is not in it and I don't have a system with V4 installed at the moment.)

             

            I think the solution might be something like ensuring that you have set up a Page Header template that covers all of the rows related to the page break and the establish an append template with a 4 row sample (maybe more if your shortest employee record will always have more than 4 lines) and select the append details in a single multi-row field. Then split it up afterwards.

             

            However I have not yet tried that with your sample data so I am guessing a little.

             

            If you let us know the answers to the questions I will be happy to experiment to see what can be achieved.

            • Trapping Issue
              Grant Perkins

              Hi Nimbuz,

               

              Is your problem related to the way the report repeats one or two lines of what you want to capture in the append template if the employee record goes over a page break?

               

              Looking at the report the detail you want should be quite simple to extract but the employee number append would be very likely to cause problems because of the repeat lines and the fact that they are not ALL available both sides of the page break.

               

              Does it matter to you if the suggested solution would only work in V8? I ask this because there are a lot of differences between V4 and V8 and the models are not backwards compatible as I am sure you are aware already. (Also it is some time since I used V4 so I don't really remember what is not in it and I don't have a system with V4 installed at the moment.)

               

              I think the solution might be something like ensuring that you have set up a Page Header template that covers all of the rows related to the page break and the establish an append template with a 4 row sample (maybe more if your shortest employee record will always have more than 4 lines) and select the append details in a single multi-row field. Then split it up afterwards.

               

              However I have not yet tried that with your sample data so I am guessing a little.

               

              If you let us know the answers to the questions I will be happy to experiment to see what can be achieved.

              • Trapping Issue
                Nimbuz _

                Hi Grant,

                 

                Yes, the problem occurs when an empolyee record goes over a page break. V8 would be fine for me.

                • Trapping Issue
                  Nimbuz _

                  Hi Grant,

                   

                  Yes, the problem occurs when an empolyee record goes over a page break. V8 would be fine for me.

                  • Trapping Issue
                    Grant Perkins

                    If you have V8 PRO one approach to consider would be to run a model the extract the employee info fields only (number, name, SSN), summarise it to single occurences of the Emp Number and then export that for use as a lookup file.

                     

                    Then use your existing detail model, just the employee number as the append and then lookup name and SSN from the external lookup.

                     

                    A 2 stage process but should be quite simple to create and automate.

                     

                    You will need the PRO version though.

                     

                    I am working on an alternative strategy that extracts 2 multi-line fields - one for the Emp number,Name, SSN lines and the other for however many lines are required for the deduction details. The idea is to then slice and dice the fields to produce the information in the required format.

                     

                    Which leads me to my next question.

                     

                    What is your required output format at the end of the process?

                     

                    As things look at the moment I think the external lookup approach offers the greatest flexibility but your formatting requirements may offer some scope for some interesting experiments with extractions from multi-line fields.

                     

                     

                    Grant

                    • Trapping Issue
                      Grant Perkins

                      If you have V8 PRO one approach to consider would be to run a model the extract the employee info fields only (number, name, SSN), summarise it to single occurences of the Emp Number and then export that for use as a lookup file.

                       

                      Then use your existing detail model, just the employee number as the append and then lookup name and SSN from the external lookup.

                       

                      A 2 stage process but should be quite simple to create and automate.

                       

                      You will need the PRO version though.

                       

                      I am working on an alternative strategy that extracts 2 multi-line fields - one for the Emp number,Name, SSN lines and the other for however many lines are required for the deduction details. The idea is to then slice and dice the fields to produce the information in the required format.

                       

                      Which leads me to my next question.

                       

                      What is your required output format at the end of the process?

                       

                      As things look at the moment I think the external lookup approach offers the greatest flexibility but your formatting requirements may offer some scope for some interesting experiments with extractions from multi-line fields.

                       

                       

                      Grant

                      • Trapping Issue
                        Nimbuz _

                        As suggested, I will experiment the lookup functionality in V8 Pro. Perhaps, a walkthough of lookup functionality will be helpful using V8 Pro. However, I can extract detail data table and lookup summary data table using V4 and import to MS Access to achieved the desired results. I was hoping for a one step process using V4 or V8 Pro. The output format for the end process is MS Access with the fields listed in the first post. In MS Access, I will generate a comparison query to another data table. I am curiously looking forward to the alternative strategy on the multi-line fields. Thanks for your great help!

                        • Trapping Issue
                          Nimbuz _

                          As suggested, I will experiment the lookup functionality in V8 Pro. Perhaps, a walkthough of lookup functionality will be helpful using V8 Pro. However, I can extract detail data table and lookup summary data table using V4 and import to MS Access to achieved the desired results. I was hoping for a one step process using V4 or V8 Pro. The output format for the end process is MS Access with the fields listed in the first post. In MS Access, I will generate a comparison query to another data table. I am curiously looking forward to the alternative strategy on the multi-line fields. Thanks for your great help!

                          • Trapping Issue
                            Grant Perkins

                            If you were to create a script which firstly extracted the data for the lookup and saved it and then ran with the second model for the detail and the lookup from the first output, that would be close to a single step process as far as executing the process and getting the output is concerned. Two V8 projects run consecutively by the script would work well I think.

                             

                            For the output the question is really what do you need as records in MS Access?

                             

                            Is each Access record to be a detail record of Deduction Code and amount with the Employee number, name and SSN added to it?

                             

                            Or is the record to be Employee centric with a list of deduction codes and amount attached?

                             

                            If the second option would you like to see the resulting table having columns for Code 1, Code 1 Amount, Code 2, Code 2 amount, etc.?

                             

                            Or would you like to see a permanent set of columns representing all possible deduction codes with only the relevant columns populated for each employee? (How many possible codes are there across all employees?)

                             

                            Or perhaps something else?

                             

                             

                            Grant

                             

                            Edit to add ....

                             

                            The Internal Help in Monarch 8 for the External Lookup feature and its associated Wizard is a really good introduction to the concept and execution. Very similar to the type of functionality  you would find in Access or Excel but a little simpler to execute I think.

                             

                            It is probably much easier to read through that than to try to follow someone elses walkthrough but if you get stuck let us know and we can look at the specific steps for your purpose.

                             

                            The key thing you need to do is eliminate any partial records for the Emp Number, Name and SSN extraction. The easiet way I can think of would be to filter to include only records where all three fields are populated, then the lookup link will be on the Employee number.

                             

                            Add the employee number to the detail records as an append template field and you will be all set for the lookup activity.

                            • Trapping Issue
                              Grant Perkins

                              If you were to create a script which firstly extracted the data for the lookup and saved it and then ran with the second model for the detail and the lookup from the first output, that would be close to a single step process as far as executing the process and getting the output is concerned. Two V8 projects run consecutively by the script would work well I think.

                               

                              For the output the question is really what do you need as records in MS Access?

                               

                              Is each Access record to be a detail record of Deduction Code and amount with the Employee number, name and SSN added to it?

                               

                              Or is the record to be Employee centric with a list of deduction codes and amount attached?

                               

                              If the second option would you like to see the resulting table having columns for Code 1, Code 1 Amount, Code 2, Code 2 amount, etc.?

                               

                              Or would you like to see a permanent set of columns representing all possible deduction codes with only the relevant columns populated for each employee? (How many possible codes are there across all employees?)

                               

                              Or perhaps something else?

                               

                               

                              Grant

                               

                              Edit to add ....

                               

                              The Internal Help in Monarch 8 for the External Lookup feature and its associated Wizard is a really good introduction to the concept and execution. Very similar to the type of functionality  you would find in Access or Excel but a little simpler to execute I think.

                               

                              It is probably much easier to read through that than to try to follow someone elses walkthrough but if you get stuck let us know and we can look at the specific steps for your purpose.

                               

                              The key thing you need to do is eliminate any partial records for the Emp Number, Name and SSN extraction. The easiet way I can think of would be to filter to include only records where all three fields are populated, then the lookup link will be on the Employee number.

                               

                              Add the employee number to the detail records as an append template field and you will be all set for the lookup activity.

                              • Trapping Issue
                                Nimbuz _

                                Each Access record will have the detail record of home department, employee number, employee name, ssn, deduction code, ytd amount, report date, report time, report title, and report short name. That is, each Access record will have ten fields.

                                 

                                How will the above information help the alternative strategy of 2 multi-line fields?

                                • Trapping Issue
                                  Nimbuz _

                                  Each Access record will have the detail record of home department, employee number, employee name, ssn, deduction code, ytd amount, report date, report time, report title, and report short name. That is, each Access record will have ten fields.

                                   

                                  How will the above information help the alternative strategy of 2 multi-line fields?

                                  • Trapping Issue
                                    Grant Perkins

                                    Originally posted by Nimbuz:

                                    How will the above information help the alternative strategy of 2 multi-line fields? /b[/quote]OK.

                                     

                                    So you just need to extract the records line by line and add the employee details. (Rather than create a single record for each employee which would contain a list of deductions and the amounts.)

                                     

                                    In effect you need to move the data from one system to another and I assume that you are using the report rather than direct access to the originating system's data files because you don't have direct access available.

                                     

                                    I can see a couple of ways of using Monarch to present the data differently and produce a new format for the output report which could then be analysed in a single step but since to do so makes it a two step process anyway (at least) and you have a version of Monarch with External Lookup capability it seems sensible to use that approach. If for no other reason that it would be the quickest approach and the most flexible as far as I can tell. (If you have to make this work in version 4 the external lookup approach is not, as far as I can recall, available to you.)

                                     

                                    So, in the first phase, build a model that only extracts the Employee information based on the Number, Name and SSN.

                                     

                                    Trap on the Number. Paint a single field wide enough to cover the longest name possible. Set the advanced properties to 'End On' "none of the above" or maybe one of the other settings.

                                    Mask out the Page Header area by creating a page header template to prevent an spurious characters being picked up there.

                                     

                                    Go to the table (a single field) and split each record into its 3 possible components.

                                     

                                    Since you cannopt be sure what each record consist of - Number only, Number and Name only or all 3 pieces of information  - you need to test the options as part of this process.

                                     

                                    Number will always exist so use the LSPLIT function to create a field for Employee Number;

                                     

                                    lsplit(,2," ",1)

                                     

                                     

                                    SSN may not always exist so we need to test the string to see it looks like if contains an SSN and then use RSPLIT to extract it.

                                     

                                    The following 'rules' seem to apply.

                                     

                                    If the last character in the string is numeric we may have an SSN or we may have an Employee number only. If the String contains any alpha characters (based on the sample posted) there must be more than just the Employee Number in it. So if there are alpha AND the end of the string is numeric then the end section must be the SSN.

                                     

                                    There are several possible tests for the logic here. I have assumed that you would not have any genuine complete records without a name and SSN but, if you have, you may want to look at this and use slightly different tests.

                                     

                                    In that way you can 'slice and dice' the field to get the 3 separate data fields you require ... however, with Version 8 there is a much easier way.

                                     

                                    Create 3 calculated fields using the TextLine function.

                                     

                                    TextLine(,1) will give you the Employee Number.

                                     

                                    TextLine(,2) will give you the Name if it exists.

                                     

                                    TextLine(,3) will give you the SSN if it exists.

                                     

                                    Now go to your table and define a filter to select only the records with all 3 fields populated.

                                     

                                    Sort the result by Employee number if required and export it to a csv file or perhaps Excel or directly to an Access table.

                                     

                                    Which ever you choose you will be able to perform the lookup in Monarch if you want to.

                                     

                                    Now create a second model to extract the deduction lines (Deduction Code and Amount). Create an Append to add the Employee Number (as a CHARACTER FIELD I would suggest) to each record and a Page Header Append to grab the report date, etc., information you need. Sort the table into Employee Number order if required.

                                     

                                    To make the lookup within Monarch use the Wizard to create a link to the previously exported Employee details and add the fields for Name and SSN to the table.

                                     

                                    That's it.

                                     

                                    If you decide to export both extractions to Access before combining you may need to take some care over what Access (or Excel) does whan interpreting the field type for the employee number. Whilst I would prefer to keep such fields as Character fields Access/Excel may, by default, assume they are numeric. Links in Monarch need to have consistent field types.

                                     

                                    If you use Monarch for both parts of the process you should be able to come up with a simple command script that will run both processes consecutively so that the whole thing is produced by clicking on a shortcut icon.

                                     

                                    HTH.

                                     

                                    I will be happy to answer any questions or clarify any of the suggestions I have made if they are unclear.

                                     

                                    Grant

                                    • Trapping Issue
                                      Grant Perkins

                                      Originally posted by Nimbuz:

                                      How will the above information help the alternative strategy of 2 multi-line fields? /b[/quote]OK.

                                       

                                      So you just need to extract the records line by line and add the employee details. (Rather than create a single record for each employee which would contain a list of deductions and the amounts.)

                                       

                                      In effect you need to move the data from one system to another and I assume that you are using the report rather than direct access to the originating system's data files because you don't have direct access available.

                                       

                                      I can see a couple of ways of using Monarch to present the data differently and produce a new format for the output report which could then be analysed in a single step but since to do so makes it a two step process anyway (at least) and you have a version of Monarch with External Lookup capability it seems sensible to use that approach. If for no other reason that it would be the quickest approach and the most flexible as far as I can tell. (If you have to make this work in version 4 the external lookup approach is not, as far as I can recall, available to you.)

                                       

                                      So, in the first phase, build a model that only extracts the Employee information based on the Number, Name and SSN.

                                       

                                      Trap on the Number. Paint a single field wide enough to cover the longest name possible. Set the advanced properties to 'End On' "none of the above" or maybe one of the other settings.

                                      Mask out the Page Header area by creating a page header template to prevent an spurious characters being picked up there.

                                       

                                      Go to the table (a single field) and split each record into its 3 possible components.

                                       

                                      Since you cannopt be sure what each record consist of - Number only, Number and Name only or all 3 pieces of information  - you need to test the options as part of this process.

                                       

                                      Number will always exist so use the LSPLIT function to create a field for Employee Number;

                                       

                                      lsplit(,2," ",1)

                                       

                                       

                                      SSN may not always exist so we need to test the string to see it looks like if contains an SSN and then use RSPLIT to extract it.

                                       

                                      The following 'rules' seem to apply.

                                       

                                      If the last character in the string is numeric we may have an SSN or we may have an Employee number only. If the String contains any alpha characters (based on the sample posted) there must be more than just the Employee Number in it. So if there are alpha AND the end of the string is numeric then the end section must be the SSN.

                                       

                                      There are several possible tests for the logic here. I have assumed that you would not have any genuine complete records without a name and SSN but, if you have, you may want to look at this and use slightly different tests.

                                       

                                      In that way you can 'slice and dice' the field to get the 3 separate data fields you require ... however, with Version 8 there is a much easier way.

                                       

                                      Create 3 calculated fields using the TextLine function.

                                       

                                      TextLine(,1) will give you the Employee Number.

                                       

                                      TextLine(,2) will give you the Name if it exists.

                                       

                                      TextLine(,3) will give you the SSN if it exists.

                                       

                                      Now go to your table and define a filter to select only the records with all 3 fields populated.

                                       

                                      Sort the result by Employee number if required and export it to a csv file or perhaps Excel or directly to an Access table.

                                       

                                      Which ever you choose you will be able to perform the lookup in Monarch if you want to.

                                       

                                      Now create a second model to extract the deduction lines (Deduction Code and Amount). Create an Append to add the Employee Number (as a CHARACTER FIELD I would suggest) to each record and a Page Header Append to grab the report date, etc., information you need. Sort the table into Employee Number order if required.

                                       

                                      To make the lookup within Monarch use the Wizard to create a link to the previously exported Employee details and add the fields for Name and SSN to the table.

                                       

                                      That's it.

                                       

                                      If you decide to export both extractions to Access before combining you may need to take some care over what Access (or Excel) does whan interpreting the field type for the employee number. Whilst I would prefer to keep such fields as Character fields Access/Excel may, by default, assume they are numeric. Links in Monarch need to have consistent field types.

                                       

                                      If you use Monarch for both parts of the process you should be able to come up with a simple command script that will run both processes consecutively so that the whole thing is produced by clicking on a shortcut icon.

                                       

                                      HTH.

                                       

                                      I will be happy to answer any questions or clarify any of the suggestions I have made if they are unclear.

                                       

                                      Grant