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

    Multi-Line and Multi-Column Detail Approach

    JoeSalvatore _

      I am working with a report that essentially produces a fully alligned pay stub. As such, it is certainly not columnar and I am struggling with an approach to using detail, append and footers to meet my needs.

       

      I'd appreciate any getting started approaches.

      Please be patient with me as this is my first time using Monarch since about 1994.

       

      Additionally, the report has DivisionDepartment summary pages that truly need to be placed into a separate table but exhibit the same complex use of non-columnar structure. I would assume that I would create separate templates for each report detail to be extracted, but could someone confirm this?

       

      Here is a sample of the employee pay report section for reference:

      [font="courier"]                                                         PAYROLL REGISTER WITH DIV/DPT TOTALS      084                                                                               

      PAYROLL CHECKS                                                           

              CSC-1     TEST PAYROLL                                                            04/03/08      B-04/03/08              07        7                                                                               

      EMPLOYEE ID                             ....... HOURS AND EARNINGS ....... .... DED AND ADJ ....  .......GROSS TO NET ......        

              EMPLOYEE NAME                           C  DESCRIPTION   HOURS    AMOUNT   DESCR   AMOUNT  CODES   Y-TO-D   TYPE   CURRENT    CHECK#                                                                               

      1-11-543210                                                                               

      JONES, IVE-BEEN                                                                               

              • CONTINUED *****                                                                               

      E-CD   K                                                                               

      WS  CA SITE*  10                                                                               

      DIST   10101055110                                                                               

      -


       

              1-11-023456                             1  REGULAR                 9500.00 401K %      6.00-7G0    57583.30 GRCOMP   9500.00        

              LUSTER, LACK                               401K                     570.00-          570.00-   E   55838.30 GRPAY    8930.00        

              237 CUERVO DRIVE                        -


      Y-TO-D -


      Y-TO-D -


          3570.16 SSEC      589.00        

              MESA AZ 65231                           1  REGULAR                57000.00 401K     1745.00-         834.96 MDCR      137.75        

                                                      4C 4CSICK          6.14     583.30                          1253.13 ST2 CA    196.60        

              SS-NO 324-35-6754  FRQ   B    STAT  A                                                                719.79 LC1 SC    118.75        

              BASE   9500.00     M/S   S    SEX   M                                                                345.50 DI  CA     57.00        

              D-RTE  27.0000     ST1   AZ   LOC1  SC                                                             49114.76 NET      7830.90   13204

              HIRE   08/08/96    ST2   CA                                                                     WAGES  Y-TO-D  TAXES        

              PTO-C  11          F#EX  02   S#EX  00                                                             57583.30 SSEC     3570.16        

              RETR   Y           FADJ  %    .0000                                                                57583.30 MDCR      834.96        

              E-CD   1           SADJ  S    65.0000                                                              55838.30 FIT                     

              WS  CA SITE*  10                                                                                36294.90 ST  AZ                  

              DIST   10101975111                                                                                19543.40 ST  CA   1253.13        

              MISC   8810                                                                                57583.30 SUI CA    345.50 DI                                                                               

      57583.30 LOC SC    719.79        

              -


       

              1-11-234567                                                                TRAVL     125.00 010         .00 GRCOMP       .00        

              MCBRIDE, ROLAND                                                            MATCH%      1.00 8N0         .00 NET          .00        

              100 MAIN STREET                                                            401K %      4.00-8G0                                     

              MARION IL 62959                                                            EIC              009                                                                               

      SS-NO 234-56-4321E FRQ   B    STAT  I                                                                               

      BASE   2000.00     M/S   M    SEX   M                                                                               

      HIRE   08/08/96    ST1   IL                                                                               

      PTO-C              F#EX  04   S#EX  04                                                                               

      E-CD   K                                                                               

      DIST   10501305130                                                                               

      -


       

              1-11-741741                             1  REGULAR                 4000.00 401K %      8.00-710    24167.60 GRCOMP   3974.00        

              NORMILE, BRUCE                             DCARE                      6.00-          320.00-   E   23187.95 GRPAY    3654.00        

              28 NORTH ST                                401K                     320.00-DENT       20.00-0X0E    1498.39 SSEC      246.39        

              EDINA MO 63537                             DENT                      20.00-SHOES      12.00 000*     350.43 MDCR       57.62        

                                                      -


      Y-TO-D -


      DCARE       6.00-070E    3319.11 FIT       500.52        

              SS-NO 123-45-6789  FRQ   B    STAT  A   1  REGULAR                24000.00 BOND3      25.00 000*    1019.00 ST1 MO    157.00        

              BASE   4000.00     M/S   M    SEX   M   4C 4CSICK          6.14     245.60 EFTA5      25.00          111.00 TOTDED     37.00        

              HIRE   10/01/95    ST1   MO                                                EFTB6                   16890.02 NET      2655.47   13203

              PTO-C  111         F#EX  02   S#EX  02                                     -


      BAL/LMT -


                  EFT A        .00        

              CK-SQ  4735                                                                BOND       25.00 BAL             EFT B        .00        

              RETR   Y                                                                   -


      Y-TO-D -


      WAGES--  Y-TO-D  TAXES        

              E-CD   1                                                                   401K      979.65-       24167.60 SSEC     1498.39        

              DIST   10711005130                                                         DENT       60.00-       24167.60 MDCR      350.43        

              MISC   4596                                                                SHOES      36.00        23187.95 FIT      3319.11                                                                               

      DCARE      18.00-       23187.95 ST  MO   1019.00        

              EFT-ACCOUNT#      ID BK TP  TRANSIT-NO                                     BOND       75.00        24227.60 SUI MO                  

              123456798          A 02 C                                                                               

      /font[/quote]Here is the divisiondepartment summary section for reference:

      [font="courier"]                                                         PAYROLL REGISTER WITH DIV/DPT TOTALS      084                                                                               

      PAYROLL CHECKS                                                           

              CSC-1     TEST PAYROLL                                                            04/03/08      B-04/03/08              07       11                                                                               

      939 CK GRP - R          - REGULAR                                                                               

      967 DV/DPT - 111        -                                                                               

              HOURS/EARNS       PC   DESCRIPTION             HOURS          EARNINGS        PC   DESCRIPTION             HOURS          EARNINGS  

                 CURRENT        1    REGULAR                 83.96         22,698.00             DCARE                                      6.00- 

                                     EXLAP                                  1,000.00             ZUEXC                                  8,750.00                                                                               

      TOTAL                   83.96         32,442.00                                                                               

      PRE-TAX ITEMS                                                                               

      CURRENT             401K                                   1,010.00-            DENT                                      20.00- 

                                     DNNAT                                     42.20-            MDFLX                                    166.67- 

                                     MDSTD                                     66.10-            TOTAL                                  1,304.97- 

               

              HOURS/EARNS       PC   DESCRIPTION             HOURS          EARNINGS        PC   DESCRIPTION             HOURS          EARNINGS  

                 Y-TO-D         1    REGULAR                503.76        136,188.00        4C   SICK                    33.80            994.56                                                                               

      TOTAL                  537.56        137,182.56  

               

              DEDUCTION TO NET                                                                               

      CURRENT        BOND3            25.00    GARN            187.50    PSLN             62.24    SHOES            12.00                                                                               

      TOTAL           286.74              

                 YTD            BOND             75.00    GARN            562.50    PSLN            186.72    SHOES            36.00                                                                               

      TOTAL           860.22              

              EARNINGS FROM DEDUCTIONS                                                                               

      YTD            DCARE            18.00-   EXLAP         3,000.00    ZUEXC        26,250.00                                        

              PRE-TAX ITEMS                                                                               

      YTD            401K          3,092.02-   DENT             60.00-   DNNAT           126.60-   MDFLX           500.01-             

                                MDSTD           198.30-                                                       TOTAL         3,976.93-             

               

              GROSS TO NET           WAGES        SOCSEC       FEDERAL        STATE          LOCAL         SDI/UC         DEDUCTION          NET  

                                                  MEDICR                                                                               

      CUR     GRPAY      31137.03       1993.13       1763.60       919.52 TOT     118.75 TOT      73.50 TOT        286.74     25515.68  

                       GRCOMP     32147.03        466.11                      47.26 AL      118.75 SC       73.50 CA                                                                               

      271.30 CA                                                                               

      284.00 MO                                                                               

      316.96 PR                                                            

               QTD     GRPAY      31137.03       1993.13       1763.60       919.52 TOT     118.75 TOT      73.50 TOT        286.74     25515.68  

                       GRCOMP     32147.03        466.11                      47.26 AL      118.75 SC       73.50 CA                                                                               

      271.30 CA                                                                               

      284.00 MO                                                                               

      316.96 PR                                                            

               YTD     GRPAY     162437.63      10262.84      11019.81      5701.31 TOT     719.79 TOT     472.05 TOT        860.22    131001.43  

                       GRCOMP    165529.65       2400.18                     283.56 AL      719.79 SC      445.05 CA                                                                               

      1730.99 CA                      27.00 PR                                                                               

      1785.00 MO                                                                               

      1901.76 PR                                                            

               

              WAGE AND TAXES          QTD WAGES     QTD TAXES     YTD WAGES     YTD TAXES     FX1 WAGES     FX1 TAXES     FX2 WAGES     FX2 TAXES 

              EE SSEC TO LMT                                      165529.65                                                                               

      MDCR TO LMT                                      165529.65                                                                               

      FUTA TO LMT                                          63000.00                                                                               

      SIT                                                                               

      AL ALABAMA                                            7492.50                                                                               

      AZ ARIZONA                                           36294.90                                                                               

      CA CALIFORNIA                                        35768.13                                                                               

      EX EXEMPT                                            28425.09                                                                       

      /font[/quote]

        • Multi-Line and Multi-Column Detail Approach
          Grant Perkins

          Originally posted by JoeSalvatore:

          I am working with a report that essentially produces a fully alligned pay stub. As such, it is certainly not columnar and I am struggling with an approach to using detail, append and footers to meet my needs. /b[/quote]Hi Joe and welcome to the forum.

           

          Certainly not the nicest of reports to be tasked with for any Monarch user. Time lapse is no barrier to the challenge this one presents!

           

          You obviously have variable record content so for much o fthe detail in the report - anything that does not appear in every record - you probably need to be looking at some of the more 'advanced' Monarch features like identifying fields by preceeding strings and some of the interesting tricks for making Appends work where you might not expect them to give what you want.

           

          The summaries are another matter. Probably a separate model as you have identifed BUT could you also create the table you need from the report detail and a Monarch summary?

           

          As a starting point to assess how complex this might become, do you need ALL the field in each record or are some of them of no interest at all?

           

          While you are considering that I will (later today hopefully) check through the previous forum entries for similar challenges or, failing finding anything that explains things clearly (often the resolution has been easier to explain sending a sample model rather than writing a description), I'll try to document some suggestions. Knowing what is or is not required would save me covering irrelevant or missing relevant parts of the report.

           

          More later from me, though others may jump in before that with their own useful suggestions.

           

          Cheers,

           

          Grant

          • Multi-Line and Multi-Column Detail Approach
            JoeSalvatore _

            The venerable Grant! Cheers!

             

            I am most interested in your thoughts, especially the following:

            Identifying fields by preceeding strings

            Tricks for making Appends work where you might not expect them

             

            Please note that I certainly don't expect a fully developed solution, but rather am interested in approaches to begin to tackle starting the design of a complex data extraction tamplate for this scenario.

             

            I'll take a stab at desired fields (or at least what we can focus on for discussion and samples):

             

            [font="courier"]

            Grouping (Header/Appends?)

            Column                Sample Value

            ReportName            PAYROLL REGISTER WITH DIV/DPT TOTALS

            ReportNumber          084

            ReportDate            04/03/08

             

            Detail

            Column                Sample Value

            EmployeeId            1-11-023456

            EmployeeName          LUSTER, LACK

            EmployeeAddress       237 CUERVO DRIVE

            EmployeeCity          MESA

            EmployeeStateCode     AZ

            EmployeePostalCode    65231

            SocialSecurityNumber  324-35-6754

            BasePay               9500.00

            DailyPayRate          27.0000

            HireDate              08/08/96

            PTO-C                 111        

            CK-SQ                 4735

            RETR                  Y

            E-CD                  1

            WS  CA SITE*          10

            DIST                  10711005130

            MISC                  4596

            FRQ                   B

            STAT                  A

            M/S                   S   

            SEX                   M

            ST1                   AZ  

            LOC1                  SC

            ST2                   CA

            F#EX                  02  

            S#EX                  00

            FADJ                  %    .0000

            SADJ                  S    65.0000

            GrossCompToNetYTD     57583.30 (from 57583.30 GRCOMP   9500.00)

            GrossCompToNetCur     9500.00  (from 57583.30 GRCOMP   9500.00)

            GrossPayToNetYTD      55838.30 (from 55838.30 GRPAY    8930.00)

            GrossPayToNetCur      8930.00  (from 55838.30 GRPAY    8930.00)

            HoursEarningsCurCode  1       (from 1  REGULAR                 9500.00)

            HoursEarningsCurDesc  REGULAR (from 1  REGULAR                 9500.00)

            HoursEarningsCurHrs   (from 1  REGULAR                 9500.00)

            HoursEarningsCurAmt   9500.00 (from 1  REGULAR                 9500.00)

            HoursEarningsYTDCode  4C      (from 4C 4CSICK          6.14     583.30)

            HoursEarningsYTDDesc  4CSICK  (from 4C 4CSICK          6.14     583.30)

            HoursEarningsYTDHrs   6.14    (from 4C 4CSICK          6.14     583.30)

            HoursEarningsYTDAmt   583.30  (from 4C 4CSICK          6.14     583.30) /font[/quote]

            • Multi-Line and Multi-Column Detail Approach
              Grant Perkins

              Hi Joe,

               

              Great information in your last post that really helped home in on the requirements. Thanks.

               

              Here are my thoughts. It's a long post, you have a lot of interesting challenges and I thought I would try and produce something that is perhaps generic enough to be of use to you and others who come across it in the future. For that reason it

              may well seem verbose but this sort of solution pack covers a number of posts that crop up quite frequently giving an opportunity to collect several ideas and concepts into one place. Also highlight the potential for V9 compared to earlier versions. My apologies for broadening things a little but I hope the detail makes it an easy walk through - please let me know if it fails in that regard. It is late here and the words as types may not be the same as intended!

               

              Here we go:

               

               

              Although this does not look like a regular columnar report it does have a form of columnar structure, at least as far as the sample records are concerned. As you develop the model you need to keep checking that what you are working with is representative AND covers the MAX field number/row numbers and so on. The usual stuff.

               

              Let's get comfortable and get some of the easy stuff out of the way by way of warm up. You have probably already figured this bit.

               

              Grouping (Header/Appends?)

              Column                Sample Value

              ReportName            PAYROLL REGISTER WITH DIV/DPT TOTALS

              ReportNumber          084

              ReportDate            04/03/08

               

              A Page Header template will do nicely here. Make it as many lines as always appear on a page heading, including any blank lines. The page header lines will then become 'invisible' to the other templates.

               

               

              DetailColumn                Sample Value

              EmployeeId            1-11-023456

              EmployeeName          LUSTER, LACK

              EmployeeAddress       237 CUERVO DRIVE

              EmployeeCity          MESA

              EmployeeStateCode     AZ

              EmployeePostalCode    65231

              SocialSecurityNumber  324-35-6754

              BasePay               9500.00

              FRQ                   B

              STAT                  A

              M/S                   S   

              SEX                   M

               

              Can, as far as I can tell from the samples, be derived directly from the Employee record in one template. Positioning seems to be consistent PROVIDING the lines do not shift up and down. Different numbers of lines in Address records for example. If you already know from other records that they cannot, don't worry yet because shortly I will provide some ideas for working with fields that may appear on different lines in a record.

               

              HOWEVER - you do need to know how many lines are in the SMALLEST record likely to be reported. In the samples it would be ROLAND MCBRIDE but there may be others even shorter. The number of lines in your Template SAMPLE is very important since if you have too many a short record will mean the template overlaps what should be the trap for the next record and that record will not be selected.

               

              On the other hand, for reasons I will outline in a moment, making the numbers of lines in the sample as BIG as possible may have offer major benefits, especially for users of older versions of Monarch who have fewer templates available to them.

               

               

              GrossCompToNetYTD     57583.30 (from 57583.30 GRCOMP   9500.00)

              GrossCompToNetCur     9500.00  (from 57583.30 GRCOMP   9500.00)

               

              As far as I can tell both of these fields always appear on the first line of a record and nothing else ever appears there so they can be mapped as well. On the other hand the rest of the fields in that 'column' of the report cannot be directly

               

              mapped so easily, especially the YTD values. (Current values can be using methods in the following sections.) Therefore one might decide NOT to include the two fields above in the detail but include them in a separate Append Template where all the values in that section can be gathered. And yes I did write APPEND template and it was not an error.

               

              If you set a trap for the Detail template based on the Employee ID  perhaps "N-  -" and then perhaps one of the decimal points firther across the line that should work fine. A 7 line sample looks safe. If the smallest record has more than 7 lines use as many lines as you can - especially if you are using older versions of Monarch where fewer templates are available and/or you have a lot of possible fields to report even if they are not always presented in each record. You will see why shortly.

               

              The rest of the fields should fit into two more templates. Or maybe one if you are prepared to 'fill' the detail template!

               

              Your choice based on how you like to structure things. If you change your mind it is not big deal especially since V9 and the ability to Import Objects from another model makes re-organisation so much easier when required.

               

              So, once you have your detail template working, add an Append Template. Sample the report for the same number of lines as the max number you can use for the smallest record ever to be reported. The actual sample data does not matter at all unless you are using the official Floating Traps - in which case you are likely to have issues that this technique will not address anyway!

               

              Give the Append template the exact same trap as the detail template. Or, to be more specific, a trap that will do the same job and does NOT extend further across the trap line (or into lower lines of the sample area) than the DETAIL template trap.

               

              Pick one of the fields, lets say

               

              DailyPayRate          27.0000

               

              which only exists n one of the records. The value we want (27.0000) is prefixed on the line by the tag D-RTE.

               

              So, ignoring whatever is in the sample area (or changing it to suit your current definition work between each field if it helps leaving the number of lines the same), paint a field on one of the lines in the sample area of the template definition

               

              screen, it does not matter which one, making sure that you position the field so that it is in the correct horizontal position and is of an appropriate length to accommodate the largest value likely to be reported there. When you name the field via the properties screen you should also use the ADVANCED tab and enter an identifier, preferably something that is unique to only identify this field, in the "Start field on" - "Preceding string in current line" box. In this example for this field "d-rte" should do the trick.

               

              Hint - to save typing time you can cut and paste the string from the REPORT section (NOT the sample/field definition section) of the Report window and paste it into the Preceding String box.

               

              The D-RTE field should now be a visible grey highlight in one of the three records of the report.

               

              Repeat this process for as many of the other fields on that side of the report that you can fit into the number of lines available. If necessary save that template and open up one or more others to create as many fields as you require. In this case there are also a couple of spare lines in the DETAIL template that could be used.

               

              OK so far?

               

              Next: Look at the other 'column blocks' in the report Some of those may be treated the same way. The Preceding String can be anywhere on the line BEFORE the data, it does not have to be immediately before it. As long as fields do not overlap each other you can use as much of each line as you want or need. The order of the field in the column is not important UNLESS you have to differentiate very similar preceding strings.

               

              NOTE: The example above uses a string which is likely unique to that field alone. However other tags - HIRE for example - are not guaranteed to be 'unique' in the context and may require care in use to ensure you don't get unexpected results.

               

              Remember that spaces count as strng characters so "  hire" would be different to "sapphire" for example.

               

              Moving right on the report the fields

               

              ST1                   AZ  

              LOC1                  SC

              ST2                   CA

              F#EX                  02  

              S#EX                  00

              FADJ                  %    .0000

              SADJ                  S    65.0000

               

              can all be dealt with in the same way and again you have the choice of using the Detail template, the append just set up or a completely different append if you choose subject to the constraints of the version of Monarch you need to develop for.

               

              Likewise any values from the extreme right of the report

               

              GrossCompToNetCur     9500.00  (from 57583.30 GRCOMP   9500.00)

               

              GrossPayToNetCur      8930.00  (from 55838.30 GRPAY    8930.00)

               

              for example can be picked out the same way using GRCOMP and GRPAY respectively.

               

              On the other hand the GRCOMP and GRPAY YTD figures are more challenging. (Even if you pick the GRCOMP as part of the DETAIL template, which looks viable here, the GRPAY value is not so simple and moves.)

               

              It looks like each case will be identified by the same string that can be used for the Current values but NOT as a preceding string since it trails the field we want. So we need a different approach based on the same string idea.

               

              In one of the templates paint a field in the columnar position that the YTD GR values appear (Include the first line for GRCOMP if you did not previously create it as a fixed location field in the detail template.). Go to the advanced properties of the field and this time skip the "Start field on" section and go to "End Field on" and set an end of field identifier. In this case "Blank Field Values" seemed to work just fine.

               

              In the extracted table you will get a Multi-Line field of all the values for that employee. We will extract just the value(s) you require from that field (which can then be hidden) into the field(s) you need to report. Note that this is much easier to do in V9 than in earlier versions following the introduction of a coupld of new functions in recent releases. On the other hand the same technique can be applied, though with longer formulas, in most earlier versions of Monarch still likely to be in use.

               

              If we call the field "Multilinefield1" the following formula will extract the GRPAY YTD value for us and put it into its own field.

               

              [font="courier"]val(lsplit(intrim(textline(,"grpay")),2," ",1))[/font][/quote]Lets break that down into something easier to follow.

               

              [font="courier"]textline(,"grpay")[/font][/quote]This uses the TEXTLINE function in its 'search' mode to find the line that contains the string "grpay" and single that out from the rest, however many there might be. (I have assumed that "grpay" will not occur more than once in this context. If it does only the first occurrence will be presented. Other ideas would need to be used to go beyond that.)

               

              The INTRIM function strips spaces from the start and end of the working string and removes duplicate spaces from within the string while we work with it.

               

              The Lsplit function may be more familiar and in this instance will split the string into two parts using the space character as the separator and return just the first part.

               

              In this case that part will be a numeric value but currently twe are working with a character field so we use god old VAL() to convert to numeric (and ensure the field is defined as numeric with a suitable number of decimal places.)

               

              The some principle can be used for the other values to be extracted by copying/duplicating the field and editing the selection string. (Or row number if the position is always consistent! That's the other mode for TEXTLINE.)

               

              Obviously in this case you have to know which field you want to create and look for them. If you need to report what has been found a similar approach could be adopted using the LINE NUMBER option of TEXTLINE and processing a set number of lines

               

              into fields.

               

              For earlier version of Monarch some of the clever stuff that TEXTLINE and INTRIM do for us would need to be handled slightly more awkwardly using INSTR(), perhaps LEN(), maybe SUBSTR() and so on as well as LSPLIT or RSPLIT and some fancy footwork with the TRIM/RTRIM/LTRIM functions to deal with multiple spaces.

               

              So that leaves us with

               

              HoursEarningsCurCode  1       (from 1  REGULAR                 9500.00)

              HoursEarningsCurDesc  REGULAR (from 1  REGULAR                 9500.00)

              HoursEarningsCurHrs   (from 1  REGULAR                 9500.00)

              HoursEarningsCurAmt   9500.00 (from 1  REGULAR                 9500.00)

              HoursEarningsYTDCode  4C      (from 4C 4CSICK          6.14     583.30)

              HoursEarningsYTDDesc  4CSICK  (from 4C 4CSICK          6.14     583.30)

              HoursEarningsYTDHrs   6.14    (from 4C 4CSICK          6.14     583.30)

              HoursEarningsYTDAmt   583.30  (from 4C 4CSICK          6.14     583.30)

               

              I was not sure whether you only needed those specific values, as identified by REGULAR from the Current section and 4CSICK from the YTD section or whether these were more like samples of need. Given that you need to report REGULAR and 4CSICk I assumed samples.

               

              Lets take the Current rows first.

               

              HoursEarningsCurCode  1       (from 1  REGULAR                 9500.00)

              HoursEarningsCurDesc  REGULAR (from 1  REGULAR                 9500.00)

              HoursEarningsCurHrs   (from 1  REGULAR                 9500.00)

              HoursEarningsCurAmt   9500.00 (from 1  REGULAR                 9500.00)

               

              If you only want REGULAR and, when it exists, it is always the first row then you can most likely just define the fields in the Detail or Append template and that would the end of it.

               

              If it is not that simple use a variation of the GRCOMP/GRPAY idea to extract a multiline record and then process it using calculated fields.

               

              In this case I can't see an easy way to End the multiline field without including the YTD figures. We can get at the YTD figures separately using another multiline field though.

               

              We can see that the 'Regular' value appear in both sections and that MIGHT be a problem but it looks like it's OK from the sample since if we use the TEXTLINE in SEARCH mode function again only the first occurrence will be identified and so we would get the CURRENT value.

               

              So, using pretty much the same technique as before, paint the field and set it to be multiline using a suitable END ON setting. If you are once again looking for certain fields use the TEXTLINE in search mode option otherwise just create and appropriate number of fields processed by TEXTLINE using LINE NUMBER mode and put some sort of secondary check on whether you have reached a YTD line!

               

              The formula will be similar to before except that you have four fields to split out, one of which may be spaces. (Or is it always just 3 fields in reality in the CURRENT section?). Also most of the splits are for Character fields so the VAL()

              function would not be required for those.

               

              Assuming 4 fields, LSPLIT (after INTRIM) will get you 1 and 2. RSPLIT (after RTRIM or INTRIM) will get you number 4.

               

              You may then need to check if there are any values in the area of the third field. Maybe try a SUBSTR() that covers the position where the 3rd field data should be and check if it returns nothing before trying to process? Or simply LSPLIT for section 3 and if it returns the same value as RSPLIT for section 4 just set 3 to no value. (Formula based onther IF() function required.)

               

              Finally for now the YTD figures in the same column.

               

               

              HoursEarningsYTDCode  4C      (from 4C 4CSICK          6.14     583.30)

              HoursEarningsYTDDesc  4CSICK  (from 4C 4CSICK          6.14     583.30)

              HoursEarningsYTDHrs   6.14    (from 4C 4CSICK          6.14     583.30)

              HoursEarningsYTDAmt   583.30  (from 4C 4CSICK          6.14     583.30)

               

              I suggest much the same approach but in this case the multiline field can be set up using the Advanced property for "Start Field on" with "String: y-to-d anywhere in the preceding line". Apart from that the slice-and-dice processing is much the same as the previous examples.

               

              Of course if you only need the Current 'REGULAR' and YTD "4CSICK" lines then the simplest route might be to extract just the one multiline field to give you both values. There are a number of options any of which may be of use to you in the context of possible variations in the fullest reports!

               

              HTH.

               

               

              Grant

              • Multi-Line and Multi-Column Detail Approach
                JoeSalvatore _

                Awesome post and information.

                I also have been working with some of the DataWatch team and they were impressed and in agreement.   smile.gif[/img] 

                 

                I am working my way through your ideas and have already learned the hard way the true pain experienced when defining too many lines as your sample for the detail or appends for this type of report. OUCH!

                 

                I will also post a separate thread some interesting things I had to do with the Address Trap.

                 

                Again Thanks and keep an eye on this post for future cries for help and understanding.

                • Multi-Line and Multi-Column Detail Approach
                  Grant Perkins

                  Thanks for your kind comment Joe.

                   

                  You did say you wanted some suggestions rather than a complete solution .... 

                   

                  The sample lines thing is important to ensure every occurrence of the trap is recognised and reports with most records containing a lot of lines but a few records having very few lines can indeed be challenging. At least with V9 there are a few more templates available to play with.

                   

                  I look forward to reading how things progress.

                   

                  Have fun.

                   

                  Grant