8 Replies Latest reply on Jan 7, 2016 7:43 AM by planteg

    Create a record without a Go To Layout




      as a test, I am trying to create a solution where there is only one Layout, for the iPad. Up to now, its working pretty good, but I can't create new records. I know the reason, because the relation is established on a non-modifiable field. Here is a look at the Layout (named Globale):



      Here is the Relation Ship Graph (the interesting part is in the green rectangle) :



      The Liste_Clients TOC is used to display the Customers List on the left, and the Detail_Client TOC is used to display the details for a Customer. Both are portals, the right one displays only a large single row. Globale::ID_Globale and Detail_Client::ID_Clients are calculated field set to 1. When I click on a client on the left list, I set a global variable to the value of the key, and the right portal is filtered on that global field value. I I try to create a new record in Detail_Client, I can't because of the nature of the fields used to set the relationship.


      I got to FileMaker Training Series Advanced for FileMaker 14 (I use FMPA 14), and found out on page 107:


      No way to create records through a relationship based on non-editable fields.


      However, it is possible to use scripting techniques to create the record and accordingly set the data so that the non-editable field will contain the match data. Scripting is covered in Module 5 (Solution Logic: Scripting).



      I got to Module 5, but didn't find the way to do it. How can I achieve my goal ? I though instead of filtering the right portal I could add a global field to the Globale table, say z_Selected_Customer, set this field to the key of the selected Customer in the list and add match fields to the relationship Globale::z_Selected_Customer = Detail_Client::_pk_Client to display the details of the selected customer. Would that work then creating a new record in Detail_Client, emptying the z_Selected_Customer field first, event if the other match fields are non-modifiable ?



        • 1. Re: Create a record without a Go To Layout

          You can use techniques such as the Selector-Connector model and the so-called "Magic Key" trick to create a record in any table you want. Just create a relationship based on a global field on the parent side and the primary key on the child side (assuming the child table has an auto-enter key of some sort). You'll likely need a new TO just for this purpose, but that's relatively trivial.





          • 3. Re: Create a record without a Go To Layout

            One option is to add two new global fields to your Globale layout:





            I'm presuming that the plus button under the Liste des Clients is to create a new client.  If so, use a popover button and place the two new global fields inside.  I would also add a Cancel and Continue button and pass the parameter "Cancel" and "Continue" (really, only one is needed but it doesn't hurt to pass both.


            Script would look something like this:


            Set Variable $result ; Get ( ScriptParameter )

            Set Variable $prenom; Globale::Prenom_g

            Set Variable $nom; Globale::Nom_g


            If [ $result = "Cancel" ]

              Close Popover

              Exit Script



              Go to Layout Clients

              Add New Record

              Set Variable $clientID ; _pk_Client

            Set Field Clients::Prenom ; $prenom

            Set Field Clients::Nom; $nom

              Go to Layout Globale

              Set Field ID_Globale::$clientID

            End If


            You'll need to be able to edit the fields in Detail client on the right.  If you don't want to allow that then you'll need to create more global fields such as






            and make them variables in the beginning of the script and then set those Client::field; $variable

            • 4. Re: Create a record without a Go To Layout
              Stephen Huston

              And you need to be sure that your relationship graph has set the specific relationships between the layout table and your new_record target table to Allow Creation of Records via this relationship.

              • 5. Re: Create a record without a Go To Layout

                Here is another option that has minimal scripting, an extra table occurrence, and a filtered portal.


                Click the Add Client button and enter the Client Info.  The added client automatically appears in the list, though you may need to refresh the portal over the LAN/WAN.

                • 6. Re: Create a record without a Go To Layout

                  Hi Daniel,


                  I had a look at your example, and I am mystified - still a lot to learn . Your method to add a new record in CLIENT seems to be a variant to the "Magic Key" method. At first I had something similar, but the relation was established on CALCULATION fields and were not modifiable. As far as I understand, your technique works because on the right side the field is auto-entered with a value, then modifiable, and on the left side the value is already set to 1. Does that mean you may create a record through a relation even though one of the fields is not modifiable ?


                  I have another question: in your script, you change one field in GLOBAL (clientID_g), but don't commit. That means there is a record with an open state in GLOBAL, I thought that would prevent creating a record in a different table . I ask because if I ever build that solution, it will be used by more than one user, so I will need to have GLOBAL populated with global fields.



                  • 7. Re: Create a record without a Go To Layout

                    You may be over thinking it.  It is pretty simple. In the Relationship GLOBAL::constant_1 = CLIENT::constant_1 is almost the exact same as GLOBAL::constant_1 = CLIENT_new::constant_1


                    The only different is that in GLOBAL::constant_1 = CLIENT_new::constant_1 the checkbox is marked for "Allow creation of records..."


                    That's the first part.  Now, let me explain the field "constant_1".  I have this in all my tables and it simple adds a field with 1 to every record.  You could do the very same thing by using the Cartesian Join (the "X") with any table.  So the above could just as well be:


                    GLOBAL::ID X CLIENT::prenom

                    GLOBAL::ID X CLIENT_new::prenom


                    I use the constant_1 because I read somewhere that it was faster than the Cartesian join.  I haven't tested it so take that bit of info for what its worth.


                    Ok, back to the technique.


                    Now, you have two nearly identical relationships.  The only difference is that one allows the user to create a new record.  But if you look at a portal based on CLIENT_new you are going to see all of the records (because it is a Cartesian relationship that uses constant_1 = constant_1).  That means, to add a new record, you'd have to scroll down to the bottom to find the empty space where FileMaker enables the user to add data.  And who wants to do that?!?


                    So, to show only the empty fields awaiting data entry, I put a filter on the portal.  The filter says, "Hide everything that has a primary key".  And voila, now you have a one row portal that is ready for the user to type in it.


                    When you add a record via a portal in FMP, the foreign key automatically populates, so the relationship happens auto-magically.


                    planteg wrote:


                    I have another question: in your script, you change one field in GLOBAL (clientID_g), but don't commit.

                    You don't have to commit Global fields.  You can, if you like, but it is not necessary.


                    Finally, I noticed that the "global" field GLOBAL::clientID_g was not set as a global.  That was an oversight on my part.  Attached is an updated file.  With this update, I've also included a second "Add Client" popover button but this time the portal inside is not filtered.  This is for comparison purposes only (to compare with the filtered portal in "Add Client").


                    Hope all that makes sense.

                    • 8. Re: Create a record without a Go To Layout

                      Hi Daniel,


                      your suggestion works quite well ! I did it a different way, that is I didn't use a Popover, because it's not possible to add a Popover on a Popover - FM says it's not recommended, in fact it's not possible to see the content of the second level Popover in Layout mode.


                      Mike, I should have thought about the Magic Key , I like Daniel's way of doing it.


                      Siplus, thanks for the link, looks like I will find useful information on that site.