Re: Formula for a ledger
If you want a traditional running ledger, Airtable will not do this for you. Formulas in formula fields only work across rows; they do not work down columns. Formulas in the summary bar at the bottom of the screen only work down columns; they do no work across rows.
Rows also do not have a fixed order in Airtable. This is a very different way of thinking when switching from a spreadsheet. A record in Airtable does not know which record is “above” it.
There are workarounds using linked records to tell which record comes “before”; however, it isn’t really intended for purposes of a ledger. Plus all of the records would need to be manually linked which is a lot of work. And when creating the formulas for a ledger you would run into circular references that won’t work.
On the other hand, if you just need the final totals (not running totals), you can use the summary functions in the summary bar at the bottom of the computer screen. If your debits and credits are in the same column, use the SUM summary function.
If your debits and credits are in different columns, use a formula field that combines the two rows. The formula will look like {Credit} - {Debit} or {Credit} + {Debit} depending on whether or not debits are negative. Then use the SUM summary function in the summary bar of the new field.
Keep in mind that the summary function is a live total of whatever records are in the current view. If you change views or change the filter, you will get different totals.
The new Scripting block has an example script to create a running total.
Setup
In order to use the script, you need to
- Install the Scripting block. You must have a Pro account to do this.
- Find the “Running total” example and copy it to the editor.
- Edit the table, view, and field names in the Running total example to match the names in your base. They can be a bit hard to find if you are not familiar with JavaScript.
- Rename the block to something more meaningful, such as “Recalculate running totals for [View Name]”.
Workflow
Whenever you want to see the running total of the view, click the Run button in the Scripting block.
Warning
- The values in the {Running total} will not auto update. They will only update when you press Run. If any data changes (new, updated, or deleted records; order of records change, etc.), the {Running totals} will no longer be correct.
- If another collaborator tries to calculate the Running totals at the same time as you, and you have slightly different but overlapping records, at least one of you will get incorrect results.
- ALWAYS press Run before trusting the Running totals.
Alternative script to make it easier to customize
This is a slightly modified version of the example script that makes it easier to set the JavaScript variables.
// To use this script you must set the names of the
// base, view, and fields in the lines below
// Change only the names within the quotation marks.
const baseName = "My Awesome Base";
const viewName = "Grid view";
const valueFieldName = "Amount";
const runningTotalFieldName = "Running total";
// DO NOT CHANGE ANYTHING BELOW THIS LINE
let table = base.getTable(baseName);
let view = table.getView(viewName);
let result = await view.selectRecordsAsync();
let runningTotal = 0;
for (let record of result.records) {
runningTotal += record.getCellValue(valueFieldName);
await table.updateRecordAsync(record, {
[runningTotalFieldName]: runningTotal,
});
}
