You are right, this should not be hard, and the good news the solution is not hard...just requires just a little finesse with Monarch.
I would suggest you use a single-line Detail template to get all invoice and cash data without regard for the second invoice data line. Among the fields you define, include a field that contains "Inv." or "Cash" (call it TYPE, for example).
Then use a Footer trap to trap the second line of invoice data. You might choose to call this data field SECOND INVOICE LINE.
Now when you look in your Monarch Table view, Monarch will not only associate the second line of the invoice data with the corresponding first line of invoice data, but also any immediately preceding cash-related data lines. In this case, we don't want that, so I suggest blanking such data out of your cash-related rows of data using a calculated field similar to this:
IF(TYPE="Cash","",[SECOND INVOICE LINE])
which tells Monarch to display a blank field if the data type is Cash. If it is not, then the data type is Invoice, so it is OK to display the second invoice line data.
I would complete the model job by hiding the original SECOND INVOICE LINE field, as the new calc. field correctly displays the second invoice line data for only the Invoice lines the way you want. smile.gif[/img]
[size="1"][ June 23, 2003, 01:23 PM: Message edited by: Mike Urbonas ][/size]
Mike's solution is clearly the best for your needs but playing around with the problem it occurred to me that there might be a requirement from time to time for something similar but with the need to have a different solution if, for example, the Footer feature was required to grab something else.
Using your sample report layout I came up with 2 other approaches that might be considered under those circumstances and offer them as reference.
Firstly, with the sample layout you provided, it is possible to use an 'append' to get the data fields though the associations are somewhat different. However since the fields related to the Cash lines are to be discarded that does not matter anyway. An 'append' which traps on "Inv." at the beginning of a line would also work in conjunction with the calculated field solution method of cleaning up the Cash records.
Secondly, and a little bit more complicated but possibly the only option in some similar circumstances, would be to use the MSRP utility (or a text editor perhaps depending upon file sizes, automation needs and so on) to change the 2 line INV records to single lines and write a new version of the file.
For your sample records that should simply mean replacing any line break 'character' which is followed by 'a number' of spaces to something else which does not include the line break instruction. For example Line Break followed by 10 spaces might simply become 1, 10, or 11 spaces depending upon the result each option might have on the line format, length and so on.
Obviously these are not ideas you need on this occasion but they might come in handy at some other time.
Just goes to show that if it doesnt quite work using a simple model then Calculated fields can be very powerful! [/b][/quote]
[url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000243"]Click here[/url] for another post that may be of use to you if this is an area that is causing you problems.
[size="1"][ May 18, 2006, 04:51 PM: Message edited by: Todd Niemi ][/size]