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.
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 )
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.
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.
That's essentially what I have already suggested. The only change is quite trivial:
f ( Taxable = "yes" ; RetailPrice )