Associating Items to Pallet ID's

[jackharris]

What I'm attempting to do is associate a pallet ID to its item and lot. The reason for this is that I'm trying to map an 856 into a flat-file format. So far, I've tried using handlebars, transforms, and chatGPT but it's not coming out correctly. Here's the input record:

{
  "record": {
    "AuthorizationInformationQualifier": "00",
    "AuthorizationInformation": "",
    "SecurityInformationQualifier": "00",
    "SecurityInformation": "",
    "InterchangeIDQualifier(ISA05)": "08",
    "InterchangeSenderID": "9254530005",
    "InterchangeIDQualifier(ISA07)": "12",
    "InterchangeReceiverID": "7012325109",
    "InterchangeDate": "211001",
    "InterchangeTime": "1130",
    "RepetitionSeparator": "U",
    "InterchangeControlVersionNumber": "00401",
    "InterchangeControlNumber": "000943686",
    "AcknowledgmentRequested": "0",
    "UsageIndicator": "P",
    "ComponentElementSeparator": ">",
    "FunctionalIdentifierCode": "SH",
    "ApplicationSendersCode": "9254530005",
    "ApplicationReceiversCode": "7012325109",
    "Date": "20211001",
    "Time": "1130",
    "GroupControlNumber": "943741",
    "ResponsibleAgencyCode": "X",
    "VersionIdentifierCode": "004010",
    "TransactionSetIdentifierCode": "856",
    "TransactionSetControlNumber": "0001",
    "TransactionSetPurposeCode": "00",
    "ShipmentIdentification": "0086113951",
    "Date(BSN03)": "20211001",
    "Time(BSN04)": "1130",
    "HierarchicalStructureCode": "0001",
    "TransactionTypeCode": "AS",
    "HL": [
      {
        "HierarchicalIDNumber": "1",
        "HierarchicalLevelCode": "S",
        "TD1": [
          {
            "WeightQualifier": "G",
            "Weight": "46800",
            "WeightUnit": "LB"
          }
        ],
        "TD5": [
          {
            "Routing": "REILE'S TRANSFER & DELIVERY",
            "OrderStatusCode": "CC"
          }
        ],
        "TD3": [
          {
            "EquipmentDescriptionCode": "TL",
            "EquipmentNumber": "5353"
          }
        ],
        "REF": [
          {
            "ReferenceIdentificationQualifier": "SI",
            "ReferenceIdentification": "5842160"
          }
        ],
        "DTM": [
          {
            "DateTimeQualifier": "067",
            "Date": "20211001"
          },
          {
            "DateTimeQualifier": "011",
            "Date": "20211001"
          }
        ],
        "N1": [
          {
            "EntityIdentifierCodeN101": "ST",
            "Name": "Reile's Whse Cty 20-#2 RE4U",
            "IdentificationCodeQualifier": "93",
            "IdentificationCode": "RE4U",
            "N3": [
              {
                "Address Information": "4007 33rd St. NW"
              }
            ],
            "City": "Fargo",
            "State": "ND",
            "Country": "US"
          },
          {
            "EntityIdentifierCodeN101": "SF",
            "Name": "American Crystal Sugar Company"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "2",
        "HierarchicalParentIDNumber": "1",
        "HierarchicalLevelCode": "O",
        "PurchaseOrderNumber": "4501715602",
        "Date": "20211001"
      },
      {
        "HierarchicalIDNumber": "3",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005008826"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "4",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005008949"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "5",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005009014"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "6",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005009052"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "7",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005009250"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "8",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005009380"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "9",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005009489"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "10",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005009595"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "11",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005009700"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "12",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005009793"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "13",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005009915"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "14",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005010027"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "15",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005010133"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "16",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900001",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005010263"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "17",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900002",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005010416"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "18",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900002",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005010492"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "19",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900002",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005010614"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "20",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "T",
        "LineNumber": "900002",
        "NumberOfUnitsShipped": "50",
        "UOMSN103": "BG",
        "MAN": [
          {
            "MarksandNumbersQualifier": "W",
            "SSCC18PalletID": "00107859216005012885"
          }
        ]
      },
      {
        "HierarchicalIDNumber": "21",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "P",
        "LineNumber": "900001",
        "ItemQualifier": "VN",
        "Item": "810090",
        "LotQualifier": "LT",
        "Lot": "M21273",
        "NumberOfUnitsShipped": "700",
        "UOMSN103": "BG"
      },
      {
        "HierarchicalIDNumber": "22",
        "HierarchicalParentIDNumber": "2",
        "HierarchicalLevelCode": "P",
        "LineNumber": "900002",
        "ItemQualifier": "VN",
        "Item": "810090",
        "LotQualifier": "LT",
        "Lot": "M21274",
        "NumberOfUnitsShipped": "200",
        "UOMSN103": "BG"
      }
    ],
    "Number of Line Items": "22",
    "Number of Included Segments": "95",
    "Transaction Set Control Number(SE02)": "0001",
    "Number of Transaction Sets Included": "1",
    "Group Control Number(GE02)": "943741",
    "Number of Included Functional Groups": "1",
    "Interchange Control Number(IEA02)": "000943686"
  },
  "pageIndex": 0,
  "settings": {
    "integration": {},
    "flowGrouping": {},
    "flow": {},
    "export": {},
    "connection": {}
  }
}

You'll find that there are multiple HL objects with a line number. Each line number has a HierarchicalLevelCode of either T or P. I need to turn these into one object that has the SSCC18PalletID that's in the MAN element, the item, and the lot. When it's done, I want to have the following output appended to the end of my record.

Line:{
    "Item": "Item",
    "Lot": "Lot"
    "SSCC18PalletID":"SSCC18PalletID"
}

The following JavaScript gets me close, but I can't iterate over the SSCC18PalletID's to make a line per each one

function associateItemsWithLineNumbers(input) {
  const associations = {};

  if (input && input.record && input.record.HL) {
    input.record.HL.forEach((segment) => {
      if (segment.HierarchicalLevelCode === 'T') {
        const lineNumber = segment.LineNumber;
        const palletId = segment.MAN && segment.MAN[0] && segment.MAN[0].SSCC18PalletID;

        if (lineNumber && palletId) {
          if (!associations[lineNumber]) {
            associations[lineNumber] = {
              Item: [],
              SSCC18PalletIDs: []
            };
          }
          associations[lineNumber].SSCC18PalletIDs.push(palletId);
        }
      } else if (segment.HierarchicalLevelCode === 'P') {
        const lineNumber = segment.LineNumber;
        const item = {
          ItemQualifier: segment.ItemQualifier,
          Item: segment.Item,
          LotQualifier: segment.LotQualifier,
          Lot: segment.Lot,
          NumberOfUnitsShipped: segment.NumberOfUnitsShipped,
          UOMSN103: segment.UOMSN103
        };

        if (lineNumber && associations[lineNumber]) {
          associations[lineNumber].Item.push(item);
        }
      }
    });
  }

  return {
    originalRecord: input.record,
    addedAssociations: associations
  };
}

Any direction that anyone can give is appreciated.

[tylerlamparter]

@jackharris something like this?

function transform (options) {
  const record = options.record;
  const transformedRecord = { items: [] };

  if (record.HL) {
    const parentItems = record.HL.filter(item => item.HierarchicalLevelCode === 'P');
    const childItems = record.HL.filter(item => item.HierarchicalLevelCode === 'T');

    parentItems.forEach(parentItem => {
      const lineNumber = parentItem.LineNumber;
      const matchingChildItem = childItems.filter(childItem => childItem.LineNumber === lineNumber);

      if (matchingChildItem) {
        for (let c of matchingChildItem) {
          const item = {
            Item: parentItem.Item,
            Lot: parentItem.Lot,
            SSCC18PalletID: c.MAN[0].SSCC18PalletID
          };
  
          transformedRecord.items.push(item);
        }
      }
    });
  }

  return {
    originalRecord: record,
    addedAssociations: transformedRecord.items
  };
}

For reference, I used Celigo AI from our release yesterday and it got me 95% of the way there. It was just giving me the first association it found instead of each one because it was using the Javascript "find" function instead of the "filter" function. After that, I just had to add a loop.

function transform (options) {
  const record = options.record;
  const transformedRecord = { items: [] };

  if (record.HL) {
    const parentItems = record.HL.filter(item => item.HierarchicalLevelCode === 'P');
    const childItems = record.HL.filter(item => item.HierarchicalLevelCode === 'T');

    parentItems.forEach(parentItem => {
      const lineNumber = parentItem.LineNumber;
      const matchingChildItem = childItems.find(childItem => childItem.LineNumber === lineNumber);

      if (matchingChildItem) {
        const item = {
          Item: parentItem.Item,
          Lot: parentItem.Lot,
          SSCC18PalletID: matchingChildItem.MAN[0].SSCC18PalletID
        };

        transformedRecord.items.push(item);
      }
    });
  }

  return transformedRecord;
}

[tylerlamparter]

@jackharris I actually followed up on the Celigo AI conversation and it came out with what I manually had to fix. So 100% good!

function transform(options) {
  const record = options.record;
  const transformedRecord = { items: [] };

  if (record.HL) {
    const parentItems = record.HL.filter(item => item.HierarchicalLevelCode === 'P');
    const childItems = record.HL.filter(item => item.HierarchicalLevelCode === 'T');

    parentItems.forEach(parentItem => {
      const lineNumber = parentItem.LineNumber;
      const matchingChildItems = childItems.filter(childItem => childItem.LineNumber === lineNumber);

      if (matchingChildItems.length > 0) {
        matchingChildItems.forEach(matchingChildItem => {
          const item = {
            Item: parentItem.Item,
            Lot: parentItem.Lot,
            SSCC18PalletID: matchingChildItem.MAN[0].SSCC18PalletID
          };

          transformedRecord.items.push(item);
        });
      }
    });
  }

  return transformedRecord;
}

[jackharris]

That's exactly what I was looking to do! So many thanks. Next I have to figure out why my handlebars in the File Parser don't work and I'll be golden.

[tylerlamparter]

@jackharris which handlebar?

[jackharris]

I eventually got that to work by putting in the relative path every time.

Follow up question on the original question. I'm trying to add the Quantity shipped so I added a line to the code but it's not coming through the preview. Any ideas?

[tylerlamparter]

@jackharris not sure on your script, but here it is working on the one I sent.

function transform(options) {
  const record = options.record;
  const transformedRecord = { items: [] };

  if (record.HL) {
    const parentItems = record.HL.filter(item => item.HierarchicalLevelCode === 'P');
    const childItems = record.HL.filter(item => item.HierarchicalLevelCode === 'T');

    parentItems.forEach(parentItem => {
      const lineNumber = parentItem.LineNumber;
      const matchingChildItems = childItems.filter(childItem => childItem.LineNumber === lineNumber);

      if (matchingChildItems.length > 0) {
        matchingChildItems.forEach(matchingChildItem => {
          const item = {
            Item: parentItem.Item,
            Lot: parentItem.Lot,
            SSCC18PalletID: matchingChildItem.MAN[0].SSCC18PalletID,
            Quantity: matchingChildItem.NumberOfUnitsShipped
          };

          transformedRecord.items.push(item);
        });
      }
    });
  }

  return {
    originalRecord: options.record,
    addedAssociations: transformedRecord.items
  };
}