Using SQL to

Discussion created by deanchampeau on Jan 11, 2017
Latest reply on Jan 11, 2017 by deanchampeau



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?