Link to home
Create AccountLog in
Avatar of rvfowler2
rvfowler2Flag for United States of America

asked on

FM - How to deal with duplicate email addresses stored in Filemaker

Using a 3rd-party sw called Blat, we have solved the problem of sending multiple attachments via Filemaker.  However, currently, when one of our agents checks a property on a FM layout and enters the email address to send to, we store this email address in a field so that if the property's financials change, this person gets an update email.  However, we now have duplicates in this field.  How would you solve that?  My first thoughts are:
1) Some check for the email address before storing it in the FM field.
2) Run some FM function on this field that cleans duplicates.
3) Change the way we do it, create a new table, and create a new record for every email address.  This would enable us to check for dupe records, but is it overkill?

I know that Exchange ignores duplicate addresses, so we are working for now, but we just want to clean up our process a bit.  Thanks.
ASKER CERTIFIED SOLUTION
Avatar of North2Alaska
North2Alaska
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of rvfowler2

ASKER

North2Alaska:  a) No the email is not associated with a contact already in the system, but I could use an autofill on the field to speed things up.  These are emails of customers on the outside, which is often changing in the Real Estate world.
b) So are you saying I should not only create a new table, but a new record is created for each new email?  If so, I've already developed a List function that concatenates email addresses.

willmcn:  a) Thanks, this validation script is very helpful.  b) I already use a self-join TO and portal filter with conditional formatting to warn of dupes in another db, so that's a good idea also.  Just to clarify, at this point, I have multiple email addresses in one field separated by semicolons; however, I want to get rid of dupes.  Yet, is best practice to create a separate table for this?

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
By the way, I just tried this to check for duplicates, but it didn't work.  Why wouldn't the 2nd to the last line work to check for duplicates?
Case( IsEmpty( emailsNew ) or ( not PatternCount( emailsNew; " " ) and not PatternCount( emailsNew; "¶") and not PatternCount( emailsNew ; "," ) and PatternCount( emailsNew; "@" ) = 1 and PatternCount(emailsNew; ".") = 1)
and emailsNew  ¿ PatternCount ( emailList; emailsNew)
 ; 1 )
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hey guys, noticed that the field validation doesn't allow for a Case or IF statement so that I can have different comments based on the type of error.  What do you do then, set up a script trigger.  I especially want to differentiate between a duplicate and an invalid email entry.
Yes, a script trigger is the way to use a Case statement to display different error messages based on what has been entered.
thanks, tried it and it worked.
Thanks, this is the info I needed.