Compare existing records with unsubscribed records

[shawnj985]

I have a contact/mailing list table with records. I am building in it the capabilities to compare it with a unsubscribed table (users who wish to be removed from the mailing list) and if a match delete any existing records.

Attached is a draft script that I would like to use but I would like to run it by those who have used Filemaker pro for awhile. You may have to disregard the commands since I wrote this using a standard word processor. I am only concerned at this point, with the logic.

I would like to thank all in advance for any support you can give. I would like those who are viewing it to pay close attention to the find command that looks for matches and the loop it is within. This part I really wasn't sure about and wanted some more expert opinion.

comparerecords.txt

[comment]

I find it a bit difficult to follow, but it seems you are looking for subscribers with e-mail addresses matching those in the unsubscribed table? If so, why not define a relationship between the two tables, and use Go to Related Record [show only related records; Match all records in the current found set ] to do the job?

[shawnj985]

thanks for reviewing and responding.

[sbg2]

I think he wants to delete the record in both the Contact and Unsuscribe tables. In that case checking the "delete related records" box for the unsuscribe table would allow the records to be deleted from the Contact table. Having a calc field where If(IsEmpty(MyTO::Email); 1; 0) would result in 1 for active contacts and 0 for unsuscribed contacts. example attached.

Then again, why delete them at all if you already have a way of distinguishing which contacts are active or inactive?

Unsubscribe.zip

[dcc]

I'm trying to do something similar, but I'm stuck with FMP6 here. We have a customer table that has an email field. I have a list of emails that bounced from an update announcement mailing. I've been trying to find a way to remove the bad bounced emails from the customer records.

I've been trying to use the Perform Find/Replace script step to replace the bad email address with nothing. This work OK if I do a manual Find/Replace via the dialog but seems to have trouble when run from a script using the Field Value instead of fixed text, especially across files.

I've tried copying the bad email field values to a global text field and the using that gField as the Field Value for the Find what: portion of the Perform Find/Replace script step but that doesn't do anything either.

I can't simply clear the email field in Customers because sometimes there are more than one email address listed for a customer in the same field.

Currently I have the bad emails related to the customer records by the email field and have the following script in the bademails.fp5 file:

Go to Related Record [show, "Customer"]

Set Field ["Customer::gTemp", "BadEmailAddr"]

Go to Field ["Customer::email"]

Perform Script [sub-scripts, External: "Customer.fp5"]

this calls the following:

Perform Find/Replace ["gTemp", "", "Replace"]

Direction is set to All, Match case and Match whole word only are not set, Search Across: is set to Current record/request and Search within: in set to All fields.

When I perform the script in bademails it does what it is supposed to: copies the current email address to gTemp and switches over to the related record in Customers. But either when run manually or when called from Perform Script, the Perform Find/Replace doesn't work.

Is there a better way to do this? Or how can I get this way to work?

Thanks!

Dev

[comment]

sometimes there are more than one email address listed for a customer in the same field

This is the source of your problem. If you split them into separate records in a related table (file in your case), things will become much more manageable.

[dcc]

Yeah, I realize that's far better DB design and it _was_ in my original plans when re-doing things back in FM3, but it got shot down by the boss.

And it doesn't really explain why the Find/Replace isn't working for the majority of cases where there is only one chunk of text in the email field. Any ideas on that?

EDIT:

I got it working by changing the Perform Find/Replace with:

Set Field ["email", "Substitute(GetField(email), GetField(gTemp), "")"]

Edited July 19, 2006 by Guest