Two People same name in Contact List

Nojolu Christine

Hello all.

Using Contact Lists, I have two different people with the same name but different emails. When working on an approval workflow, I use INDEX/MATCH to find who each person Reports To. The problem is that Smartsheet only recognizes (MATCH) the first occurrence of the actual first name and last name BUT does NOT differentiate based on the email address portion of the Contact List (which differs).

Help! How do I handle this? Is there a better function which will accurately find the singular person with differing email, same name?

Christine

Paul Newcome

Exactly how do you determine which name gets assigned?

Nojolu Christine

Entries are made within a form. The field is column type Contact List and has drop down predetermined values of possible employees from which the entrant may select.

Thanks in advance for your help!! I appreciate whatever insight you may have.

Paul Newcome

Ok. So how do the form users know which "John Doe" to select if both of them have the same name?

Nojolu Christine

Good question. On the form, the drop down values include:

First-Name Last-Name (email-address)

e.g., John Doe (john.doe1@myorg.edu) vs. John Doe (john.doe2@myorg.edu)

Users are familiar with the different email addresses which identify each person even though each John Doe spells his name the same.

Paul Newcome

I would suggest finding a way to differentiate the two in the dropdown list. Then insert a text/number column that replicates the dropdowns next to the email addresses and use these as your reference.

Nojolu Christine

hm. I think I understand your suggestion. That would take some redesign since I use one drop down list, but my INDEX/MATCH accesses two different Employee Lists based on whether they are a Student or Staff member. I believe then I would need to add separate drop downs for Students and Staff separately, and still add the text/number reference for each to check for the duplicate within each Employee list, and rewrite my formulas. Well ok. I did not know that Contact List evaluates a match on only First-Name and Last-Name, and not email...... if I understand it correctly....that is disappointing.

Thank you for your help, Paul. Much appreciated.

Paul Newcome

You may not need to separate your dropdowns. You could use an IFERROR. If an INDEX/MATCH doesn't find a match, it will throw an error. So we write one to look through the student list and another to look through the staff list. Then we use an IFERROR to say that if the student list formula returns an error (no match), then run the staff list.

=IFERROR(INDEX({Student List}, MATCH(........................)), INDEX({Staff List}, MATCH(........................)))

I also had another thought while reading through the thread again...

You say the email address is already included in the dropdown selection? If that is the case, then we can forget about hitting against a list and use a formula to just parse it directly from the dropdown.

=SUBSTITUTE(RIGHT([Dropdown Column]@row, LEN([Dropdown Column]@row) - (FIND("(", [Dropdown Column]@row) + 1)), ")", "")

The above should pull the email out of the "First-Name Last-Name (email-address)" string.

Nojolu Christine

I like the parsing idea. Going to try it. ....hopeful :)

Paul Newcome

Let me know how it goes. I haven't done any testing to make sure the exact details are correct, so if it doesn't work then we will need to do a little tweaking.

Nojolu Christine

Tried it: Interestingly, although the dropdown list appears as "First-name Last-name (email address)", it is merely the visual display of the contact list fields of Name and Email. And since, disappointingly, Smartsheet only looks at the Name portion, your awesome SUBSTITUTE(RIGHT....LEN..) formula does not return the Email. Validation for Smartsheet only occurs on the Name. Therefore only the Name returns. I will keep your formula in back pocket for another time [thank you] - but not for use on a Contact List column.

This situation of duplicate Names w/ different Emails for type Contact List column has me stumped to find an elegant solution.

Paul Newcome

Ah. My apologies. I read "dropdown" as a dropdown type column with the options listed as text strings. I didn't realize that you were referring to an actual Contact type column.

Hmm... too bad we don't have an "EMAIL" function that could pull the stored email address from the contact listed (maybe a product enhancement request and throw in a "PHONE" function too).

Nojolu Christine

Nice. Would like that. :) also anxiously awaiting Contact List drop downs in Forms - that alone would help significantly. I wonder when SS is targeting that solution(?).

Thank you again for the help. Can't tell you how much I appreciate the dialogue with a well seasoned guru and getting perspective outside of my own head.

Paul Newcome

Happy to help. 👍️

I will keep trying to think of something.

Maybe another dropdown field in the form that has the values of "A" and "B". Use conditional logic to only show if it is one of the duplicate names. Instruct users to select "A" if it is the first John Doe or "B" if they want the second.

Then we can add a column to your table where you would manually enter the "A" or "B" and then switch from an INDEX/MATCH to an INDEX/COLLECT so that we can incorporate this.

Do you think that's something the users could handle?

Nojolu Christine

Not these users. Not yet. I think I will need to handle it for them and not ask too much from them. They are somewhat reluctant users right now. in time.

Paul Newcome

Ok. Unfortunately then I think we are kinda stuck at the moment. If you can think of a way that users would be open to indicating which one, then I am sure we can find a solution to automate the rest of the process.