How do I keep a Running Balance on a Field?

[Rudy]

I originally asked this question here:

Original question

But I think I got a little ahead of myself. My real problem is keeping track of Running Balance data in a useful way. For reference here is my app:

Demo App

Email:
demo@example.com
Password:
temppass

I have created a thing called a Transaction with the following fields:

I created a RG to list all Transactions for the Current Pages Account using input elements:

The Initial Content of the last input in the RG shows the Running Balance of all Transactions up to the Current Cells Index:

When I preview the page, the running balance is displayed correctly:

But I can’t get the Running Balance to save properly in the Balance Field of the Transaction in the database. Here’s the workflow that I have created:

The Amount field updates correctly, but the Balance field stores the data that is shown in the input before the update:

Also only the current transaction Balance field is updated. How can I update the Transaction Balance for all transactions after updating a single Transaction’s Amount?

In the end I would like to have Running Balance that could be plotted on a graph.

[louisadekoya]

I think the problem is that input $0.00 Running’s value is not being changed directly. I can’t see your app’s editor but I guess you’re setting input $0.00 Running’s value based on a calculation on input $0.00 Upcoming’s value.

Here’s what I suggest. You could have a custom state of type integer on input $0.00 Running. Let’s call this myValue. Then set input $0.00 Running’s initial content to its myValue.

Now in your workflow event for when input $0.00 Upcoming’s value changes, first set input $0.00 Running’s myValue based on your calculation, then in your step to save to the database, set balance to input $0.00 Running’s myValue (not value).

Of course, you could do this without the custom state and just set Balance to your calculation directly, but this would mean doing the calculation twice, and I always prefer to do a calculation once only, so as not to have to maintain it in multiple places.

[Rudy]

Wonderful! Thanks for the help. @louisadekoya

This solves the first part of my problem, which is saving the updated Balance for the current Transaction to the database . Here’s how I set it up:

First I created the custom state (Current Balance) for Input $0.00 Running and set it as a number. Then I set the Initial Content for Input $0.00 Running to Input $0.00’ Running’s Current Balance:

Next I added a Set State Action and then a new Make Change action to the Workflow as you suggested:

Here’s how it worked out:

On pageload we see this

After making a change to the second transaction, this is what we see

Then I make a change to the first Transaction and this is what we see

So the repeating input is no longer displaying the Balance dynamically. However, the correctly updated Current Balance data does get saved to the database now. I also tried the option without the Custom State. The outcome is pretty much the same with the data saved in the database. But the Initial Content for Input $0.00 Running is shown for all Cells in the RG:

I agree that option 1 is preferred since you don’t have to duplicate the expression to get the Current Balance. But for now I’m using option 2 in the workflow since the running balance is displayed correctly.

The brings me to the second part of the problem. Since the change is only made to one Transaction, the Current Balance Value for any given Transaction quickly becomes out of date when you make a change any Transaction Amount that occurs before the previously edited transaction (as seen in the example images for option 1 above).

Would it be possible to capture the Running Balance’s for all Transactions in the RG at one time and save them to the database or to a custom state as a list?

PS.: Sorry I wasn’t sure exactly how to share access to the app editor:

Here’s a link, maybe this will work:
Demo App Editor

If this doesn’t work, please let me know how to give others access.

[louisadekoya]

Pleased that you made some progress. First off I have to admit that I haven’t been able to fully digest your update above, so please forgive me if I have misunderstood anything. I am bit pressed for time so I thought it might be quicker to simply attempt myself to replicate what I think you’re trying to do. You can find it here.

I thnk it mostly works. Take a look and let me know what you think.

[ashley.benson.tait]

Hey @louisadekoya.

That looks good! Nice work

[Rudy]

No worries. I appreciate any help you could provide. I had a chance to look at your example and it is very similar to what I have going on. In fact to get the running balance on the front end, your are using the exact same expression as I am. But it also has the same issue in the database. For example

When I load your page I see this:

This shows the transactions with the amounts and running balances next to it. And if I edit a Transaction Amount the running balance updates as expected:

However, in the actual database, only the Transaction Balance of the changed Transaction is updated leaving the remaining Transactions out of sync:

This is not so much a problem while working with the Transactions in the RG. But it does become a problem if I want to display that saved data in a chart later, for example to show past or future running balance data in a graph. If only a single Transaction is updated in the database, that means that only that same single balance point will be updated in the chart. For example:

Here you can see the data in the chart doesn’t match the running balance in the RG. It should really look more like this red line:

I’m thinking there would have to be a loop to update the Balance for each Transaction any time any amount input is changed by the user. Is that even a possibility with Bubble?

PS.: I hope its ok that I added the chart to your example.

[louisadekoya]

Ah, yes, I see the problem now. I have added a few things to address it:

