Forum Discussion

JoshHamilton's avatar
JoshHamilton
Qrew Trainee
3 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,"adjustments":[{"id":2013,"lineItemId":1235,"name":"text","value":1},{"id":2014,"lineItemId":1235,"name":"text","value":1}],"description":text}]

Is there a way using formula fields to either:
A) Create a list of just the parent {}, so the adjustments content within are included in the parent list item
B) Remove out all the adjustments content entirely

The goal is to get that content into a list that would look like the following;

1.{"id":1234,"weight":0,"length":0,"adjustments":[],"description":text}
2.{"id":1235,"weight":0,"length":0,"adjustments":[{"id":2013,"lineItemId":1235,"name":"text","value":1},{"id":2014,"lineItemId":1235,"name":"text","value":1}],"description":text}]

The amount of id's and adjustments is not locked and can be more than what I have shown in the example.

At the moment I am using this to clean it up but it doesn't work when a parent id has more than one adjustment in it.

var number startFieldId = Find([Body],"\"lineItems\"")+12;
var number endFieldId = Find([Body],"\"orderAdjustments\"");
var text field = Trim(Mid([Body], $startFieldId, $endFieldID-$startFieldId));
var text itemlistUnfiltered = ToText(Split($field, "},"));

var text itemOne = Part($itemlistUnfiltered,1,";");
var number itemOneAdjustStartId = Find($itemOne, "\"adjustments\":");
var number itemOneAdjustEndId = Find($itemOne,"]")-$itemOneAdjustStartId;
var text itemOneAdjust = Mid($itemOne, $itemOneAdjustStartId, $itemOneAdjustEndId);
var text itemOneClean = SearchAndReplace($itemOne, $itemOneAdjust, "");

var text itemTwo = Part($itemlistUnfiltered,2,";");
var number itemTwoAdjustStartId = Find($itemTwo, "\"adjustments\":");
var number itemTwoAdjustEndId = Find($itemTwo,"]")-$itemTwoAdjustStartId;
var text itemTwoAdjust = Mid($itemTwo, $itemTwoAdjustStartId, $itemTwoAdjustEndId);
var text itemTwoClean = SearchAndReplace($itemTwo, $itemTwoAdjust, "");

var text itemThree = Part($itemlistUnfiltered,3,";");
var number itemThreeAdjustStartId = Find($itemThree, "\"adjustments\":");
var number itemThreeAdjustEndId = Find($itemThree,"]")-$itemThreeAdjustStartId;
var text itemThreeAdjust = Mid($itemThree, $itemThreeAdjustStartId, $itemThreeAdjustEndId);
var text itemThreeClean = SearchAndReplace($itemThree, $itemThreeAdjust, "");

var text itemFour = Part($itemlistUnfiltered,4,";");
var number itemFourAdjustStartId = Find($itemFour, "\"adjustments\":");
var number itemFourAdjustEndId = Find($itemFour,"]")-$itemFourAdjustStartId;
var text itemFourAdjust = Mid($itemFour, $itemFourAdjustStartId, $itemFourAdjustEndId);
var text itemFourClean = SearchAndReplace($itemFour, $itemFourAdjust, "");

var text itemFive = Part($itemlistUnfiltered,5,";");
var number itemFiveAdjustStartId = Find($itemFive, "\"adjustments\":");
var number itemFiveAdjustEndId = Find($itemFive,"]")-$itemFiveAdjustStartId;
var text itemFiveAdjust = Mid($itemFive, $itemFiveAdjustStartId, $itemFiveAdjustEndId);
var text itemFiveClean = SearchAndReplace($itemFive, $itemFiveAdjust, "");

var text itemSix = Part($itemlistUnfiltered,6,";");
var number itemSixAdjustStartId = Find($itemSix, "\"adjustments\":");
var number itemSixAdjustEndId = Find($itemSix,"]")-$itemSixAdjustStartId;
var text itemSixAdjust = Mid($itemSix, $itemSixAdjustStartId, $itemSixAdjustEndId);
var text itemSixClean = SearchAndReplace($itemSix, $itemSixAdjust, "");

var text itemlist = List(";",
$itemOneClean,
$itemTwoClean,
$itemThreeClean,
$itemFourClean,
$itemFiveClean,
$itemSixClean
);

$itemlist

------------------------------
Josh Hamilton
------------------------------
Formulas and functions

2 Replies

Sort By
  • JoshHamilton's avatar
    JoshHamilton
    Qrew Trainee
    3 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
      3 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
      ------------------------------