String_split in BAQ

Epicor ERP 10
,
1

Does anyone know how I’d be able to use the string_split function within a BAQ? I can do it in SQL, but not sure how to go about implementing it into a BAQ.

Thanks,

2

almost same SQL code you create in a calculated filed from the same type mate, if you state what exactly you want to do many people can suggest some ideas

3

You can do it like this:

Using a tilde separated list in a BAQ

SalesRepList

[Ice].entry(1,OrderHed.SalesRepList,’~’)

Increment by one for additional sales reps.

You can use this in a criteria or calculated field. It’s hard coded for the first occurrence in the list, so you’d use it once for each spot in the list or have a calculated field for each spot in the list.

Joe

3 Likes
4

To add on to @jdtrent’s comment…

Make a sub that returns the top 100 rows(or whatever the max number of fields you expect) of a table that has at least that many rows (parttran always has lots of rows). It only needs 2 fields:

  • Company (just something to link it to the next subquery)
  • Calc field:
    • Name: RowNum
    • Type integer
    • Expression: ROW_NUMBER() OVER(ORDER BY PartTran.TranNum)

Now make a subquery with the table containing the field to split , and the above subquery.

  • The Calc field will be:
    • Type nvchar,
    • Expression: [Ice].entry(ListGen.Calculated_RowNum,UD05_1.ShortChar01,',')

You’ll need to do something special to include “empty” sub strings (see the example below doesn’t include the field consisting of just the space)
This yields:

image

edit

It will give all 100 rows (or however many your Top xxx was in that first subquery). Use a subquery-criteria to filter out the blank rows.

1 Like
5

Is that a stored procedure?

If so, is documentation on other stored procedures available?

6

Thanks, this helps!

7

Nope. Standard Epicor. You’ll see it in the Ranges section:

image

It’s a hold over from Progress/Vantage.

1 Like
8

These are custom functions that you can see in Programmability - Functions folders in SSMS.

@hkeric.wci posted how to create your own a long time ago.

1 Like
9

If it is, I didn’t write it. I think I stole that from someone on this site. :slight_smile:

Joe

10

Just saw this
BAQ Example for obtaining information in tilde separated list - KB0030301

1 Like