1 2 Previous Next 20 Replies Latest reply on Aug 22, 2011 10:56 AM by philmodjunk

    Invoice Template "TAX"

    WilfriedSip

      Title

      Invoice Template "TAX"

      Post

      Hello,

      We have a problem with the invoice template on the Tax.

      In the invoice we want to have 2 different taxes. High tax 19% and Low tax 6%

      It is the meaning that when we make the invoice that automatic the invoice calculate how much tax low and how much tax high is on the bill.

      We have different products and 2 taxes is it possible to make that on the invoice that if we print them that our clients can see what product is high tax and low tax and that below by the subtotal they can see how much high tax they pay and how much low tax they pay.

      Hope you can understand what i mean and that you can help me.

        • 1. Re: Invoice Template "TAX"
          philmodjunk

          Do you mean that some products are taxed at 19% and the others are taxed at 6%?

          If so, you can add the rate as a field in Products and add a field in Line items to look up the tax rate. A calculation field in line items can then use this looked value (see how prices are looked up in the starter solution to see how this works) to compute the tax amount for each item and a summary field in line items can compute the total tax for the invoice.

          • 2. Re: Invoice Template "TAX"
            WilfriedSip

            Thanks i will try this, i am totally new here so i must find out how to put everything in there.

            you mean that i must put in the product table a field with 6 and 19%. that means 2 fields.

            Then in Line Items also add a field then with auto-enter a look up to the rate tax.

            A calculation field in Line Items with a looked Up Value (example by looked up in the starter solution).

            Then a summary field in Line Items for the total tax.

            Is this correct or do i miss somethings.

            Thanks for all the help.

            • 3. Re: Invoice Template "TAX"
              philmodjunk

              Just use one number field in Products for the tax rate, but then edit the data in your record so that you can type in .06 or .19 into each field for each product record.

              One reason for doing things this way is that future changes in your tax rate or structure can usually be handled simply by editing the data in your products table.

              • 4. Re: Invoice Template "TAX"
                WilfriedSip

                I made a field in products with data .06 and .19.

                Made a field in Line Items that have a look up to the field in products.

                A calculation field in Line Items i understand but don't know what to put in the calculation. sorry

                • 5. Re: Invoice Template "TAX"
                  philmodjunk

                  You'll need to use the actual fields in LineItems as I am not taking the time to look up each exact field name, but a calculation such as:

                  Quantity * Unit Price * LookedUptaxRate would compute the tax for each line item.

                  Quantity * Unit Price * ( 1 + LookeUptaxRate ) would compute the total cost (price plus tax) for each line item.

                  To add a new field for this in LineItems, you can Open manage | database | Fields, select Line items from the table drop down, then enter the field name and select "Calculation" from the type drop down. You'll see a dialog box appear where you can build the needed calculation.

                  BTW, between this thread on your question on pricing, I can see that you have a lot to learn about FileMaker. While I'm glad to help out, it would be a good idea to invest some time in some training tutorials and/or a book on FileMaker to help you get a basic understanding of how to use this program. This additional training will help you better understand the suggestions offered to you here in this forum.

                  • 6. Re: Invoice Template "TAX"
                    WilfriedSip

                    Thanks for the help but i don't get it to work.

                    The other one you help me is working but the tax is difficult.

                    If i now put in the invoice a product then he will give me nice 19% but over that product but he also give me 19% over the product that must be 6% it depends on with product is standing on top of the invoice. If the product from 6% is on top then everything is 6%.

                    In the invoice i made 2 fields with tax high and tax low so the client can see how much tax they paid on high and how much on low but that doesn't work yet.

                    Please help me to make this invoice work perfect same as the price by customer. That one is working very good. Thank for you help for that.

                    • 7. Re: Invoice Template "TAX"
                      philmodjunk

                      So far, all we have discussed here is how to compute the taxable amoung for each row in the portal. Totals for each tax rate are something that we have yet to set up here.

                      Let's add one more fields in Products and also in LineItems. We'll name them "TaxType". Set TaxType in LineItems to look up the TaxType in products. On a Products layout, you can place the TaxType field from products and use a value list (Radio buttons would be good) so you can quicly and easily identify each product as either "high" or "low" tax types.

                      Now define a summary field that computes the total of our calculation field that computes the tax amount. (See previous post.)

                      This method only works with FileMaker 11. If you are using an older version, let me know and I'll describe a different approach that will work for the older versions.

                      Add a new portal to LineItems on your Invoices layout. Make this only a one row portal. Click the Portal Filter option and enter this calculation:

                      LineItems::TaxType = "High"

                      Place your new summary field inside this portal's single row. You can change the "pen" settings for this portal to make the portal borders invisible so that this single row portal looks like just a single field on your invoices layout. The summar field on this layout will show the total for all High tax rate items.

                      Once you have this portal in place and working for you, you can make a copy of it, but change the Portal Filter expression to be:
                      LineItems::TaxType = "Low"

                      to show the total low tax paid on this invoice.

                      On your data entry layout, these two sub totals will not update smoothly without a bit of extra help. In layout mode, select the original LineItems portal and select the OnObjectExit trigger to perform this script:

                      Commit Record
                      Refresh Window [Flush Cached Join Results]

                      Note: on the print layout for starter solution, you can add a sub summary part when sorted by LineItems::TaxType and then can update the print invoice script to sort your line item records by tax type. This will group the records by tax type to make the invoice easier to read and you can put the summary field inside such a sub summary part to display a sub total for each group.

                      Here's a tutorial on summary reports you may find helpful:  Creating Filemaker Pro summary reports--Tutorial

                       

                      • 8. Re: Invoice Template "TAX"
                        WilfriedSip

                        I tried my best but i can't get it to work, i think something with my calculation is not good. And i have FM 11.

                        I made in the invoice 2 fields one for 6% and one for 19%.

                        If i put on the first line a product with 6% he shows me nice 6% in the correct field 6% and he give me the right calculation.

                        If i put on the first line a product with 19% he shows me nice 19% in the correct field 19% and he give me the right calculation.

                        So far everything work good, but. If i put in the first line a product with 6% he shows everything perfectly and then i put in the second line a prodcut with 6% everything goes well. and if i put in the second line a product with 19% then he still calculate 6% over the product and the field for 19% stays empty.

                        So as soon as i put 2 different Tax rates in there it goes wrong and i try everything what i can think about and still it will not calculate 2 different taxes.

                        I know i do something wrong but i can't figur out what it is.

                        • 9. Re: Invoice Template "TAX"
                          philmodjunk

                          I made in the invoice 2 fields one for 6% and one for 19%.

                          This is NOT what I suggested that you do here. Please read my previous posts again.

                          • 10. Re: Invoice Template "TAX"
                            WilfriedSip

                            Now define a summary field that computes the total of our calculation field that computes the tax amount. (See previous post.)

                            No matter how i read it and look back of our conversation i can not find it out.

                            • 11. Re: Invoice Template "TAX"
                              philmodjunk

                              That summary field is ONE field and it's defined in LineItems--not invoices.

                              Then you use one row Portals to lineitems on the Invoices layout, each with different filter expression, so that one the same summary field in one portal displays the high tax total and in the other portal it displays the low tax total.

                              • 12. Re: Invoice Template "TAX"
                                WilfriedSip

                                Thank for all the information, but so far everything works, i only have 1 problem.

                                If i put on the first line a product with 19% it shows me nice the 19%

                                If i put on the second line a product with 6% it show me nice the 6%

                                But if i then put on the 3 line and more it will not calculate the % anymore. Only the first 2 lines are working.

                                • 13. Re: Invoice Template "TAX"
                                  philmodjunk

                                  Are you using a script to refresh the window?

                                  From a previous post:

                                  On your data entry layout, these two sub totals will not update smoothly without a bit of extra help. In layout mode, select the original LineItems portal and select the OnObjectExit trigger to perform this script:

                                  Commit Record
                                  Refresh Window [Flush Cached Join Results]

                                  • 14. Re: Invoice Template "TAX"
                                    WilfriedSip

                                    Everything is working now, thank you very much for your help.

                                    All the calculation are perfect.

                                    One question there is no other way to let the script go automatic?

                                    If i now fill something in then i must click on the script to do the calculation, i put the script in all fields what you said but i can only run the script manual.

                                    Thanks again for everything.

                                    1 2 Previous Next