Many to One provides wrong answer

[PhilipOfOrange]

I am writing a tax report, based on invoices. The lead table (from which all links originate) is the “invoice item” table and it is linked into the “invoice” table that contains the information about the invoice as a whole (customer number, ship to, method of payment, etc.). The tables keep track of up to five different sales taxes (such as federal, state, county, city or a special jurisdiction). The report must display the taxable sales for each type of tax and the tax collected.

Only the “invoice item” table can provide the amount of the sales for a given tax type because it depends on what was sold as to whether or not it was taxable. Taxes overlap, so the sale of an item may not be taxed at all or it may have one or more taxes applied to it. Taxes are rounded up at the invoice level (at the time the invoice was corrected) for each of the accumulated taxes on the items according to tax laws, which to my knowledge, are universal in requiring the round up on any fraction cent regardless of how small the fraction may be. This means that the taxes collected will be slightly more than the tax rate times the taxable sales. Taxing authorities want to know the taxable sales and the tax collected. Consequently, only the “invoice” table can provide the amount of the tax collected.

Now here is the problem, when Crystal steps through the item table, summing up the sales, it also sums up the tax collected in the linked invoice table. If there are three items on the invoice, it will correctly report the taxable sales but it will triple the amount of the tax collected. How do I prevent this, without recalculating the figures in the invoice table, which I prefer not to do as a defense against integrity issues, like missing records.

 

[dgillz]

Try putting a running total count field on the line items by invoice, evaluate on every record, reset change of group-invoice. Then create formulas that sum up the taxes once, when count=1.

Sidebar: I am at a loss as to how the invoice table can give you the tax amount COLLECTED, as opposed to the tax amount INVOICED. If you meant invocied, fine, but as a former controller, this is critical. If you actually do not collect the tax for some reason - company goes out of business, etc. - you do not have to remit the tax to the taxing authorities. There is a line on your sales & use tax return for just such events.

I am not sure what your report is designed to do, but if I were placed in your shoes right now, I would go back to whomever is asking for the report and run this issue by them.
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[IdoMillet]

The simplest solution is to sum up the tax collected in the linked invoice table using a running total that is set to evaluate "On Change of Field": Invoice_Number (assuming Invoice_Number in the unique key of the Invoice table.

This would ensure that the total accumulates only once
for each Invoice.

Cheers,
- Ido ixm7@psu.edu

 

[PhilipOfOrange]

To IdoMillet:
I liked your answer best because it is simpler and I will use it. I would like to give it a star but don't know how.

To gdillz: Your answer would work too. I very much appreciate the time and effort you both took to offer solutions.

In regard to your question about whether we are using the correct approach, we are a POS company that develops, sells and supports our product. You raise some valid issues but in a retail environment, most of the sales are tendered in a way that makes tax collection automatic. For those customers that use house accounts, which we support, their accounting professionals would be aware that our tax report does not take uncollected accounts into consideration. For those that need a more rigorous approach, we provide a data export facility to Quick Books.

 

[IdoMillet]

If you don't know how to start creating a running total,
click on Insert, Running Total Field..., New,
Select the tax field in the Invoice table as the
Field to summarize and set the "Evaluate" option
as I had indicated above.

Cheers,
- Ido
ixm7@psu.edu

 

[PhilipOfOrange]

I used the running total, just as you said. It worked great. I was aware of it but I hadn't used it much and it seemed redundant in that it seemed to duplicate sub-totals,etc. and frankly I forgot that it existed. Now that I have a good use for it, I'm sure that I'll be using it more in the future. Thanks.

 

[dgillz]

I agree Ido's answer was better than mine.

Philip are you in Orange, CA? It used to be one of my stomping grounds. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office