Imports & Lookups
I want to talk a little about a small problem with Imports. It’s not quite a bug. FileMaker admits it’s a problem, but apparently it would be difficult to fix, so it remains. It has to do with the Import order. The Import dialog box is pretty intuitive and even amazing, the choices and flexibility you are given.
Import Order and Disorder
What they don’t tell you is that the order is rather fragile. Not fragile in the sense that FileMaker forgets it. Quite the opposite. The problem is in the inflexible way that it assigns and remembers the order. Everything works great as long as all the fields originally assigned are there. But if you delete one of those fields, you’re suddenly in big trouble. Likely as not, all the fields created after the one you deleted will be re-serialized, which means they’ll no longer match those of the source database file you are importing from. You’ll get the data, but in the wrong places.
Not only that, but unless you resort to drastic measures, it will never match again! You can drag the field names in the dialog box so they seem to match, but the order will still be off when importing. You’ll notice the problem if you scroll down to the bottom of the list then back up to the top, everything will be one off. You fix it and scroll down again; now the bottom’s off… I’ve found that after this disaster, which usually happens to a scripted Import, I can’t even get it to import correctly manually.
There is one easy prevention: Don’t delete fields. Change ones that are unneeded or mistakes to Global fields, then rename them beginning with a mark, such as “`”, so that they sort to the bottom alphabetically. They’ll take up very little space in the file and be out of the way. If you need a new field, use one of them and change the name. It will keep the import order intact.
The safest and easiest way to create an import order is to try and use the same field names in both files. Set both Define Fields lists to creation order. Then in the Import dialog choose Matching Names. Everybody should line up pretty close. Drag the few misaligned into place. Try hard to get them all.
Turn off the arrows for any fields that don’t have to be imported, calculation fields, globals, etc. They will be recreated in the new file anyway, and it will save time.
Reorder Fields (you’d rather not)
There is a cure for a wrecked order, but you’re not going to like it. If you deleted a field in the file that you’re importing into, then all the fields above where that one was got moved up to close the gap. You’ll need to move them all down.
One way to do that is to create the new field that you need to match the old one in the source file, to fill the gap so to speak. Then delete the field after where you want it go (in creation order). Recreate the deleted field, so it’s now last in creation order. Repeat for each field after, until you have a new order, with the new field in place and the rest after. Everyone should line up again.
If you do all this without closing the Field Definitions dialog perhaps the field names will be preserved in Layouts and Scripts. I don’t know; I’ve never tried it, and hope to never have to.
Another perhaps easier way to change the field name order is to rearrange it, then export the file as a FileMaker file. It will have the new order as creation order. But it will also drop all of the layouts and scripts. The layouts can be recreated easily enough, with copy and paste. But the scripts can’t, unless you use a third party tool to copy them over (I haven’t tried this, but there are tools available).
Imports with Lookups
Besides all of the above, there is another problem with Imports. If you have a lot of fields they are painfully slow. FileMaker assumes that since a field was indexed in one file, you’d want it indexed in the other. So it pauses to recreate the index of those fields when importing.
You could turn off the option to perform indexing in the second dialog box that appears when importing, but then you wouldn’t get any serial numbers or other auto-enter functions either, which are usually important.
Importing with Lookups
In any case, I’ve switched to using a combination of Importing and Lookup options to get the best of both worlds.
I import only one field, the defining ID field of the records I’m bringing in. It may be an auto-entered text serial number in the source file, or it may be a concatenated text field of two ID fields put together, but it will be a regular text field in the target file that I’m bringing them into.
By the way, if this field is created early on, you are pretty safe from the deleted-field disaster mentioned earlier.
Now I can get the main ID field safely and quickly into the new file. The other fields can all be referenced from this ID. What I need to do this is to open Define Fields and redefine them as auto-enter Lookups.
There should be a relationship to the other file, between the two ID fields. I redefine each field by clicking on Options, Auto-enter, Lookup. I choose the ID relationship, then the same-named field in the old file as the field to copy.
Relookup Script Step
It is usually necessary to run a Relookup script step, based on ID field, as a second step after importing the ID field, so that FileMaker can register the new relationship before doing the lookups. This takes a little time, but it is still several times faster than importing all the fields.
Imported Records are Found Set
An important point to remember is that immediately after importing, the imported records are the “found” set. This means that any extensive operation, such as a Relookup or a Replace, only occurs on those records.
For me this is critical, because I use a different lookup (mentioned in previous articles) to mark the first entry of a group of similar records (first person, first job, etc.).
First Mark (short version):
Basically, it uses a self-relationship between the ID field and itself (ID= ::ID).
There is a MarkFirst field that is defined as an auto-lookup based on that relationship.
It looks up a “Null g” field, a global field that has nothing in it. If there is no match it is set to use 1. The box for “Don’t copy if empty” is unchecked. This allows it to copy the empty Null.
A self-relationship isn’t valid until there are two records with the same data in the field. So, for every first entry it looks up a 1. For all later duplicate entries it looks up the Null g field and there is nothing in it.
Now, the problem is that once the self-relationship is valid, a Relookup command on those records using that same ID field will wipe out all those First Entry marks. So it can only be run on new records. This works fine. If they are a first entry they’ll get a 1, if not, they won’t; but the marks on the earlier records, which are not in the found set, will be left alone.
Any other operation, such as Replace, that changes the ID field would do the same, because changing the field automatically triggers a Relookup. Fortunately, there are very few reasons to change an ID field once it’s entered.
Replace on Imports
You can take advantage of the imported records being the found set to run Replace script steps. You wouldn’t need to do this often, as records would generally have the data they need in the other file already. But I can think of one case where I do it.
I have a template file, that stores a base set of data, job-costing amounts for making estimates. After filling in the number of units I move all those records into an actual Estimate file, which has several different estimates, one for each separate job.
The problem is that there are no such things as “jobs” in the template form. But the estimates entries must be identified by job ID; and the job IDs come from a third Jobs file. So, just before the Import, I go to a layout that has the active jobs from the third file displayed in a portal. The script pauses until I click on one.
The Job ID value from the portal row is set into a global field, which can then be easily set, via a Constant (1=1) relationship, to a global, Job ID g, in the other file (or even set directly via the relationship). Right after importing, this local global, now holding the Job ID, is used in a Replace script step to enter it into all the found records.
(Remember that Replace is like paste. The field has to be on the current layout. I won’t say how many times this has screwed me up.)
Lookups vs. Relationships
This is a big topic (and this article is already long), but I’ll just make a couple of points.
On the other hand, things like names, especially concatenated names, take up a lot of space. Once their associated ID field is in the other file, there is little reason to have the name in both files. You can just display it using a relationship back to the original between the ID fields. Just drag a the field tool onto a layout and, when prompted for the field name, choose the ID relationship, then the name.
The slight loss in speed is more than made up for by the gain in saved disk space. It’s also more convenient, in that a change to a name in one place will be reflected everywhere (if you don’t want this, make the field non-enterable in the related files).
Value Lists
There may be a few glitches. One is that only local fields can be listed in the “Show also” option for a Value List. Often the field is an ID field, and you also want to show the name, so you can see what to choose.
One work-around is to create the name field in the local file, but as a calculation field, based on the ID relationship, equal to the name field in the other file.
= (ID relationship::Name)
It can now be shown as the second field in the Value List. It won’t be alphabetical, though, if the ID field is indexed (as it would be).
This, by the way, is how you make a Value List sort by the first field when both fields are indexed. Create a mirror of the second field as a calculation field, based on a self-relationship. Use that in the Value List and it won’t be used for sorting.
If the data is being deleted in the old file after being imported into the new, then you have less choice in transferring the data, and will probably have to use Lookups. There is still a possibility to use relationships to other files to display some names, if you can establish ID relationships to them.
Generally, use the method that gives you the data storage and display that you want while keeping the speed and file size reasonable. That’s what a “relational database” is all about.
Remember also that what is reasonable with 100 records may become much less so with 100,000.
Fenton Jones
Fenton Jones is a FileMaker database designer and consultant, based in San Diego, CA. FileMaker is a cross-platform rapid-development tool for affordable relational databases. If you have need of a FileMaker Pro expert, please be sure to visit his home page at http://www.fentonjones.com
Leave a Reply
You must be logged in to post a comment.