Parsing out pieces of a field into substrings in a BAQ

Epicor ERP 10
1

I am not a BAQ super-user. I’ve done a ton of basic BAQs and created Dashboards, but this is my first time with substrings and I’m having a heck of a time. We are on version 10.2.300.24.

The data looks like this SO12345-001-0, where the JobHead.JobNum is a concatenated field from a 3rd-party program that creates the job in Epicor with the SO prefix to indicate that this job is SO-related, then the SO Number (12345), then a dash, then the SO Line Number (1) which has leading zeroes, then -0.

I just want to parse out the 12345, and the 1. There are a couple of issues here. First, when my customer started out, their number convention was not always consistent, so they have some old SO-related job numbers that are not prefixed with SO or are less than 5 digits. Now that the customer is established, their SO-related jobs are consistent, have the SO prefix, and have SO numbers of 5 digits. So, I just want any records where the SO number is 10000 or greater, and have the SO Line number trimmed of the leading zeroes.

Can anyone help me with this? Your assistance is GREATLY appreciated! Thank you!

2

Epicor has been using delimited strings for awhile and they’ve added functions to do this for you:

image

Briefly, if you have a string: so-string = “SO12345-001-0”

You can use the Num-entries function to tell you how many segments there are:

Num-Entries(so-string, '-')

returns 3.

You want the first segment:

Entry(1, so-string, '-')

returns SO12345. Assign that to segment-one-variable.

Now clean it up

replace(segment-one-variable, 'SO', '')

removes the ‘SO’ if it’s there and you’re left with ‘12345’.

Hope that gets you started.

Mark W.

5 Likes
3

Thank you! I’ll get right on that! :slightly_smiling_face:

4

To add on to @Mark_Wonsil’s post, Entry(x, y, z) functions return a string.

If you need the actual SO Num and line number (say for relating to OrderDtl.OrderNum and OrderDtl.OrderLine (or is it LineNum?) …), you’ll need to convert each to an integer.

1 Like
5

Thanks, guys. No matter what I do, I keep getting this error, “must specify table to select from”. Even when I put JobHead.JobNum anywhere, the Syntax Check is not happy. I think I’ll just dump the data to Excel and take care of it there. Unless I can get the entire syntax written out, I don’t know where else to put the table and filename. :frowning:

6

Can you post a screen shot of the error message and your formula?