• An ID field to the transaction table - just a number that is incremented each time a new transaction is added. May not be absolutely necessary, but it made it easier for me to do the next bit.

• An API workflow endpoint called set_balance, that sets the balance of each transaction to the sum of the amounts of all transactions before and up to this transaction (by ID).

• An action (when an amount is changed) to schedule this API workflow on the list of all transactions, such that it iterates through them and sets the balance of each as above.

I haven’t done lots of testing but it seems to work. Of course you need to be mindful that it takes a bit of time to iterate through the transactions, so your chart will be updating visibly as this happens, which may or may not be desirable. Also, Bubble I think limits list operations to 50, so you may need to constrain your list to run the workflow on to below 50.

There may be other ways to do this of course, but I’ve tried to keep it as close as possible to what you already had. I hope it helps.

[Rudy]

Very nice Work! That’s exactly what I was looking for. I briefly had a chance to look at how you set up the workflows/API, and I mostly get how you got it working. You’ve given me a lot to work with and I have a ton of questions, but I’m going to play around with it for a little bit and see if I can transfer what you did over to my app and see what I can get working.

Thanks again for the help. I’ll post my results asap.

[louisadekoya]

You’re welcome. Note that the custom state is no longer needed with this approach. I just updated my example to remove it, along with the two workflow steps that set and used it.

[Rudy]

Ok so i’ve had a chance to try your solution on my app and I ran into another hiccup. The problem with my app is that I have a Date Input that can be changed. Since the list of Transactions is sorted by Date, if I change the Date of a Transaction, the Transaction ID is no longer in order and the API workflow again updates the Transaction Balance incorrectly. But this brought a few questions to mind:

1.) Is it possible to get a transaction with the API by the Date Field instead of the ID Field?

I tried this but expressions do not allow me to get a Transaction Amount by Date in the same way that you were able to get a Transaction By ID

2.) Is it possible to convert or format a date as a number/integer (Serial Date) and Save and use that as the Transaction ID?

[louisadekoya]

As a rule, I think that if a field can be changed, it shouldn’t be used as an ID. My recommendation would be to introduce an ID. Of course every record in Bubble has a GUID anyway, but it is alphanumeric and for the API workflow I think you need an ID that you need a unique field that you can sort by, if I remember correctly. This is why I used a numeric ID.

That said, perhaps you don’t have to use the same field to identify a transaction as you do to sort it, in which case unique id (for id) and date (for sorting) might work.

[Rudy]

Ok that makes sense about the ID, and yes I was purely trying to use the Serialized Date as the ID for sorting purposes.

I messed around with and API Endpoint a some more and I actually was able to figure out how to get things working using Transaction Date instead of Transaction ID. It worked great except if to Transactions had the same date, they would have the same Current Balance. I was then able to solve that by adding 1 minute to a Transaction’s Date if it had the exact same date as another Transaction. So far everything seems to be working well.

However, I had to Schedule the API workflow any time a Transaction Date or Amount Changes. Looking at the Logs, unless I’m reading them wrong, it seems like this approach uses a great deal of resources especially since right now I’m just one user and I have a relatively small list of around 20 Transactions.

Any suggestions on how I can improve the workflow efficiency and speed up the update of the graph?

Here’s a link to my app:
MyFinancial App

[mishav]

@Rudy

Any suggestions on how I can improve the workflow efficiency

The ‘update all balances’ operation only needs to happen if editing a existing transaction or entering a backdated transaction, and not all the records need rebalancing.

Most transactions entered would only need a single balance calculated.

Also, ‘Make changes to a list of things’ would possibly be faster or more real-time than the API workflow operation.

[louisadekoya]

@mishav

Also, ‘Make changes to a list of things’ would possibly be faster or more real-time than the API workflow operation.

This is a good point that @mishav makes. I think I went down the route of an API workflow because I initially thought I might have to pass additional parameters. It is certainly worth trying ‘Make changes to a list of things’. Good luck.

[Rudy]

Ok so after a lot of trial and error, I was finally able to understand how to get things mostly working without the API workflow. This made a huge difference on the amount of resources being used. The app is so much faster now and I have the Chart updating in almost real-time (easily less than a couple of seconds and usually less than a second).

Thanks @mishav You gave a lot to think about on how to make my app more efficient.

And especially thank you @louisadekoya. Your workflow examples helped me to more fully understand how to get thing working.

I still have a little more tweaking to get things working just right, but I’ll give you guys an update as soon as I finish up.

Update 06/28/2017

Ok I made my final tweaks and changed the app to View Only Mode so the Workflows can’t get messed up. Hopefully others can learn something useful from this example.

Here are the app links again for reference:

Demo App Editor (View Only)

Demo App Preveiw

P.S.: Since this is just a demo creating a new user account may not be fully functional. Please use the following user account:

Email:
demo@example.com

Password:
temppass