Exclude from sum if field value contains

Epicor ERP 10
, ,
1

Hi

In my BAQ i am collecting row in a sub query from the JobMtl table. This works ok for all rows-

sum((JobMtl.UnitLength * JobMtl.ItemQty) / 12)

but now i need to conditionally sum only on certain parts. We have 100’s of parts to exclude so it makes more send to use a common filter which is any part that starts with ‘PRT’, ‘SUB’ or ‘CPT’

I have tried various flavors from MATCH %PRT% to SubString(JobMtl.PartNum, 1, 3) = ‘PRT’

in a sub query, what do i need to filter the parts base on the first 3 letters so that they are excluded from the sum?

Thanks

2

The SubString formula that you have there should work. What isn’t working?

3

probably could use something like:

sum(iif('PRT,SUB,CPT' contains left(JobMtl.PartNum,3),(JobMtl.UnitLength * JobMtl.ItemQty)/12,0)

What this does is it first is an IF statement (using IIF… yes, that is EYE EYE EFF) which is treated like an excel if statement… then I have a string that has the three possible answers, and I look to see if that “list” contains the left three digits of the part number… if it DOES, then we add the value in your calculation, otherwise we add zero in the sum.

4

Hey

one method i tried was at sub query level on the JobMtl table as a criteria with an expression

case 
when substring(JobMtl.PartNum, 1, 3) = 'PRT' then ''
else JobMtl.PartNum
end

i tried the same as a calculated field

case 
when substring(JobMtl.PartNum, 1, 3) = 'PRT' then 0
else sum((JobMtl.UnitLength * JobMtl.ItemQty) / 12)
end

but i got a groupby column error. without the case, there wasnt an error.

5

You’ll need to put the sum around the case statement

sum(
case 
when substring(JobMtl.PartNum, 1, 3) = 'PRT' then 0
else ((JobMtl.UnitLength * JobMtl.ItemQty) / 12)
end
)
2 Likes
6

ahhh i see. makes sense now :slight_smile:

thanks