3 Replies Latest reply on Jan 15, 2013 11:01 AM by philmodjunk

    Write 1st Related Child Record and Auto Set Key Fields

    hoffmanjan@umkc.edu

      Title

      Write 1st Related Child Record and Auto Set Key Fields

      Post

           I use FileMaker Pro 11.  I have a ParentTable with an IDNumber as unique key.  IDNumber is fingerprinted by the user in initial creation of a ParentTable record and should never need the user to input again.  ChildTable is related to Parent Table by IDNumber as one to many.

           My question:  How do I write the initial 1st record in ChildTable without forcing the IDNumber to be typed in by the user?  There might be multiple ChildTable records with the IDNumber or there might not be any records.  New Record Request requires input from the user.  I need to set ChildTable::IDNumber to the value of ParentTable::IDNumber and automatically populate in the ChildTable.  Never will a ChildTable record exist without it's related ParentTable record.

           Thx,

           JanH

        • 1. Re: Write 1st Related Child Record and Auto Set Key Fields
          philmodjunk
               

                    IDNumber is fingerprinted by the user in initial creation of a ParentTable record and should never need the user to input again.

               Ideally, ID numbers that you use in relationships in your database should not be input by the user. They should be auto-entered serial numbers or use Get ( UUID ) in an auto-enter calcualtion. (Get ( UUID ) is a new function in FileMaker 12.) Perhaps that's what you are doing here, but I don't know what you mean by "fingerprinted" in this sentence.

               Given this relationship:

               ParentTable::__pkParentID = ChildTable::_fkChildID

               There are six different methods I can think of that automatically enter a matching value in _fkChildID to link it to a record in Parent. Your layout design and user needs will make one or the other the best option for you:

               Enable "allow creation of records via this relationship for ChildTable in the above relationship and:

               1) Put a portal to ChildTable on your ParentTable based layout. When you enter data in the blank "add" row of this portal, Filemaker will automatically enter the value of __pkParentID into _fkChildID of the new record this action creates. By using a portal, you can create as many related child records as you need.

               2) Just put fields from ChildTable on your ParentTable layout. When you enter data into any of these fields from ChildTable, the "allow creation..." setting will kick in and automatically create a new child record and enter the ID values for you just as in 1) unless at least one such related record already exists. This option limits you to just one such related record, however.

               3) The script step: Set Field [ChildTable::anyDataField ; "somedata"] will also create a new related record in ChildTable and copy over the ID values if one such related record does not already exist for the current ParentTable record. This must be executed from the context of a layout based on ParentTable in most cases and like 2) above only works to create the first such related record.

               The following options do not require the "allow creation..." setting in Manage | Database | Relationships...

               4) Sometimes you need/want to change layouts and/or windows to switch from a layout based on parent to a layout based on ChildTable where you may choose to create any number of new child records and you want them all to automatically link to the current record on the ParentTable layout. You can set up a script performed via script trigger on the ParentTable layout that copies the current value into a global variable or global field. You can then set up an auto-enter calculation for ChildTable::_fkParentID that automatically enters the value of this global field or variable each time a new record is created in that table. This requires careful work to ensure that this global field/variable always has the correct value.

               5) If you are going to have a script copy the ID number into a variable, you can have the script do the rest of the job for you as well. It can copy the ID into a variable, change layouts to a layout based on ChildTable create a new record and copy the ID from the variable into the _fk field of the new child record.

               Set variable [$ID ; ParentTable::__pkParentID ]
               Go To Layout [ChildTable ( ChildTable ) ]
               New Record/Request
               Set Field [ChildTable::_fkParentID ; $ID ]

               This method is often used to add new records to a portal to save the user from having to scroll multiple records up to get to the "add" row you'd need to use with method 1) above in order to add the new record.

               6) Often, you select the Parent Record for a new child record via a drop down list, pop up menu or other custom value selection device. These methods can be set up to display a name or other information from the Parent Table so that the user can select a parent record without needing to know the ID number, but the value list or other construct then enters the corresponding ID number for them. Value Lists, for example can list values from two fields, the ID number for "field 1" and a name or description field from the same table for "field 2".

          • 2. Re: Write 1st Related Child Record and Auto Set Key Fields
            hoffmanjan@umkc.edu

                 Thank you so much!  I used #5.  My portals in FileMaker Pro 11 did not show an Add Row; maybe an older version.  But #5 worked like a charm.  I'm new to FileMaker Pro but have worked in relational databases on the AS400 for 18 years.  I know what I want to do.  I just don't know the mechanics yet.  smiley

            • 3. Re: Write 1st Related Child Record and Auto Set Key Fields
              philmodjunk

                   All versions of FileMaker from 3 through 12 have an "add" row in their portals if "allow creation" is enabled. It looks like any other row in the portal, but you'll find that you can add data in the first blank row to appear after the last visible record in the portal. (If your portal is empty, this will be the very first row in the portal.)

                   Depending on how you format the fields and portal, this can appear as though you have an extra blank record in your portal.