13 Replies Latest reply on Jan 22, 2014 9:58 AM by fabiuz

    How to sum VAT when there are more then one percentages

    fabiuz

      Title

      How to sum VAT when there are more then one percentages

      Post


           Hi,

           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.

           Thanks!

            

      vat.png

        • 1. Re: How to sum VAT when there are more then one percentages
          philmodjunk

               IF you had a table of VAT percentages, a global field and a script could enable this relationship to work:

               InvoiceRows::vatPercentage = VATs::vatPercentage

               VATs::gInvoiceID = InvoiceRow_currentVATPercentage::InvoiceID AND
               VATs::vatPercentage = InvoiceRow_currentVATPercentage::vatPercentage

               gInvoiceId would be a global field with the same data type as InvoiceID. The trick is to use OnRecordLoad on your Invoice layout to perform this script:

               Set Field [VATs::gInvoiceID ; Invoice::InvoiceID ]

               so that VATs matches to the correct set of InvoiceRow records. A portal to InvoiceRow_currentVATPercentage will then list each VAT used and a sum field in VATs can compute the totals needed. For printing/Preview/Save as PDF purposes, you can make this portal extra tall but set it to slide up/resize enclosing part so that only the number of rows needed for a given invoice are shown.

               But if you are using FileMaker 12 or newer and are willing to try using SQL, you can also use ExecuteSQL to produce this data: FMP 12 Tip: Summary Recaps (Portal Subtotals)

          • 2. Re: How to sum VAT when there are more then one percentages
            fabiuz

                 Thanks for the quick reply!

                 I am on FM11 Pro Adv, I cannot use the Execute SQL way,

                 I have now tried to add one more calculation field to the InvoiceRow table:

                 If ( invoiceRow::rowID = invoiceRow_currentVatPercentage::rowID ; 1 ; 0 )

                 in this way it should mark 1 all unique values of VAT percentages and their total amount.

                 If it is correct till here, I only need a way to select these rows where unique = 1 and may be use the List function to show them in layout or reports, may be by adding a calculation field into invoice table.

                 Could it be?

                 I am attaching here my test database, I have renamed it .gif, I don't know it this will work.

                 I will try your suggestion too, I hope I can understard how to implement it...

                 thanks!

            • 3. Re: How to sum VAT when there are more then one percentages
              philmodjunk

                   Good idea. I've used that trick via a portal filter to show only one unique instance of each value. But I set it up this way as a portal filter expression, no calculation field needed:

                   InvoiceRow::RowID = InvoiceRow_currentVATPercentage::RowID

                   and used this with a portal to InvoiceRow.

              • 4. Re: How to sum VAT when there are more then one percentages
                fabiuz

                     ok, now I have understood: I can put in one more portal and format it for my lay out so that values I need are there already without any other coding.

                     Seems good!

                     What I still don't understand, I am honest, is how does this relation work:

                InvoiceRow::RowID = InvoiceRow_currentVATPercentage::RowID

                from the InvoiceRow point of view, I am saying look at your table "copy"/yourself, anche compare each RowID value with your table copyID, when they are equal the value is 1, 0 otherwise.

                But shouldn't it evaluate each invoice row? When VAT is 22%, if I have two rows with that it should result in 1 for both when sencond row turn comes :-?

                     I am missing something in this... can you explain how does it work in FM or DBs mind? 

                      

                thanks!

                • 5. Re: How to sum VAT when there are more then one percentages
                  philmodjunk

                  InvoiceRow::RowID = InvoiceRow_currentVATPercentage::RowID

                  is a portal filter expression that you specify in Portal Setup.

                  Whenever you have a one to many relationship and you reference a single field on the "many" side  (Invoice_rowcurrentVAT...) from the context of the "one" side (InvoiceRow, your portal), it refers to the first related record. Thus, if you have 5 related records with a VAT of 0.2, the above reference will refer to the RowID field in InvoiceRow_CurrentVAT... of the same record in all 5 InvoiceRow records. The RowIDs will be equal in only one of the 5 and thus the filter drops out all but one of the related records. This then gives you one row for each VAT percentage used in this invoice.

                  But a calculation field in InvoiceRow that uses Sum to compute the needed sub total takes place at the data level. How it evaluates is strictly determined by the relationship graph and the "context table" you select at the top of the Specify Calculation dialog. Any portal filtering, which takes place on your layout, will have no effect in how that calculation field evaluates so you'll still get a sum of all the Invoice rows that are from the same invoice and that also have the same VAT percentage.

                  • 6. Re: How to sum VAT when there are more then one percentages
                    fabiuz

                         Thank you very much for the explaination!

                         I was still using the unnecessary calculation field instead af the direct portal filter.

                         I see from the graph that a crow feet is on both tables so I was supposin it was not a one to many relation. I am still not very comfortable with this behaviour understanding :-( I will think more on it...

                         About the result, it works for the standard layout but it seems not when I click on Preview: portal rows are always completely blank... mhmm....

                         I am going to try build a new report to see if I can fix this....

                    • 7. Re: How to sum VAT when there are more then one percentages
                      philmodjunk

                           When you preview, make sure that you are previewing the same record that you have up on your screen in browse mode. Preview previews the first record in your found set and you have to click the page control in the status tool bar to see the preview for other pages/records. It's like FileMaker Preview assumes that you are going to be using "records being browsed" as your print option.

                           My comments on how this work were guilty of a bit of oversimplification. This isn't a true one to many relationship in the classic text book sense, but it functions like one in the case of our portal filter.

                           The "crows feet" are FileMaker's "guess" as to what the relationship will be. Unless field options specify "unique values" or an auto-entered serial number, it can't tell that the relationship will function as a one to many or many to one relationship. When there are "crow's feet" on both ends of the relationship like this, I tend to think of it as an "Indeterminate many to many" relationship (My jargon). A single record in table 1 can link to many records in table 2 and a single record in table 2 can link to many records in table 1, but you can't have both like you can with a true "Many to many" relationship implemented with either a join table or a Multivalue match field.

                      • 8. Re: How to sum VAT when there are more then one percentages
                        fabiuz

                             Uhm, I am locked again with the Report :-/

                             The Invoice report has to be created from the context of invoiceRow, so it is broken the portal relation to show VATs and Vat Partial Sums.

                             I think I need a related invoiceRow table instance, or may be a TOG just to show the VATs portal... but I should also relate invoiceID between TOG and the current report... seems to complicated to be the right way... mhmmhm..

                             I could create an invoiceRow table selfjoin using invoiceRowId... but then I am missing the other selfoin relation on VAT and invoiceID... may be I have to replicate that too and then get data from there. Could it be?

                              

                             ...mhm.. no, only one record is shown.

                        • 9. Re: How to sum VAT when there are more then one percentages
                          fabiuz

                               Ok thanks!! That was the problem: I was on the wrong record in preview and thank you for the detailed explanation, now is more clear!

                               The real problem in the Report seems the fact that the context has changed to invoiceRow and I still didn't find the way to replicate the little portal for the VATs...

                          • 10. Re: How to sum VAT when there are more then one percentages
                            philmodjunk

                                 Printing from a list view layout based on the portal is definitely the better way to go for printing, but this would require a different relationship design in order to get your portal to work. I suggest two table occurrences of InvoiceRow linked to InvoiceRow instead of one. The first would link by InvoiceID to InvoiceRow and the second would link to the first by VAT percentage and InvoiceID.

                                 Invoic----<InvoiceRow>---<InvoiceRow|SameInvoice>----<InvoiceRow|SameInvoiceAndVAT

                            • 11. Re: How to sum VAT when there are more then one percentages
                              fabiuz

                                   Yes! Like I supposed it has perfectly worked!

                                   BUT, why I wasn't able to reach to the same solution by my self!? That is starting to become frustrating for me!!

                                   You have saved my day! Thank a lot! I am going to bed happy tonight and hope to understand more tomorrow when I will repeat all!

                              • 12. Re: How to sum VAT when there are more then one percentages
                                philmodjunk

                                     BUT, why I wasn't able to reach to the same solution by my self!?

                                     That's a question only you can answer, but I will gently point out two things:

                                       
                                1.           I didn't do it by myself either. The final solution here came from an idea you posted. I just helped you resolve the final details.
                                2.      
                                3.           I started out with a BS in computer programming back when I first discovered this product called FileMaker Pro 2.5 produced by a company named Claris. I've learned  trick or two about FileMaker and database design over the intervening years...wink
                                • 13. Re: How to sum VAT when there are more then one percentages
                                  fabiuz

                                       Thanks Phil!