How to sum VAT when there are more then one percentages
I have spent the last week thinking and trying about how I could solve this question: I have one invoice table and one invoiceRow table.
I am able to insert invoices items in rows and I can get the invoice total with VAT and without VAT.
My problem happens when I try to find a way to sum distinct values for different VATs listed in the invoice rows.
For example an item could have VAT at 4%, one 10% and two items 22%.
At the end of my layout I need to have the vat list and the single sums:
2% Euro 0,34
10% Euro 1,5
22% Euro 13,4
I have tried to make a selfjoin of the invoiceRow table by using the invoiceID and the VAT fields, in this way I am able to create one more calculation field into invoiceRow table:
Sum ( invoiceRow_currentVatPercentage::vatAmount )
where vatAmount is: price*vatPercentage
Now I can see the sum of the single VAT on each invoice row but I don't know how to capture such information in my layout: I cannot know how many different percetages will be listed.
Could anybody give me some suggestions?
I can eventually publish a demo database somewhere if needed.