Dynamic values based on stored data

Hi Greg,
I saw no answers to your question Greg, so I’ll take a shot :slight_smile: Technically there are multiple ways to achieve your requirement in Bubble, but I am not sure which would be “best”. Some SQL typey things are still in Bubble but other things aren’t. Bubble has strengths and weaknesses :wink:

  • You could have a workflow that operates after any transaction update to do the summing . In other words do the calculation at insert time (of a deposit or withdrawal).
  • You could calculate it on the fly when the balance is requested.

This is not really a Bubble specific design question to my mind. If your objective was a consistent balance, and you were worried about timing issues with inserts and database summing, and wanted some sort of locking on the database to avoid dirty reads - this type of DB access control isn’t really a good use case for Bubble’s inbuilt database. (Using an external database is another whole interesting subject :slight_smile: )

If you had infrequent changing transactions and so you were happy with dirty reads, and the number of total transactions was “low” eg hundreds, and your UI could work with on the fly calculations (eg a few hundred milliseconds) , then why not do it on the fly? You can do aggregating like summing in DB queries with Bubble. (http://forum.bubble.io/t/new-feature-grouping-and-aggregating-data/27795)

This “current balance” problem with changing transactions is a common design problem. One thing that is a really beautiful thing about Bubble, and lends itself to the calculate on insert approach is how the UI will automatically update when a DB value changes. It can be quite an impressive UI experience.

You can calculate at insert time SQL style like this (you could imagine calculating a running balance with the same technique) …

Edit - having thought about it overnight, they way I would do it would be “The simplest possible thing I can think of”. Have a transactions table, have a debit, credit and balance column. At insert time insert in all three, calculate the balance. Easy end of period (eg day) maths check to sum each to check consistency.