AnsweredAssumed Answered

How to sum VAT when there are more then one percentages

Question asked by fabiuz on Jan 21, 2014
Latest reply on Jan 22, 2014 by fabiuz


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.