Dynamic values based on stored data

[dashbabykids]

Hi,
I am more used to SQL type applications as was wondering if anyone can help with the following problem and pseudo code for how to implement in Bubble.

A simplified version of what I am after is a running “availableFunds” calculation alongside each customer transactions that simply sums deposits and minus withdrawals given a user and a date.

For example:

Table Transactions
transactionDateTime As DateTime
user as String
deposit as Number
withdrawal as Number

function AvailableFunds(user, asAtDateTime)
Begin
totalDeposits = SUM(deposits) for User where transactionDatetime < asAtDatetime
totalWithdrawals = SUM(withdrawal) for User where transactionDateTime < asAtDatetime

return totalDeposits-totalWithdrawals;
End;

I would then like to display this field on the screen against each transaction (i.e. given user and date show availableFunds)

Any tips, including links to help documentation that discusses this?

Regards
Greg

[lindsay_knowcode]

Hi Greg,
I saw no answers to your question Greg, so I’ll take a shot 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

• 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 )

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.

[dashbabykids]

Thanks for taking the time to reply! Some excellent advice there.
My “gut” has some concerns with maintaining a balance due to the problem with back calculating any changes. I would prefer the totals were completely dynamic to avoid any issues with maintaining deltas. It isnt so much a dirty read problem (non-committed results) as it is the need to keep recalculation simple.

Transaction, (Begin Balance), Credit, Debit, (End Balance)

1, 0, 10, 0, 10
2, 10, 15, 5, 20
3, 20, 0, 5, 15

If we go back and edit transaction 2 to have more credit then we need to forward push the changes all the way downstream. Whereas my preference would be that it was 100% dynamically calculated

Greg