I want to talk a little about a small problem with Imports. It's not really a bug. They know it's a problem, and apparently it would be difficult to fix, so it remains. It has to do with the Import order. Whenever you choose Import to bring in new data you are shown the Import dialog box. It 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. It remembers it forever. The problem is in the inflexible way that it assigns the order.
Everything works great as long as all the fields originally assigned are there. But if you delete one of those, you are in big trouble. Likely as not, all the fields created after the one you deleted will be reserialized for you. They will 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 be ignored when importing. You'll notice that if you scroll down to the bottom of the list then back up to the top, everything will be one off. You fix it, scroll down, then 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 very 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, faster than they can be imported.
Reorder Fields (you'd rather not)
There is also 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. 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.
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).
Import Speed (or lack thereof)
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 each 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 define them as auto-enter Lookups. There should be a relationship to the other file, between the two ID fields. Each field is then redefined by clicking on Options, Auto-enter, Lookup. Choose the ID relationship, then the ID field in the old file as the field to copy.
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 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 will left alone.
Any other operation, such a Replace, that changes the ID field would do the same, as changing the field automatically triggers a Relookup. Fortunately, there are very few reasons to change an ID field once it's entered.
[In a later article, #19, "Auto-Enter vs. Lookups," I use an auto-entered calculation rather than a lookup to mark first entries. It's safer, because it doesn't Relookup.]
Replace on Imports
You can take advantage of the imported records being the found set to run Replace script steps as well. 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.
There are no such things as "jobs" in the template form. But the estimates are identified by job ID; and the job ID's 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 passed, via a Constant (1=1) relationship, to the other file. Right after importing, this 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.
You don't always need to lookup fields to see the data in another file. When you lookup a field, you basically move the data into the new file. It takes up space.
You can display data from one file in another just by using a relationship. It takes up very little space.
Presuming that related data exists in two files and isn't being deleted in the original file: Things like ID's and numbers generally should be looked up and exist in both files. They are needed for all kinds of things, not having them locally would cause loss of function or speed.
Things like names, on the other hand, especially concatenated names, take up a lot of space. Once their ID field is in the other file, there is little reason to have them in both files. You can just display them using a relationship back to the original between the ID fields. 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).
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. There is still the possibility of using relationships to other files to display some of it, especially names. There are also ways to only enter names only once per file. But that's another article.
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. Generally the field is an ID field, and you also want to show the name, so it makes sense.
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= ::ID
It can now be shown as the second field in the Value List. It won't be alphabetical however.
This, by the way, is the way to 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 you don't have a self-relationship, just set the Storage Options for the second field (the mirror calculation field you just created, not the original one) to "Do not Store." Same thing basically.
(Or get v.5, which lets you choose which field to use for sorting.)