1 of 1 people found this helpful
I would use one join table not three. I'd add a field in the join table for identifying the person's role in the project. If a person has to where two or more "hats" in that project, you can create more than one join table record linking that person to the project, but with a different role specified in each.
This can give you a single portal on your People and Projects layouts and there are a number of nice summary reports that you can create from layouts based on the join table.
Thanks, Phil. Makes sense.
I have a similar situation exactly regarding people's various roles to a project, and found this thread to answer to my question- should I have multiple join tables or a single with a "Role" field.
My question now is: If one of the Roles is "Project Manager" and from the project table/records I want to show who is/are the manager(s) of each project my instinct is to still create an additional join table, but with an additional Global Key field "Project Manager" to match "Role" in the join table. Is this the most elegant solution?
The only alternative I can think of is a filtered portal, but I'd rather not having a scrolling portal in multiple matches, and instead be in favor of being able to list and manipulate multiple entries, or access the fields of the Project Manager's record from further away in the database. Perhaps using a calc field in the rpojects table? I've always thought with multiple related records a calc will simply deal with the first record so I don't know how it could find the records with the correct role.
I'm fine to create another table occurrence, but between these single use TOs and those used just for creating certain value lists, I feel my relationships diagram and TO list gets bloated and difficult to navigate and I'm not sure if that's normal. For what seems like a modest solution I am already up to 40 TOs, but I realize there are just so many ways the data relates to itself, and each TO does seem to serve a unique and necessary purpose.
Many thanks for any and all advice,
There are several easy to retrieve multiple (related) records as a list, even more so in FM 16. The thing we still miss is a way (well, an easy way) to aggregate values conditionally.
Project --< Team >-- People
create a calculation field (or an expression in the Data Viewer if you've FM Advanced) with
Substitute ( List ( People::name ) ; Char(13) ; ", " )
You could use
ExecuteSQL ( "
FROM Team t
JOIN People p ON t.id_person = p.id
WHERE role = ?
" ; "" ; "" ; "Project Manager"
If you want to avoid another TO and don't feel like SQL (or other, more involved methods, not outlined here), try this:
In Team, create a calc field, type text, as
If ( role = "Project Manager" ; People::name )
and in Project, use
Substitute ( List ( Team::cNameIfManager ) ; Char(13) ; ", " )
Why not use a filtered portal? It's simple to set up and I don't follow your reasons against using a filtered portal.
It it is also possible to set up multiple relationships, each to a different occurrence of the same join table, but that is still just one join table.
Primarily for a list view of projects as an overview to list project managers, to me it's awkward to have filtered portals in list views. In the detailed view I indeed have used filtered portals for certain roles and then a catch all for all others.
Make it a list view based on the join table. You can sort to group your records both by project and then by by type. A sub summary part can show data from the project and fields from People can be included in the body.
True and simple, but creates a fair amount of white space adding lines for each manager no? Essentially each project would then take two lines, the summary line of project fields and the people line with possible additional people lines, correct? My "Projects" list is 1509 records so any method that keeps each entry as a single entry is ideal for me. For me, while possibly taxing computationally, the use of calc fields as suggested by erolst results in the clean density I'm seeking.
Yes, but do you list all 1509 records at once? You certainly don't need to.