How might I search for all contact records which share the same email domain as a contact for which the relationship manager field (user picker) is not blank?

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

We have a Contacts table in our QuickBase application. The only three fields which I think are relevant to this question are:

"Relationship Manager" (User Picker field)

"Email" (Email Address field)

"Contact Status" (Text multiple choice field)


When a contact has a user in the Relationship Manager field, it is important that all of the other contacts which share the same email domain (e.g. @Microsoft.com) show "Do not email" in the Contact Status field.  I can do this relatively easily for existing contacts but it is the new contacts which need to be captured.

I set out to do this in a semi-manual fashion by creating a report with a long list of filters, such as:

----------------------------------

Email contains "@Microsoft.com"

or Email contains "@Apple.com"

or....

and Contact Status does not contain "Do not email"

and Relationship Manager is equal to <blank>

----------------------------------

My idea was to then set a daily email alert which sends only if the report displays any records, thereby catching any new @Microsoft.com or @apple.com records within 24 hours of them being added to the database.

The problem with this is that I'm dealing with a list of 250+ email domains which are laborious to enter since I have to create a new filter for each one. I have also discovered a limit of 100 filters per report meaning I had to split them across multiple reports. I finished entering all of the domain filters only to realise that I am now unable to add the Contact Status and Relationship Manager filters alongside the email domain filters without starting all over again since I didn't indent/group the email domain filters.


Any help/suggestions will be greatly appreciated!

Photo of Kipp

Kipp

  • 258 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Kipp

Kipp

  • 258 Points 250 badge 2x thumb
What if I was able to automatically separate email domains in to a new field in the contacts table, create a list of the domains which have a relationship manager and then use that list to run a report? Not sure how but throwing it out there.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
I suggest making a table of Domains with managers with the Key field being the domain, such as apple or microsoft.

On the contacts table I would make a field called [email domain] with the formula


right([email address],"@")


Then make a summary report filtered where there is a Relationship manager and Summarize the count of the email domain field. Then export that to Excel and import it to the domains table.

Add a formula checkbox field to the Domain table with a formula of true called [Domain exists]

Make a Relationships between the contact and Domains table based on that [email Domain] field and then lkkup the value of the [Domain exists] field.

Now make two exception reports.

Contacts with Relationship Managers but with no Domain loaded in the Domains table ie [Domain exists] is false.

Contacts with [Domain exists] is true, but do not mail is not flagged. 
Photo of Kipp

Kipp

  • 258 Points 250 badge 2x thumb
Hi Mark, it has worked, that's a great improvement, thank you.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
Thx for letting for me know and that my steps made sense to you.