deduplicate email addresses

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

Hi,

A logic challenge for those of you out there with much bigger brains than my own!

I use open notifications to send email alerts to users.

Each record can have up to 6 email addresses populated (in 6 separate fields)

For various reasons (some valid, others due to user error), there can be duplicated email addresses in 2 or more of the 6 fields. Some fields can also be empty.

The emails we send to these addresses are actually 'email to SMS' email messages, so there is a cost associated with each email sent - and more importantly, it's annoying for users receiving two copies of the same SMS message.

So I want to ensure that we don't send multiple emails / SMSs to the same address - in other words remove any duplicates.

Given how notifications work, and all the combinations / permutations that could exist in terms of the duplicates, I'm really struggling to figure out how to check for and remove the duplicates.

Any solution needs to be entirely native using formula fields without user interaction.

David

Photo of James

James

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
You should remove or prevent duplicates as part of your data entry process.

If you are beyond that then try this scheme:

(1) You can use a User List field in the "Notify Whom" selection in the Notification setup form.

(2) When combining multiple users or userlist fields using the function ToUserList() duplicates are removed.

(3) Set up a user list field named [NoDupes] with the following formula:

ToUserList(
  ToUser([email1]),
  ToUser([email2]),
  ToUser([email3]),
  ToUser([email4]),
  ToUser([email5]),
  ToUser([email6])
)

I didn't test this but I think it will work.

If not you can always hand code a formula that will do "6 take 2" comparisons to manually sort the emails looking for duplicates. Like this:

https://www.youtube.com/watch?v=ywWBy6J5gz8
Photo of James

James

  • 0 Points
Hi Dan,

Thanks for your typically prompt response.

It's not just user error.

There are valid reasons why sometimes there will be duplicated emails where the recipient has more than one 'logical' role / function - but they only need a single alert.

The other thing, they are not users - so using the userlist field will not work.

The email addresses are email_to_sms emails for example 07453133244@SMSProvider.com

So I think the folk dance solution is what I need - it's just beyond what I could do personally within quickbase formula fields (ultimately for this to work with open notifications, it needs to create 6 email fields with duplicates values removed)

David
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
It can be done but it will be a very long monotonous formula. If you really want it contact me off-world using the information in my profile. It can also be done with script in a more respectable manner but something is going to have to trigger the script manually.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
You will need 6 new fields

Like Dan says it will be a longish formula.

The first formula will be

// get the first non blank email address

[Unique email 1]

IF(

[email 1]<>"", [Email 1],

[email 2]<>"", [Email 2],


[email 3]<>"", [Email 3],


[email 4]<>"", [Email 4],

[email 5]<>"", [Email 5],

[email 6]<>"", [Email 6])




Then the next one will be called [Unique email 2]

IF(

[email 1]<>"" and [email 1] <> [Unique email 1], [Email 1],

[email 2]<>""and [email 2] <> [Unique email 1], [Email 2],

[email 3]<>""and [email 3] <> [Unique email 1], [Email 3],

[email 4]<>""and [email 4] <> [Unique email 1], [Email 4],

[email 5]<>""and [email 5] <> [Unique email 1], [Email 5],

[email 6]<>"" and [email 6] <> [Unique email 1], [Email 6])

The next one will be for [Unique email 3] IF(

[email 1]<>"" and [email 1] <> [Unique email 1] and [email 1] <> [Unique email 2], [Email 1],

[email 2]<>""and [email 2] <> [Unique email 1] and [email 2] <> [Unique email 2], [Email 2],

[email 3]<>""and [email 3] <> [Unique email 1]and [email 3] <> [Unique email 2], [Email 3],

[email 4]<>""and [email 4] <> [Unique email 1]and [email 4] <> [Unique email 2], [Email 4],

[email 5]<>""and [email 5] <> [Unique email 1]and [email 5] <> [Unique email 2], [Email 5],

[email 6]<>"" and [email 6] <> [Unique email 1] and [email 6] <> [Unique email 2], [Email 6])

etc
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Try this set of formulas where [e1] - [e6] are the emails (I used text fields) and [u1] - [u6] are the unique or empty formula emails (again I used formula text fields):

formula [u1]:
[e1]

formula [u2]:
var Text seen = [e1];
If(Contains($seen, [e2]), "", [e2])

formula [u3]:
var Text seen = [e1] & "|" & [e2];
If(Contains($seen, [e3]), "", [e3])

formula [u4]:
var Text seen = [e1] & "|" & [e2] & "|" & [e3];
If(Contains($seen, [e4]), "", [e4])

formula [u5]:
var Text seen = [e1] & "|" & [e2] & "|" & [e3] & "|" & [e4];
If(Contains($seen, [e5]), "", [e5])

formula [u6]:
var Text seen = [e1] & "|" & [e2] & "|" & [e3] & "|" & [e4] & "|" & [e5];
If(Contains($seen, [e6]), "", [e6])

See this application (you can add your own records to test):

Unique Phone Numbers
https://haversineconsulting.quickbase.com/db/bk7eg2paw?a=dr&rid=3


The key to this may be that no email will be sent to an empty email field.
Photo of James

James

  • 0 Points
Thanks Mark (PS I posted the question in error in a test account name) David
Photo of James

James

  • 0 Points
Thanks Dan (PS I posted the question in error in a test account name) David
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Now you are stuck being James for the rest of the day.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Dan's solution is more elegant than mine.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
native +1
Photo of James

James

  • 0 Points
I've been called far worse things!!! (-:
Photo of James

James

  • 0 Points
(-: I love a bit of competition!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Me too - I am going all native today and let Mark handle the scripting.
Photo of Avinash Sikenpore

Avinash Sikenpore

  • 162 Points 100 badge 2x thumb
Hi,

Here's something that might work. 

Convert all the email addresses to users and create a user list. This will eliminate the duplicates.

Now convert that userlist back to email addresses. 

Here is the how the formula would look like

UserListToEmails ( ToUserList(touser([Email1], touser([Email2], touser([Email3], touser([Email4], touser([Email5], touser([Email6]))


It might create a placeholder for those email addresses to which you may assign the same role.


Let me know how it works.