Help with getting related records from linking table to display in dropdown list
I am running into a simple issue on my FMPro 12 Advanced database.
I have a table named projects and a table named ressources. I want to be able to assign multiple ressources to multiple projects and I tried doing so using a table named, for our example, project_ressources.
The project table contains the id and project_name fields. The ressource table contains the id and ressource_name fields.
The project_ressources table contains the id, fk_project_id and fk_ressource_id fields. The fk_project_id field is linked to the id field of the project table and allows the creation of records on project_ressources. The fk_ressource_id field is linked to the id field of the ressource table.
On the project table is a portal displaying related records from the project_ressources table and that portal contains a simple dropdown displaying the names of the ressources. Selecting a ressource creates a record in the project_ressources table linking the IDs of the project and the ressource. So far, so good.
My issue starts when I want to display a dropdown menu listing projects only related to a specific ressource identified in a field on another layout called timesheet. Basically, if that field says John Doe, I want the dropdown menu to display only the projects related to John Doe in project_ressources. I have not been able to do so using value lists. My value list shows the project_name field of the project table but if I choose to only show related records based on the project_ressources table, nothing shows up. What am I doing wrong?
Attached is a screenshot of the relationships.