1 2 Previous Next 17 Replies Latest reply on Jan 5, 2012 6:41 AM by DennisBeaupre

    changing  sales tax

    DennisBeaupre

      Title

      changing  sales tax

      Post

      I'm not sure how to make new tax changes, with out effecting previous tax rates.

      My new tax rate is 7.25%.

      Here is my old calulation:

      If( Creation Date < Date( 01 , 01 , 2010 ) , Round(Cat quantity tax * .0650, 2) + Round(Cat quantity discount tax * .0650,2), Round(Cat quantity tax * .0675, 2) + Round(Cat quantity discount tax * .0675,2))

       

      I'd like before 01 , 01 , 2010 to be 6.50%

      After 01 , 01 , 2010 to 12 , 31 , 2011  to be 6.75%

      and new rate starting 01 , 01 , 2012, to be 7.25% 

       

      Hope this makes sence to someone!

      Thanks

      Denbeau53

        • 1. Re: changing  sales tax
          philmodjunk

          Putting sales tax into a calculation field like this sets you up with the need to modify the calculation definition every time the tax rate changes. You would be better off putting the tax rate in a table where each new invoice looks up the rate (copies it). Then you can just edit the tax rate in this one record and new invoices will look up the new rate while existing invoices stay unchanged.

          What version of FileMaker are you using? (Current versions would have ; where you have , in your calculation, so I am guessing you are using a much older version of FileMaker here.)

          The trick would be to replace your current calculation field with a number field that with a looked up value setting to copy tax rates, without messing up old records. The details on how to do that may turn out to be version specific so I need to konw the version before I go further with this.

          • 2. Re: changing  sales tax
            DennisBeaupre

            Fiemaker Pro 5  

            I had someone set it up for me many years ago.  It's been working fine so never felt the need to upgrade. I was able to make the last tax change 2 years ago, but I cant figure it this time. I'm open to setting up a table, but I dont know how.

            Thanks

            Denbeau53

             

            • 3. Re: changing  sales tax
              philmodjunk

              For future reference, please tell people you are using FileMaker 5 (5.5 maybe?) when you ask for help here. FileMaker has changed a lot over the years and we could easily spend a great deal of time suggesting a solution that works for FileMaker 11--the latest release but which fails miserably for fileMaker 5.

              I'm also concerned that you current calculation does not correctly return the needed sales tax. It would appear to double the tax amount as it computes the same percentage twice, rounds them separately and then adds them together. The calculation is computing approximately a tax at a rate of 0.13 (13%) for dates prior to January 1st, 2010 and 0.135 (13.5%) after 12/31/2009.

              Here's the short term "fix" for your calculation:

              Case ( Creation Date < Date( 01 , 01 , 2010 ) , Round ( Cat quantity tax * .0650 , 2 ) ,
                        Creation Date < Date( 01 , 01 , 2012 ) , Round(Cat quantity tax * .0675, 2),
                        Round ( Cat quantity tax * .0725, 2 )
              )

              To avoid having to update this calculation every time the rate is changed, First make a back up copy of your file, then add a brand new number field, taxRate, to your file. Put this field on a layout in the file. Perform a find for all records with creation date < 1/1/2010. Use Replace Field contents to put 0.0650 in all of those records. Perform Select Show Omitted from the records menu to pull up the records created after 12/31/2009. Use replace field contents to put 0.0675 in the taxRate field. Now change your calculation shown above to be:

              Round ( cat quantity tax * taxRate ; 2 )

              The values computed in each record should remain unchanged at this point. Now to make this something where you change one field in one spot and the new rate is used for all future records but existing records remain unchanged.

              Open Define Fields and add a new field, gSalesTaxRate. Make this a global number field by selecting "global" as the field type and then specify number in the dialog that pops up. Find the TaxRate field you added earlier and double click it. Select the auto-enter tab, then click the "by calculation" radio button and specify gSalesTaxRate as the sole term in this calculation. This way, each time you create a new record it automatically copies the contents of gSalesTaxRate into TaxRate. Changing the tax rate now requires simply editing this global field. (Note: if you are hosting this DB over a network, take the file down off the server, open it with fileMaker Pro update the field and then put it back up on the server or a change made to a global field will not persist after closing the file on the client machine.)

              Also, you should be warned that the time is fast approaching where you will be unable to replace your current computer with one that can still run Filemaker 5. You should upgrade to a newer version as soon as you can to avoid being stuck with a non functioning database should your current machine suffer a system failure.

              • 4. Re: changing  sales tax
                DennisBeaupre

                Sorry I forgot to tell you which version. I will in future. 

                The old calculation, did apply the tax correctly. I've had the old tax rate memorized would have noticed if Filemaker was figuring it in-correctly.

                I will try your quick fix and study the longer fix.

                Do I put it in exactly like this?

                Case ( Creation Date < Date( 01 , 01 , 2010 ) , Round ( Cat quantity tax * .0650 , 2 ) ,
                          Creation Date < Date( 01 , 01 , 2012 ) , Round(Cat quantity tax * .0675, 2),
                          Round ( Cat quantity tax * .0725, 2 ))

                Does the current version open these old .FP5 files?

                Thanks

                Denbeau53

                • 5. Re: changing  sales tax
                  DennisBeaupre

                  Case ( Creation Date < Date( 01 , 01 , 2010 ) , Round ( Cat quantity tax * .0650 , 2 ) ,
                            Creation Date < Date( 01 , 01 , 2012 ) , Round(Cat quantity tax * .0675, 2),
                            Round ( Cat quantity tax * .0725, 2 ))

                   didnt work.....?

                  • 6. Re: changing  sales tax
                    philmodjunk

                    Round(Cat quantity tax * .0675, 2) + Round(Cat quantity tax * .0675, 2)

                    Mathematically, it's identical to this expression: 2 * Round(Cat quantity tax * .0675, 2)

                    It doubles the applied tax. Can you explain why it was set up this way in your original calculation? I can see no way for that expression to correctly compute the tax amount as you have indicated that it does. I have to wonder if something else unsual was set up in other calculations that made doubling this amount like this necessary...

                    FileMaker 11 files can convert Fp5 files. You can download the 30 day free trial and see what happens when you launch the new version and then use open from the file menu to open your current set of fp5 files. (For better results, open them all for conversion at the same time.)

                    Here's a knowlege base article on file conversion that you'll want to read:  http://help.filemaker.com/app/answers/detail/a_id/750/kw/converting

                    • 7. Re: changing  sales tax
                      DennisBeaupre

                      I made some ajustments to old calulation.

                      If( Creation Date < Date( 01 , 01 , 2012 ) , Round(Cat quantity tax * .0675, 2) + Round(Cat quantity discount tax * .0675,2), Round(Cat quantity tax * .0725, 2) + Round(Cat quantity discount tax * .0725,2))

                      And it works!

                      But will not work correctly with files older than 2010. So I backed them up renamed them and removed them from my working data base.

                      I can open it if I need to refer back. current data base with active accounts is good for the last 2 years and the new year.

                      I bet this is what I did the last time....

                      Thanks

                      Denbeau53

                      • 8. Re: changing  sales tax
                        philmodjunk

                        Why not use the case function as I suggested so that this works for all your data? The only issue is why you need to double the taxable amount in this calculation field in order to get the correct values to appear in your database--which has nothing to do with using Case instead of IF. If you truly have to double the amount like this, you can do that with the case function just like you did with IF, but now it handles all three time periods correctly without having to save a separate copy of your data.

                        • 9. Re: changing  sales tax
                          DennisBeaupre

                          20 years a go when I took over the business, I had a Filemaker setup person, custimize the program for our needs. She is long gone and  I've make minor changes since but not very confident on big stuff.

                          Den

                          • 10. Re: changing  sales tax
                            DennisBeaupre

                            Are you interrested in seeing a empty copy of my data base?

                            For now I'm going with this last one that is working.

                            I appreciate your help.

                            Den

                            • 11. Re: changing  sales tax
                              philmodjunk

                              I confess to being intrigued as to why your calculation doubles the tax amount like this.

                              You can post a clone of it to a file sharing site and then put the download link for it here. I have a copy of FileMaker 5.5 so I can probably open it without having to convert it. Are you sure that this is only one file and not a group of files? That was pretty much a given for any database system from FileMaker 6 and older.

                              • 12. Re: changing  sales tax
                                DennisBeaupre

                                We do have 3 files. But only 1 is for entering invoices and calculating tax.  2nd is a customer list, no trans actions, and the 3rd is a product list, so we can can type in a code into the 1st and it looks up and fills in the item.

                                I'm checking out Google for filesharing. I'll get back to you.

                                Den

                                • 13. Re: changing  sales tax
                                  DennisBeaupre

                                  Phil, try this.  Once you get it let me know, I'll want to delete it after you get it.

                                  https://docs.google.com/open?id=0B2CAocDJ9hk1MzlmNTRiNmYtOWVmMi00ZjQwLWFkZDUtOWU0YTJkOTYzNDhi

                                  • 14. Re: changing  sales tax
                                    philmodjunk

                                    I've downloaded the file, but need a full access password in order to look at its design. You can post the password here or send it to me in a private message. (Click the rainbow folder to the right left of this post.)

                                    1 2 Previous Next