Checking a contact field for a specific contact

John_Foster

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

Paul Newcome

Are you basically trying to check a box on rows where the [Primary Contact Name] matches the [Created By] field?

John_Foster

HI @Paul Newcome, yes, then I can utilise the checkbox to determine whether or not to send one of the automation emails.

John_Foster

@Paul Newcome it is worth noting that the Primary Contact field can have more than one contact in it.

DKazatsky2

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

Paul Newcome

My next question would be... Are they all always email addresses, or are they sometimes email addresses and sometimes names?

John_Foster

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.

Paul Newcome

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?

John_Foster

@Paul Newcome It could be a hundred or more, but yes I could create a directory.

Paul Newcome

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)

John_Foster

Thank you @Paul Newcome, I will give this a try and let you know how I get on.