Use a Button To Calculate a Running Balance

The following steps will help you set up a button to Update Value on Another Sheet to calculate a running balance.

Let's say we have the "Account Balance" sheet which includes a Subtable recording all transactions.

Step 1: Generate a New Sheet From Subtable to create "Transaction ID"

In the "Account Balance" sheet, create a "Transaction ID" sheet by generating a New Sheet From Subtable.

Select "Account ID" as the Key linking field to the new sheet.

After clicking "Create new sheet", you can now see this new sheet is with the fields that are from the Subtable in the sheet "Account Balance".

Step 2: Configure Loaded Fields and Formulas

Add additional fields and assign formulas to calculate the transaction balance in Design Mode.

To load "Account Name" and "Current Balance" data according to its "Account ID", we can use Link and Load to link them as Loaded Fields for our existing set "Account Balance".

Assign a formula to the "New Balance" field: C3 + A4 ("Current Balance" + "Amount").

Step 3: Create an Action Button

And now we can create an Action Button to Update Value on Another Sheet.

Select the "Account Balance" sheet as the target for updates, configure the values to be updated, and create the button.

Step 4: Configure the "Account Balance" sheet

Navigate back to the "Account Balance" sheet to add another Subtable field, "Initial Balance", and apply the formula: E5 - D5 (New Balance - Amount).

Additionally, if you want to quickly create transaction records in the "Transaction ID" sheet from the "Account Balance" sheet, you can configure it to create new records in the linked Subtable.

Click the gear icon next to the sheet name under the New Sheet From Subtable section.

Click Create Button and name the Action Button.

You will see the newly created action button under the Action Buttons tab on the left sidebar. To prevent the page from closing automatically after a new record is created, remove the parameter "Close" from the Action URL.

Step 5: Create a Transaction Record

Now let’s try creating a transaction record for the account "ABC Bank".

Click the "Create transaction" Action Button in the lower-right corner.

It will redirect you to the "Transaction ID" sheet page. After filling in the details and saving the record, the "New Balance" field will automatically calculate the updated balance. Then, click the "Run Account Balance" button.

The system will display a message indicating that the update is complete and automatically populate the "Run Account Balance executed time" field.

When you return to the "Account Balance" sheet, you will see that the "Current Balance" has been updated to $1200.