There are times in this less than ideal world when you have to bring data in from outside, either from another FileMaker file or from a spreadsheet. One of the most tedious tasks on earth is manually going through the records afterwards, searching for duplicates, trying to figure out which one to keep, etc..
Here are two different techniques. The first is used in the instance when you definitely don't want any information from duplicate records to be imported. It finds the duplicates in the new file before importing and deletes them, saving time, as imports are what I call "butt-slow" (blame my teenage daughter for that one).
It requires that both files have either a unique ID or a unique concatenated calculation field (such as First & Last). You should also have a Serial ID field, type text, auto-entered serial number, incremented by 1 (it's still text).
I know that First & Last is a pretty flimsy unique field. I'm using it in this case because if you add the phone number or address to it then you won't be able to update them with a lookup.
[OK, I couldn't stand it, so I added a warning message and a few steps to let you review and delete the duplicate names. I can't explain it now. But for some real fun, tile the 2 database file windows horizontally and watch what happens.]
If you're importing from a spreadsheet or tab-separated text file, you need a separate FileMaker file to temporarily house the records for importing, Transit. It has the same fields as those in the main Import database that you'd want to get data for, as well as a similar Concat field of First & Last, to use as the relationship key.
Copy All Records Trick
The first operation you'll use is the "Copy All Records" trick, which I wrote about a couple of articles ago; but I'll give it again quickly. It allows FileMaker to create a many-to-many relationship between two files.
In this case you'll first need a global field, Concat g, type text, to mirror the Concat field. Then create a blank layout. Put only the Concat field on it. Put the global Concat g field on another layout in the body part (doesn't have to the only field on the layout).
Create a relationship between this Concat g field and the Concat field in the Transit file:
(Import) Concat g= ::Concat (Transit)
Create the following script:
Find All
Go To Layout ["Concat Only"]
Copy All Records
Go To Layout ["Concat g"]
Paste [Select, No style, "Concat g"]
Exit Record/Request
Go To Related Record [Show, "Concat g to Concat"]
Perform Script [External: "Transit"]
This external script in Transit (which you have to create first, then call by choosing "External" at the bottom of the drop-down script list, then finding Transit, then the script) has two steps;
Delete All Records
Find All
The first part of the script copies the Concat field from all records, then pastes them all into one global field, Concat g, separated by returns. Make sure you check the "Select" box, or you'll quickly reach the text field limits.
The last 3 steps will go to only the records in Transit that are duplicates of ones in Import, using the many-to-many relationship, then delete them all. The last step will find the remaining records, which could be imported into Import (but see below for more).
Eliminating Duplicates and Updating Records
OK, that was simple enough (groans). But what about the more useful operation of using the data in the new file to update the duplicate records in the Import file, then import non-duplicates as new records. No problemo.
In this case we'll use the Concat relationship to establish a link with records in the text or database file that represent people we already have records for in our main file.
An Alternative Method Using Serial ID
You could also do this entire operation with the Serial ID field, instead of the Concat field. You'd use the Serial ID method if you were synchronizing two databases (or a spreadsheet), such as from two different locations of a business or from a template file to an active file, where the Serial ID field would be what would be used to identify items. In that case you wouldn't need the Concat field at all, and could do the Copy All Records trick, layouts, lookups, etc., with the Serial ID field and a Serial ID g global field.
I don't want to confuse you with two alternatives. But there are different possible solutions depending on the problem. One person may want to import basic contact data from simple non-categorized text lists, whereas someone else might be importing itemized inventory data from another FileMaker database. Use the Concat method for the former, and Serial ID method for the latter. We'll stick with Concat for now.
Define Fields as Lookups
In Import create a relationship to Transit, Concat= ::Concat (Transit). Redefine all the fields you want data for to be auto-enter lookups, based on this relationship. It's pretty easy, as the field names are the same in both files.
Make sure to put that Concat field somewhere on the layout you'll be on. It can be hidden by making it really small, the same color as the background, removed from the tab order and non-enterable. But lookups only work if the referenced key field is on the layout.
Transit Relationships and External Scripts
In Transit create a similar relationship between Concat in Transit and Concat in Import.
Create a calculation field, type number, in Transit to count occurrences of this relationship, using the aggregate function. Count Concat =
Count ("Concat Relationship::Last")
This will enter a 1 in any record that has a match in the Import file, so we can find them later. "Last" could be any field that always has data.
There are two scripts which must be created in Transit, to be called as external scripts from Import. The first will find and delete the matching records after the lookup has brought over the new values into Import, then show the remaining ones. The second will delete the remaining records after the import; then the entire operation will be finished.
The first script, "Find Count Concat,"
Go To Layout ["List"]
Enter Find Mode
Paste Literal ["Count Concat", ">0"]*
Perform Find []
Delete All Records [No dialog]
Find All
The 2nd script, "Delete All,"
Delete All Records [No dialog]
*You could just use Set Field ["Count Concat", "1"]; but the ">0" works even if there are duplicates in Transit itself, whereas the "1" doesn't.
One final detail. You should import some records from Transit into your Import file first, getting all the fields lined up properly (not too hard, as they're named the same). Put a check in the "Perform auto-enter options" box. Create a script in Import, "Import Transit," to save the order so that it's automated for all future imports (I've done it for you in the example).
Relookup Script
Here's the whole shebang, as one script, "Relookup," in the Import file (also available as an External script from Transit):
Enter Browse Mode []
Go to Layout ["List"]
Relookup [No dialog, "Concat Relationship"]
Perform Script [Sub-scripts, External: "Transit"]
Comment ["Find Count Concat\Delete"]
Perform Script [Sub-scripts, "Import Transit"]
Perform Script [Sub-scripts, External: "Transit"]
Comment ["Delete All"]
Find All
Sort [Restore, No dialog]
The Comments name the External scripts in Transit (which are annoyingly hidden within the script otherwise, reachable only through dialog boxes). It's a good idea to use Comments to name these in your scripts, as well as weird Sort orders, Finds, etc..
I would like to thank Ilyse Kazar and Anne Verrinder, two very smart and helpful women (together now as Datatude), for the mailing list tips that inspired these two techniques (you didn't think I came up with this all by myself, did you?)
Websites mentioned:
John Mark Osborne, Database Pros
Send comments, questions, toys to Fenton Jones