tally sales tax in invoice with line items report

[mediarare]

So i have a Invoice with line items, I currently store the sales tax in the invoice file and the transaction reports runs out of the line items file.

I have summary fields for total list price, total discount givin, total selling price and want to show total sales tax.

Since sales tax isn't in my line items file, I can't make a summary on a related field, I'm looking for suggestions on how i could include it.

I'm calculating sales tax on the invoice as a total, not on each line item and if I did place a lookup field in the line items file my tax value would be off.

Any suggestions are welcome because i'm not seeing any way to get the sales tax in as a summary field.

[SlimJim]

From your description the Sales Tax isn't a summary since you have already done that on the invoices. I would think you can just read it back via the Invoice-Item relationship.

[mediarare]

My report returns multiple invoices. I want to provide a total sales tax paid among all invoices, I already have a total list price, total discount given and total selling price(which is total list price - total discount) among all invoices. I accomplished the other three with summary fields in the line items file. I'm not doing tax per item, I'm doing total tax on the invoice, and the tax paid per invoice is in the invoice file.

But when I went to make a summary field for the sales tax in the line items file, I didn't have sales tax as an option because apparently you can't use relation fields when making a summary in FM7?

[SlimJim]

I misunderstood the nature of the report. You are right about Summary fields, it may be that you need to look at this report via a different table. I would guess that proportioning the Sales Tax amongst the items would lead to rounding errors so that option is out - sorry no ideas.

[mediarare]

Thanks for the input, Maybe someone else will have a trick that will apply, but I could not think of anything else myself.

Rounding errors and nabbing the tax per piece rather than per total invoice will have different values.

I'll keep some hope that maybe someone else has a trick but my hopes are lowering

[comment]

if I did place a lookup field in the line items file my tax value would be off.

I don't see why. Eventually, if you want to report on tax from line items, you will need a tax field in line items. I would add a tax rate field, relookup the rate from existing invoices and calculate the tax for each item.

Rounding errors are going to be the same here as they would be in case of calculating the tax in line items first, then summing it up for the invoice. The only difference is in the chronological order of actions.

[mediarare]

A valid point.

Though the way this report is used it can constrain to specific artists(who are the manufacturors of the items) and the invoices can have multiple artists per invoice in which all but who's being searched for would be excluded and would throw off the totals per invoice because of that.

I guess I'll leave it out for now so that my users wont get lost a couple months down the road and ask me why the sales tax tally is weird between doing a date range and doing a date range and constraining to an artist.