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.
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!
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.
And is there a way to do this with a List, so that all the information for each musician is in a single field?
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 ) ; ¶ ; " ," )
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.
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.
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.
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.