Checking a contact field for a specific contact
We have a form that can be submitted by users for system change requests (CR's). When submitting the CR's the user can specify another user to be the primary contact.
Due to a requirement to use Dynamic View were by default we want users to be able to see any CR's where they are outstanding and they are the primary contact, we need users to always complete the primary contact, even if they are the person raising the CR.
Off the back of a CR being raised, we send out a notification via an automation process, to the user who raised it. Then we send out an additional email to the primary contact. This means that if a user raises a CR where they are the primary contact, they get two notifications.
I have tried to use the following formula to establish if the Created By field can be found in the primary contact, but this is not working and is always showing a count of 0, does anyone have any ideas on what I could do?
=COUNTIFS([Primary Contact Name]@row, HAS([Created By]@row, [Primary Contact Name]@row))
Thanks,
John
Answers
-
Are you basically trying to check a box on rows where the [Primary Contact Name] matches the [Created By] field?
-
HI @Paul Newcome, yes, then I can utilise the checkbox to determine whether or not to send one of the automation emails.
-
@Paul Newcome it is worth noting that the Primary Contact field can have more than one contact in it.
-
Hi @John_Foster,
The "HAS" formula returns true/false, so it is not working as you expect inside the COUNTIFS formula. Try using an IF formula instead.
=IF(HAS([Primary Contact Name]@row, [Created By]@row), 1)
Also note, you had the range and criteria reversed in your original HAS portion.
Hope this helps,
Dave
-
My next question would be... Are they all always email addresses, or are they sometimes email addresses and sometimes names?
-
Hi @Paul Newcome they should always be email addresses, it cannot be guaranteed as there is no way to perform validation on the form that is being used.
-
How many different people could potentially be listed for the Primary Contact? Are you able to create a directory of sorts where you have the name in one column and the email in another column?
-
@Paul Newcome It could be a hundred or more, but yes I could create a directory.
-
Ok. In that case, I would suggest creating the directory and then using a helper column on the source sheet to pull the email (if needed) so that a comparison can be made to the [Created By] column.
=IFERROR(INDEX({Directory Email Column}, MATCH([Primary Contact Name]@row, {Directory Name Column}, 0)), "")
Now that we have email addresses on every row (assuming a name wasn't entered that isn't in the directory), we can use an IF/OR to say that if the [Primary Contact Name] matches the [Created By] or the [Helper Column] matches the [Created By] then check the box.
=IF(OR([Primary Contact Name]@row = [Created By]@row, [Helper Column]@row = [Created By]@row), 1)
-
Thank you @Paul Newcome, I will give this a try and let you know how I get on.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!