12 Replies Latest reply on Dec 20, 2012 2:22 PM by StephenWonfor

    Creating records with Set Field


      A while ago, I picked up the technique of creating related records using a global field in the parent table, set to “”, and related (in a “create” relationship) to a field in the child table which would never be equal. Hence the creation is forced when setting any other field in the child record. So far so good. Like this, in a situation where the parent record is a Session (SSN) with one or more related Room Booking (RBK) records:


      Set Field [SSN::SSN_g_RBKCreate; “”]
      Set Field [SSN to RBK for creation::RBK__kf_RSC_ID; $CurrentRoomID]


      But then I needed to set a further field in the newly created child record. Simple, I thought, just do a further Set Field, via the same “create” relationship, like this:


      Set Field [SSN::SSN_g_RBKCreate; “”]
      Set Field [SSN to RBK for creation::RBK__kf_RSC_ID; $CurrentRoomID]
      Set Field [SSN to RBK for creation::RBK_RoomSetup; $RoomSetup]

      But I now see that the second Set Field via the create relationship just creates a second record, which is definitely NOT what I want, although I understand why it's doing it.


      So what I’m now doing is using a second global field in the parent table, and relating that to the relevant field in the child table. Then, setting the one field through the create relationship has the effect of creating the record, and setting other fields involved in the relationship. So the script is now like this:


      Set Field [SSN::SSN_g_RBKCreate; “”]
      Set Field [SSN::SSN_g_RoomSetup; $RoomSetup]
      Set Field [SSN to RBK for creation::RBK__kf_RSC_ID; $CurrentRoomID]


      It seems to work fine, but is this the best way to achieve what I want? I’d really appreciate any feedback.





        • 1. Re: Creating records with Set Field

          Huh? Why don't you go just to the layout with the related table, create a new record and fill in foreign key and all the other info just there?

          • 2. Re: Creating records with Set Field

            Search for "Magic Key", these blogs will get you started:

            http://www.filemakerhacks.com/?p=2364 "Magic Key, part 1"

            http://www.filemakerhacks.com/?p=2440 "Magic Key, part 2"


            I use the method to create and EDIT related records (if you clear the global you can create a new child record, if you enter the related childID you can edit the child record).



            • 3. Re: Creating records with Set Field

              Dave, here is what I do. When I need to create a related record. I copy the foreign key into a script variable ($variablename vs. $$variablename) I then navigate to the layout with related table and create a new record in the script and paste the value of variable in the appropriate field. Finally the script navigates back to the original layout if that is what needs to happen or stays on the related layout if the user needs to fill out more information. Hope this helps.




              • 4. Re: Creating records with Set Field

                Basically because it just seems more elegant, and presumably quicker - much fewer script steps, switching layouts, etc.


                • 5. Re: Creating records with Set Field

                  Thanks Beverly, these are really useful articles.  There is reference to possiby having to do a Commit between after the "Set Field" which creates the record, before doing other "Set Fields" to populate the record further, and sure enough, I'm finding that to be necessary.  i.e. if I don't do the Commit, I get the extra record, but if I DO do the Commit, the subsequent Set Fields correctly populate the same record.  Strange.  Maybe this is because it's a separated solution. 


                  Anyway, I now realise that my options are EITHER to use the multi-predicate option that is explained in the "Part 2" blog post (and which is what I had found to be working when I wrote my original post), OR use mutliple Set Fields, with a Commit after the record-creating one. 


                  Thanks again for your help,



                  • 6. Re: Creating records with Set Field

                    Thanks Paul.  The "magic key" thing seems to me to be more slick, but I appreciate your help re. what I guess is the more "traditional" approach.



                    • 7. Re: Creating records with Set Field

                      Dave, I'm not sure if it is more "traditional" but a "brute force" solution.




                      P.S. I'm also looking into the "magic key" posts. Thanks Beverly.

                      • 8. Re: Creating records with Set Field

                        "Magic key" is a very unfortunate name for a fundamental and intended feature of FileMaker relationships: "allow creation of related records".

                        • 9. Re: Creating records with Set Field

                          And virtual technique is just an unfortunate name for fundamental concept of using global variables with a table ... all native FM as well.  And anchor-buoy is unfortunate name also and ...


                          Naming a specific approach does not mean it is unfortunate ... in any way, Bruce.  :-)

                          • 10. Re: Creating records with Set Field

                            No, I think "magic virtual technique" and "magic anchor-buoy" would make their names unfortunate.

                            • 11. Re: Creating records with Set Field

                              As an aside ... I have a technique I call "TwoForOne" because it creates a new record in two different tables simultaneously without script, based upon existing standard relationships.  It is using native FM and even WITHOUT Allow Creation to one of the two tables (between it and the table writing to it) a record is created in it anyway.  It is very handy.  So although Allow Creation is an intended feature, it can be flexed and used in unexpected ways.  THIS is the magic.


                              I mention TwoForOne because naming a technique groups a concept into a package which provides additional functionality beyond the value of its parts ... native FM or not.  Name away I say and show us all the possibilities!  Mankind does not invent; we only discover and it is in the daily discoveries that the fun happens. :*]

                              • 12. Re: Creating records with Set Field



                                I use a technique possibly called "Faux Import" to avoid the agony of import mapping between densely populated data tables. (I do a lot of renovation work on client-created home-made FMP solutions).


                                I add to every table two fields _gtTempLink (global text) and _stTempLink (text) and I make sure that each TOG [I am one of the Anchor Boys] anchor has a _gtTempLink::_stTempLink relationship.

                                Rather than importing data and using that awful import mapper (awfuk when there are a few hundred fields) I write a looping script that uses a loop counterthay increments within the loop and glues it to a timestamp in _gtTempLink.  So it might get  "12/21/2012 14:30:23 - 234".  I use a bunch of manageable SetFields to write the data into the other table - making sure to populate the foreign keys for the legitimate joins.  Nice thing about this technique is that you don't get caught out by import map failures when fields are added or removed from the tables.


                                I very much doubt that I came up with this technique - I'm sure I learned it from someone somewhen.



                                "It's a poor sort of memory that only works backwards." --- Lewis Carroll