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

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!)

[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).

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

[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.

[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.

[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.

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

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

[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:

                           NET                                    ACCT TYPE       INS   BAD DEBT     CLIENT                ------------------------------------AGING------------------------------------
              OPEN     BILLED      RCPTS       ADJS       REFS      CHANGE     XFERS      XFERS      XFERS      CLOSE           0-30      31-60      61-90     91-120    121-150    151-180   TURN OVER
BC TOTAL
         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
MCR TOTAL
        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
WC TOTAL
          64420.13             -19328.27  -14944.49                  0.00                                    30147.37                             18500.13                         11647.24           0
GRAND TOTAL
       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

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!

[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

[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!).

[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:

replace(A,space(11),"|")

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:

Intrim(B)

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

Replace(C," ","|")

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:

Val(LSplit(D,40,"|",1))

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:

Val(LSplit(D,40,"|",2))

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

[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.

Sam,

There should be a law against that ...

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