Speeding up related record creation with importing

The following technique can be used when you have to create related records on a huge found set.

What is the main problem?

FileMaker can be really slow when you manually create many related records using a script, and we faced this when we developed our mass action (bulk mailing, exporting) part of our FileMaker based CRM. (Note: in our CRM there are two other related tables in which we needed related record creation which made things much worse)

Let's say you have client table, which has about 100.000 records. You also have a related table called History, which is to hold the events that happened to the clients.

If you try to add a history record to all clients in the found set, you'll have a script like this:

Freeze window

Go to Layout[Clients]

Go to Record/Request/Page[First]

Loop

Set Variable[$clientid;Clients::ClientID]

Go to Layout[History]

New record/request

Set Field[History::ClientID;$clientid]

Set Field[History::Event;"Client exported by user"]

Go to Layout[Clients]

Go to record/request/page [next, exit after last]

End Loop

It's an easy related record adder script which adds a "Client exported by user" text to the History table(it's related to the Clients table).

This would work well on small found sets. But when you try this on a big found set, you'll find yourself waiting for ages (and things get worse if we speak about a shared database and online usage).

How could it be sped up?

This is where exporting and importing can be a good solution, as you may noticed these functions create records much much faster.

This is how our script should be modified:

We'll need a global text field at the Clients table, where we set the "Client exported by user" text, let's call this Event_Global.

Now we can do an export on the clients table (we only need ClientID, and the Event_Global) to a temp directory in FP7 format.

After this we go to the History layout (and also table) and import that exported file as the following:

Clients::ClientID to History::ClientID

Clients::Event_Global to History::Event

And we're done.

So the modified script is the following:

Freeze Window

Set Field[Clients::Event_Global;"Client exported by user" ]

Set Variable [$path; Get(TemporaryPath) & "export.fp7"]

Export Records[No dialog; "$path"] //Note: here you need to add field Event_Global and ClientID

Go to Layout[History]

Import Records[No dialog;"$path";Add;Mac Roman]

Go to Layout[Original Layout]

And that's all.

So first we set the global field, then generate a path using Get(TemporaryPath) and save it to the $path variable.

Then we export the fields, go to the History layout and import it into the history fields.

Note: You need to be sure to set up both the export and import orders correctly!

Note2: Do not store sensitive data in temporary files, or if you have to make sure you delete it immediately after the script has finished!

Please visit our site, http://crm.fm which has our main FileMaker product, and support us by buying it. Thanks!

The material on this document is offered as is. There is no representation or warranty, expressed or implied, nor does any other contributor to this post. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.