I would love to be able to do that, but with your question, I realize I left out a detail in my problem. There are some subtotal and total lines that are unavoidable to capture in the second line. So then it's grabbing the second detail line but there is no matching first line. So my records get out of whack.
Just to clarify, do the values at the end of the first line always contain two sets of "0.00", or do they contain other values in the real report, such as 7.69?
I think that you'll be able to use a two line template. The details of what to capture and how to handle the data might change depending upon the answer to the question.
If they're always literally "0.00" then just don't bother capturing them.
If they do have values, but you don't want include the zeros in your data, then capture those fields and filter out the records with zeros in the table.
Is there a clear way to determine which records are the totals? Can you avoid including them in the captured data, or filter them out if you must capture them along with the regular details?
They sometimes have real numbers. and sorry, if it makes a difference those numbers in the first line that I don't care about are always whole numbers like 0, 1, or 2 etc.
For the total lines, they always say "total" or "subtotal" in the line, so the only way I can figure out how to get rid of them is with a filter looking for those strings. But in the meantime when it grabs these records, it throws off the sequence of how the append line is lining up with the detail. So hear is a more comprehensive example of what my report looks like
MC 50 1213 EIRF 1 0
51.00 5.00 2.25%
MC 50 SUBTOTAL 1 0 (I want to ignore this line)
51.00 5.00 (ignore)
MDBT 52 1213 EIRFMT 3 0
86.00 .00 0.00%
MDBT 52 SUBTOTAL 3 0 (ignore)
86.00 .00 (ignore)
MC TOTAL 4 0 (ignore)
137.00 .00 (ignore)
I want to the output to be
MC 50 1213 EIRF 51.00 5.00
MDBT 52 1213 EIRFMT 86.00 0.00
OK, definitely use a two line detail template; no appends.
Trap each line as a single field, Line1 and Line2.
Filter out the totals:
then use a few calculated fields with LSplit (splitting on spaces) to extract your individual fields from Line1 and Line2.
I'm sure that you'll be done in no time.
Thank you! The two line detail template worked. One last obstacle that I am stuck on!
Unfortunately, the first character field is either 2 or 4 characters. So, the ones that are 2 characters have spaces after them to mess up with my lsplit function. There are also other fields that have spaces in the field itself. If there another function I can use to split that line into fields based on position in the the line, rather than space separators?
Here is the type of data I have after putting it into the 2 line field.
VISA 40 1213 EIRF/MERT1 0 0 .00 .00
MC 50 1213 EIRF/RET C12 0 0 .00 .00
Thanks again for all the help. I eventually arrived at my solution which was to do the one line detail trap, using a 2 single line field to catch the second line stuff. I cleaned up the varying space issues I had using Intrim function (didn't know it existed, but great function to eliminate unwanted excess spaces) and the extracted the fields I wanted using Lsplit. Great learning experience and of course, outcome. Thanks again for the help here.