I am using FM Pro 6, but can't find any references for how to interact with external files (other than import/export) for any version of FM. I've been searching the web and have 3 FM books in front of me. What I'm trying to accomplish seems like it would be a common task that an administrator or power user would want to be able to perform in the most basic DB with many records.

I have an .xls file containing one column of email addresses. I'd like to end up with a found set that includes all records containing the emails in the .xls spreadsheet, so that i can add data to those records en masse. The real world task I'm trying to accomplish is the following: We dump out 8,000 email addresses from FM and send an e-mail newsletter to those addresses. As the DB has not been maintained well, we have about 1,500 bad emails. I'd like to have a found set containing those bad emails, so i can operate on those records (append a bad email note to an appropriate comment field... and/or delete those emails later).

I have ascertained that I can use import to partially accomplish my goal, but import is not really flexible enough for this and clearly is not designed to do this. For instance, if I add a second column to my .xls file with the text "bad_email" in each row, then import can use the email in the .xls file as the matching field and it can import (overwrite) "bad_email" into a field in each matching record in my DB. And the resulting found set after the operation is the found set i'm looking for. The 2 issues with this are, 1) I want to append "bad_email" to a field, not overwrite it and 2) if instead I try to not import any field and just have a matching field be the email address, FM will not let you do the import without importing/overwriting at least one field.

Any thoughts on how to find/read from external files would be most appreciated. Reading from external files in a programming language like C, Perl, or what have you is extremely simple. Seems like this feature would be key to successful database maintenance...whether in Filemaker 1 or Filemaker 11.

Thanks,

Matt

Automatic message

This topic has been moved from "FileMaker Product Family → FileMaker Legacy → FileMaker.fp5" to "Database Schema & Business Logic → Importing & Exporting".

Just fired up my old G5 and FM6, and it seems that FM6 wasn't even able to do matching

import with native files … Anyway, here's a quick solution to your problem.

Set up a new FM database file with just a field for the e-mail (like “badEMail”). Import the “bad” addresses.

From your main file, establish a relationship with the new file, using the e-mail addresses as match fields.

Now perform a calculated field replace by checking for a related field via the new relationship;

if you find one, the address is bad.

If ( isValid ( myUtilityRelationship::badEMail ) ; myEMailAddress & " - BAD E-MAIL" ; myEMailAddress ).

Don't forget to make a backup of your working database before using out this technique.