Skip to main content
Question

Removing/Ignoring sub strings to create a list of items

  • July 12, 2021
  • 0 replies
  • 37 views

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
------------------------------
This topic has been closed for replies.

  • Author
  • Registered
  • July 12, 2021
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
------------------------------