Skip to main content

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}`);

 

Hey ​@FS Tech Support,

Would you mind providing further context on your use case?
I did not go through your script, but calculating balances should be posible using an additional table and rollups (more on rollups here). Right? Meaning that you would have no need of having a script. I might be missing smth!

Mike, Consultant @ Automatic Nation 
YouTube Channel

Hi ​@Mike_AutomaticN ,
Thank you for your response.

The thing is my table has approx 2k entries (incoming & outgoing separate - like a single entry is created for incoming & likewise for outgoing) for different service providers. If I use roll ups & formula fields, won’t it cause a circular error (as mentioned in one of the use case in airtable)? That’s why I switched to scripting automation so closing balance is calculated automatically. Also, formulas can’t retrieve data from the above rows in airtable. So what’s the best possible solution in this case?

Hm, your script works fine:

I’m guessing your ‘Cr Dr.’ a single select field though as that would be how I’d set it up as well, if so you’d just update your code here to use the name instead:

if (crdr.name === "Incoming") {
    closingBalance += amount;
    console.log('incoming', closingBalance)
} else if (crdr.name === "Outgoing") {
    closingBalance -= amount;
    console.log('outgoing', closingBalance)
}

The problem then is that the comparison never works because the single select value is an object of ‘{name: “Incoming”}’ and we’re comparing it to a string ‘Incoming’

If that still doesn’t work, could you provide a screenshot of your table?  If you could DM me a link to a duplicated copy of your base that’d make it a lot easier to help you too, as without it I’ll have to attempt to recreate your base on my end instead

Hi ​@TheTimeSavingCo ,
Thank you so much for the help.

But I want to display closing balance for all entries & I want this automation to access the closing balance of the previous record to calculate the new record’s closing balance. How to do that like what changes will I have to make in my script?

Yeah, your code’s working fine for that?  Sorry, I’m kind of confused as to what you’re asking!


Is the ‘Cr Dr.’ field a single select field?  If so, did you make the following update to your script and test it?  

if (crdr.name === "Incoming") {
    closingBalance += amount;
    console.log('incoming', closingBalance)
} else if (crdr.name === "Outgoing") {
    closingBalance -= amount;
    console.log('outgoing', closingBalance)
}

If it’s not a single select DM me a link to a duplicated copy of your base and I can take a look at it for you!

 
Terms & Conditions