Getting a Contact from a form input

Eric Horn

I'm implementing a Sheet to receive input for Time Off Requests. For the submission form, the users will enter their name, the names of their approvers (supervisors), and their department's timekeepers.

For these 4 employee columns, I have created them as Contact Lists. I have created an approval workflow and also reports and these are all built off of the Contact List column functionality.

The submission form, though, doesn't allow the input of a contact. When the sheet is submitted, the name enters as text instead of the contact and nothing can be done with the row until the sheet is manually updated to be a Contact instead of just the text.

Are there any methods, workarounds, or Contact List formulae I can use to fix this?

Andrée Starå

Hi @Eric Horn

You'll need to add so-called helper columns for each role and then use a formula to get the email address.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Eric Horn

Ok, I think I get what you're suggesting (you'll have to excuse me, I'm new to SS). If I'm to understand correctly,

1. Create a Text column that will receive the input from the form. Let's say the user inputs the email address into the form.
2. Put a formula into the contact column that will copy in the email address as a contact as opposed to just text.

Is that correct? Also, what would the formula be?

Andrée Starå

@Eric Horn

No worries!

Yes, that is correct.

You'd add something like this to the helper column.

=[Email from form]@row

Make sense?

Eric Horn

Thank you. I put in the change and it accomplished exactly what we needed, so thank you for your assistance.

Andrée Starå

@Eric Horn

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Brett Smith

Hi all,

I am having a very similar problem. I want to use Contact List for approval workflows. But when I use a Contact List in a form - it is basically just a text entry field. This creates work elsewhere, as somebody needs to manually update the sheet so the entry is in-fact a Contact. And that essentially defeats the point of trying to automate.

I can add in optional values to the Contact List, but then that creates a longer term problem of updating/maintaining those optional values as people move/change etc.

How can I use a Form to pass an actual Contact to a sheet. Surely this has been worked out previously??

Cheers

Brett

Andrée Starå

Hi @Brett Smith

How did it go? Did you manage to get something set up?

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Dale Murphy

@Andrée Starå

Andree, I have followed the path here, and in a sheet the column looks like a contact.

However, if i try to use this column in a report an filter on "Current User", the report filtering does not function properly. I am certain it is because the process to turn the text entry into a contact does not include both name and email address.

Any ideas on how to build the contact list column from a form entry so that it is recognized by reporting?

dm

Robin Herrick

The helper column works fine, but I wonder why I can't use the system column Created By. The formula in the sheet does not appear to recognise this column. Why does this not work, and why is it not possible to make this system column visible?