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.
There are some details not quite clear. On what layout do you need this conditional value list? With your current relationships, NO records in project_Ressoruces are related to any name field in any table. Nor does your screen shot show any name field other than the project_Name field--I don't think that's the field you had in mind is it?
I think that you can add a new occurrence of the projects table and link it by name to a name field in TimeSheets, then use that relationship for a conditional value list, but am unsure that I have all the details correct as to what you want and where.
My mistake, the relationships in the screenshot should have been between the name fields and not the id fields. Perhaps these 2 images will make everything clearer?
I need the conditional value list on the timesheet layout. I should be able to select a ressource from the first dropdown and then the projects related to that ressource in the second dropdown.
My mistake, the relationships in the screenshot should have been between the name fields and not the id fields.
On the contrary, they should not be by the name fields. They should be by the ID fields. Using names instead of the ID numbers is asking for trouble here.
Do the following (and I am assuming the relationships in your original screen shot--not name based relationships):
In Manage | Database | relationships, make a new table occurrence of ressource by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be SelectedRESSOURCE.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Repeat this process to make a new occurrence of Project named SelectedProjects and a new occurrence of project_Ressources named SelectedPROJECT_RESSOURCES. (I am using All caps to identify the name of the occurrence's data source table.)
Add them to your relationships like this:
TimeSheets::fk_resource_id = SelectedRESSOURCE::id
SelectedRESSOURCE::id = SelectedPROJECT_RESSOURCES::fk_ressource_id
SelectedPROJECT_RESSOURCES::fk_project_id = SelectedProject::id
Define a value for the fk_project_id field to list SelectedProject::id for field 1 and SelectedProject::project_name for field 2. Click the "include only related values" opton and specify TimeSheets as your "starting from" table.
Note: a good way to set up these relationships to make them easier to understand it to start with a new occurrence of TimeSheets and link the new occurrences of the other tables to it instead of the original occurrence. Then set up your timesheet layout to refer to this new occurrence of TimeSheet instead of the original table occurrence.
This method is referred to as "anchor buoy". See this article for more on the concept: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/
Thanks for the detailed reply.
Unfortunately, I have followed your instructions but the projects dropdown list remains empty after selecting a ressource. The ressource value list includes all records from SelectedRESSOURCE::id for field 1 and SelectedRESSOURCE::ressource_name for field 2.
Better check all the details in how you set this up. This is a pretty standard use of relationships to produce a conditional value list so it should work.
You are correct, I had made a mistake.
Thank you very much for your help, I appreciate it. I am new to FileMaker so sometimes the most standard procedures can take me a while to figure out.
Thanks again and have a nice day!