3 Replies Latest reply on Sep 26, 2012 12:05 PM by philmodjunk

    How to exclude a particular item type from a calculation.

      Title

      How to exclude a particular item type from a calculation.

      Post

           In my invoicing system, I have an invoice layout with a line item portal.  There is a "subtotal" field which uses the calculation: "Sum ( Line Items::RetailPrice )".  Their is a "sales tax" field which uses the calculation: "Subtotal * .07".  

           We have some line items which should not be charged tax.  Example: Security Deposits.

           Is there a way to make the "sales tax" calculation not include certain items?  Something like "(Subtotal *.07 unless subtotal includes "security deposit" and or "labor", in that case, subtact total of all security deposits  and labor from subtotal, then multiply by .07)

           How would I script this in the "Specify Calculation" dialouge box?  Is there an easier approach?

            

        • 1. Re: How to exclude a particular item type from a calculation.
          philmodjunk

               Sum can't be any more selective than the relationship that determines what records are related to the table where you define the calculation. You have to filter out the unwanted values before they get to the Sum function.

               Option 1:

               Define a calculation, cTaxablePrice in your LineItems table as:

               If ( Taxable ; RetailPrice )

               Make Taxable a number field that looks up a 1 from a matching field in your products table or format it with a check box that enters a 1 when selected and put it in your LineItems portal (Do this only if you don't have a products table listing all available products).

               Then define your sum function as: Sum ( LineItems::cTaxablePrice )

               Option 2:

               Define a new relationship to a new occurrence of LineItems that matches by both InvoiceID and by the value in "Taxable". This requires defineing a calculation field in Invoices, constTaxable as a calculation field that returns the number 1.

               The relationship match fields would be:

               Invoices::__pkInvoiceID = TaxableLineItems::_fkInvoiceID AND
               Invoices::constTaxable = TaxableLineItems::Taxable

               Now Sum ( TaxableLIneItems::RetailPrice ) will return the total of only taxable line items just like option 1 does.

          • 2. Re: How to exclude a particular item type from a calculation.

                 Would it be possible to create a "taxable" radio button with values "yes" and "no" for all inventory items, and then create a "taxable price" field with a calculation something along the lines of (if "taxable" = "yes", then "taxable price" = "retail price" / if "taxable" = "no", then "taxable price" = "0")?

                 Then "sales tax" = (SUM ( taxable price)) *.07

                 If this is possible, how would I script these calculations?  If not possible, please forgive me as I'm not very familar with programming at all.

            • 3. Re: How to exclude a particular item type from a calculation.
              philmodjunk

                   That's essentially what I have already suggested. The only change is quite trivial:

                   f ( Taxable  = "yes" ; RetailPrice )