I am attempting to tap into the Ice.ChgLog table to make a dashboard for purchasing to better track when promise dates are changed. I am struggling with trying to break out the delimiter ~ to separate out the PO / Line / Rel.
Example:
Key1 = PO Number
Key 2 = PO~Line~Rel
I would like a calculated field displaying the PO Line and PO Rel, but cannot work out the code to do so. Attached is a screen shot for reference. I am able to break out the PO Num using the following calculated field, but cannot get the line or release. Once I figure this out I should be able to create a join back to the PORel table and include other data points.
I’ve played this game on this table for the same purpose… just not with the PO changes. What you effectively need to do is to keep going with that thought process. The next calculated field only needs to return the left N characters from the substring with start position at the ending point of your last calculated field and at an ending point of the 2nd ~ in your ChgLog.Key2 field. It’s tricky. But you can do it. I broke it into different calculated fields to help keep it simpler. You can get there by combinations of left, substring, and charindex.
That may not be exactly right but adding a +1 or -1 in the appropriate spots should get you what you’re after. Let me know how it goes. I’m curious.
@geofmcguire I bailed on doing it in sql and made the baq updatable and did the processing in C# in getlist. That would not allow you to do other joins, so once you were down that path you would also have to get the PORel info in that bpm which is doable.
Going down this road may not be the right way. I say that because there will be cases where a PO Line or Release will be > 9. So dipping into double digits will not work. What I truly need is a delimiter that breaks out a PO/Line/Release into three different calculated fields. From there I can join back to PO Detail or PO Rel to include other fields. Trimming left/right/len will not work if the numbers can grow beyond one digit.
Thank you Mark and Chris. Range is exactly what I was looking for. Had some trouble with formatting on my own, but ended up with a similar solution and now have them broken out across 3 different calculated fields. Really appreciate the help. Below are a couple screen shots for reference:
