Need Help With PreSave Script

I have a requirement that Celigo support said would require a preSave script. I'm not sure how to implement this in my data loader flow using a csv file to Netsuite.

I have rows in a csv that represent a cash refund transaction. Each row has header data and line item data. A refund will typically have just two rows - an invoice row and an adjustment row denoted by a field called “invoice_type”. The line item data on each row has an item and an amount.

My requirement is to check the invoice_type and if it equals “adjustment” then I want to map the item amount from that row into a header field on the cash refund.

According to the support person, I need to use a preSave script to modify the data to put the value of the refund_type = adjustment outside the array. I have no idea how or where to do this.

Hi @martinhambalek,

I've moved your post to the Troubleshoot custom flows section for more visibility. Here are some resources on the pre save script and how to use it.

Hi,

If I understand correctly, your CSV file contains one invoice line and one adjustment line for each cash refund transaction.

In the data loader settings, you can first group the data so that the invoice and adjustment lines are grouped together into one array. This will make it easier to process both lines as part of a single transaction:

For example, after grouping, your data would look like this:

{
 "page_of_records": [
   {
     "rows": [
       {
         "invoice_number": "INV12345",
         "customer_name": "John Doe",
         "transaction_date": "2024-10-22",
         "invoice_type": "invoice",
         "item": "Product A",
         "amount": "1000.00",
         "refund_total": "1050.00",
         "header_adjustment": null
       },
       {
         "invoice_number": "INV12345",
         "customer_name": "John Doe",
         "transaction_date": "2024-10-22",
         "invoice_type": "adjustment",
         "item": "Adjustment Item",
         "amount": "-50.00",
         "refund_total": "1050.00",
         "header_adjustment": "50.00"
       }
     ]
   }
 ]
}

Next, you can add a transform script to process the adjustment and map its amount to the header field.

Here’s an example of the script you can use:

function transform(options) {
  // Early return if no records
  if (!options.record || !Array.isArray(options.record) || options.record.length === 0) {
    return options.record;
  }

// Find the adjustment row
const adjustmentRow = options.record.find(row => row.invoice_type === 'adjustment');

// If we found an adjustment row, update all rows with its amount
if (adjustmentRow) {
const adjustmentAmount = Math.abs(parseFloat(adjustmentRow.amount)).toFixed(2);

// Update all rows in the transaction to have the adjustment amount in header_adjustment
return options.record.map(row => {
  const newRow = Object.assign({}, row);
  newRow.header_adjustment = adjustmentAmount;
  return newRow;
});

}

// If no adjustment row found, return original records
return options.record;
}

This script will produce the following output:

[
{
"invoice_number": "INV12345",
"customer_name": "John Doe",
"transaction_date": "2024-10-22",
"invoice_type": "invoice",
"item": "Product A",
"amount": "1000.00",
"refund_total": "1050.00",
"header_adjustment": "50.00"
},
{
"invoice_number": "INV12345",
"customer_name": "John Doe",
"transaction_date": "2024-10-22",
"invoice_type": "adjustment",
"item": "Adjustment Item",
"amount": "-50.00",
"refund_total": "1050.00",
"header_adjustment": "50.00"
}
]

Does this help?

Hi Nuri. I never saw your response previously. I didn't get a notification. Let me work on what you've suggested here and I'll respond back shortly. Thank you!

Hi Nuri. I spent some time looking at your solution and it's not quite what I'm looking for. As shown below, when the line item's “Refundable Type” is an “adjustment”, then I want to add the “2” value to a header field called “Refund Item Amount Header”.

Current example:

Desired Outcome:

Also, where exactly where and how exactly do I add a transform script?

Thank you for your time.

Hi @martinhambalek

No worries!

If you click the plus (+) sign (orange arrow in my screenshot), you’ll see the transform icon (red arrow):

Now, you can add a transform script like this. The function will also sum all adjustment amounts if there are multiple:

function transform(options) {
    let record = options.record;

    // Ensure 'lines' exists and is an array
    if (!Array.isArray(record.lines)) {
        throw new Error("Invalid record structure: Missing 'lines' array");
    }

    // Find all adjustment rows
    let adjustmentRows = record.lines.filter(line => line["Refundable Type"] === "adjustment");

    // Sum all adjustment amounts (ensure numeric values)
    let totalAdjustmentAmount = adjustmentRows.reduce((sum, line) => {
        return sum + (parseFloat(line["Refund Item Amount"]) || 0);
    }, 0);

    // Add the total to the header
    record["Refund Item Amount Header"] = totalAdjustmentAmount;

    return record;
}


This script will modify the record so that the "Refund Item Amount" from the "adjustment" row is mapped to a new header field "Refund Item Amount Header" at the top level.

Example Output:

{
    "Account Email": "mike@hotmail.com",
    "Payment Provider": "visa",
    "Refund Created At": "9/4/2024",
    "Order ID": "53651462144",
    "lines": [
        {
            "Refund Item Amount": "20",
            "Subscription Uuid": "efd5402c-06a9-4272-9fd2-70afd228ab7f",
            "Invoice Uuid": "191922b9-8ca6-4025-9f46-b15b23e670d6",
            "Transaction Uuid": "8f432591-31ac-49cb-8f08-fe75d9f1ed8f",
            "Original Vendor Charge Token": "dHJhbnhNY3Rpb25fNHpzZG5od30",
            "Refundable Type": "invoice_item"
        },
        {
            "Refund Item Amount": "2",
            "Subscription Uuid": "efd5402c-06a9-4272-9fd2-70afd228ab7f",
            "Invoice Uuid": "191922b9-8ca6-4025-9f46-b15b23e670d6",
            "Transaction Uuid": "8f432591-31ac-49cb-8f08-fe75d9f1ed8f",
            "Original Vendor Charge Token": "dHJhbnhNY3Rpb25fNHpzZG5od30",
            "Refundable Type": "adjustment"
        }
    ],
    "Refund Item Amount Header": "2"
}


some extra thought:
Is “adjustment” always required?

  • If some refunds don’t have an "adjustment", does the target system still expect "Refund Item Amount Header"?
  • If the field is missing, should it default to 0?