Relationships through ID# but still see informative text
In my company we have handling our projects through Filemaker for years.
I recently got the job of designing a completely new database set when we are merging with our sister companies. Probably since I am the one with most knowledge about FileMaker (still, I am a chemist as a profession).
I am trying to streamline the database as much as possible thus not making it an unsearchable swollen monster like it is today. I have one major question that has come up and which influences several instances of my design. Probably it is quite simple to answer but still.
I want to link different tables though ID numbers instead of text fields.
I have the following tables
- ProjectTeam (connecting Projects table with team table)
Essentially the ProjectTeam table should be able to consist solely of three numbers (two foreign keys) . The primary key from a project, a primary key from the team member and a primary identification number from the ProjectTeam table itself.
That way if someone changes his last name in the Team table, the Project will automatically have the right name.
I have been able to link the keys together and by using “second field” on my value lists I can choose “John Andersson” for example and still get the correct ID number (say #146). The problem is that I still want the name of the members to show. A list of ID numbers does not say very much to anyone. Is there any way around this problem?
I could have a calculation field show the name “John Andersson” but it seems unnecessary and unintuitive to choose a name on a drop down menu just to see it replaced by a identification number and the name appearing in another place.
[WinXP, FM9 pro (previously 6)]