Hi, still learning bubble (i know SQL so could be a hindrance).
simple example - I have Mechanics data type that is a child of Workshop data type as per below. when i want to search Mechanics for a type of mechanic (all good) but i also want to search for a named Workshop. To do that I would needing to say Workshop’s Workshop Name = ‘West End Workshop’… but in the Constraints dropdown it lets me pick Workshop but not the field within Workshop I need to test. What do I need to do to be able to do this in the design menu (eg also add List of Mechanics to each Workshop - surely not?) Or can’t it be done in Design and I need to do it in Workflow? thanks in advance Jan
Hi there, @jk11… with the set up you have in place, you need to use an advanced filter on the search for mechanics to get access to the workshop’s name.
Oh, and just a heads up… when you go to add the constraint on the filter, the Advanced option is all the way at the bottom of the dropdown. It can be pretty easy to miss if, even after 2.5 years of using Bubble, you still aren’t in the habit of finding what you need in dropdowns by just typing what you want instead of scrolling around like a Neanderthal, which I mention for no particular reason because nobody would do that… well, certainly not me, of course. 
Hope this helps.
Best…
Mike
1 Like
spot on works - thanks Mike
jk
2 Likes
SQL can be a hindrance here, for sure 
What you are thinking is hmmm need to (inner) JOIN Mechanics and Workshops so …
SELECT Workshop.Name, Mechanic.Name
FROM Mechanic
INNER JOIN Workshop
ON Mechanic.CurrentWorkshopId=Workshop.WorkshopID;
Whereas in Bubble, being Object Relational, the Mechanic has their Workshop embedded in the record, so the Join is already done.
To do what you are doing you need to do …
So your join is effectively saying join where this object (“thing” in bubble terms) is the same object that is embedded in the Mechanic table.
The “first item” is there to tell bubble that there should only be one (even though that may not be the case) Workshop with that name. We don’t do database constraints here
However … it is far more likely that you will know the actual Workshop in advance. So you are on a workshop page, or have a dropdown that the user has selected. Or even if they have searched for a workshop, you can use a search box to determine which Workshop you want.
Then it is a much simpler search, You just say … Where the workshop is THIS ONE I ALREADY HAVE HERE. You don’t need to then find it again. No need for keys.
Bubble is much more visual in that you can point searches at things on pages and say “The one in there please”. So in this case…the Workshop that is selected in the Dropdown.
1 Like
Given the simplicity of this query, an advanced filter is probably not the most efficient way to do this. I would suggest leaving them for when you really really have to.
In this case, you will probably have the Workshop thing knocking around anyway.
In DB2 terms … an Advanced Filter is like a Tablespace scan.
1 Like
this is gold, thanks for this Nigel, yes works, and I understand the advanced filter pros and cons better too.
ta again
jk
1 Like
Echoing jk11’s response, thanks so much for the clarification, Nigel… gold, indeed. I still struggle (obviously) with finding the most efficient way to do things in Bubble, so the additional knowledge is greatly appreciated. Thanks again, man.
Best…
Mike