Formula for a ledger

Hi @Teresa_Dey,

Airtable won’t do this in the same Table (sheet), you can do it in a second table. The best way to do so would be to Rollup the Credit in a field, then Rollup the Debit in another Field, and in the 3rd field do a Formula to calculate the difference.

BR,
Mo

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.

So I think I can make this work if I go for a daily balance and not a running total. If I add a column for the previous day’s end balance I should be able to use a formula to get the current days balance. Does this sound correct? I don’t think I need to change the view for this particular table. Also it is for only one account that I need to watch closely so I wouldn’t need to do this for multiple accounts.

Although this sounds good in theory, in practice it does not work. You end up getting the circular references error that I mentioned before. Even a daily balance (versus the daily income/loss) would need a running total.

One way to workaround the circular reference error is to build the system with a gap in the logic:

• a {Previous Record} field that links to the previous record
• a lookup field named {Previous Balance} that looks up the {Current Balance} in the linked record
• a manually entry field for the previous balance {Previous Balance Copy} (this is the gap)
• a {Current Balance} formula field that uses the {Previous Balance Copy} and the {Amount} for the record.

You bridge that gap by manually copying the {Previous Balance} to {Previous Balance Copy} for every record. You also have to wait for the formula fields to update before you can do the next copy/paste.

Eventually, when the Script Block comes out, it will probably be possible to have an automated process that calculates the new balance every day and places it in a new record in a [Daily Log] table. However, if there are any changes to historic data, the log will not reflect those changes.

There might be a workaround that would look similar to the Script Block solution, only using a third party integration with Zapier that would populate a [Daily Log] table. However, it would involve multiple views in multiple tables, several formula fields, rollup fields, multiple Zaps, and logic to make sure that you do not exceed Zapier’s limits. It might be a bit much to try to implement while you are still working on transitioning from a spreadsheet mindset to a database mindset. You are probably better off waiting for the Script Block to be released to Pro subscribers and hiring someone to write the script.

You can have multiple views for the same table, which is another difference from working with a spreadsheet.

In fact, multiple views of the same table is one of the powers of Airtable. Multiple views lets you analyze the data better by filtering, sorting, and grouping the data in different ways on the fly.

The new Scripting block has an example script to create a running total.

Setup

In order to use the script, you need to

1. Install the Scripting block. You must have a Pro account to do this.
2. Find the “Running total” example and copy it to the editor.
3. 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.
4. 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,
    });
}

// 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,
    });
}

Thanks for all your help. This seems to be working!