9 Replies Latest reply on Mar 7, 2016 11:29 AM by fitch

    Populate a child foreign key from parent key

    LENDALNA

      I have two tables - Vendor and Vendor Address - as we receive raw materials from vendors with multiple plants and warehouses. The following is my script code for my button on Vendor. I have also the foreign key to auto enter using the following calc

       

      Vendor Address::Vendor_ID_FK = $$VendorID - When I run the button it either fills in a 0 or nothing at all in the child field. I put a merge variable field on Child Layout to see what value my Variable $$VendorID had and it's correct. If I manually fill in the field and then go back to the Vendor layout the Else segment code works. I'm really stumped as I can't see what I'm missing. Any help would be appreciated.....

       

      add vendor address.tiff

        • 1. Re: Populate a child foreign key from parent key
          BruceRobertson

          If you are going to use that approach, the calculation for the auto-enter value needs to be just:

          $$VendorID


          As it is now, you have written it as a boolean expression and you are getting the expected results.

          Stated in a slightly longer way, your expression really says:

          "Does the value in Vendor Address::Vendor_ID_FK = $$VendorID?

          Give me a yes or no answer. Yes, 1; no; 0."

          • 2. Re: Populate a child foreign key from parent key
            LENDALNA

            thank you very much, I should have realized that with the 0

             

             

            Duffy

             

             

             

            Lendal North America

            1600 North Clinton Ave

            Rochester, NY 14621

            tel 877-496-3584  fax 585-270-5791

            Born In Scotland  -  Made In America  -  Paddled World Wide

            www.lendalna.com

            • 3. Re: Populate a child foreign key from parent key
              LENDALNA

              Bruce:

               

              Is there a better method or approach that you would take regarding what I am doing. I'm new to Filemaker, came from an Oracle and mysql background. Thanks for the help, was a very careless mistake, late night programming...

               

              Duffy

               

               

               

              Lendal North America

              1600 North Clinton Ave

              Rochester, NY 14621

              tel 877-496-3584  fax 585-270-5791

              Born In Scotland  -  Made In America  -  Paddled World Wide

              www.lendalna.com

              • 4. Re: Populate a child foreign key from parent key
                Jade

                Hi Michael,

                 

                I was going to suggest this method:

                https://filemakermisc.wordpress.com/2012/06/30/creating-records-with-set-field-the-efficient-way/

                 

                Your method may cause problems if a user adds a new record in the Vendor Address layout.  In that case the Vendor_ID_FK will be set to whatever is in your $$VendorID.

                • 5. Re: Populate a child foreign key from parent key
                  keywords

                  I assume the task you are trying to perform is to create the related Vendor Address records for all those "multiple plants and warehouses". The simplest way to do this in FM is to create an address portal to show related address records AND to set this relationship to Allow Creation of related records. With that setting, the portal will present a blank record as the last row of the portal and you can create address records simply by entering the details—behind the scenes FM will auto enter the VendorID into the FK field.

                  • 6. Re: Populate a child foreign key from parent key
                    fitch

                    Yes, the Magic Key technique is good to know. Here's another good write-up (the original?):

                    Magic Key, part 1 | FileMakerHacks

                     

                    Also, if you're comfortable with SQL, there are FileMaker plugins that enable you to use INSERT etc. (vs. FileMaker's native ExecuteSQL which only does SELECT).

                     

                    Seems there's always more than one way to do it in FileMaker.

                    • 7. Re: Populate a child foreign key from parent key
                      BruceRobertson
                      Is there a better method or approach that you would take regarding what I am doing. I'm new to Filemaker, came from an Oracle and mysql background.

                       

                      There are a lot of ways to create and select records in FileMaker, I think it's quite good to be aware of the different methods.

                       

                      I see that the method you started with here - auto-enter calc based on a global variable - is demonstrated in several of the starter solutions; but I have never liked it. I wonder what other developers think.

                       

                      Some other methods:

                      Use an allow-creation relationship and portal.

                      Use an allow-creation relationship based on a global field pointed to the record ID of the child record. Note that this also allows the record to be selected for editing.

                      The relationship can be based on an auto-enter record ID such as incrementing integer; or on a UUID

                      Populate script variables, then freeze window; change layouts, add new record, populate fields based on variables, return to original layouts.

                      Variation on above: populate script variables, open new window, select proper layout, create new record, populate, fields, commit, close window. This leaves the original window completely undisturbed. Note that it is possible to open the new window "off screen".

                      PSOS (perform script on server) pass variable to a PSOS script.

                       

                      Personally, I have always been a big fan of global field based auto-create relationships. This can lead to a lot of graph complication, and some people don't like that.

                      Note that some of these methods perform differently on iOS and WebDirect.

                      • 8. Re: Populate a child foreign key from parent key
                        LENDALNA

                        Tom,  thank you, I'm really blown away by the professional help on this site.Really helps a newbie get up to speed, my mistake was stupid, late night programming....

                         

                        Again Thanks

                         

                        Duffy

                         

                         

                         

                        Lendal North America

                        1600 North Clinton Ave

                        Rochester, NY 14621

                        tel 877-496-3584  fax 585-270-5791

                        Born In Scotland  -  Made In America  -  Paddled World Wide

                        www.lendalna.com

                        • 9. Re: Populate a child foreign key from parent key
                          fitch

                          Nice summary, Bruce. Sounds like a candidate for a DevCon topic, or PoE.

                           

                          I tend to shy away from auto-entry in general, other than creation/modification tracking and serial numbers. It seems to inevitably come back to bite me because of some exception to the rule.

                           

                          One other variation worth mentioning: if you're creating a record in another file -- e.g., a log file -- you can call a script in that file that creates the record, and the file's window will stay in the background.