Referencing a top level data value from a nested query

[saeed.t.richardson]

I have an “Association” table with three fields: “institution”, “applicant,” and “status”.
I am creating a query with constraints on the “Association” table (on the “institution” and “status” fields) that then pulls in applicants and uses a “filtered” list function. Esentially, I am trying to do a “join” query on the “Applicant” table.

(Here are the “Association” details.)

(And here are the “Applicant” details from the “filtered” list options.)

Note that my type is “Applicant” and in the second image, I can access the “last_name”, “first_name”, etc. values from the Applicants table.

The problem is that I cannot access anything from the “Association” table. I cannot add a new constraint in the previous image. I cannot display a value from the “Association” table in a repeating region.

Is this a bug? Is there a way to do this? I consider myself fairly versed in Bubble but this seems a strange problem. I’ve run queries like this before but never needed to access values from the first table.
Thanks in advance for the help.

[mebeingken]

Shouldn’t you be having a Type of content as Association?

So, Data Source becomes Search for Association with the same constraints you have >> :filtered >> Advanced >> This Associations’ applicant’s last_name contains Input Last Name AND This Association’s applicant’s first_name contains Input first name AND This Association’s applicant’s aamc_id contains Input AAMC AND This Association’s applicant’s institution = SearchBox Institution

–Ken

[saeed.t.richardson]

@mebeingken good question. It should be Applicant. The “Applicant:filtered” query supplement makes the object of the query be Applicant. If I were just searching for an Applicant I’d just run the query directly, but since I want to find the Applicants from the Associations with a particular insitution and status I’m running the query this way. Bubble doesn’t directly do joins, but this is a way of getting the same details.

Also, the Association table doesn’t have those values you’ve mentioned (last_name, first_name, etc.). Those values are only in the Applicant’s table.

I’ve run this type of query before but never had to get the top level values until now. And now I see I can’t access them.

[mebeingken]

So you are trying to get Associations matching Institution and Status, that have as children a list of Applicants matching last/first/aamc/institution, correct?

Like this?

Main search:

Filtered:

Search within filter:

Which would allow you to then reference the Association fields, including the Applicant for those Associations, as a nested repeating group.

[saeed.t.richardson]

Thanks SO MUCH!!! That’s exactly what I needed. You would not have believed how much this plagued me.
I’ve actually not used the advanced operation before; so I’ve learned something new.
Thanks again.