Iterate through rows and sum values - Using Transformation 2.0

Integrator.io
1

Hi,

Could anyone give me some guidance in how to iterate through rows and create a value of all sub values.

The goal is to get a new value of TotalBoxesperPallet from BoxesperPallet values in each row.

5301043caa20740200000001-8548d25ddf23401faff0c9e5ed3e3ff9.png
5301043caa20740200000001-8548d25ddf23401faff0c9e5ed3e3ff9.png1837×868 141 KB

Any guidance on the would be great.

Thanks in advance.

2

I would switch over to a JavaScript transform. I highly recommend using AI to write JavaScript code now days. You can do it in the product directly, and I promise we have a better UX coming soon, or you can do it in ChatGPT outside the product too.

5301043caa20740200000001-0c2abf1fb8ea4681af4d8602729870d5.png
5301043caa20740200000001-0c2abf1fb8ea4681af4d8602729870d5.png2390×1253 255 KB

3

Hi Scott,

Thanks for the direction, I have thrown it into ChatGPT and got exactly what I needed for the flow, after a few tweaks to the prompts.

For anyone who comes across this, and would like to know the solution I got to in the end.

Using Javascript transform… 


function transform(options) {
  const formatDateString = (dateStr) => {
    const [day, month, year] = dateStr.split("/");
    return `${year}-${month}-${day}`;
  };

const data = options.record; // Input JSON data

const transformed = {

ShipmentID: data[0].ShipmentID,

Carrier: data[0].Carrier,

Reference: data[0].Reference,

ShipmentDate: formatDateString(data[0].ShipmentDate),

DeliveryDate: formatDateString(data[0].DeliveryDate),

DeliveryTime: data[0].DeliveryTime,

OrderNumber: data[0].OrderNumber,

CustomerPO: data[0].CustomerPO,

OrderMemo: data[0].OrderMemo,

LineDetails: ,

OrderTotals: {

TotalUnits: 0,

TotalBoxes: 0,

TotalPallets: 0,

TotalProducts: 0

},

UniqueSKUs: new Set() // Temporary set to track unique SKUs

};


// Process each line

data.forEach(item => {

const {

Pallet,

SKU,

Batch,

Expiry,

UnitsinBox,

TotalUnits,

BoxesperPallet,

“Pallet Weight”: PalletWeight,

Dimensions

} = item;


// Add LineDetails
transformed.LineDetails.push({
  Pallet,
  SKU,
  Batch,
  Expiry: formatDateString(Expiry),
  UnitsinBox,
  TotalUnits: parseInt(TotalUnits, 10),
  BoxesperPallet: parseInt(BoxesperPallet, 10),
  PalletWeight,
  Dimensions
});

// Update OrderTotals
transformed.OrderTotals.TotalUnits += parseInt(TotalUnits, 10);
transformed.OrderTotals.TotalBoxes += parseInt(BoxesperPallet, 10);
transformed.OrderTotals.TotalPallets += 1;

// Track unique SKUs
transformed.UniqueSKUs.add(SKU);



});


// Finalize TotalProducts by counting unique SKUs

transformed.OrderTotals.TotalProducts = transformed.UniqueSKUs.size;

delete transformed.UniqueSKUs; // Remove temporary set


// Assign the transformed data to options.record

options.record = [transformed]; // Single record in an array

return options;

}

4

The goal is to get a new value of TotalBoxesperPallet from BoxesperPallet values in each row.

If that is still your goal, you can totalize the BoxesperPallet with a single line of code:


//  @Celigo - there is a bug that is adding the string "Copy" to formatted code blocks
const totalBoxes = options.record.reduce((acc, cur) => acc += parseInt(cur.BoxesperPallet), 0)

There is a lot more going on in that code and I can't tell if it's just garbage chat GPT code or if your actual needs are much more complicated than your original stated desire.

5

I do this by creating a transformation that maps rows[*].boxesperpallet to a string array named boxesperpalletarray.

Then, you can do a {{sum boxesperpalletarray}} handlebars expression in your import mapping and you'll have the total. Easy and no code needed.