7 Replies Latest reply on May 5, 2017 2:09 PM by philmodjunk

    Further Questions about Populating Child Tables

    gswest

      So I am trying to create a script where when a record in the main table is created, it populates a child table and plugs in some values. This is the script I'm working with at this point:

       

      New Record/Request

           (I have a feeling this needs to be changed but I can't figure out what to change it to)

      Set Field [charCombatTechniques::_fkcharCombatTechniquesID; TDECC::charRecID]

           (this one is also a problem, I want it to copy to parent key into the foreign key)

      Set Field [.....

      Set Field [....;Lookup(...)]

       

      and then it repeats.

       

      The script is indeed creating records, but records in the parent table - TDECC. I would like it to create those records in the child table - charCombatTechniques. The auto entry stuff works fine as does the lookup, but when I run the script it creates brand new parent table records...not what I want.

       

      Best,

      Geoff

        • 1. Re: Further Questions about Populating Child Tables
          mikebeargie

          Easiest way to create child records is to do this:

           

          #Start on parent layout

          Set Variable [ $id ; ParentTable::PrimaryKey ]

          Go To Layout [ childtablelayout ]

          New Record/Request/Page

          Set Field [ ChildTable::ForeignKey ; $id ]

          Go To Layout [ original layout ]

           

          This will create the child record and establish the relationship to the related parent record, which will in turn perform lookups. While you're at the child layout, set any other values you need to as well. Then when you return, you'll already have the work done and visible from the parent record.

          1 of 1 people found this helpful
          • 2. Re: Further Questions about Populating Child Tables
            mikebeargie

            BTW, it is creating new parent table records because you are on the PARENT layout when the new record script step is executed.

             

            The magic word here is CONTEXT...

             

            Important to learn, could be on the certification test...

            • 3. Re: Further Questions about Populating Child Tables
              philmodjunk

              While Mike has outlined the simplest method, it isn't necessarily the best method.

               

              Once you gain a bit more expertise, you might web search on the term "MagicKey". This is a method that you can use to create records in a child table without ever leaving the parent table's layout.

              • 4. Re: Further Questions about Populating Child Tables
                mikebeargie

                Really? Magickey?

                 

                Reasons not to use magickey, since you brought it up:

                1) requires an extra global "magic key" field in each table you want to use it on.

                2) requires an additional table occurrence if you want to display a portal without the empty row at the end.

                3) gets really convoluted for creating a child record for multi-predicate relationships, (vs. using lookups instead of relational keys for better performance)

                4) Can suffer from performance issues (as mentioned here: "Magic key" record creation slow on certain tables )

                5) Scripted commits required when using data separation model.

                 

                Given the nature and flexibility of filemaker, there will always be an argument about what is the "best" way. But since Geoff here doesn't even understand contextual scripting yet, telling him to go google something like MagicKey and expecting him to understand it is definitely not "the best method".

                • 5. Re: Further Questions about Populating Child Tables
                  gswest

                  Mike this worked perfectly. I had considered the go to layout technique but I was trying to figure out a way to do it without that and that is where I got tripped up. The script works great now and populates the child table as intended.

                   

                  Geoff

                  • 6. Re: Further Questions about Populating Child Tables
                    mikebeargie

                    You can create new records through a relationship without leaving the parent layout. But as long as you’re not looking for some sort of performance gain from minimizing your count of script steps, then simple works just fine.

                    1 of 1 people found this helpful
                    • 7. Re: Further Questions about Populating Child Tables
                      philmodjunk

                      Seems like I hit a nerve there Mike. Didn't mean to offend you at all. Apologies if I did.

                       

                      If you read my last post again, maybe this time, you'll note a few details that you missed the first time.

                       

                      Never said MagicKey was best.

                      I suggested this as an option to research after gswest had gained more expertise.

                       

                      Every method has its pros and cons and that's why I suggested it as an option to consider not as a better way.

                       

                      1) it does not require adding an extra field to every table where you need it if you use a connector table

                      2) adding an extra occurrence is easy to do so that doesn't strike me as a problem.

                      3) but we aren't describing a multi-predicate relationship here are we? Didn't say that it was always the best option

                      4) again, all methods must be evaluated and tested in the real world. This issue, though is a new one so I'll have to read your link and thank you for bringing it to my attention.

                      5) Scripted commits are often required in many situations so again, not a major issue

                       

                      In addition, MagicKey:

                       

                      Leaves portal scroll bars unchanged

                      Leaves all slide, tab and popover panels unchanged

                      Leaves all script triggers untripped

                      Enables batch updates in a single database transaction--making for better data integrity

                       

                      The top 3 of those last details have produced quite a few questions by new and intermediate users right here in the forum by people trying to use this simple method (that I often also use).