Splitting text column by ~ tilde?

Epicor ERP 10
1

I’m having a write a SORel change log report and data from the OrderRel table. The field ChgLog.Key2 contains the SONum, Line, and Rel data I need but in a tilde delimited manner.

I’ve been working to split it out and got the SO number to work via the case below, but I can’t get Line or Release to work.

Case
When ChgLog.Key2 like ‘%~%’
Then LEFT(ChgLog.Key2, PATINDEX(‘%~%’, ChgLog.Key2) -1)
End

For LINE, I thought I was onto something by modifying the Case above with Substring but I can’t get the syntax correct.

For REL, I was trying RIGHT instead of Left in the syntax but it returns too much data, ie: 162~1~21 instead of just 21 for the Rel number.

case
When ChgLog.Key2 like ‘%~%’
Then Right(ChgLog.Key2, PATINDEX(‘%~%’, ChgLog.Key2) )
End

2 Likes
2

Are you in the BPM expression editor?
If I recall, it was something like: splitLine = Ice.entry(1, ChgLog.Key2, ‘~’)

3

Here is a bit of code I used from something similar. First set it to an array and then to a variable.

I am assuming BPM editor as well.

string [] PartKeys = partTranPKs.Split('~');

int intTran;
int intSysTime;

int.TryParse(PartKeys[2], out intTran);
int.TryParse(PartKeys[1], out intSysTime);

using (var txScope = IceContext.CreateDefaultTransactionScope())
{
	var PTRow = (from PT in Db.PartTran where 
				PT.TranNum == intTran && 
				PT.SysTime == intSysTime select PT).FirstOrDefault();
	if(PTRow != null)
	{
	
		PTRow.EWO_c = callContextBpmData.Character01;
	
	}
	Db.Validate();
	txScope.Complete();
}
4

I’m writing a BAQ so all I can do is a calculated field in SQL commands.

The field ChgLog.Key2 lists the SP, Line, Rel separated by ~'s: 12345~1~1. I need to split the three apart into calculated fields, so I can use them to relate to the OrderRel table.

5

This is a calculated field I use in one of my BAQs to split OrderHed.SalesRepList which is a list of 5 rep IDs separated by ~.
Eg: 100~240~900~923~235
It’s searching for which rep has the split of 100% and getting the value from the tilde string with this statement: [Ice].entry(1,OrderHed.SalesRepList,’~’)

case       
    when OrderHed.RepSplit1 = 100 then [Ice].entry(1,OrderHed.SalesRepList,'~')     
    when OrderHed.RepSplit2 = 100 then [Ice].entry(2,OrderHed.SalesRepList,'~')     
    when OrderHed.RepSplit3 = 100 then [Ice].entry(3,OrderHed.SalesRepList,'~')
    when OrderHed.RepSplit4 = 100 then [Ice].entry(4,OrderHed.SalesRepList,'~')
    when OrderHed.RepSplit5 = 100 then [Ice].entry(5,OrderHed.SalesRepList,'~')
end

Your example would be something like this:
For the SP: [Ice].entry(1,ChgLog.Key2,’~’)
For the Line: [Ice].entry(2,ChgLog.Key2,’~’)
For the Rel: [Ice].entry(3,ChgLog.Key2,’~’)

The 1-3 being the index of the array that the split creates.

Edit** I have used that case statement to join the SalesRep table as well, so you should be able to use it to relate to the OrderRel table.

6 Likes
6

Check this out. It breaks out a tilde-delimited field into multiple
calculated fields in a BAQ.

1st Calculated field
(case when [Ice].num_entries(QuoteDtl.ConfigData_c, ‘~’ ) > 0 then
[Ice].entry(1, QuoteDtl.ConfigData_c, ‘~’) else ‘’ end)

2nd Calculated field
(case when [Ice].num_entries(QuoteDtl.ConfigData_c, ‘~’ ) > 1 then
[Ice].entry(2, QuoteDtl.ConfigData_c, ‘~’) else ‘’ end)

I haven’t tried seeing what happens if you exceed the array length.

Joe

1 Like
7

@hmwillett,

Thanks, that’s exactly what I needed. Didn’t even need a case statement as SO / Line / Rel have their own calculated fields. The BAQ only looks for changes to the OrderRel table, so there will always be results in each column.

@jdtrent, that’s a good idea too and I may need it for another report for Shipping and AR reports later.

1 Like
8

If you have access to EpicCare, this is a very helpful KB article: KB0030301

1 Like