7 Replies Latest reply on Jul 20, 2009 4:25 PM by Mike_Diehl

    How to make sales tax amount conditional on date?

    bdmsb

      Title

      How to make sales tax amount conditional on date?

      Post

      California sales tax changes by 1% today. My order entry program uses a variable "tax rate" set to ".0775". I need it to be ".0875" now, but I don't want the new value to recalculate everything in the past. I've tried to make the field "tax rate" be a calculation based on the field "Invoice Date", but I'm not getting the result I expect. I've been coasting for years on this program that I wrote literally in the last century. I no longer remember enough to get this to work. Can someone help?

       

      I use this calculation field:    If (Invoice Date < 04/01/2009; .0775; .0875) 

       

      But the tax rate stays .0875 no matter which date I enter into the field "Invoice Date".

       

        • 1. Re: How to make sales tax amount conditional on date?
          raybaudi
            

          Hi bdmsb

           

          FileMaker evaluates that value:  04/01/2009 as two divisions !

           

          So your calc must be:

           

          If ( Invoice Date < Date ( 4 ; 1 ; 2009 ) ; .0775 ; .0875 )

          • 2. Re: How to make sales tax amount conditional on date?
            philmodjunk
              

            Using a calculation field is not what you want. Everytime the government changes the rate, you'll have to redefine the calculation.

            You should store tax rates in a separate table and define a relationship so that the tax rate is copied from that table into a matching field in your sales transaction table every time a new record is created.

             

            One option is to define a lookup option on your tax rate field in your sales transaction table. This is an Auto-enter option you can check out.

            Other developers choose to use a script.

            The user clicks a new invoice button and the script:

            New Record

            Set Field [SalesTable::TaxRate,Taxtable::TaxRate]

             

            copies the data into your new record.

             

            When the tax rate changes, you simple change the value in your tax rate table and each new Sales record will use the new value, but previous transactions will retain their original value.

             

            Since we're talking sales records here, you should be managing price list info the same way.

             

            • 3. Re: How to make sales tax amount conditional on date?
              bdmsb
                

              Wow. That was fast. Your solution works perfectly. Thank you very much.

               

              • 4. Re: How to make sales tax amount conditional on date?
                bdmsb
                  

                PhilModJunk - Your reply makes very good sense, but implementation is beyond my skill level at this instant. I will use the calculation for today and begin to look into your method for a more durable method. Can you direct me to a reference area to understand this technique? I don't know about lookup tables or how you would decide which table entry to apply at any given time. Specifically, if the table has ".0775" and ".0875", what determines which entry to use on a new order?

                 

                Perhaps the answer is too long for this forum.....

                 

                • 5. Re: How to make sales tax amount conditional on date?
                  philmodjunk
                    

                  bdmsb wrote:

                  PhilModJunk - Your reply makes very good sense, but implementation is beyond my skill level at this instant. I will use the calculation for today and begin to look into your method for a more durable method. Can you direct me to a reference area to understand this technique? I don't know about lookup tables or how you would decide which table entry to apply at any given time. Specifically, if the table has ".0775" and ".0875", what determines which entry to use on a new order?

                   

                  Perhaps the answer is too long for this forum.....

                   


                   

                  You can create a table with a single record, the desired tax rate.

                  When the rate changes, you change the value of the rate field in that one record.

                  Keep in mind that the actual rate in a given sales transaction is stored in the transaction record in a different field. It won't change existing records when you change the rate field in the 2nd table.

                   

                  To set this up as a look up operation: (That's simplest)

                   

                  Try this out on a copy of your database:

                  Let's call your existing table "Invoices"

                   

                  Select Manage | Database | Fields

                  Select the Invoices table

                  Create a new number field "SalesTax"--we'll come back to this field later.

                  Edit any calculation fields that need the sales tax to use SalesTax in place of the current calculation field.

                   

                  Click the Tables tab

                  Enter TaxRate as the name of a new table and click the Create button.

                  Click the fields tab

                  Enter CurrentRate as a new number field and click Create.

                  Click Relationships

                  Use your mouse to click and drag a relationship line from CurrentRate in your TaxRate table to a field in the Invoices table. The best choice is a field that auto-enters a serial number. I'll call this field "InvoiceNumb" for this example.

                  This creates a relationship line linking the two tables with an = symbol in a box half way between your two tables. Double click that symbol.

                  A dialog box will pop up.

                  In the middle of this dialog, there's a field with this same = symbol. Click on it and select X instead of the = sign.

                  Click Change. Click OK.

                   

                  Return to your field definition for SalesTax in the Invoices table.

                  Double Click it and select the Auto Enter tab.

                  Click the Looked Up Value check box.

                  You'll see two drop down menus in the dialog box that pops up. The top menu should show the name of your current table. Select "TaxRate" in the second menu.

                  You'll now see CurrentRate listed in the window below these windows.

                  Click CurrentRate to select it and click OK as many times as needed to leave the Manage... part of Filemaker.

                   

                  FMP automatically creates a layout for each new table you create.

                  Switch to this new layout, TaxRate. You'll find the CurrentRate field displayed here, enter the old tax rate for starters.

                  Switch to a layout where you record your sales transactions and add the new SalesTax field to this layout. If not already present, add InvoiceNumb to your layout.

                  Click or tab to put your cursor into the InvoiceNumb field and choose Relookup Field contents from the Records menu. This action copies the old tax rate into your existing records.

                  Now return to the SalesTax layout and change the rate.

                  Create a new invoice and you should see the new rate appear. If you examine an older record, it should still show the old rate.

                   

                   


                  • 6. Re: How to make sales tax amount conditional on date?
                    Mike_Diehl
                      

                    This is exactly the issue I was seeking information about, as the CA tax rate changed AGAIN on July 1.

                     

                    I have several types of invoices; most are fees (requiring no sales tax). So I've crafted this script to calc. sales tax only on those invoices for which I designate it to be necessary:

                     

                    If(Inv Type = "Tax"; Round(Subtotal * .0925; 2); 0)

                     

                    I'm not too swift w/ FM, but MIGHT be able to navigate through your excellent instructions; however, since we're halfway through the year, I have quite a few record that already have a tax field. I was able to generate quarterly tax reports, but now when I'm trying to revisit all my deposits for the first half of the year, I note that all the tax is calculated at the 2nd quarter rate. I'd like to set something up that will "hard code" the tax figure into the record, and it sounds like your method will do that. But for existing records, would you suggest setting this up as I've done, then just going back through all the taxable invoices for the first quarter, then again for the second, then changing the figure again for July (forward) ? 

                     

                    Thanks,

                    Mike

                     

                    • 7. Re: How to make sales tax amount conditional on date?
                      Mike_Diehl
                        

                      Your explanation is great, but when I attempted to muddle through it, I found my understanding and familiarity with the program more than a bit limiting, and things didn't look or perform as you described, perhaps because I'm using v. 8.5 of the program, and the interface might (likely) has changed. Nonetheless, the functionality you describe is all there.

                       

                      In any case, I seem to have been able to to negotiate the multiple-variable challenge while "hard coding" the sales tax figures.


                      In the end I have two fields in my Invoices table, Sales Tax, and SalesTaxRate. The latter looks up the rate from the separate tax rate table, while the former performs this function:

                      If(Inv Type = "Tax"; Subtotal * SalesTaxRate; 0)

                      I never would've figured out the file connection between the fields in the two tables that enabled me to "relook up the values." Never. But again, it allowed me to rebuild the tax rates for the first two quarters, each of which rates are different from the current one, pretty easily, all things considered.


                      Thanks for your help.