2 Replies Latest reply on Jul 10, 2012 1:23 PM by disabled_JackRodgers

    Sales tax by zipcode



      Sales tax by zipcode


      Im newish to filemaker (never had to do anything too advanced)... Im working on an invoicing application for field technicians.  


      Im having trouble with the sales tax, I would like it to look up the zip code and return the related sales tax for that zip. i only have about 50 zip codes we service.


      I have a table customers and tax, Customers has the zipcodes and where i want it to return the tax rate.  The tax file has the zipcodes and related sales tax.  Im having trouble with the lookup.  


      Could someone give me a general idea of how to do this again Im new.


      Thanks in advance

        • 1. Re: Sales tax by zipcode

          Establish this relationship between customers and your tax rate table:

          Customers::ZipCode = TaxRates::Zipcode

          You can add the TaxRates::TaxRate field to your customers layout and it will show the tax rate for that customer. And you can define a calcualtion field in Customers that simply refers to TaxRates::TaxRate to access the taxrate for that customer but I would not use either of those for invoicing. IF a customer changes their location or the government changes the tax rate, you don't want either change to alter past invoices.

          You need this relationship and table:

          Customers::CustomerID = Invoices::CustomerID

          A taxRate field in Invoices should auto-enter the current tax rate for that customer. If you have defined the calculation field I mentioned earlier, this TaxRate field in invoices can use an auto-enter field option (Either calculation or looked up value can be used for identical results), to copy the current taxRate into the Invoices record.

          • 2. Re: Sales tax by zipcode

            Use the ZipCode in the INVOICE file since that is what counts.

            Your tax table will have fields: Zip Code, Fed Tax, State Tax, County Tax, City Tax etc.

            Your invoice file will have corresponding tax rate fields and tax total fields so you can do reports and totals for the variious authorities.

            Thus your invoice will look something like:

            Zip Code

            Invoice Sub Total:
            Fed Tax
            State Tax
            County Tax
            City Tax
            Invoice Total

            The Tax Rates are brought in using the Calculation function of a field definition. This brings the number in when you create the record and enter the Zip Code.

            Then your calculation multiplies each tax rate field by the rate to derive the amount.

            You can then create a report with summary for the invoices and the total amounts billed and due.