Multiple line items report

[clemente]

HI I have a question about a report like the following

Clerk 12345
Inv 6788
Date 7/24/2003
Oil 23.25
Subtotal 23.25
Tax 1.41
Total 24.86
Clerk 12345
Inv 6789
Date 7/24/2003
Vinegar 20.00
Salt 10.00
Subtotal 30.00
Tax 1.80
Total 31.80

I want to export it as a DBF for manipulation in a customized audit program.

When I get the database it looks like this
Fld1 fld2 fld3 fld4 fld5
12345 6788 07/24/2003 Oil 23.25
12345 6788 07/24/2004 Subt 23.25
12345 6788 07/24/2005 Tax 1.41
12345 6788 07/24/2006 Total 24.86
12345 6789 07/24/2007 vinegar 20.00
12345 6789 07/24/2008 salt 10.00
12345 6789 07/24/2009 Subt 30.00
12345 6789 07/24/2010 Tax 1.80
12345 6789 07/24/2011 Total 31.80

If I create a summary, with a key field across, I get up 36 fields across or more depending on which key field I select across. This is fine for one or two record but if I have thousands or millions the database program or the report program cannot handle it.

I want a final report/database to show one instance of the clerk, date, invoice, subt, tax and total. Then on the same line/record show each individual item followed by its cost. The order is not important as long as each complete invoice is in one single line.
ex:
fl1 fld2fld3 fld4 fld5 fld6 fld7 fld8 fld9 fld9
clerk inv date sub tax total oil $$.cc salt $$.c

and so on. Can this be done with Monarch 5.2?. We probably won't be upgrading until sometimes next year. Thanks
:cool:

[Grant Perkins]

Hi,

This looks like one where the detail of the report layout might be useful - perhaps there is something that does not appear to be important but could help us separate the data as required.

The format of sample looks interesting. Does the Clerk number really repeat at the start of each section? That would be unusual I would think but of course it all depends upon where the report originates in the programmer's mind. ;)

Also I am not sure how your wished for solution really differs from your 36 fields across summary solution. If you have, say, 15 products each with a price you will need 30 fields across plus the basic record header and subtotals. You could probably concatenate all of them into a single field, for example, but the data size for the field might then be a problem anyway.

What exactly are the constraints that you must avoid for the solution to work?

I would be happy to look at a sample of the real original file if you are able to release it. Send me a Private Message and I will respond with my email address.

Best regards,

Grant


Originally posted by clemente:
HI I have a question about a report like the following

Clerk 12345
Inv 6788
Date 7/24/2003
Oil 23.25
Subtotal 23.25
Tax 1.41
Total 24.86
Clerk 12345
Inv 6789
Date 7/24/2003
Vinegar 20.00
Salt 10.00
Subtotal 30.00
Tax 1.80
Total 31.80

I want to export it as a DBF for manipulation in a customized audit program.

When I get the database it looks like this
Fld1 fld2 fld3 fld4 fld5
12345 6788 07/24/2003 Oil 23.25
12345 6788 07/24/2004 Subt 23.25
12345 6788 07/24/2005 Tax 1.41
12345 6788 07/24/2006 Total 24.86
12345 6789 07/24/2007 vinegar 20.00
12345 6789 07/24/2008 salt 10.00
12345 6789 07/24/2009 Subt 30.00
12345 6789 07/24/2010 Tax 1.80
12345 6789 07/24/2011 Total 31.80

If I create a summary, with a key field across, I get up 36 fields across or more depending on which key field I select across. This is fine for one or two record but if I have thousands or millions the database program or the report program cannot handle it.

I want a final report/database to show one instance of the clerk, date, invoice, subt, tax and total. Then on the same line/record show each individual item followed by its cost. The order is not important as long as each complete invoice is in one single line.
ex:
fl1 fld2fld3 fld4 fld5 fld6 fld7 fld8 fld9 fld9
clerk inv date sub tax total oil $$.cc salt $$.c

and so on. Can this be done with Monarch 5.2?. We probably won't be upgrading until sometimes next year. Thanks
:cool:

[Mike Urbonas]

This will get you most of the way:

1. In Report View, define a Footer trap, highlighting an instance of Subtotal, Tax and Total (3 rows). You can trap based on the presence of the word Subtotal in the first line. Define fields for Subtotal, Tax and Total.
2. Create a Page Header (or Append) trap, highlighting an instance of Clerk and Inv (2 rows). Define fields for Clerk and Inv.
3. Now create a Detail trap, highlighting ONLY a Date line. You can trap based on the presence of the word Date. Define a field containing the word "Date" AND the date itself (ie, your field will contain "Date 7/24/2003"). Call this field: DATE AND ITEM(S).
4. Double-Click or Right-Click on this field to open the Field Properties wiondow. Click on the Advanced tab. You want to End Field On "Minimum Action only"-when another data field is encountered. (For V7 you click the "None of the Above" End Field On option.

This is what your Monarch Table will look like:

CLERK INV SUBTOTAL TAX TOTAL DATE AND ITEM(S)
12345 6788 23.25 1.41 24.86 Date 7/24/2003 Oil 23.25
12345 6789 30.00 1.80 31.80 Date 7/24/2003 Vinegar 20.00 Salt 10.00 That gets you 85% there... smile.gif

Now you can define some Calculated FIelds to (1) Pull the Date into its own column/field and (2) Use, most likely, the LSPLIT command to break apart the rest of this field into individual columns/fields.

Be sure to look at the upcoming Monarch Report newsletter, coming out later this week, which also utilizes the Advanced Field Properties to include in a desired field data that spans more than one row of the report.


Let me know if this helps,
Mike

[ August 19, 2003, 11:49 AM: Message edited by: Mike Urbonas ]

[clemente]

Thanks it looks like I can use the "minimun action only" option .
There are only a couple of things.
Sometimes there is no Tax line. The problem is that Monarch shifts this field down depending on when it encounters this line.
Then the entire database is wrong since each invoice amount is shifted. invoices cannot be verified. Is there a procedure for making sure that each

[Grant Perkins]

Clemente,

Did you try the models I sent by email?

Did you find a problem, either with a model or with the process I suggested?

Grant



Originally posted by clemente:
Thanks it looks like I can use the "minimun action only" option .
There are only a couple of things.
Sometimes there is no Tax line. The problem is that Monarch shifts this field down depending on when it encounters this line.
Then the entire database is wrong since each invoice amount is shifted. invoices cannot be verified. Is there a procedure for making sure that each

[clemente]

Grant your models were beautiful and I was able to complete the project. BTW did I say thanks. If I did not please accept my delayed "Thanks". I was just trying someone else's reply but when I try, the numbers do not line up correctly. I also noticed that if you try to capture both the subtotal, the tax and the invoice number then the numbers do not lineup correctly. I am not sure if it is something I am doing or if it is a Monarch thing.

Again Thanks smile.gif