BAQ Calculated Field - PopulateSuffix of Part Number if Not Like 'X'

Epicor ERP 10
,
1

Part numbers have a prefix and a suffix. For example: ASO-40001. I’ve created two calculated fields so I have have a column for prefixes and a column for suffixes, as well as a column for the entire part number.

My calculation to create the suffix works great: SUBSTRING(PART.PARTNUM,5,5)

But I discovered a few parts that are ‘tests’ that I don’t want to appear on my query. Those suffixes are: “Part” or “99999” or “54322”.

I tried various things I found online to no avail. I bet this is really easy, but I’m stumped. I appreciate any assistance.

2

WHERE … NOT SUBSTRING(PartNum,5,5) IN (‘Stuff’, ‘I’, ‘don’t’, ‘Want’, ‘To’, ‘See’)

2 Likes
3

Hi @CSmith thank you so much for responding!
WHERE … NOT SUBSTRING(PARTNUM,5,5) IN (‘PART’,‘99999’,‘54322’)
Still getting syntax error: Incorrect syntax near the keyword ‘WHERE’.

I also tried…
WHERE…NOT SUBSTRING(PARTNUM,5,5) IN (‘PART’,‘99999’,‘54322’)
WHERE NOT SUBSTRING(PARTNUM,5,5) IN (‘PART’,‘99999’,‘54322’)

4

Ummm add a coondition to the SubQuery criteria…
Your condition is against the calc field suffix so
Click the NOT box
Table: Calculated
Field: Suffix (guessing here)
Operation: IN
Filter Value: Constant List → ‘99999’ Click New top right → ‘54322’

this will produce the above in the WHERE clause

5

Whoa! I would have NEVER figured that out. Brilliant! Thank you so very much!

1 Like
6

I would do it this way:

Make a calculated field called ValidPart which is a bit

CASE 
    WHEN Part.PartNum LIKE '%-%' THEN 1 
    ELSE 0 
END

Check if ValidPart = 1 in your subquery

2 Likes
7

Or LIKE '_____-%' (5 underscores) to ensure it has five characters before the hyphen?

Doing it @julian-exile’s way only does the string evaluation one time per row and one bit comparison, instead of two substrings and one string compare… (probably doesn’t make much difference)

8

@jwphillips , @julian-exile:

@Yodelayheewho (OPs) condition was the suffix not be either of 2 values.
I provided the generic way to specify exclusions to the final dataset returned from the subquery. Upon them not understanding my response I gave them a step by step how to click in the BAQ editor (10)… AFAIK, neither of your suggestions would provide the appropriate datasets the OP desires, but both are good examples of a possible solution to problems not relevant to the OPs initial post. Thanks for sharing!

1 Like
9

I appreciate everyone’s input!

10

Oh I read his original post wrong. He wants to make sure that specific suffixes are removed. My solution wouldn’t work for that.

1 Like