8 Replies Latest reply on Dec 30, 2009 1:04 PM by RickWhitelaw

    Change tax with out changing past tax

    Denbeau

      Title

      Change tax with out changing past tax

      Post

      I have a small business(florist) and our sales tax has gone up for the fist time in years. from 6.5% to 6.75%. 

      I'm using Filemaker 5, I know its old but it works fine for us. The problem is that I'm not sure how to change the tax with out changing all the taxes from the past too. 

      Here is may formula the field is a  calulaction.

       

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

       

      By the way I didnt set this up so I'm not sure how we got to this point. 

      Thanks 

      Dennis 

       

        • 1. Re: Change tax with out changing past tax
          Denbeau
            

          Ok I just read the instructions to post....

          I'm using Win XP home.

          This is a data base that was setup for me by a Filemaker programer years ago. I post my sales and print statements. 

          I have made some ajustments in "Define fields" but dont really know what I'm doing. 

          I cant afford to hire someone to fix this, so I'm hoping for help here. :-)

          I have made a copy for test purpores. 

          Thanks 

          Dennis 

          • 3. Re: Change tax with out changing past tax
            Denbeau
              

            I have read some comments about doing a list then a lookup. But I dont know how to start?

             

            • 4. Re: Change tax with out changing past tax
              Denbeau
                 I think I know where to setup the Values List but not sure the of the language/formula?
              • 5. Re: Change tax with out changing past tax
                philmodjunk
                  

                The key suggestion in Comment's suggested thread is that you don't put the actual value in directly into a calculation. Instead, you store such rates in a related table and use either a looked up value or auto-entered calculation to copy this data into a field in your current table (file in you case since you are using FMP 5). Your calculation would refer to this field. Since the lookup copies data from a second table, past records will not be affected by a new change in the tax rate.

                 

                Create a new file to store your tax rate and define a relationship to it.

                Define a number field that looks up the tax rate from your file.

                Replace your explicit tax rate with this new field.

                 

                (You will have to update existing records so that they compute the same original tax, but that's not too hard to do.)

                • 6. Re: Change tax with out changing past tax
                  RickWhitelaw
                    

                  Lookups vs. enter each time or enter last value used. . . . my business data structure is, in some ways, based on a contract to contract cycle. I live in Canada and we've seen two major changes to the GST (Goods and Services Tax) rate since I began using my solution. I've always kept rates like pension percentage, GST, scale, in fact all "rates" as contract to contract variables. That said, when a new record is created it fills in the last used value in these fields, so this method doesn't represent a hardship. The net difference between this method and a lookup is nil. There's certainly no difference in storage requirements nor in convenience. The important issue is: whether by lookup, manual entry, or auto-enter, rates should be accounted for on a record to record basis. What that record consists of depends on the units your endeavor needs. For mine the unit is a contract with variable start and end dates, number of services etc. Either way also offers similar opportunities to update records previously entered or calculated with a static rate. If using "re-lookup" to update it's important to be very exact as to what constitutes the current found set before doing a re-lookup (or a replace).

                   

                  My two cents . . . 

                   

                  RW 

                  • 7. Re: Change tax with out changing past tax
                    comment_1
                      

                    RickWhitelaw wrote:

                    That said, when a new record is created it fills in the last used value in these fields, so this method doesn't represent a hardship. The net difference between this method and a lookup is nil.


                    But there is a big difference: if the last visited record happened to be an older record (which isn't unlikely in the period following the change) then a new record will inherit the old tax rate - so user has to constantly watch this and intervene if necessary.

                     

                    For developers that do no want to tackle the lookup from a table of rates, I'd suggest keping the current rate in a global field and auto-entering it into the "local" Rate field. If your solution isn't hosted, then that's it - otherwise you need to launch in single-user mode every time the rate changes.


                    • 8. Re: Change tax with out changing past tax
                      RickWhitelaw
                        

                      Comment wrote 

                       

                      "But there is a big difference: if the last visited record happened to be an older record (which isn't unlikely in the period following the change) then a new record will inherit the old tax rate - so user has to constantly watch this and intervene if necessary."

                       

                      Good point and true of course. However on an often used layout I have five "rate" type fields all from a separate table. When I fill in one, all the others populate accurately. Call me old fashioned, but I'm attached to the notion of a "build"  . . . in my case a build represents a week of a production or two and can involve up to fifty or sixty people. In my situation I find it a good thing to spend a minute or two on a layout and punch in a few numbers. It gives me a chance to notice if there's anything different etc. Of course, rates are only one part of the picture and I'm speaking for myself and my situation only. Your point is well taken though. I should have prefaced my words with "For me" the net difference is nil . . . .

                       

                      RW