Multiple email addresses for a contact or employee
In today’s modern age, many people have multiple email addresses. They may have a work email, a home email, a personal email, etc. I think I may have at least 1/2 a dozen.
In Clarify (and Dovetail), there’s one field for specifying an email address. Back in 1990, it was uncommon that people had multiple email addresses, so a single field was sufficient.
But what do we do when a contact has multiple email addresses? Where do we store that info?
I’ve seen systems where multiple custom fields are created, such as:
- table_contact.x_home_email
- table_contact.x_work_email
- table_contact.x_alternate_email
- table_contact.x_alternate_email2
etc.
Yuck.
I’ve also seen systems where multiple email addresses are crammed into the one existing email field.
Yuck again.
How about…
I think what would be useful is a table that allowed for many email addresses, each one with a description of its usage. There could be a one to many relationship from the contact table to this email address table.
We have a similar scenario for phone numbers. A contact has many phone numbers (home, work, mobile, fax, etc.) As does a site.
And we have a similar scenario for employees. An employee can have many phone numbers, and many email addresses.
Meet table_e_addr
There’s an existing table named e_addr. (Electronic Address). It’s been around almost forever (since at least CFO98)
It has fields, including:
- e_num: stores the phone number or email address
- e_type: 1=email, 2=phone, etc.
- useage: Use of the electronic address; e.g., primary phone, alternate phone, primary fax, and so on
And it has relations, including:
- MTO relation to contact
- MTO relation to employee
- MTO relation to site
- MTO relation to bus_org
Seems to be just what we’re looking for.
BOLT gives all the details:
(click to view full size)
Where/How is e_addr currently used?
This table is used by Clarify’s eResponse Manager, and the workflows that involve dialogues and communications.
Why isn’t it part of baseline Clarify UI (such as from a contact, employee, or site)?
Good question. I have no idea.
Why isn’t it part of baseline Dovetail Agent UI (such as from a contact, employee, or site)?
Good question. Traditionally, because we tend to mimic the Clarify UI. But this is something we plan on adding to the base Dovetail Agent product.
What about Business Rules and Notifications?
Can we send a notification (from a business rule action) to one (or all) of these email addresses?
Indeed you can. We can simply create new business rule recipient aliases (rule properties).
Contact Primary Email Address: case_reporter2contact:contact2e_addr(useage=’Primary Email’):e_num
The primary email address will find the e_addr record(s) with a usage of Primary Email.
Contact Alternate Email Addresses: case_reporter2contact:contact2e_addr(e_type=1):e_num
The alternate email addresses will use all the e_addr records of e_type = 1 (email)
Here’s what the contact alternate email addresses rule property looks like in Dovetail Admin:
You could also keep using the existing contact email address (table_contact.e_mail) as the contact’s primary address, and only store the alternate/additional ones in table_e_addr. Probably a good idea.
You have control
Do you want to send an email just to their primary email for a contact, or to all of their email addresses?
The different aliases give us the control to do exactly what we want.
Give it a whirl
I whipped up a little script that inserted some email addresses into the e_addr table, and related them to a contact.
I set up a business rule to notify the Contact Primary Email Address and the Contact Alternate Email Addresses.
The emails were sent as expected.
Reviewing the Dovetail Rulemanager logs, I can see that the recipients were evaluated just as I expected:
Path “case_reporter2contact:contact2e_addr(useage=’Primary Email’):e_num” expands to SQL command
“SELECT T2.objid, T2.E_NUM FROM table_case T0, table_contact T1, table_e_addr T2 WHERE T0.objid = {0} AND T2.useage = {1} AND T0.case_reporter2contact = T1.objid AND T1.objid = T2.eaddr2contact ORDER BY T2.objid DESC”
Property “Contact Primary Email Address” using “case_reporter2contact:contact2e_addr(useage=’Primary Email’):e_num” expands to gsherman@dovetailsoftware.com
Path “case_reporter2contact:contact2e_addr(e_type=1):e_num” expands to SQL command“SELECT T2.objid, T2.E_NUM FROM table_case T0, table_contact T1, table_e_addr T2 WHERE T0.objid = {0} AND T2.e_type = {1} AND T0.case_reporter2contact = T1.objid AND T1.objid = T2.eaddr2contact ORDER BY T2.objid DESC”
Property “Contact Alternate Email Addresses” using “case_reporter2contact:contact2e_addr(e_type=1):e_num”expands to gsherman@dovetailsoftware.com,gary@garysherman.com,gsherman@gmail.com
So where do we go from here?
Now that we’ve found that a good data model exists, and that Rulemanager can do the right thing, the next step is to build out the UI that allows creating/editing/deleting of multiple email addresses and/or phone numbers for contacts/employees/sites/etc.
No promises yet – but we’ll see what we can do to make this happen.
