I’m trying to find a way to add a calculated value to series of records in the database. The value to add consists of taking the Month portion of a date, subtracting it from 13, then dividing that value by 12. So:
With an input of 08/12/2020, the calculation would be: (13 - 8) / 12 = 0.416667
I’d like to be able to do this while uploading, maybe with backend workflow, but for my current needs, a regular workflow solution would be just fine to update records that already exist. I just cannot seem to find a way to perform the calculations with regular workflow solutions.
I’m not sure exactly what issues you’re having with it, but if I were doing it I’d do it like this…
Bear in mind that Bubble does all calculations from left to right (rather than following standard order of mathematical operations) and there isn’t any way of using parentheses.
So the simplest way around that (other than using plugins) is to use custom states to calculate and store the values you need.
I’d use 3 custom states (all with number data types) with the following values:
13 = 13 (default value)
Month = selected date > extract month
13-Month = 13-Month
I’d run a workflow when the input of date selector is changes, with the first step to set the state ‘Month’ to the month of the selected date, and the second step to set the state ‘13-Month’ to the state 13’s value - the state Month’s value.
Then you can simply divide that by 12 to get your final number.
You can use that in a display element, set it as a state, save it direct to the database, or add it to a previously existing number from the database.
Thank you, I should have pointed out that there are over 500 records to manage at a time pertaining to a single property in my database, and I have 20 of them in the DB now, with more to come.
I’ve used backend workflows to add data large amounts of data to the DB I’m just struggling with how to perform the math. The full calculation is a SumProduct, but the SumProduct uses this fraction which doesn’t exist for each record yet. Creating the fraction is my challenge so I’ll look at how to implement your suggestion with the workflow.
