need help to exclude two items when calculating total

[COOLIGHT]

hi, i want to calculate the total cases of the shipment for one invoice, excluding two items TAX and SHIP. I also want to add text "Cases" behind sum qty. My formula is below and it does not work. can anyone help me here? thanks


if {SO_InvoiceWrk.ItemCode}<>"TAX" and {SO_InvoiceWrk.ItemCode}<>"SHIP" THEN Sum ({SO_InvoiceWrk.QuantityShipped})+"Cases"

 

[Turkbear]

Hi,
One problem is in the concatenation, instead of yours use:
ToText(Sum ({SO_InvoiceWrk.QuantityShipped}))+"Cases".

Numbers cannot be concatenated with strings...
BUT:

Where are you using this formula?

Try a formula like:

if 
({SO_InvoiceWrk.ItemCode}<>"TAX" and {SO_InvoiceWrk.ItemCode}<>"SHIP" )
THEN 
{SO_InvoiceWrk.QuantityShipped}
Else
0

Place this in your details section ( you can suppress its display) and insert a Summary of it ( using the SUM operator)-



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[COOLIGHT]

I just want to put the total cases of shipment on the invoice. But I have item#tax and ship. I want to exclude TAX and ship when I calculate the total case. I hope this will be clear for you guys to help me.

Item# Description Cases Price Amount
100312 Chardonnay 10 50 200
110012 Cabernet 12 60 720
Tax Excise Tax 1 2.10 2.10
Ship Freight charge 1 20 20

Total ? 944.10

 

[lbass]

Turkbear gave you the correct solution. Did you try it?

-LB

 

[COOLIGHT]

I do not think Turkbear's solution works. I need the sum of total cases excluding tax and ship.

item description case
10010 chard 1
10020 cab 2
ship 1
tax 1
total ?

 

[lbass]

And what was the result you got with Turkbear's solution? Because it is the correct approach--unless you have suppressed data. You would just place his formula in the detail section->right click->insert summary->sum and choose the group level (if you want it at a group level) or grand total level.

-LB

 

[COOLIGHT]

After I ran Turkbear's solution, item#TAX and SHIP are gone from the invoice and the total is just the number of last item of the invoice. I want to ITEM#TAX and SHIP on invoice, but the total excludes SHIP and TAX. Turkbear's solution does not have the total.

thanks

 

[Turkbear]

Ok,
Create that formula and add it to the details like I posted ( suppressed) and SUM it..BUT also be sure the Item field is also in the Details...


My formula, by itself, would not prevent the TAX and SHIP from showing....





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You have to create Turkbear's formula in the field explorer->formula->new, and then place it in the detail section and right click and insert a summary ->sum on it. It sounds like you are mistakenly using it in a suppression or selection formula area.

-LB