FileMaker Tips and Tricks
Contents
Funky Matching
- = means only match blank
- !! means only match duplicates
- * means match any non blank
Importing only when field is blank
For the SED-Grad-Admit database, we wanted to only import Program IDs where there were NO Program IDs, but not over-write hand set Program IDs (aka degrees).
First do a "find" with PROG set to "=". The equals sign means "only match blank."
Then you can import with matching UIDs and only resetting PROG. Make sure to have no other fields matched or reset! That import screen is tricky.
Importing only UNexisting records
If you want to import only unexisting records, and not existing ones, this is what you do.
First, the definition of existing is understood to be "This UID exists in the database"
Next, there is no way that we know of to directly import NON matching records. But have no fear, there is a workaround.
- Do a tabbed delimited data dump of only the UID numbers existing in the database.
- Paste this list into the first column of the second sheet of the Excel spreadsheet of the data you want to import.
- Add a new column on the first sheet after the UID.
Into this column paste the following formula: =MATCH(A2,Sheet2!$A$1:$A$2000,FALSE)
- Now, you will see that any nonmatching UIDs will have a #N/A next of them.
- Sort the whole spreadsheet based upon this column.
- Delete any row that doesn't have the #N/A in it.
- Delete the Matching ID column as you no longer need it.
- Save the file under a new name, maybe with -only-new-uids tacked onto the end.
Import that first sheet into FileMaker.
Adding a dividing line in layout lists
See: http://filemakerterm.blogspot.com/2007/09/dash-layouts-scripts-list-items.html
