5 Replies Latest reply on Apr 9, 2017 11:25 PM by philmodjunk

    Creating related record with a script


      I have a typical "invoice" type of solution with Invoice record that have related line-item records displayed in a portal.  The user creates line items from within the portal either by simply adding a record to the portal, or with a button within the portal.  I want to be able to add related records from a script that is not activated via a button within the portal, so this portal will not be "active", and there are other portals on the layout.  In earlier version of FM I'd switch to a "line item" layout to create a new record, then switch back to the parent layout, and I always thought this was a clunky way to have to perform this function.  I'd like to think that by FM15 there would be a more direct, elegant way to do this, but I haven't found it.  Is there?

        • 1. Re: Creating related record with a script
          Jason Wood

          The issue you mentioned with multiple portals can easily be solved by adding a "Go To Object" script step. Give the portal an object name in the inspector. Then you can follow with steps like "Go To Portal Row", and it will always be the right portal.

          • 2. Re: Creating related record with a script

            You probably need to explain more clearly what you want.

            For instance - you already know the part number and quantity for this new record?

            You just want to create a new, empty row?

            What problem are you solving?
            Not that there is a method, sometimes called "Magic Key" for creating related records without navigating the portal.

            • 3. Re: Creating related record with a script

              I recommend that you websearch the term "MagicKey". This method enables you to add as many related records as you need from the context of the parent without: Changing layouts, opening a new window or by interacting with the portal itself. And the records thus created can be a single database transaction--which can better protect data integrity during batch creation of related records.


              This is a method is not unique to FileMaker 15. It's been used in many versions of FileMaker.

              • 4. Re: Creating related record with a script

                I read a bit on Magic Key awhile ago... didn't quite get it, but didn't need it at the time.  The "go to object" suggestion is interesting.  For more detail, this isn't actually an invoice, that's just a simple way to describe it.  My solution is for an automotive company (mine) and creates estimates, repair orders, and invoices.  One related table for the parent record is for payments (customers often make several payments in the course of a job), and the payment is entered by a calculation in a script rather than directly entered into a field.  That's basically it.

                • 5. Re: Creating related record with a script

                  I am not in favor of scripts that use go to object and go to portal row to create related records. While go to object helps, the resulting script is vulnerable to failure due to future layout changes.


                  MagicKey uses a relationship in reverse of the expected primary key in parent to foreign key in child. Instead, it matches the primary key of the child to the foreign key of the parent.


                  1) create a new occurrence of the payment table. I'll call it PaymentsMagicKey

                  2) link a text or number field (foreign key) in the invoice table to a field in PaymentsMagicKey that auto-enters a serial number or UUID (Primary key). Often, the foreign key field has global storage specified.

                  3) enable "allow creation... for PaymentsMagicKey. 


                  Your script can now use this relationship to create records in the payments table.


                  #foreign key must be empty to create a new record

                  Set Field [ Invoice::ForeignKey ; "" ]

                  #Next step creates new record and sets match fields so that new record will appear in portal

                  Set Field [ PaymentsMagicKey::ForeignKey ; Invoice::PrimaryKey ]

                  #Any additional set field steps update fields of new payment record.

                  Set Field [ PaymentsMagicKey::payment ; calculated payment goes here]


                  When this step:  Set Field [ PaymentsMagicKey::ForeignKey ; Invoice::PrimaryKey ] Creates a new record, PaymentsMagicKey::PrimaryKey auto enters a unique value (serial or UUID) and this value is copied back to invoice::ForeignKey to link the new payment record. The new record will not appear in the portal until the new record or records are committed.