Forum Academy Marketplace Showcase Pricing Features

Table Join to create repeating group

Hi All
Apologies if I’ve asked this before (I think I may have - but can’t find the post or any similar).
My app is for a flying school … I have a table of aircraft which holds amongst other details the date/ time of the last maintenance. Then a table of flights which holds (again amongst other things) the aircraft and the date/time of the flight.
What I want is to get all flights (for all aircraft) where the flight date is after the last maintenance date for that aircraft. I could do this in SQL but I can’t for the life of me see how to do it in Bubble.
I think I can do it an aircraft at a time … but I really want to build a repeating group of flights for all aircraft.
Any help appreciated.
David

Hi there, @mrdbetts… after thinking about it for a bit and building out a quick example, the only way I can get to the desired result is to use an advanced filter.

While that filter works like a charm, it’s not going to be great from a performance perspective because the system will have to return all flights to the browser and then filter them on the client side. I tried storing an aircraft’s last maintenance date in the Flight data type itself, but I am still not able to get the desired result by using only constraints on the search for flights.

Anyway, I might be missing something simple, and I will probably keep playing around with it for a bit, but I didn’t think it could hurt to throw the advanced filter option out there.

Best…
Mike

Well, something came to mind, of course, shortly after I posted that response.

I am never opposed to adding an extra field or two (especially if it will help with performance) in order to achieve a desired result. That being said, one way you could go is to add yes/no field called flight after last maintenance (or whatever you want to call it) to the Flight data type. Then, when a flight is created, compare the flight’s date to the last maintenance date of the associated aircraft, and if the former is greater than the latter, set the aforementioned field to yes.

With that setup in place, your repeating group’s search is as simple as a search for flights with a constraint where flight after last maintenance = yes.

Anyway, that might be how I would go about it, and I hope this helps.

Best…
Mike

2 Likes

Hi Mike
Thanks for looking at this. I’ll try the advanced filter. Your second option is a nice idea but won’t work because when the plane next goes for maintenance the date is updated but the flight after last maintenance flag would still read yes for earlier flights!! But again thanks for the thought.

David

1 Like

Wait, you mean aircraft get maintenance on a regular basis?!? :slight_smile:

Ug, sorry about that suggestion… I definitely didn’t think it all the way through.

Wouldn’t you update that field also at the time of maintenance?

2 Likes

Ah, to Jared’s point, you could have a workflow (likely backend) that makes changes to a list of things (an aircraft’s flights, which you could store as a list on the Aircraft data type) when an aircraft’s maintenance date is updated to change that field from yes to no, and that still might be better than going with the advanced filter.

Anyway, like all things with Bubble, you’ve got options.

2 Likes

interesting idea, thanks

… I used the advanced filter. The page is only looked at say once a month so a delay is acceptable. I also limited the search based on flight date > earliest maintenance date so only a subset of flights is being returned to the browser …

1 Like