4 Replies Latest reply on Feb 4, 2009 7:40 PM by miw

    Properly getting a link amongst two relational tables



      Properly getting a link amongst two relational tables


      I have two tables that are linked to one another.  In order to establish the proper links, following good DB design (I think?), I am not allowing the user to actually edit the linked fields on either layout window.  Thinking that TableOne is the Master table, I have autocreated a unique serial number called TableOneID and a unique calculated field, called TableOnePlace, that concatenates two other input fields in TableOne into one name.  On the TableTwo layout, I want to establish the proper link to TableOne.  In TableTwo, I have a field named TableOneLink which I want to be used to establish the link to TableOne's TableOneID field record.  I want to be able to establish the link by having a pulldown in the TableTwo layout that shows the records in the unique TableOnePlace field.  Once that is selected, I want to record the TableOneLink in TableTwo as the TableOneID that corresponds to the TableOnePlace selection in the pulldown menu.


      Issue 1:  I thought I could put the TableOnePlace field on the TableTwo layout in order to create the pulldown menu and select the record I want linked.  However, since the TableOnePlace field is calculated, I cannot make the pull down selection work in the TableTwo layout since it cannot be modified.  What is an alternative to achieve this objective of using a pulldown menu to select one of the TableOnePlace records?


      Issue 2:  Once a TableOnePlace record is somehow selected (after resolving Issue 1), how do I properly get the TableOneLink in TableTwo to equal the TableOneID in TableOne so the link has been properly established?


      I have struggled with layouts and scripts to help resolve this to no avail.  Any help would be greatly appreciated!

        • 1. Re: Properly getting a link amongst two relational tables

          To establish the link you will be entering the ID field, using a drop-down list (or pop-menu). You will not be entering the calculation field (though it can show after you make the link). In the Value List (when you create it) the ID field will be the field on the left side of the dialog. You can choose to "Show values from a 2nd field." That is where you show the calculation field (it must be a stored calculation). 


          You could also choose the option, lower down on the left, to [x] Show only values from the 2nd field. It will then Sort by the 2nd field. The 2nd field MUST be unique (as you say it is). Because otherwise only 1st of duplicate records will be shown; others will be completely ignored by the value list, hence could not be chosen.


          The above will make it appear that you are choosing from the calculation field. But you will always be choosing the ID field, which will establish the link. The relationship should be on the ID field. The calculation does not even need to be in the 2nd table. Though you may want it locally for faster searches. In which case it could brought in by a Look Up, or an auto-enter by calculation.


          It is "redundant" data however. Changes made to the fields in the 1st table will NOT automatically update in the 2nd table. Hence it really better to just leave it in the 1st table, unless it becomes too sluggish to use. Or use a FileMaker 10 script trigger to run a script to update it in the other table(s).

          • 2. Re: Properly getting a link amongst two relational tables

            What you suggested I originally did on my regular layout screen.  I was able to use some formating trickery so that the ID field link does not display (thus not confusing the user) and only the 2nd concatenanted field shows in the pull-down menu.  However, this is not an elegant solution in a table layout - I have to show both fields (the one with the hidden ID field link) and the 2nd field result.

            • 3. Re: Properly getting a link amongst two relational tables

              Create a value list, for the ID field, use the option, "Also show values from a 2nd field, and also the option, [x] Only show values from the 2nd field.


              Attach it to your foreign key ID field, formatted as a "popup menu" (not a drop-down list). It will show only the name field, though the real value set into the ID field will be the ID. It will also work in Table view. 


              The font may look a bit clunky however, as there is no smoothing in popup menus; works OK with Verdana though. 

              • 4. Re: Properly getting a link amongst two relational tables

                Fenton:  Thanks for the additional input!  Showing the field as a popup menu rather than a drop down box works like a charm and solves my problem.  I use a script to make sure that a selection must be made so a link is established.