Hey there Mike,
The checkbox for 'create related records' only works one table away - it sounds like you are trying to create two related records, really: one in the SQL table and one in an FM table. This checkbox works like this: if I am on a record in TableA and I set a field in a record in TableB, it will create a new record in TableB to hold that field that I just set (assuming that the record in TableB doesn't already exist).
So are you the one creating the record in the ESQL table? Or is it happening from some other process? If you are the one creating it, you can create the record in the 2nd table (the FM table) as well: either just grab the ID of the new ESQL record create and do a simple "go-to-Layout[TableB], create new record, set FK field to value from ESQL record" process. Or you could use the checkbox you were describing: start on a layout base in the ESQL table, create a new record; while on that record set a field in the FM (TableB) record. This should create a new record and automatically set the relation field to the PK value from the ESQL record.
That's a short overview.
Hi, Justin. Thanks for your feedback. So, to give an idea of what is actually going on, here's a brief example:
The data in the SQL database is stored by an accounting system which I am not personally entering data into. One instance in which I'd like this to work is, let's say a new client is created in the SQL database, and I've linked the table that stores that data to a table in FileMaker, joining the table by a client name field. Ideally, whenever a new client is created in the SQL database, I would like a new record to be created in the corresponding FileMaker table, populating the client name field. When I initially joined these tables, all of the existing data carried over, but it doesn't seem to update as new records in the SQL table are created. Based on your input, it sounds like I may need to execute a script that reads new records from the SQL table in order to create these records in the table in FileMaker. I was hoping that these records could be created automatically, in real time, but is that just not possible?
My experience with external SQL is somewhat limited, but I believe that yes, you would have to run a scheduled script to periodically check for new records and create the related records. It is a general rule that you can't create a related record automatically anywhere/anyhow in FileMaker (External SQL or not) just by creating a record in a table. There has to be some intervening script or human UI interaction that will do the related creation for you.
I'm not sure what process you went through when you describe, "When I initially joined these tables, all of the existing data carried over...". You can do imports that will create related record in a target based on a source table, but that is an explicit process. I'm not familiar with any process that will create records simply by linking or joining tables with a relationship on the FM Relationship Graph interface.