Filter Repeating Group by formula result

Hi friends,

I’m building a system for a group of program instructors to track teacher training (among many other things).

I would like to create a report so that our team can easily see and pay teachers who have reached 6 hours of training.

Right now this system consists of two things: School Staff and Teacher Trainings. Each Teacher Training contains a duration and a list of School Staff who attended.

I’m able to use a repeating group to return a list of school staff and add a field that searches Teacher Trainings for those that contain each teacher and sums the duration of all those Teacher Trainings. So I can see, for example, Homer Simpson has completed 4.5 hours, etc.

But what I’d like to do is only see those teachers who have completed 6 hours or more. It seems like the only way to do this is to add a field to the School Staff thing, which I can then filter by (i.e., Fully Trained = yes or no).

The problem is how to get Bubble to check that box for me. Is this a calculation I should run every time the site loads? Or whenever a new Teacher Training is added? And how do I have Bubble look at the list of teachers, run that calculation on every one, and make the change to all of those for which the calculation returns >= 6 hours?

It’s actually a bit simpler to show the list of all staff but “flag” those who’ve completed 6 or more hours - i.e. make those rows appear visually different. IOW, the list wouldn’t be filtered per se, but the individuals who satisfy the condition will “stand out” in the list. This approach would make use of a formula in a Conditional of an element in the RG.

That said, it is indeed also possible to actually filter the list. That approach would implement the formula as part of a filter after the search query.

The following thread has an example which illustrates both approaches, but that example is actually more complicated than you need, since it uses multiple conditions, and you have only one (6 or more hours of training completed).

Perhaps it will help though…

Thanks for this. I’ll try out some of the solutions.

I’d also be happy with being able to sort by number of hours completed (descending), but I’ve already discovered how complex that can be to implement …

It seems like sorting should be straightforward, although I haven’t actually tried to do it on a computed value. :neutral_face:

Just tried it, and yes, using conditional formatting is a quick way to get this information, and certainly better than having to scroll through and find teachers to pay by hand (and much better than waiting for instructors to email us teachers’ names!).

Yeah, it seems that way, but when the field you’re sorting on is part of a different (connected) thing, it doesn’t show up in the sort by list.

I’ve gotten around this in the past by adding a duplicate field with text of the connected thing. But I’m not sure how to make that work with calculations.

I wanted to update this thread with the solution I came up with for this:

I created a field in each Teacher record for total hours, and made a button that carries out a workflow that searches Teacher Trainings for each name, sums the duration of any session that includes that teacher, and then populates the “Training Hours” field in each teacher record with the total hours.

The two issues with this as is are that, A) it’s a manual refresh, and B) with 41 schools and counting, this is a very slow operation.

To solve A, I pasted the workflow onto the button that opens the popup that contains the repeating group (now filtered for teachers with Training Hours >=6) so it’ll update the hours automatically.

To solve B, I created a new data type with a date field called “Stipend Refresh Logs,” and I created an entry for today since I know that the training hour sums are accurate as of today.

Then I added a step to the workflow that creates a new Stipend Refresh Log every time the popup window is opened, and amended the calculation step to only run when a search for teacher trainings that have occurred since the last Stipend Refresh Log date is not 0. That way, it won’t run at all if no more teacher training sessions have occurred. When it does run, it only sums hours from training sessions that have occurred since the last refresh and adds that total to whatever number is already in the teacher’s total training hours field.

Hopefully this is helpful for anyone else trying to filter a list by a calculation.

TL;DR: Basically by logging each time the workflow runs, you can limit the calculation to data that has been added since the last run and drastically reduce the workflow run time, making it feasible to update the calculated field on every item and thus filter by that result.

1 Like