6 Replies Latest reply on Aug 3, 2012 11:53 AM by BenjaminDestrempes

    Help with getting related records from linking table to display in dropdown list

    BenjaminDestrempes

      Title

      Help with getting related records from linking table to display in dropdown list

      Post

      Good afternoon,

      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.

      example.jpg

        • 1. Re: Help with getting related records from linking table to display in dropdown list
          philmodjunk

          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.

          • 2. Re: Help with getting related records from linking table to display in dropdown list
            BenjaminDestrempes

            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?

            http://imgur.com/KjAAS,6sBLZ

            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.

            • 3. Re: Help with getting related records from linking table to display in dropdown list
              philmodjunk

              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---<SelectedRESSOURCE-----<SelectedPROJECT_RESSOURCES>-----SelectedProject

              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/

              • 4. Re: Help with getting related records from linking table to display in dropdown list
                BenjaminDestrempes

                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.

                • 5. Re: Help with getting related records from linking table to display in dropdown list
                  philmodjunk

                  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.

                  • 6. Re: Help with getting related records from linking table to display in dropdown list
                    BenjaminDestrempes

                    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!