11 Replies Latest reply: May 15, 2014 10:00 AM by Grant Perkins RSS

    Need Help With Variable Records and Fields

    Sam Chambers

      I'm a fairly new Monarch user, who's having a problem figuring something out.

      I have a general ledger report that I'm trying to extract data from. Some of the records are all on one line, while others are on two lines. Here's an example:

       

      ACCOUNT                                                                                OPEN          DEBITS         CREDITS          CHANGE           CLOSE

                  JOURNAL    DATE     BCH ENTRY         DEBITS       CREDITS  DESCRIPTION

                                                                                      DEPARTMENT: 01.6000  SAMPLE DEPARTMENT

      01.6000.2000 - SAMPLE DEPARTMENT CONTRACT LABOR

                                                                                      0.00       61,129.96       15,540.35       45,589.61       45,589.61

                  GEN        10/11/06 3   2-1                         550.00  ACCRUE ABC INV#323142                                        

                  AP         10/17/06 2   48             80.00                V# V000003 I# 11-7 F# SYSTEM                                                                               

      Remit to: LINDA SMITH

                  AP         10/24/06 1   89            550.00                V# V000048 I# 323142 F# SYSTEM                                                                               

      Vendor: ABC SERVICES

                  AP         10/24/06 1   94            336.00                V# V000846 I# 4460 F# SYSTEM                                                                               

      Vendor: CBA, INC

                  GEN        11/07/06 3   11-1          982.00                ACCRUE CBA INV #004536                                       

                  GEN        11/07/06 3   12-1          799.00                ACCRUE CBA INV #004510                                       

                  GEN        11/07/06 3   15-1          603.00                ACCRUE CBA INV #004549                                       

      /code

       

      I need to be able to capture each of the fields shown in the header, plus the Vendor number (V#) and invoice number (I#). My questions are:

       

      1. How can I capture both the GL fields and the 2-line fields at the same time?

       

      2. How do I capture variable length fields? Notice the "I#" field, which is variable length.

       

      3. There's a similar issue with the "Remit to:" and "Vendor:" data. They're in roughly the same spot, and I want to treat them the same when I extract the data, but they start in different positions.

       

      Thanks for helping the "noob"!

       

      Sam.

       

      [Edit:  Found that pinned thread and was able to post the report so it looks right.  Woohoo!)[/I]

        • Need Help With Variable Records and Fields
          Data Kruncher

          Hi Sam, and welcome to the forum.

           

          The structure of your report was somehow familiar, so I did a quick search of old posts and found the exact report posted by another user (http://www.monarchforums.com/showthread.php?t=393[/URL]).

           

          The solutions posted there should address your needs, but do let us know if you have further questions and of course if this is what you needed to know.

           

          HTH,

          Kruncher

            • Need Help With Variable Records and Fields
              Sam Chambers

              Thanks, Kruncher.  Even though post #4 is now blank, I think I can see ways that will address questions 2 and 3.  I'll give it a try this evening. 

               

              But I still don't see how to trap both single and multi-line records.  If I trap on "AP", I'll ignore the "GEN" records.  If I trap based on text in the location where "AP" lives, and define it as a 2-line record, that won't work.

                • Need Help With Variable Records and Fields
                  Data Kruncher

                  I didn't notice that Joey's post is gone. Odd... I'll have a thorough look at this in a little while, unless of course, someone else steps in by then.

                  • Need Help With Variable Records and Fields
                    Grant Perkins

                    But I still don't see how to trap both single and multi-line records.  If I trap on "AP", I'll ignore the "GEN" records.  If I trap based on text in the location where "AP" lives, and define it as a 2-line record, that won't work.[/quote]

                     

                    Sam,

                     

                    See if this works for you.

                     

                    Treat the GEN  and AP lines as the detail lines trapping on the / characters in the date field (for example). Grab the fields as per the report page header. Obviously some fields only apply to one or other type of record and if you treat "GEN" and "AP" as data fields each record type will be easy to identify along with whether it is a credit or debit for a GEN record.

                     

                    Take the "Description" field as a single long Character field. You can split it up to extract the component parts. This will work especially well if you can be sure the format of each type of line will be quite consistent throughout the report.

                     

                    To split the field in this case you will need a couple of Calculated Fields and most likely use one of the SPLIT() function - LSPLIT or RSPLIT - to break the field into the components you want. This is quite flexible so if you find there are variations it should still be possible to split the fields but you will need to work out how the deviant records can be identified and what their rules are. For now lets look at your posted sample.

                     

                    V# V000003 I# 11-7 F# SYSTEM

                    /code

                     

                    A very common way to split lines like this is to use the spaces as break points. It works well so long as you allow for the possibility that some of the spaces may in fact be more than 1 space character. The TRIM, RTRIM and LTRIM functions are you allies for fixing that sort of problem. V9 ussers may also enjoy using INTRIM.

                     

                    Assuming you have single spaces in the sample above you can extract V# from the DESCRIPTION field with the formula:

                     

                    LSPLIT(DESCRIPTION, 3, " ",2)

                     

                    Which translates as:

                     

                    Working from the LEFT, split the DESCRIPTION field into 3 parts identified by spaces as the break points and give me the second part.

                     

                    That should return  V000003.

                     

                    It also means that data entries, as long as they are contiguous if you are using spaces, can be of variable length and the formula will still give the results you need provided you have ensured that the field you are using to store the extracted value as at least as big as the longest extract you are ever likely to get.

                     

                    There are a number of other functions available that allow you to slice and dice character based fields in different ways when the need arises. Some time spent browsing the Function List in the Help file is highly recommended  as it will provide some ideas about what is available even if you don't need to use the function right now.

                     

                    Let's move on to the 2 line record problem.

                     

                    There is a trick for Advanced users which almost always becomes THE solution for the very early projects given to people new to Monarch.  Why this should be I have no idea but it is a good challenge so let's go for it.

                     

                    Since we know that many of the detail records will only have one line of data we can only work with single line data samples for our template. In fact the data in the sample does not much matter (there are some exceptions to this statement but they don't concern us here), we just know we can only use one line and we only want the fields that MAY exist in that line populated when they do on fact exist. Appends and footer will not work for us as they are normally presented since they are intended to attach to multiple detail records.

                     

                    However, we can work around that.

                     

                    Create an APPEND template that uses exactly the same trap as your detail record. It will need a one line sample. The line does not matter much other than as an assist for positioning the trap characters and the field you want. You can change the selected sample for each part of that if you wish.

                     

                    Lets go for the "Remit to" data for this one.

                     

                    Having created the trap, paint a field where the "remit to:" data will be when there is any. Right click on the field to get to the Properties, select the Advanced Properties tab and set the field to "Start on" a Preceding string of "remit to:"  (WITHOUT the quote marks of course!).

                     

                    That's it. Close the tab and save the template.

                     

                    So, what you now have is an APPEND template which, by design intent SHOULD be used for picking additional data from BEFORE a detail record but in this use will pick it afterwards but ONLY of the field required actually appears. It will also be reset for each new detail record since it shares the same trap. So in effect it will start to look for the character string "Remit To:" on the detail line and continue looking on each line (in the section BEFORE the field is defined to start) until it finds what it is looking for or comes across another detail record. Either way it will stop at that point if set up as advised. (There are other options  - but they are for another time ...)

                     

                    Create another Append template to do the same thing for the Vendor: field required.

                     

                    And that should be about it other than setting the display order of the fields, hiding the intermediate extracted fields - like the Description field and any work fields and then creating a few excellent and powerful Summaries to impress people.

                     

                    See how you get on. I'm happy to clarify anything or cover stuff that I may have missed, just let us know if you need more input.

                     

                    Enjoy.

                     

                    Grant

                     

                    E&OE

                      • Need Help With Variable Records and Fields
                        Sam Chambers

                        Grant:

                         

                        Sorry haven't replied before now, but I got pulled off onto another project. Thanks very much for the clear and thorough explanation for the "noob"! I'll work on this one and see if I can put your advice to good use.

                         

                        Now I have a somewhat similar problem, and again, my inexperience with Monarch is showing. I have a report that looks like this:

                         

                        [FONT=Courier New]                           NET                                    ACCT TYPE       INS   BAD DEBT     CLIENT                -


                        AGING----


                        [/FONT]

                                      OPEN     BILLED      RCPTS       ADJS       REFS      CHANGE     XFERS      XFERS      XFERS      CLOSE           0-30      31-60      61-90     91-120    121-150    151-180   TURN OVER[/FONT]

                        BC TOTAL[/FONT]

                                 549428.75  424468.29 -173614.33 -300985.92       0.00  -11869.87  -44360.03       0.00       0.00  443066.89      321047.56   39894.32   34844.97  -32766.67    -179.92   80226.63          -0[/FONT]

                        MCR TOTAL[/FONT]

                                4356874.49 3887773.75 -1447498.42 -2542241.09       0.00       0.00 -130712.26       0.00       0.00 4124196.47     3185408.90  260237.40  154257.40  157188.27  175734.26  191370.24          -0[/FONT]

                        WC TOTAL[/FONT]

                                  64420.13             -19328.27  -14944.49                  0.00                                    30147.37                             18500.13                         11647.24           0[/FONT]

                        GRAND TOTAL[/FONT]

                               13174622.19 6984031.36 -2854893.59 -4196472.77   12526.20   -7605.05       0.00  -86216.37       0.00 13025991.97     5462462.17 1508750.56  715365.32  638972.73  636953.05 4063488.14          -0[/FONT][/code]

                         

                        I started by using a defining my detail template as a 2-line floating trap, keying on the word "TOTAL". So far, so good. But, as you can see, my fields don't line up. I thought about using a space to define the breaks between fields, but that won't work because the report doesn't print zeroes in every field, as you can see in the "WC TOTAL" record.

                         

                        Any thoughts on how to handle this report format?

                         

                        Thanks again!

                          • Need Help With Variable Records and Fields
                            Grant Perkins

                            Sam,

                             

                            I have a lot of sympathy with you on this one.

                             

                            Is there any chance that the file you get has been corrupted by processes of one sort or another on the way to you and that there might be a much more usable (or indeed just plain usable)  version somewhere back up the chain?

                             

                            Failing that is there potential for tracking down the (alleged) programmers and politely insisting that they 'fix' the format? (Get them to do that before removing them from the software coding gene pool.)

                             

                            Lines 1,2 and 4 can be handled but for the third line of the sample we have to make some serious assumptions. Is it possible to be sure that only certain columns are intended to be populated, hence the blanks rather than zeros?

                             

                            If so some specific processing relating to those "WC total" lines could be applied. Failing that I'm really not sure at this stage how reliable any solution might be.

                             

                            What can you tell us?

                             

                             

                             

                            Grant

                              • Need Help With Variable Records and Fields
                                Sam Chambers

                                Grant:

                                 

                                Sadly, the report is not an anomaly.  It always prints this way.  And no, I doubt the software vendor will be willing to fix this...They've been reulctant to fix these kinds of things in the past.  I wish their system was better and producing ad-hoc reports, but frankly, it sucks at that. 

                                 

                                I think I can probably extract the data using the Excel methods I used before buying Monarch, but I won't be able to easily repeat the extraction every month.

                                 

                                Thanks for the help (and sympathy!).

                                  • Need Help With Variable Records and Fields
                                    Data Kruncher

                                    Gents,

                                     

                                    I've got this one worked out, and while a similiar solution may exist in the forum somewhere, I don't recall it. Nonetheless, here we go...

                                     

                                    It occured to me that this floating/missing value problem wasn't entirely unlike something we'd seen before in one of Grasshopper's reports, iirc.

                                     

                                    The difference was that his had field delimiters in the line; your sample doesn't Sam. It made slicing and dicing Grasshopper's report possible.

                                     

                                    So, we need to go about creating them where they don't exist.

                                     

                                    Rather than even try to get this modeled traditionally, instead trap the whole line as one character field, named A.

                                     

                                    Now create calculated field B, with the expression:

                                    [SIZE=2]replace(A,space(11),"|")[/SIZE][/code]

                                     

                                    This will replace any occurence of 11 spaces with a pipe, which we'll use as a delimiter.

                                     

                                    Now, because B still leaves us with some duplicated spaces, create field C with:

                                    [SIZE=2]Intrim(B)[/SIZE][/code]

                                     

                                    Getting close now. We get where we really want to be with new field D, with:

                                    [SIZE=2]Replace(C," ","|")[/SIZE][/code]

                                     

                                    which cleans up all of the remaining spaces, replacing them with pipe characters.

                                     

                                    Now we slice and dice field D. The Open calculated field uses:

                                    [SIZE=2]Val(LSplit(D,40,"|",1))[/SIZE][/code]

                                     

                                    I used the value of 40 because I didn't know exactly how many (and didn't care to count) fields there were in the report. Monarch doesn't care if you specify more than you need.

                                     

                                    NetBilled uses:

                                    V[SIZE=2]al(LSplit(D,40,"|",2))[/SIZE][/code]

                                     

                                    and so on, and so on, adding one to the last parameter of the LSplit as you go across the report.

                                     

                                    Use the "duplicate" button to define the calculated field; it makes it pretty quick. I defined all of them in just a few minutes.

                                     

                                    A bit of effort is required to model in this manner, but as with most Monarch models, you'll only do it once.

                                     

                                    HTH,

                                    Data Kruncher

                                    • Need Help With Variable Records and Fields
                                      Grant Perkins

                                      Grant:

                                       

                                      Sadly, the report is not an anomaly.  It always prints this way.  And no, I doubt the software vendor will be willing to fix this...They've been reulctant to fix these kinds of things in the past. /quote

                                       

                                      Sam,

                                       

                                      There should be a law against that ...   :mad:

                                       

                                      More seriously, the BC, MCR and Grant Total lines (if you need grand total extraction) could probably be fairly easily dealt with by a couple of approaches.

                                       

                                      For example one could create a trap for the decimal lines using a floating trap (there are some special rules to follow there in order to allow for variable field sizes) base on the decimal point (probably). The line types from the line above could be harvested with an Append template.

                                       

                                      Or a less subtle approach would be to simply grab the entire line with its values as a single field and then us the SPLIT functionality to slice them up. If you have V9 it would be a little easier to deal with the variable number of spaces between the value strings. (See the INTRIM function.)

                                       

                                       

                                      The  "WC Total" line  (rather an apt association I thought) is more of a problem UNLESS it is possible to assume that any columns with blanks rather than zero entries simply do not exist for that line. In which case one could apply some conditional logic within the line splitting formulas so that,  for example (based on the sample), the second value always went into the RCPTS column, the third into the ADJS columns and so on. However since the lines have an aging element it seems very unlikely that such rules could be applicable.

                                       

                                      What might be viable, using the 'extract entire line and slice and dice' concept would be field identification based on position. For example the decimal places seem to be quite consistently positioned across the line and even in line with the top line and the 'notional' column headers. So, using the slice and dice concept, one could simply run some conditional processing using functions to identify the line character positions at which decimal points appear and then allocate the values that surround the decimal points to the appropriate fields. Or rather one would define the calculated field to be extracted along the lines of :

                                       

                                      For the "Open" column, written as a description not the actual formula  ....

                                       

                                      IF(Extracted_Line_Field_Type="WC" and character position 16 =".", then the substring of the field from position 8 (??) to 18 is the value we want, otherwise the Open field will be zero.)

                                       

                                      And so on for the other fields. Once you have one formula of course the others are simply a matter of duplication with a new name and a few minor changes to the position values.

                                       

                                      In fact, based on the sample you posted, It may be possible and simpler in some ways for the overall model maintenance, to apply the same sort of logic to all the lines. So long as the decimal points never drift to a position where one column might entirely overlap another column at the expected decimal point position one could simply change the formula to look for decimal points and where they appear on the line tolerating a range of possible locations for each column.

                                       

                                      OK, so I know what I am thinking but how well an I explaining it? And are my implicit assumptions about the variable format realistic

                                       

                                      Let me know the answers and I'll put together a rough model based on your sample lines.

                                       

                                       

                                      Grant

                                        • Need Help With Variable Records and Fields
                                          Sam Chambers

                                          Grant:

                                           

                                          I agree that this kind of thing just shouldn't be allowed. But, if their stock reports were better, and/or their report writer were decent, I wouldn't need Monarch to extract stuff!

                                           

                                          The issue that shows up on "WC Total" line - blank spaces where there really should be zeroes - can show up at any time, on any line in this report. In fact, it does, but I didn't post the entire report here. So, what I need is a model that will work regardless of the title of the line. That means that I can't do something specific to the "WC" line.

                                           

                                          Tell you what...Can you PM me with an e-mail address? (Too bad I can't attach a file to a PM...)  I'll send you the text file, exactly as it comes off our system, so you can see exactly what I'm dealing with.

                                           

                                          Thanks again,

                                           

                                          Sam.

                                            • Need Help With Variable Records and Fields
                                              Grant Perkins

                                              Grant:

                                               

                                              I agree that this kind of thing just shouldn't be allowed. But, if their stock reports were better, and/or their report writer were decent, I wouldn't need Monarch to extract stuff! [/quote]

                                               

                                              That system has not yet been written!

                                               

                                               

                                              The issue that shows up on "WC Total" line - blank spaces where there really should be zeroes - can show up at any time, on any line in this report. In fact, it does, but I didn't post the entire report here. So, what I need is a model that will work regardless of the title of the line. That means that I can't do something specific to the "WC" line.[/quote]

                                               

                                              OK. Samples always leave that possibility open. It is in the nature of samples to ask and answer some but not all related questions.

                                               

                                              If I were you I would ignore my approach above and go with Data Kruncher's solution which should work very well unless you have something really obscure at some point in the report in which case both out approaches might struggle without further work.

                                               

                                              I have another approach that should also work (same caveat as above) but Kruncher's is more elegant and less work assuming you have version 9 of Monarch and almost as elegant using V8 which lacks a function that is useful here iirc.

                                               

                                              If you have a version lower than 8 I may be that my alternative (though sort of similar) approach would be of interest.

                                               

                                              Tell you what...Can you PM me with an e-mail address? (Too bad I can't attach a file to a PM...)  I'll send you the text file, exactly as it comes off our system, so you can see exactly what I'm dealing with.

                                               

                                              /quote

                                               

                                              I would be happy to have a look at the full report although I am busy through until the middle of next week so unlikely to do much with it before then. But, honestly, I think if you follow Kruncher's solution you won't need mine.

                                               

                                              Try it out and let us know if the full reports throws up another curve ball or two.

                                               

                                               

                                              Grant