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?