Discussions

Expand all | Collapse all

Removing/Ignoring sub strings to create a list of items

  • 1.  Removing/Ignoring sub strings to create a list of items

    Posted 24 days ago
    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
    ------------------------------


  • 2.  RE: Removing/Ignoring sub strings to create a list of items

    Posted 24 days 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
    ------------------------------



  • 3.  RE: Removing/Ignoring sub strings to create a list of items

    Posted 24 days ago
    Edited by Josh Hamilton 24 days 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
    ------------------------------