I’m working on an app where we need to track a user’s professional development (PD) points over the last two years. If the user has attended PD sessions within this timeframe, calculating their points is straightforward. I simply update the sum of the user’s current points within that date range.
However, I’m unsure how to handle users who have no PD sessions in the last two years. In that case, their PD points should be zero.
I’m assuming that Bubble’s Search for PD Sessions (with a constraint like Date ≥ Current date + years: - 2 ) would return an empty list if there are no matching sessions. If I try to sum the points from that empty search, will it return 0 or null? And what’s the best way to ensure it reliably returns 0?
Any advice on how to handle this logic cleanly would be appreciated.
@leo.saumure you can use an expression like this:
( Search for PD Session:count > 0 ) :formatted as text
Formatting for yes: PD Points calculation
Formatting for no: 0
All you need to do is the search as you stated with your constraint, and after that part of the expression you use the :count operator, which if empty is 0, and if not empty is the count. But if you are trying to summarize points that are another data field on the PD Sessions data type you need to after the search part of expression use the ‘each items points’ : sum which if there are no PD sessions the sum of the points would be 0 and if there are PD sessions the sum of the points would be the sum of the points.
Do not do this as the PD Points Calculation would need the Search for PD Session each items points :sum expression anyway, so avoid unnecessarily adding more dynamic expressions for the same type of result.
I thought if the PD Point Calculation is more complex than just a sum, using :formatted as text would be a good solution (for example, it may use multiple fields to calculate)
No, in practice when needing to calculate the total points of something, we just need to use :sum operator.
If needing multiple fields to calculate just use arbitrary text and then converted to number operator afterward, so you have from arbitrary text that same input area as you have from the :formatted as text (although only one and not two, one for ‘yes’ and one for ‘no’).
The main reason to use the formatted as text operator would be if you need different values from a yes or a no evaluation from the first part of the expression, but if you just need to know if the sum of a set of numbers is 0 or not, you do not need formatted as text for that and can just simply use one expression.
all the math expressions result in 0 not null so you just have to use the sum or count
although annoyingly this is different for field data…
field + field = null instead of 0 if any is null
to get around this you have to set the default on the field in the table to 0
then if empty it will be 0 and therefore
field + field = 0 instead of null
similar bubble peculiarities also apply to dates where null dates aren’t treated as null but as jan 1 1970 - this can cause real whackiness for "get min date etc) if you don’t also apply date isn’t empty
one of those bubble gotchas that you only learn after hitting your head against the wall for a while trying to debug something that should work but doesn’t
Thanks for the input. To add some context, here’s what I am trying to achieve:
The interpreters listed in the screenshot below have all registerred for a PD session. All the data about that session is listed in the interpeter’s ProfDev table, including points, date, duration, etc. When the administrator clicks the attendance checkbox, the first portion of the workflow changes their attendance in the ProfDev table to yes. All well and good.
The second portion of the workflow is meant to update two fields on the user’s table: Current Point & Last PD Date. This gives an up-to-date and at-a-glance record of the interpeter’s current PD status.
The Current Point field is set to number with a default value of 0
The points are adjusted properly when the search criteria yields results. However, if the search criteria (as in this case) yeilds an empty set or null results, the field is adjusted as a null value rather than a zero. Note: The Last PD Date change is working as needed as it uses a different search criteria.
What I’m thinking I might need to do, is adjust this step of the workflow to run only when the search criteria delivers an actual result. And add another step in the workflow that will change the value to zero if the search criteria count is less than 1.
When you need to default a value to 0, and aren’t sure if you’re going to return a null or a 0, just slap a :rounded to 0 at the end of your search and sum.
As long as you’re not looking for a fraction, its done the trick.
