BAQ Help with nested replace

Epicor ERP 10
,
1

I am using calculated field in a BAQ to convert my plant to the customer’s plant code.
After catching a prior discussion on BAQ help, I have used the following successfully.

replace(replace(replace(replace(replace (RcvHead.Plant,‘2’,‘0091’),‘6’,‘0093’),‘15’,‘0076’),‘17’,‘0075’),‘18’,‘0090’)

Now I need to add in a (replace,‘1’,’0001) and it is giving unwanted output.

If I put the ,‘1’,’0001) at the end of the nesting, I get – the correct ‘0001’. The error comes in with the ‘2’ becoming ‘0090001’. The replace of the 1 is also happening on my output of the ‘0091’

If I put the ,‘1’,’0001) at the beginning of the nesting, I get – the correct ‘0001’. Now the error comes in with the 15 and 17 replace. I get ‘0000076’and ‘0000075’. The replace is happening twice on my 2 digit sources as they begin with a 1.

Is there a way around this within my limited knowledge of the BAQ logic?
I could ask the customer to change his code for the 1, but I could guess this would be too much.

Thanks
Scott

2

A CASE WHEN should work.

CASE WHEN RcvHead.Plant = ‘1’ THEN ‘0001’ WHEN RcvHead.Plant = ‘2’ THEN ‘0091’… and so on.

Or you might consider storing this data somewhere, maybe User Codes, and then look them up from your BAQ. That way you don’t have to hardcode it all in the BAQ.

3

For the 1 replace, try adding in spaces around the 1, like ’ 1 '. Something like this might replace only instances of 1, and not other times where 1 shows up with another number beside it like 11 or 17.

4

Alternatively, use a unique mapping that doesn’t include any of the values you are replacing. Instead of replacing 2 with 0091, replace it with ABCD, then go back and do a second replace to change the unique ABCD to 0091.

5

Thanks
I am going around in circles working on the logic to get one of these to work. I will also research user codes.
Scott

6

would be the way to go…
Pierre

2 Likes
7

I have Nate’s double replace to work for the next report. When I have more time I will look at my Case syntax problem. The User Codes would be something new to look into.
Thank you.
Scott

8

Mine is certainly the brute force approach. The case statement is simpler and probably the best way to get your result. To expand on the others, this syntax should work like a charm!

case 
    when RcvHead.Plant = '1' then '0001'
    when RcvHead.Plant = '2' then '0091' 
    else 'missing'
end
9

Thanks Nate
I had earlier revisited the calc field editor to see this syntax. It is working now.
The user codes look like a winter project to look into.
Scott