Relatively new to Bubble, struggling to implement what I thought would be simple
I have a type Expense, with fields ‘amount’ and ‘cumulative amount’. So each Expense needs to hold the to-date total of all of the Expenses.
I can do Create new Expense, with cum amt=Search for Expenses:last item’s cum amt + current amount. But this breaks if Expenses are not entered in strict order, ie today is 9.13 and an expense from 9.8 is entered. This will frequently happen. Its also possible that an Expense’s amount will have to be changed in the future. This also breaks this logic.
Because each Expense is the sum of all the previous + the current, I thought I might do Create new Thing where cum amt=Search for Expenses(where date<=current expense date):sum+current amount. To solve out-of-order insertion, theoretically only the Expenses with dates>current Exp date would need to be recalculated. But I ran into problems with make change to list of things. This was complicated and frustrating and didn’t work. One issue was multiple Expenses with the same date.
Then I thought a backend database trigger would work, but that seemed geared towards picking up on a change to a specific element—fine for editing an Expense’s value—but it wasn’t obvious how to trigger a workflow based on a change—adding a new item—to a list of expenses.
I realize I can derive the cumulative expense at any point without storing the value, but I want to easily access it without bulky expressions. Right now I don’t care about optimization or efficiency so I don’t mind running thru every item and recalculating whenever I add or change an Expense, even if it means doing unnecessary work.
So I’d love to hear any comments on my approach or suggestions on how to approach this!
If you got this far, thanks for reading. I hope it was clear enough.
/tf
Each Expense’s cumulative amount is the sum of all the previous ones plus the current amount.
Since changing an amount on 9-8 will affect any after it like yours on 9-13, have a database trigger with the condition Amount before change is not Amount now
Make changes to a list of things → Do a search for Expense, add a constraint Date ≥ Expense now So we get the same Expense, and any dated after it.
Set the Cumulative Amount = Do a search for Expenses, constrain Date ≤ This Expense, then do :each item's Amount:sum.
So for each one being modified, we are summing the amounts for itself + any below it.
If you are only modifying the Expense Amount in one or a few areas, you could also trigger this from a “When input’s value as changed” instead of a database trigger to make it a bit quicker.
on 2nd thought, don’t do the “When input’s value has changed” because that won’t take into account the new Amount unless you reconfigure the expressions a little bit.
Yes, this is exactly the logic I (thought I’d) implemented late last night. But there was an issue with multiple Expenses with the same date (which frequently happens). I’m pretty sure the equality condition was picking up extra amounts when I entered a new Expense with a duplicate date, but things were blurry. Gotta test with smaller data set,
Ok, so on the backend I use the trigger when an Expense amount is changed (this is infrequent, but it happens) and now I realize I already have the trigger for when a new Expense is added—that’s what fires my workflow in the first (and only). A new Expense is the same as a changed Expense, so presumably I write a custom workflow which I can attach to the backend as well as the button?
I know I said I didn’t care about optimization, but that’s only true for the next few days. My first approach has to be more efficient, because for each item I’m just adding the current amount to the last item’s cumulative amount. Whereas using :sum—particularly over several hundred Expenses—seems to require a lot of extra work. Agree?
Guess there’s a few new things to test out…
Do you think there could be significant workflow and/or speed implications due to the extra work? Am I hitting the database with each :sum call or are these processes all executing in my local browser? Does it even matter?
What is your intended result from having multiple Expenses on 9-13? Do you want to include all of those in the cumulative amount? Right now what I suggested would do that.
Take note that dates fields also contain time, so the search will be very specific and only grab the ones specifically = or below the date/time.
This may be irrelevant if your Expenses are being entered in with an input set to Date format, cause those would all be at 00:00 o’clock, but if they are getting timestamped with Current/date or you are using a date/time picker where you can specify time, they will definitely have different hours/minutes/seconds/milliseconds.
I figured I could be more granular with the times, likely down to milliseconds. But for most use cases, wouldnt this break equality conditions? Or can this be conditionally toggled?
In a list of Expenses, if four of them have the same date, will their order always be the same when sorted by ascending date?
Conditionally toggled… if you needed to you could duplicate the workflow once you get it all dialed in and modify it to be more or less restrictive. There would be a few ways to switch between those…
Since some could have identical date/times, I think the fallback sorting is the Creation date, but you can add another sort for Amount like you were saying.
But sorting doesn’t really have to do with the calculation because all Expenses with the same or less date/time will be summed and included in the calculation
I think what was happening with multiple expenses on the same day was this:
say there were three expenses on 9.13. The cumulative amount is all of the previous + the current. But because of the 00:00 format, when that’s expressed with <= aren’t all of the 9.13 expenses getting rolled up with each of the three expenses from that day?
This was really the reason I pivoted towards the :sum approach—it didn’t matter what the order was. Until of course something was added out of date sequence or changed.
Oh I wasn’t referring to Creation Date field as your search constraint, I pictured like they add something on 9.13, but they need to change some other Timestamp date field to 9.8. Then have it recalculate all the ones with greater dates after it with the WF I described
Like by using some other date field the Creation Date is irrelevant, it will just find the amounts you need and sum them. You just need to change some stuff if you want to exclude multiple on the same date in your sum
Yea if you mean you have some discreet order/number field, yep just filter by that so only the ones that are ≤ are included
Unless you mean the index like in a repeating group, but that is determined by your sorting, which at that point what do you sort by if you don’t want date?
Right, like a repeating group. An internal index. Except the underlying data structure for a list of things may be a non-indexable collection. eg in Java an Array is indexed and an ArrayList is not.
You may have given me the key in a previous comment, though—a secondary sort by amount will put expenses with duplicate dates in a strict order.
And I just looked at the editor and it looks like a list of Expenses is indeed indexable. Gotta play around with those operators…tomorrow.
Thank you, sir. This has been a really good process and I appreciate you taking the time to help me sort it out. I’ll let you know how things go.
