Database search - seems like it should be simple, but I'm stuck! Help!

I thought I was OK at Bubble Database searches, but this one’s got me stumped.

I just want to search within a application data table (Support Visits), for each School in the table, whether the last visit was marked ‘Yes’ under Concerns, and count how many schools this applied to.

Take the following simplified example table

In this case the search should return the value of ‘1’ (only one school had concerns at the last visit, DEF School).

NB: The value in the ‘School’ column is a Data Type in it’s own right (‘School’ which has its own fields e.g ‘name’ ‘country’ etc. ), which possibly complicates things.

Is there a way to run a search like this? I’m going round in circles and getting nowhere, so thanks for your help Bubble database search gurus!

Where does Concerns field live? Under Support Visits data type? If yes - do a search for Support Visits where concerns = yes

Thanks so much for the suggestion. Unfortunately that search would return all results (visits) where there was a Concern (‘Yes’) -i.e. 3 in my above example- not those where only THE LAST visit to the School revealed concerns.

What about adding another constraint for the date?

I’m afraid you’d need a field last visit that you should update in a trigger from yes to no each time new visit is created. Otherwise it’s gonna be a headache everywhere as bubble database queries are very limited.

1 Like

Thanks @vladimir.pak that does seem like a viable work around!

Although if anyone else can suggest some clever way to set up a more sophisticated query like for the one I was trying to do, please do share so we all can learn - thanks!!!