6 Replies Latest reply on Jan 11, 2017 6:52 PM by deanchampeau

    Using SQL to




      I know almost nothing about doing SQL. But I'd like to have just enough knowledge to do one simple (?) thing... create a new record in an FMP table and insert a value into the foreign key field.


      The reason I want to to do this is because I use portals a lot, and I'm not a fan of the "cheap" method of creating related records, where you simply turn on "Allow creation of records..." in the relationship definition.  (It's awkward and ugly from a UI point of view, IMHO.) So my solutions generally use an "Add" button that invokes a multiple-step script which does the following...


      - Makes note of enclosing tab/slide control state (if necessary)

      - Goes to the related layout/table

      - Makes a new record

      - Populates the foreign key field with the value of the primary key of the parent table

      - Goes back to the original layout

      - Resets tab/slide control state (if necessary)


      I do scripts like this very frequently. It's not a big deal to write these scripts, but they are not very "elegant" and I'm sure they affect performance. Over a WAN, there's a definite pause (ever so slight) when clicking that "Add" button.


      It occurs to me that the Execute SQL script step might allow me to really streamline this process. Can I use that script step to make a new record in a related table and populate a field within that table?

        • 1. Re: Using SQL to

          Why not use MagicKey?


          You can add records in a related table via that method without leaving the current layout or changing the focus on that layout.


          And the method you described can be simplified a lot if you use the New Window option to open a window hidden off the edge of the monitor. You'd still need to capture the parent key, but you wouldn't need to reset the focus on tab panels etc.

          1 of 1 people found this helpful
          • 2. Re: Using SQL to

            New Window is not an option because most users are running Windows, not Mac. If you do a "New Window" script step, it de-maximizes existing windows, which is very jarring.


            i'll check out Magic Key. Thanks for that.

            • 3. Re: Using SQL to

              It's jarring only if windows are maximized. Many windows solutions avoid maximizing the windows for that reason.

              • 4. Re: Using SQL to

                I'm just the opposite... My solutions are always single-window, and they are always maximized. Basically, I force my Mac users to suffer with a single-window solution to accomodate the Windows people.


                I wish FileMaker would (if technically possible) reconcile the behavior of Mac and Windows versions in this regard.

                • 5. Re: Using SQL to

                  If you check the product road map in Resources, you'll see that there are plans to do that in the next version.


                  I designed my first hosted solution just like you. It works well and I even found ways to unmaximize windows so that I could use a second window in a way that makes the window resize as "less jarring" as possible, but then I was hired by a company where they did the opposite and I've found that much as I'd like that application window to disappear, you get used to it and it's not that awful.

                  • 6. Re: Using SQL to

                    Yeah, you're probably right. As a Mac user, I'm really spoiled by how nice the UI is. But apparently Windows people are not traumatized by the idea of multiple "document" windows within an application window (eg, Excel), so maybe I should revisit my design rules.


                    So you're saying FMI is promising window-management parity in version 16? That's huge!