6 Replies Latest reply: May 15, 2014 10:04 AM by Grant Perkins RSS

    Data inconsistently linking with detail template

    MonUserCJ _

      Hi everyone,

       

         I'm running into a problem with some ID numbers getting inconsistently linked to the detail template. There are two ID number footer templates that are to be linked to the name above them. Not every name has two ID numbers associated with it, so I have both footer templates cleared by the detail template so that an ID doesn't get linked to the name above the name it's supposed to.

       

         The problem is, the ID numbers aren't getting linked to the detail templates consistently. At first, it seems that the ID numbers are offset by one--that is, the ID numbers of one name are getting linked to the record below it. If it were as simple as this, it would be fairly easy to work around. However, occasionally the ID numbers are linked to the correct name. Thus, it seems like Monarch is unable to consistently extract the ID numbers from the report. Incidentally, there is also phone number and address data which seems to get extracted fine.

       

         Does anyone have any ideas of what's going on? This seems to be a problem that I experienced before, and that I wrote about in [URL="http://http://www.monarchforums.com/showthread.php?t=2818"]this previous post[/URL]. In the replies, I noticed this remark by Ken Indorato:

       

      In V10 the "Cleared by" feature does not work correctly with multiple Footers. This bug will be addressed by our developers in the next release.

       

      Based on MonUserCJ's description of the behavior he is seeing this appears to be the issue.

       

      A possible workaround when encountering this bug (the success depending upon the layout of the report) is to create a single multiline footer (Advanced Field Properties) and capture the data with a floating trap.[/QUOTE]

       

      I was wondering--do you think this bug will be fixed before the next full release (i.e. V11)? In the mean time, it may be hard to make a multi-line footer, since the IDs aren't always present. There's enough space so that if they aren't present, other stuff won't get captured. However, the text I use to pick up on the ID numbers isn't present in every line. Perhaps I can find some other formatting text that is present more consistently to trap this data.

       

      Otherwise, perhaps I could make the detail line in the middle of the data I want to capture for a record, so that the data could be linked with the detail line with appends and footers instead of just footers.

       

      Does anyone else have any ideas? I would greatly appreciate hearing them, and I also wanted to bring up this bug again to see if any progress has been made on it, or if anyone else is running into this problem.

       

      Thanks.

        • Data inconsistently linking with detail template
          MonUserCJ _

          Hello,

           

             I have a question related to my original post. Do multiple append templates with Cleared by specifications (as opposed to multiple footers templates) work? I'm going to give it a try, but if anyone has any experience with them, I would greatly appreciate hearing it.

            • Data inconsistently linking with detail template
              Steve Hunt

              I'm having the same dilemma with v10.  I'm trying to create a model that employs multiple footer templates, each using "cleared by" to the detail line.  In building the report, it seems to be treating one of these templates correctly, but not the others.

              Is there a work around for this bug in v10?  This functionality will make Monarch much more valuable to me, so if the only answer is to go to a newer version, please just let me know.

                • Data inconsistently linking with detail template
                  Olly Bond

                  Hello Steve, and welcome,

                   

                  If you can send me a sample of the report and model I can have a bash. It might be that some clever use of the preceding string, or the guru trap, or mulitple columns will solve it.

                   

                  Remember with footers they should be cleared by the Detail above them, unless you have an exceptionally weird report layout.

                   

                  Best wishes

                   

                  Olly

                    • Data inconsistently linking with detail template
                      Steve Hunt

                      Thanks for trying to help!  There is probably a better way to send this information to you, but I don't know what that is.  Below is a "cut/paste" excerpt of the data I'm trying to capture.  It's not lining up below like it really does, so this may not be as helpful as it could be.

                      My detail line is the line that contains the date, then the word "BATCH:...".

                      On 1 or more lines directly below this line are the hours worked for the period, the rate, the factor, earned and pay code.

                      I set up several separate footers to capture each possible pay code.

                      Pay codes include HOL (holiday pay), PYVAC (prior year vacation pay), and many others.  A blank pay code means it is regular pay not otherwise labeled.  I have 4 footer templates set up right now, one for whatever pay code(s) may occur for any particular set of detail.

                       

                      Please let me know if I can better help you help me.  Is there a way to attach a file and send it through this forum?  If so, that would give you a better reference.

                       

                      Thanks!

                       

                      Steve

                       

                      01    01/10/13  11:01:00                                                                                REPORT# 373   PAGE   1  

                                                                  

                      • * *   P A Y R O L L   H I S T O R Y   * * *                                                                               

                      WEDDLE BROS. CONSTRUCTION COMPANIES                                                   

                      PAYROLL TYPE ALL                    95245 ZZZZZZZ, EDWARD C                SS# xxx-xx-xxxx             PERIOD 01/01/12 THRU 12/31/12  

                                                                                                                                                                                                                                                                                            •    

                                                         PAY                      DEDUCTIONS      ADDITIONS       PAID                     FRINGE BENEFITS  

                        HOURS   RATE  FACTOR    EARNED   CODE     WITHHOLDING     CODE   AMOUNT   CODE  AMOUNT    CHECK#      NET   DATE   DESC     AMOUNT  

                      ====================================================================================================================================  

                       

                        01/01/12      BATCH: P#001485      SET:     26                                                                               

                      8.00  40.0000  1.00    320.0000 HOL      EARNED  1600.00   10  1077.77                 **    61     0.00 01/06/12                  

                        32.00  40.0000  1.00   1280.0000 PYVAC    F         85.36  100     6.66                                                                               

                      FW       200.26 125C    30.77                                                                               

                      LW/OW     18.16 125H    10.00                                                                               

                      SW/IN     47.51 125M    24.00                                                                               

                      125S    24.51                                                                               

                      401K    75.00                     Last Job Worked      6                  

                       

                        01/08/12      BATCH: P#001489      SET:     24                                                                               

                      32.00  40.0000  1.00   1280.0000          EARNED  1600.00   10  1077.77                 **    61     0.00 01/13/12                  

                         8.00  40.0000  1.00    320.0000 HOL      F         85.36  100     6.66                                                                               

                      FW       200.26 125C    30.77                                                                               

                      LW/OW     18.16 125H    10.00                                                                               

                      SW/IN     47.51 125M    24.00                                                                               

                      125S    24.51                                                                               

                      401K    75.00                     Last Job Worked      6                  

                       

                        01/15/12      BATCH: P#001493      SET:     23                                                                               

                      40.00  40.0000  1.00   1600.0000          EARNED  1600.00   10  1077.77                 **    61     0.00 01/20/12                  

                                                                  F         85.36  100     6.66                                                                               

                      FW       200.26 125C    30.77                                                                               

                      LW/OW     18.16 125H    10.00                                                                               

                      SW/IN     47.51 125M    24.00                                                                               

                      125S    24.51                                                                               

                      401K    75.00                     Last Job Worked      6                  

                       

                        01/22/12      BATCH: P#001496      SET:     24                                                                               

                      40.00  40.0000  1.00   1600.0000          EARNED  1600.00   10  1662.77  AUTO   585.00  **    61     0.00 01/27/12                  

                                                                  F         85.36  100     6.66                                                                               

                      FW       200.26 125C    30.77                                                                               

                      LW/OW     18.16 125H    10.00                                                                               

                      SW/IN     47.51 125M    24.00                                                                               

                      125S    24.51                                                                               

                      401K    75.00                     Last Job Worked      6[/code]

                        • Data inconsistently linking with detail template
                          Grant Perkins

                          Steve,

                           

                          Can you tell us what you need the output analysis to look like?

                           

                          Are there always 8 data lines per record plus a blank line for separation between records? Or are the number of line variable?

                           

                          Grant

                            • Data inconsistently linking with detail template
                              Grant Perkins

                              Steve,

                               

                              If you base your detail record on the BATCH line I don't see a clear need to get into footers for this record structure. Guessing at the extraction you need suggests you are looking for weekly records per employee that contain the detail lines primarily but may also have a purpose for the summary analysis to be included in the extraction. Is that about right?

                               

                              If so, based on what the sample provides, the BATCH line and the summary analysis seem to have a consistent, repeated structure and therefore should be easy enough to map and extract.

                               

                              The details lines for the hours, rate and pay code(s) are slightly more challenging and there are options on how one can approach that challenge based on what you heed to do with the extracted information.

                               

                              As a guess I could assume that for each week you need an array of fields that provide you with the opportunity to analyse by paycode 9including blank code). You might, ofr example, need a table that had columns for employee, week, and then each possible pay code in a column or group of columns that provides you with the hours and rate related to that paycode.

                               

                              Now, if you really have a LOT of paycodes things could get a little more demanding but in an initial approach might be to extract all of the "detail" payment breakdown lines as a single TEXT BLOCK field. After that, in the Table I would create some calculated fields that would treat each line in the block as a separate sub-record and then split the data in the line as required to populate the full table array.

                               

                              Are you already familiar with multi-row text block selections? If not I will describe them in another reply.

                               

                              Once you have your text block field you can break it out line by line as is required in this case. It helps here that the lines are naturally structured of course!

                               

                              The way to do that is to create a calculated field and use the TEXTLINE function to identify the line you want. Text line has 2 modes - Line number and Search.

                               

                              Line number allows you to specify the line you want and Search allows you to specify a search string that will identify the line you want. Taking this second example first, if you want to create a new field that caontains only the data line for the code "HOL" then you would TEXTLINE to search for the string HOL and it would fine the line with HOL in it. In this case that should work very well unless you ever have records that have more than one HOL line. The concept can be repeated for al the other codes you need to analyse.

                               

                              The lack of a code for a regular pay line is a slight complication. Using a string of blanks MIGHT work provided that string will be unique to any lines in the block. You might be lucky with that or you might not. You may need to make the comparison a little more specific to look for an empty string at the end of line. No big deal but I'll leave describig that for the time being as right no I am only mapping out the concept for you to consider.

                               

                              If you extract to a fields called, for example,  "Pay" (for the blank code) and then "Hol", "PYVAC" etc you will get basic columns containing the values of the lines. You could then drop the code from the end of the line since you have already named the column for the code. These will be Character Type fields.

                               

                              Should you need to split the values in the lines into, e.g., Hours, Rate, Factor and Earned (or some of those) then you can use the SPLIT based functions (LSPLIT, RSPLIT  - see the Functions Reference in the Help for details) to slice and dice the line text into separate fields. This can be done from your newly extracted line text or, to save having to work with the extra field, directly with the text line by making the SPLIT function parameters part of the extraction. If you have not used TEXTLINE and the SPLIT functions previously I would suggest working at the simpler level first to become familiar with them individually and then discover how to combine them into a single action.

                               

                              You can repeat the process for as many fields as you need. So you may have Pay_hours, Pay_Rate, Pay_Factor and Pay_Earned for example, then 4 more fields for HOL, etc. Maybe you don't need all four of them?

                               

                              Of course to make them more useful you may want to convert them to numeric values where appropriate. You can do that too as part of the Calculated field formula using the VAL function.

                               

                              There are some technical constraints  - for example a limit of 255 fields in a table record. However I would be surprised if your report used so many codes that that limit would be tested. If it does then we would need to look for some efficiencies in the approach.

                               

                              For more information about Technical Specifications have a look at Appendix D in the built in User Help Guide.

                               

                              (Apologies if you already know all of this but I am assuming you may not know in order to try to address as many things as is practical at this stage without getting into too much technical stuff before we get your feedback on the concept.)

                               

                              HTH.

                               

                              I'll look out for your observations and take my cue as to the the next stage from them.

                               

                               

                              Grant