2 Replies Latest reply on Mar 20, 2010 2:02 AM by paint

    Relationships through ID# but still see informative text



      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

      - 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

        • 1. Re: Relationships through ID# but still see informative text

          You can place the related field (e.g. Team::LastName) on the layout of ProjectTeam. Make it non-enterable so they cannot modify it acidentally. If you prefer, you can place it over the ID selection field.


          Another option is to use a popup menu insted of drop-down, with the value list showing only second field - but this requires the names to be unique.

          • 2. Re: Relationships through ID# but still see informative text

            I did as you said and hid the actual selection field (Team::_kp__TeamID) behind a calculated, non modifyable field (Team::Z_TeamNameFull). If i set the background color right the underlaying field is completely hidden.


            When clicked upon a dropdown shows with values from the "Z_TeamNameFull" but actually the TeamID is stored.


            _kp_TeamID = Primary identification key of the Team table, unique value. i.e. "105"

            Z_TeamNameFull = Calculation field; (First name + last name) i.e. "John Andersson"


            Thank you comment!

            The solution was not actually what i thought it would be but the result is very much what i was looking for.