Iterate through rows and sum values - Using Transformation 2.0

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. 

Any guidance on the would be great. 

Thanks in advance. 

0

Comments

4 comments
Date Votes
  • 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. 

    0
  • 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;
    }
    0
  • 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.

     

    0
  • 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. 

    0

Please sign in to leave a comment.

 

Didn't find what you were looking for?

New post