AnsweredAssumed Answered

Relationships through ID# but still see informative text

Question asked by paint on Mar 19, 2010
Latest reply on Mar 20, 2010 by paint

Title

Relationships through ID# but still see informative text

Post

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.

Example:

 

I have the following tables

- Projects

- Team

- 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)]

 

Rgds Paint

Outcomes