Forum Discussion

JoshHamilton's avatar
JoshHamilton
Qrew Trainee
4 years ago

Removing/Ignoring sub strings to create a list of items

I have a text field that contains content from a webhook. Looks like the following; [{"id":1234,"weight":0,"length":0,"adjustments":[],"description":text},{"id":1235,"weight":0,"length":0,"adjustment...
Formulas and functions
JoshHamilton's avatar
JoshHamilton
Qrew Trainee
4 years ago
Okay, so I found a solution. Basically just switched the order of the process.

Used this before I did the split to find each instance of adjustments in the text and remove it out.

var number adjustOneStartId = Find($field, "\"adjustments\":");
var text newAdjustOneStart = Mid($field, $adjustOneStartId, Length($field));
var number adjustOneEndId = Find($newAdjustOneStart,"]")+1;
var text adjustOne = Left($newAdjustOneStart, $adjustOneEndID);

var text removeAdjustOne = SearchAndReplace($field, $adjustOne,"");

var number adjustTwoStartId = Find($removeAdjustOne, "\"adjustments\":");
var text newAdjustTwoStart = Mid($removeAdjustOne, $adjustTwoStartId, Length($removeAdjustOne));
var number adjustTwoEndId = Find($newAdjustTwoStart,"]")+1;
var text adjustTwo = Left($newAdjustTwoStart, $adjustTwoEndID);

var text removeAdjustTwo = SearchAndReplace($removeAdjustOne, $adjustTwo,"");

var text itemlistUnfiltered = ToText(Split($removeAdjustTwo, "},"));

Now this isn't a perfect solution, as it has a hard limit on items it can process. If there is a way I can make first find the frequency "adjustments" appears in the text at the start and then have it somehow run the process that many times... that would be ideal but I think I would be needing to create a code page and go down that path to run a javascript function.

------------------------------
Josh Hamilton
------------------------------
JoshHamilton's avatar
JoshHamilton
Qrew Trainee
4 years ago
Last update,

Just cleaned up the code to use fewer lines, here is the code that extends up to 10 line items;

var text field = Trim(Mid([Body], Find([Body],"\"lineItems\"")+12, Find([Body],"\"orderAdjustments\"")-Find([Body],"\"lineItems\"")-13));
//Extracts the entire line items content

var text removeAdjustOne = If(Contains($field,"\"adjustments\"")=true,SearchAndReplace($field,Left(Mid($field, Find($field, "\"adjustments\":"), Length($field)),Find(Mid($field, Find($field, "\"adjustments\":"), Length($field)),"]")+1),""));
//Removes the adjustments content from the first line item

var text removeAdjustTwo = If(Contains($removeAdjustOne,"\"adjustments\"")=true,SearchAndReplace($removeAdjustOne, Left(Mid($removeAdjustOne, Find($removeAdjustOne, "\"adjustments\":"), Length($removeAdjustOne)), Find(Mid($removeAdjustOne, Find($removeAdjustOne, "\"adjustments\":"), Length($removeAdjustOne)),"]")+1),""),$removeAdjustOne);
//Removes the adjustments content from the second line item

var text removeAdjustThree = If(Contains($removeAdjustTwo,"\"adjustments\"")=true,SearchAndReplace($removeAdjustTwo, Left(Mid($removeAdjustTwo, Find($removeAdjustTwo, "\"adjustments\":"), Length($removeAdjustTwo)), Find(Mid($removeAdjustTwo, Find($removeAdjustTwo, "\"adjustments\":"), Length($removeAdjustTwo)),"]")+1),""),$removeAdjustTwo);
//Removes the adjustments content from the third line item

var text removeAdjustFour = If(Contains($removeAdjustThree,"\"adjustments\"")=true,SearchAndReplace($removeAdjustThree, Left(Mid($removeAdjustThree, Find($removeAdjustThree, "\"adjustments\":"), Length($removeAdjustThree)), Find(Mid($removeAdjustThree, Find($removeAdjustThree, "\"adjustments\":"), Length($removeAdjustThree)),"]")+1),""),$removeAdjustThree);
//Removes the adjustments content from the fourth line item

var text removeAdjustFive = If(Contains($removeAdjustFour,"\"adjustments\"")=true,SearchAndReplace($removeAdjustFour, Left(Mid($removeAdjustFour, Find($removeAdjustFour, "\"adjustments\":"), Length($removeAdjustFour)), Find(Mid($removeAdjustFour, Find($removeAdjustFour, "\"adjustments\":"), Length($removeAdjustFour)),"]")+1),""),$removeAdjustFour);
//Removes the adjustments content from the fifth line item

var text removeAdjustSix = If(Contains($removeAdjustFive,"\"adjustments\"")=true,SearchAndReplace($removeAdjustFive, Left(Mid($removeAdjustFive, Find($removeAdjustFive, "\"adjustments\":"), Length($removeAdjustFive)), Find(Mid($removeAdjustFive, Find($removeAdjustFive, "\"adjustments\":"), Length($removeAdjustFive)),"]")+1),""),$removeAdjustFive);
//Removes the adjustments content from the sixth line item

var text removeAdjustSeven = If(Contains($removeAdjustSix,"\"adjustments\"")=true,SearchAndReplace($removeAdjustSix, Left(Mid($removeAdjustSix, Find($removeAdjustSix, "\"adjustments\":"), Length($removeAdjustSix)), Find(Mid($removeAdjustSix, Find($removeAdjustSix, "\"adjustments\":"), Length($removeAdjustSix)),"]")+1),""),$removeAdjustSix);
//Removes the adjustments content from the seventh line item

var text removeAdjustEight = If(Contains($removeAdjustSeven,"\"adjustments\"")=true,SearchAndReplace($removeAdjustSeven, Left(Mid($removeAdjustSeven, Find($removeAdjustSeven, "\"adjustments\":"), Length($removeAdjustSeven)), Find(Mid($removeAdjustSeven, Find($removeAdjustSeven, "\"adjustments\":"), Length($removeAdjustSeven)),"]")+1),""),$removeAdjustSeven);
//Removes the adjustments content from the eighth line item

var text removeAdjustNine = If(Contains($removeAdjustEight,"\"adjustments\"")=true,SearchAndReplace($removeAdjustEight, Left(Mid($removeAdjustEight, Find($removeAdjustEight, "\"adjustments\":"), Length($removeAdjustEight)), Find(Mid($removeAdjustEight, Find($removeAdjustEight, "\"adjustments\":"), Length($removeAdjustEight)),"]")+1),""),$removeAdjustEight);
//Removes the adjustments content from the ninth line item

var text removeAdjustTen = If(Contains($removeAdjustNine,"\"adjustments\"")=true,SearchAndReplace($removeAdjustNine, Left(Mid($removeAdjustNine, Find($removeAdjustNine, "\"adjustments\":"), Length($removeAdjustNine)), Find(Mid($removeAdjustNine, Find($removeAdjustNine, "\"adjustments\":"), Length($removeAdjustNine)),"]")+1),""),$removeAdjustNine);
//Removes the adjustments content from the tenth line item

var text itemlist = ToText(Split($removeAdjustTen, "},"));
$itemlist
------------------------------
Josh Hamilton
------------------------------