Setting the Source Table in Import Records -- Step by Step Guide

Discussion created by madoverlord on Jul 6, 2014

Search bait: Import from Filemaker Database, Set Source Table, Select any Source File


Like so many before me, I have been bitten by the problem of setting the source table on a scripted import of a filemaker database when trying to write an upgrade function for a filemaker application. The provided documentation is useless, and googling for the answer generates a lot of links but it's mostly chaff not wheat. After a couple of hours of frustration, I finally found a couple of threads that provided the clues I needed to do it properly, and so I have decided to document the precise series of steps needed to properly set the source table AND permit the user to select the file to be imported.


There may be a more minimal sequence of operations, but this works and works repeatably. Thus, for the benefit of TNPB (The Next Poor Bastard), here is how you do it:


The step-by-step guide to setting the Source for importing from another FileMaker database.


Create Import Records Script Step


Click Specify Input Order. Select the desired Target Table, set Arrange by to "matching names", and choose the desired character set (this is the only time you can choose a character set!). Click OK. Depending on the target table, you may be asked about Import Options; set these as needed and click Import.


Click Specify data source. Click Add File... and select the source Filemaker file (you will be able to change this later; for now, you just need a file with the same tables as your eventual source file). Click OK.


Click Specify Input Order again. This time, you will be able to set the source table. Check that the Target table is what you want, and in particular that Arrange is still by matching names. Click OK, set any Import Options, etc.


You now have an import script step that specifies source and target tables.


MAKE A COPY OF THIS SCRIPT STEP AND SAVE IT IN A DUMMY SCRIPT! Because otherwise you will mess up and have to repeat the above steps.


For each other table you wish to import, make a copy of the script step, then use Specify import order to change the source and target tables.


To allow user to specify a source file for your import:


Define a container as a global in one of your tables. In my example (below), it is called Config::UpdateContainer


Use script steps like this to determine and validate the file path (this is grabbed from a database design report)


Go to Layout [ “BackerSupport Import” (Config) ]

Set Field [ Config::UpdateContainer; "" ]

Insert File [ Config::UpdateContainer ] [ Custom title: "Select Old BackerSupport Database" ] [ Storage method: Reference ] [ Display icon ] [ Compression: Never compress ] // you cannot use drag-and-drop to get the file path, it doesn't provide the full path. You have to use Insert File

Set Variable [ $$UPDATEFILE; Value:GetValue(GetAsText(Config::UpdateContainer);2) ]

If [ $$UPDATEFILE = "" ]

Show Custom Dialog [ Title: "Import Not Attempted"; Message: "You did not choose an older database."; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]

Exit Script [ ]

Else If [ Substitute($$UPDATEFILE;["filemac:";"file:"];["filewin:";"file:"]) = Substitute(Get(FilePath);["filemac:";"file:"];["filewin:";"file:"]) ]

Show Custom Dialog [ Title: "Import Not Attempted"; Message: "You appear to be trying to update the database with itself. I really don't think that's a good idea."; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]

Exit Script [ ]

End If

Set Field [ Config::UpdateContainer; "" ] // if you don't do this, your old database may be included in the new one, doubling the size. Note 100% sure about whether Insert File does this, but when I was playing with Drag and Drop, that certainly did! I recommend you do this as a startup action for your database, btw.


This sets a global $$UPDATEFILE to the path to the desired file. You should do as much validation as possible to ensure this is really a file you can properly import from. I'm probably going to add a hidden table to my database that contains validation info; if it doesn't get imported correctly, I know things are FUBAR and can abort the imports.


Now that you have $$UPDATEFILE set, go back to your Import Records script steps and change the data source File paths list from the explicit path to your original source file to $$UPDATEFILE.


One you have tested everything and are sure it is working fine, you can check Perform without dialog.


Other threads that relate to this topic: