11 Replies Latest reply on Mar 1, 2017 5:02 PM by philmodjunk

    Extract unique records from related table

    matthewsknight

      Hi, I am trying to extract unique records from a related table, and need some help!

       

      I have a database that is set up to manage a music group. Our schedule is organised into Patches, in which there are various Projects. There are 7 musicians assigned to each project.

       

      From the Patches layout, I would like to be able to produce a schedule, which would list all of the musicians for the Patch. However, since the different Projects can use some (or all) of the same musicians, I only want each name to appear once. It would also be great if the projects that they're involved in could be listed. In other words I want to end up with a schedule that looks something like this:

       

      Mark (Project1, Project3)

      John (Project2, Project3)

      Elise (Project1, Project2, Project3)

       

      Thanks for any help – I'm sorry if my question isn't clear!

        • 1. Re: Extract unique records from related table
          philmodjunk

          Do you have tables and relationships set up for this already or are we starting from a blank page?

           

          It sounds like you need these relationships:

           

          Musicians---<Musician_Project>----Projects>-----Patches    (---< means "one to many" )

           

          I am assuming that a Musician can be assigned to many projects and a given project can have more than one musician. Further, I am assuming that Projects can be further grouped into "patches". What I show here shows that a record in patches can be linked to many projects but that a project only links to a single Patch. That may be an incorrect assumption on my part. But if this data model is correct, A portal, or the list function can list all Musicians for a given project or a given record in Patches and each musician will be listed only once if the portal or the list function references the records in Musicians. And from a record in Musicians, all projects for that Musician can be listed via portal or List function as well.

           

          There is another function that can also list values from FileMaker tables, the ExecuteSQL function. It its not yet clear whether that function need be used here.

          • 2. Re: Extract unique records from related table
            matthewsknight

            Thanks – I have the tables and relationships set up as you suggest.

             

            With the List function or Portal I can get the list of players for a Patch or Project no problem. I can similarly get the list of Projects for each player. What I need though is to narrow this down to a list of projects for each player within a particular Patch. (The projects that a player is involved in outside of the patch in question will be irrelevant to the schedule for that patch).

             

            Thanks again for your help!

            • 3. Re: Extract unique records from related table
              philmodjunk

              From a  record in Patches. Use Go To Related Records to bring up a found set on  a layout based on Musician_Project and sort them by musician. Set the layout up to be a summary report with any of the fields that you need from any of these table occurrences.

              • 4. Re: Extract unique records from related table
                matthewsknight

                And is there a way to do this with a List, so that all the information for each musician is in a single field?

                • 5. Re: Extract unique records from related table
                  philmodjunk

                  In the summary report, you can remove the body and just keep a sub summary layout part "when sorted by" musician. This will give you one row of info for each musician.

                  You can add this calculation field to the Musician_Project table to list all projects for that musician in a horizontal list. I'm separating the list items with commas, but other options are also possible:

                   

                  Substitute ( List ( Projects::ProjectName ) ; ¶ ; " ," )

                  • 6. Re: Extract unique records from related table
                    matthewsknight

                    Thanks, but that List will give me ALL of the projects that the musician is in, whereas what I'm after is the projects in a particular patch that the musician is in.

                    • 7. Re: Extract unique records from related table
                      philmodjunk

                      Good point, it's been a bit since I started responding here.

                      You could use ExecuteSQL here, but sticking with the "non SQL" approach that I started with here, we can rework this calculation this way:

                       

                      Add a "list of" summary field that lists the Project IDs in Musician_Project. Define a calculation field like this:

                       

                      GetSummary ( sIDListSummary ; MusicianID )  // use the same field as you specify as the "sorted by" field in your relationship.

                      Now use this calculation field as the match field to another occurrence of Projects and you can then use the previous calculation but list project names from this added occurrence instead of the original.

                      • 8. Re: Extract unique records from related table
                        matthewsknight

                        I'm afraid I don't seem to be able to get this to work. The two calculation fields are just blank.

                         

                        My table relationships look like this (Promoters & Repertoire are irrelevant for the purposes of this question). Does that seem to be correct?

                         

                        Thanks (and sorry if I'm being a bit slow!)

                         

                        table relationships.jpeg

                        • 9. Re: Extract unique records from related table
                          philmodjunk

                          The relationships look correct thought I'd use a more descriptive name than "projects 3" as a more descriptive name makes it easier to pick the right table occurrence when setting up the needed calculations.

                           

                          The next step is to look at those calculations. Your list function, BTW, should list data from Projects 3, not projects.

                          • 10. Re: Extract unique records from related table
                            matthewsknight

                            The calculations look like this:

                             

                            project players table.jpeg

                            • 11. Re: Extract unique records from related table
                              philmodjunk

                              Why do you have PatchID in this join table?

                               

                              In any case, You should be sorting your records by player ID and Player ID should be the break field used for the GetSummary function call.