Hi,
Please guide me about how we can use scripting automation to run through the records of a specific service provider and update the closing balance (just like it is done in excel). I am providing my script below. The problem is, everytime a new entry is created in service provider ledger, the automation updates the closing balance as 0. I can’t seem to figure out what’s wrong.
SCRIPT:
let table = base.getTable("Service Provider Ledgers");
// Input from automation (recordId of new record)
let inputConfig = input.config();
let recordId = inputConfig.recordId;
// Fetch the new record
let query = await table.selectRecordsAsync({
fields: ["Service Provider", "Date", "Cr Dr.", "Amount", "Closing Balance"]
});
let record = query.getRecord(recordId);
if (!record) {
console.log("Record not found.");
return;
}
let provider = record.getCellValue("Service Provider")?.[0]?.id;
if (!provider) {
console.log("No service provider linked.");
return;
}
// Get all records of this provider
let providerRecords = query.records
.filter(r => r.getCellValue("Service Provider")?.[0]?.id === provider)
.sort((a, b) => {
// Sort by Date ascending
let dateA = a.getCellValue("Date") ? new Date(a.getCellValue("Date")) : new Date(0);
let dateB = b.getCellValue("Date") ? new Date(b.getCellValue("Date")) : new Date(0);
return dateA - dateB;
});
// Find this record's position
let index = providerRecords.findIndex(r => r.id === recordId);
// Get previous record's closing balance
let prevClosing = 0;
if (index > 0) {
prevClosing = providerRecords[index - 1].getCellValue("Closing Balance") || 0;
}
// Calculate new closing balance
let amount = record.getCellValue("Amount") || 0;
let crdr = record.getCellValue("Cr Dr.");
let closingBalance = prevClosing;
if (crdr === "Incoming") {
closingBalance += amount;
} else if (crdr === "Outgoing") {
closingBalance -= amount;
}
// Update only the new record
await table.updateRecordAsync(record.id, {
"Closing Balance": closingBalance
});
console.log(`Closing balance for new record updated: ${closingBalance}`);
