4 Replies Latest reply on Apr 3, 2014 10:42 PM by delibebek

    "Add" button for many-to-many related record



      "Add" button for many-to-many related record


           Hi, I know I can add a related record to a portal just by filling out the blank row, but for usability reasons I'd rather do it with a button that would pop up a friendly form.

           I have a first cut of it now, a button that launches this script, below.  Can anyone check my work and suggest any improvements or tell me if there's any gotchas?

      New Window [Name: "New Widget"; Height: 500; Width: 400; Style: Floating Document]
      Set Variable [$customer_id; Value:Customer::id]
      Go to Layout ["Widget" (Widget)]
      New Record/Request
      Set Variable [$widget_id; Value:Get ( RecordID )]
      Go to Layout ["CustomerWidget" (CustomerWidget)]
      New Record/Request
      Set Field [CustomerWidget::customer_id; $customer_id]
      Set Field [CustomerWidget::widget_id; $widget_id]
      Commit Records/Requests []
      Go to Related Record [From table: "Widget"; Using layout: "Widget" (Widget)]
      View As [View as Form]

           Details: From a Customer, this creates a new related Widget.  "Customer" has a customer_id field; "Widget" has a widget_id field; and they are many-to-many related through a "CustomerWidget" table that has a customer_id field and a widget_id field.  The tricky part is that I wasn't quite sure how to accomplish creating both the new Widget row, *and* the new CustomerWidget row in one process, so that's the workaround so far.


        • 1. Re: "Add" button for many-to-many related record

               Your script does a lot more than create a new record in a portal. It's creating a new record in two tables and linking them in relationships by setting match fields to ID values. Without seeing the relationships that you have set up, there may be issues in this script that are not apparent just by reading it.

               But I see a less than optimum choice for how you set up your primary key for the "Widget" table. Get ( RecordID ) is not the best choice for a primary key as it can change on you and break your relationships: Why Record ID's should not be used as Primary Keys in Filemaker Relationships.

               The other issue to look out for is that any script that changes layouts can trip a long list of script triggers both on the layout being exited and the layout being entered. I control that issue by adding this step at the beginning of this type of script:

               Set Variable [$$TriggersOff ; True ]

               and this one at the end and every exit point of the script:

               Set Variable [$$TriggersOff ; False ]

               Then, I enclose all trigger performed scripts in:

               IF [ Not $$TriggersOff ]
                    #Rest of Script goes here
               End IF

               That way I can keep any such trigger performed script from interfering with this type of script.

          • 2. Re: "Add" button for many-to-many related record

                 I'll second Phil's use of the "no-trigger" variable. The important thing to remember is to reset the variable before all "Exit Script" steps.

                 Additionally, I use two buttons on my New Widget layouts - "Done" and "Cancel". "Done" runs a script which adds the new record to the join table for the Widget and Customer, closes windows and refreshes the layout I started from. "Cancel" runs with a "delete" script parameter to get rid of the Widget I just created, in the case that I changed my mind or realized I didn't want that Widget after all. There are situations I use the "Cancel" script without the "delete" parameter, but on New Widgets, that's how I do it.

            • 3. Re: "Add" button for many-to-many related record

                   Thanks!  Yes, I misunderstood how Get (RecordID) worked.  I didn't realize that I could simply refer to the Widget::ID field once I invoked New Record/Request .

                   Matthew, I will try out what you mention.  I had assumed that the CustomerID would not still be accessible from the popped up layout in the Add script, so that is why I created the join record first.

                   Also, I realized I can actually accomplish this with a Popover Button.  If I add the join row first, then I can immediately go to the new row in the Customer layout's Widget portal and enter the row's popover to be able to fill out the rest of that widget's information.  I think I like that better than popping up a whole new layout in a separate floating window.

              • 4. Re: "Add" button for many-to-many related record

                     You're capturing the customer ID in a variable. If you made that a global variable, it would be accessible to the follow-on script.

                     You're probably right about the popover being a neater solution. I haven't played with popovers yet.