FileMaker 101 – Part 10

Marking with lookups, duplicates & imports
Example file: Import-Transit

In last month’s article I mentioned John Osborne’s excellent Serialize by Category file (at http://www.best.com/~jmo) and the ability to mark first entry records automatically without having to run a script. It involves a simple technique, but with an unusual twist. Basically it relies on not being able to lookup a match based on a self-relationship.

The second part of his solution uses this fact and goes on to lookup a count of the self-relationship in order to serialize sub-records (people working for a company, etc.). I’ll have to get to that next month. It’s pretty tricky. Let’s just look at marking first entries for now, using my file Phone.all (different from last month’s Phone.fp3).

First you’ll need a self-relationship based on a unique identifying field. It could be a concatenated calculation field, type text, using the name fields (First & Last), or it could be any indentifying ID field, if you have one assigned. We don’t, so I’m using the Concat field, with the self-relationship, Concat= ::Concat.

Create a field First Lookup Not, so named because it looks up nothing for the first entry. Click the Options choice to see the Auto-enter dialog, then click the box for Lookup. In the next dialog choose the Self Concat relationship in the drop-down box. In the box for what to enter if there’s no match click “use,” but don’t enter anything.
For the field to lookup if there is a match I chose the simple Constant=1 field.

 

If you enter a new unique name, it will attempt to lookup a match. It won’t find one, so it will enter nothing in the First Lookup Not field. If there is a match it will lookup 1.

First Lookup Not will be empty for every first entry, and have 1 in it for any duplicate entries. For our purposes, it would be better to have the opposite, a 1 in a field and nothing in duplicate entries.
So make a field, First Lookup, type calculation=
If (IsEmpty, First Lookup Not, 1, “”)

Having the 1 will be useful. Since it is indexed, a simple relationship from the Constant=1 field to the First Lookup field will allow you to quickly Go To Related Records, showing only the first entries on list views. Portals using the relationship will also show only the first entries. It is by far the fastest most reliable way to control visibility.

There are a few cons however. Lookups are dynamic in a funny way; they change when the key (the field that the relationship is based on) is changed, but not when the related field changes (like regular related fields do).

If you delete that first entry, the next one won’t change to take its place, so there will no longer be a marked First Lookup for that name; therefore it will disappear from your filtered lists.

If you duplicate a first record the 1 will duplicate, so you’ll have two first entries for the same name.

If you cut one of the names out then paste it back in, or even edit it, the First Lookup mark will be deleted (only if there is a duplicate record), as the name is part of the key. It causes a Relookup on the relationship, which is now valid, since there’s more than 1 record with the same name.

The very worst, however is if you do a Relookup on all the records, using the Concat field (which you might be tempted to do in order to update some other field that also uses the relationship in a lookup–but don’t do it). Since there is now a valid relationship in all the records that have the same names, all of those First Lookup marks will be wiped out.

Even if you do, all is not lost. Going back to last month’s article, we can use the old scripted method of marking first entries to fix it. On the First Lookup Not field, use the self-relationship in a Replace operation, calculated result,
= If (Serial ID = Self Concat::Serial ID, “”, 1)
You can’t use it on the First Lookup field because it’s a calculation, but it updates. I’ve included this Replace First Lookup in the Script Menu.

Warning: Make sure the field First Lookup Not is on the layout in this case. It can be hidden, but it must be there for the Replace to work.

By the way, I know this whole business of marking the first entry in this way is unnecessary in a simple phone index-type file, where you only have one record for each name. It is very useful in many other files, even phone files if they have multiple entries for each company, such as in Serialize by Category. The Phone.all file had already been built for the second part of the article; I grafted this little operation into it so you could see an example in a simple file. Eventually we’ll get into a Job Service file where there are always multiple entries and marking is essential.

Part 2
Importing and Duplicates

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 Phone.all 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 months 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:
(Phone.all) 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. You could also put a Clear [“Concat g”] step before the paste, but Select seems to work.

The last 3 steps will go to only the records in Transit that are duplicates of ones in Phone.all, using the many-to-many relationship, then delete them all. The last step will find the remaining records, which could be imported into Phone.all (but see below for more).

Eliminating Duplicate 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 Phone.all 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 Phone.all 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.

 

You can’t lookup their name; it’s being used in Concat (that would be another case of needing to use the Serial ID method). You can’t lookup a field that’s being used to establish the relationship (FM will tell you so if you try).

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 Phone.all.
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 Phone.all 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 Phone.all. The first will find and delete the matching records after the lookup has brought over the new values into Phone.all, 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
Set Field [“Count Concat”, “1”]
Perform Find []
Delete All Records [No dialog]
Find All

The 2nd script, “Delete All,”
Delete All Records [No dialog]

One final detail. You should import some records from Transit into your Phone.all 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 Phone.all, “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 Phone.all 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 tips that inspired these two techniques. (You didn’t think I came up with this all by myself, did you?)

---

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