7 Replies Latest reply on May 29, 2013 12:14 PM by timwhisenant

    Problem with a tax script...

      I am trying to change a script that is for sales tax. I do not understand how it is calculating the information that it is. Our database is really old and I've just upgraded to FileMaker 12. I want to make it where the tax field will auto populate that if it is bought or shipped within the state of Texas it would charge sales Tax to not only the product but the shipping as that is what is required within the state of Texas. Can someone see what's wrong with my script? The first picture is of what is currently working but only pulling from the sold to. The second is where I changed it to include the ship to state. The third is the Tax Calculation, (Again that is currently working on Sold to only.) I want it to be from either the sold and ship to state. Please let me know if you have any questions. I'm really stumped on this. I'm an intermediate programer and trying to learn everything I can.





        • 1. Re: Problem with a tax script...

          This doesn't require a script at all.


          In your "grand total" field just add this tax to the total of the items.

          • 2. Re: Problem with a tax script...

            It would require a calculation though correct? This is how it is currently set up. We sell all over the US and only need it to apply the 8.25% tax to sold or ship to in the state of Texas. Sorry I'm a bit confused.


            • 3. Re: Problem with a tax script...


              David is right there is no need for a script. However, I would suggest a few changes to the tax calc and the record in general.

              First, add a field "Tax Rate" and have it auto enter the rate. This preserves the historical tax rate in effect when the sale takes place and prevents the tax from being calculated at a different rate should you reprint a this sale some time later after a rate change. when rates change simply open the field in manage database and change the autoenter amount.


              Second, add a calculation field "IsTaxable" with your requirements like:

              If( Exempt = "No"; Case( state ="Texas"; 1;

                                                         state ="TX"; 1;

                                                         shiptostate ="Texas"; 1;

                                                         shiptostate ="TX"; 1;

                                                         0)  ) // 1 = true & 0 = false


              Finally, adjust your Tax Calc to use the results of the two fields above

              If( IsTaxable; "your tax calc here using Tax Rate as your multiplier"


              don't forget to use the round function to avoid the odd rounding where the truncated numbers don't add up to the total charge.




              • 4. Re: Problem with a tax script...

                Thanks so much for the help. I've worked with FileMaker for 5 years and taken Beginner and Intermediate classes. I thought I was intermediate but am learning that am still a novice. I have fixed this calculation and it works great but it does round down like you stated it would. How do I find the rounding function? I didn't see that in the inspector. I did a search and it stated that .5 would round up but what if is is .608 How do I get that to round up to .61? I appreciate your help greatly and am learning by leaps and bounds! Thank you for your help!



                • 5. Re: Problem with a tax script...

                  Hi Sandi,


                  Wrap your tax calculation in a round function, like so..


                  Round( TaxableAmt * ( Rate/100); 2)







                  • 6. Re: Problem with a tax script...

                    Hi Tim,


                    I have to thank you again for your help. Please see two attachments.


                    The rounding worked with your change slightly modified to *1 and removing the );2).

                    With Rounding Fixed.png


                    It is working if the "sold" to state is Tx but not the ship to. I've tried adding this to the calculation and it isn't fixing it. Can you see the below and tell me what is wrong with what I've written.


                    Adding ship to not calculating.png


                    Again I appreciate your help! My classes didn't go over scripts and barely touched on Calculations. I am finding them very difficult to understand and really can't tell you how much I appreciate you explaining it to me!




                    • 7. Re: Problem with a tax script...

                      Hi Sandi,


                      your case statement addresses only one state field, i am guessing that it is the sold to state (which is why it works). I do not see a reference to the ship to state field = "Tx" (which is why it is not working). I would also wrap the state fields like Upper(State) = "TX" in my tests to prevent data entry errors from breaking or by-passing your conditions.


                      also you need to nest the statement for efficiency. have a look at my previous post, the case is nested in the if statement and it is only executed if the condition of the if statement is true.


                      Your tax calc is not rounding, your answer may be as expected on this order but may not be next time.


                      Round((Orders::Subtotal - Orders::Total Discount + Orders::Rush Percentage + Shipping ) *.0825; 2)


                      ex. 1234.77 x .0825 = 101.86852    Rounded = 101.87  w/o rounding = 101.86  but the total sale w/tax could show as 1336.64 not 1336.63

                      Rounding should be used everywhere money is calculated to avoid the error of the pennies.