3 Replies Latest reply on Aug 6, 2013 10:52 AM by philmodjunk

    Creating a record with a foreign Key

    MrMLK

      Title

      Creating a record with a foreign Key

      Post

           I must be tired today, because I can't remember how to do something basic.

           I have Two Tables

           Table A

           ----------      

           Name                       

           Key A1                    

            

                Table B

                ----------

                Number  

                Key B1

                Key A1

                 

           I want to create a form for creating new records in Table B that allows people to type in the number and use a drop down to select the Names from Table A and then automatically populate Table B with the matching Key A1.

            

           This seems so basic to me, but right now I am drawing a blank.

            

           Thanks.

        • 1. Re: Creating a record with a foreign Key
          philmodjunk

               Set up a value list that lists values from Table A. Specify Key A1 as the first field and Name as the second. If you sort the values by the second field, the Name field has to be unique or duplicate values are omitted from the list. This option works best for modestly sized value lists.

               There's also a way to use the Name to look up the ID number and enter it into the appropriate field. This option works better with longer lists of names and can be scripted to deal effectively with duplicate names. That's the method used in this demo file:

               FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                

          • 2. Re: Creating a record with a foreign Key
            MrMLK

                 >  Set up a value list that lists values from Table A. Specify Key A1 as the first field and Name as the second. If you sort the values by the second field, the Name field has to be unique or duplicate values are omitted from the list. This option works best for modestly sized value lists.

                  

            This worked for me, thanks.

            The only issue now is that the field is showing (as you would expect) the Key A1 value rather then the Name.  I can hide the field behind another field that does show Name, but it still shows the value when I press on the down arrow. ALso, that seems a little kludgey to me. Is there a better way to do it?

            • 3. Re: Creating a record with a foreign Key
              philmodjunk

                   You only see the number when there is already a value selected. In another thread, we played around with ideas where the existing value was copied to a global variable and then cleared via an OnOBjectEnter script trigger and then on Object save, put the value back if the field was empty--piling Kludge on top of kludge. That worked but complicated any case where you actually wanted to clear the field.

                   Another option is to use a Pop up Menu instead of a drop down list.

                   And then there's also the method in my demo file.