8 Replies Latest reply on Jan 26, 2010 11:11 AM by Steve Wright

    Omitting Tax from My Total...

    paintboothguy

      Title

      Omitting Tax from My Total...

      Post

      Hello All, 

      I have an Invoice Layout wherein I  Have a field that calculates sales tax at the bottom of the Invoice.  When I sell products out of state, I do not charge this tax.  Is there a way to place a 'clickable or checkable' box next to the field label that will return a value of '0' when checked?

       

      the formula for the sales tax field is currently :

      Round ( Extended Price*.0825 ; 2 )

       

      the formula for the total field is currently:

      Sub Total + Line Items::Sales Tax + Freight + Installation

       

      Also,

      Is there a way to generate a new Layout by copying everything from an existing layout?  Example:  I have an Invoice Layout, and now I need to make a P.O. Layout.  They will be virtually the same, except wholesale values will be reported instead of retail values.  It would save a ton of time if it was possible! 

       

      Thanks,

      Andrew 

        • 1. Re: Omitting Tax from My Total...
          mrvodka
             Sales tax should be a looked up amount rather than the rate in the calc as the rate can change and thus if you go to change it will affect all records.
          • 2. Re: Omitting Tax from My Total...
            Steve Wright
              

            "Is there a way to generate a new Layout by copying everything from an existing layout?"

             

            In layout mode, click Layouts -> Duplicate Layout.

             

            As Mr_Vodka suggests, you should not be hard-coding the tax rate. 

            All your old invoices would become the wrong amount if you ever had to change this calculation.  It happens !

             

            Using a lookup field and a checkbox would allow you to put the calculation like :

             

            Case (

            tax_checkbox = 1 ; Round (Extended Price * LookupTaxRate ; 2 ) ;

            Round (Extended Price ; 2 )

            )

            • 3. Re: Omitting Tax from My Total...
              paintboothguy
                

              Thanks, SW!

               

              I have changed the Sales Tax approach to:

              Round ( All Invoices::Sub Total*All Invoices::Sales Tax Rate LU; 2 )

               

              I am unclear, however about the  following:

               

              Case (

              tax_checkbox = 1 ; Round (Extended Price * LookupTaxRate ; 2 ) ;

              Round (Extended Price ; 2 )

              ) 

               

              What tool do I use to place the checkbox?  Do I place it on my All Invoices layout>

               

              Thanks for the help!

              -Andrew 

              • 4. Re: Omitting Tax from My Total...
                Steve Wright
                  

                The checkbox would have to be a field, in my example I named it tax_checkbox

                Set the field up as a Checkbox using a value list which contains 1   (no need for anything else)

                 

                If checked, the fields value will be 1, therefore

                 

                Case (

                tax_checkbox = 1 ;  // If the checkbox is ticked

                Round (Extended Price * LookupTaxRate ; 2 ) ;  // Apply TAX

                Round (Extended Price ; 2 ) // Otherwise don't Apply Tax

                ) 

                 

                You could of course reverse this, that way you could use the checkbox only on layouts where you may want to omit the tax.

                 

                Case (

                isempty(tax_checkbox) ;  // If the checkbox is not ticked

                Round (Extended Price * LookupTaxRate ; 2 ) ;  // Apply TAX

                Round (Extended Price ; 2 ) // Otherwise don't Apply Tax

                ) 

                 

                 

                • 5. Re: Omitting Tax from My Total...
                  paintboothguy
                    

                  SW, 

                  Thanks for the help.  If I understand correctly:

                   

                  The formula you specified will REPLACE the content of my current field for calculating the tax?

                   

                  ?,

                   

                  Andrew 

                   

                   

                   

                   

                   

                  • 6. Re: Omitting Tax from My Total...
                    Steve Wright
                      

                    Kind of...

                     

                    Depending on which way round you want it to work and using your own field names (but read below first)

                     

                    Check box 'checked' for TAX

                     

                    Case (

                    YourCheckboxFieldNameHere = 1 ; 

                    Round ( All Invoices::Sub Total * All Invoices::Sales Tax Rate LU; 2 )

                    Round ( All Invoices::Sub Total ; 2 )

                    ) 

                     

                     

                    Check box 'unchecked' for Tax to be applied

                     

                    Case (

                    isempty( YourCheckboxFieldNameHere ) ; 

                    Round ( All Invoices::Sub Total * All Invoices::Sales Tax Rate LU; 2 )

                    Round ( All Invoices::Sub Total ; 2 )

                    )  

                     

                     

                    Also, it depends on how you have your fields and calculations

                     

                    For instance, you may have 

                     

                    All Invoices::Sub total

                    All Invoices::Tax Amount

                    All Invoices::Total

                     

                    In which case using the example for : Check box 'unchecked' for Tax to be applied,

                     

                    The calc for Tax Amount could be 

                     

                    Case (

                    isempty( YourCheckboxFieldNameHere ) ; 

                    Round ( All Invoices::Sub Total * All Invoices::Sales Tax Rate LU - All Invoices Sub Total ; 2 )

                    )  

                     

                    The calc for Total Amount could be :

                     

                    Case (

                    isempty( YourCheckboxFieldNameHere ) ; 

                    Round ( All Invoices::Sub Total + All Invoices::Tax Amount ; 2 )  ;

                    All Invoices::Sub Total

                    )  

                     

                     

                    To be honest, that is a somewhat basic example...  In our solution we calculate the tax of each line item seperately, then round those and add them up accordingly.

                     

                    So each line item has its own net, tax and gross figure.

                    The totals are then derived from All Line Items::Net  /  All Line Items::Tax 

                    The grand total is then a total of All Line Items::Net + All Line Items::Tax

                     

                    • 7. Re: Omitting Tax from My Total...
                      paintboothguy
                        

                      SW, 

                      No matter which scenario I use, I get an error message:

                       

                      'The specified field can not be found.'

                       

                      Is the error message referring to the 'Tax Checkbox' field, or what?  (In 'Field/Control Setup', Control Style is = Display as Checkbox Set, and Display Values From is = Tax Checkbox (use custom values, and '1').  On the right side of the same dialogue box, Display data from = Line Items,  ::Sales Tax, where I have entered the formula:

                       

                      Case (Tax Checkbox = 1 ; 

                      Round ( All Invoices::Sub Total * All Invoices::Sales Tax Rate LU; 2 ) ; 

                      Round ( All Invoices::Sub Total ; 2 ) 

                       

                       

                      ?-

                      Andrew 

                       

                      • 8. Re: Omitting Tax from My Total...
                        Steve Wright
                          

                        When you get the error, it should highlite which field cannot be found in the calculation.

                         

                        "On the right side of the same dialogue box, Display data from = Line Items,  ::Sales Tax, where I have entered the formula:"

                         

                        The checkbox field should be its own field literally... 

                        What you typed above is making me think you are trying to make the calculation field also act as a checkbox, which will not work.

                         

                        You need to add the checkbox field (via define databases) into the parent table where you are calculating the final figures then add that physical field to the layout and format it as a checkbox.

                        or..

                        You need to add it to the line items (child) table and put that field on each row of the line items portal (if your doing it this way)
                        It all depends on how you want to omit the tax.

                         

                        For instance, you may have line items which are tax free & line items which are taxable, for this you would want a checkbox for each child record.

                        Having a checkbox on the parent table, will only allow you to omit ALL the tax.