Send Form for Completion to Individuals with Pre-Populated Fields taken from Data in the Sheet

SusannahAmuri

I have a sheet called 'Farmer Access Survey' which will contain the current data we hold on record for 130 farmers. I have created a form, which I want to send to each farmer to confirm the current data we hold, and collect additional information from them.

Can I send a link to the Form to each individual in an Outlook email, and when they click on the form link, have the form open up pre-populated with the data we already have on file for them?

I don't want the farmers to have to populate a blank form when we already have a lot of their data. I do not want to send an update request, as the form looks much more professional and I need the descriptions.

I have seen a number of references to using the following URL query formula and others similar to it (which refers to the first two columns I'd like to pre-populate) like this: =https://app.smartsheet.com/b/form/06a1e7297b1a4d6a80a9796e06d7062b?Shareholder&20Name="+SUBSTITUTE([Shareholder Name]@row," ","%20") ;Property&20Name="+SUBSTITUTE([Property Name]@row," ","%20")

I cannot get this to work. Is what I want to do is actually possible?

What is the correct formula to use? My sheet has multiple columns but the first few I would want to pre-populate for each farmer is Shareholder Name, Property Name, Contact 1 Name, Contact 1 Position, Contact 1 Telephone.

We planned on copying the link from each row into the individual farmers' emails in Outlook (as we have another item to attach and a cover email to explain the form). Will it still pre-populate with the row information from that email? If not, what would be the best way to share the link to each farmer?

I've attached images of the sheet and the form. The sheet only has one line of placeholder data in it at the moment.

Farmer Access Survey FORM.pdf

Farmer Access Survey SHEET.pdf

Thanks so much for any assistance.

Andrée Starå

Hi @SusannahAmuri

I hope you're well and safe!

Have you explored using the Update Request feature instead?

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 support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SusannahAmuri

Hi Andrée - I don't want to use Update Request because the form looks a lot more polished and professional, and also has the benefit of the extra description text. If you look at the form PDF I attached, you will see there is a lot of extra dialogue I need my farmers to read.

Paul Newcome

I would suggest writing out the formula to generate the URL without the individual SUBSTITUTE functions, then wrap the whole thing in a series of SUBSTIITUTE functions to replace every instance. An example for the spaces would be:

=SUBSTITUTE(https://app.smartsheet.com/b/form/06a1e7297b1a4d6a80a9796e06d7062b?Shareholder Name="+[Shareholder Name]@row + "Property Name="+[Property Name]@row, " ", "%20)

This will replace the spaces in field names as well as cell data all at once and may make things a little easier to manage.

SusannahAmuri

Thank you so much Paul! However I pasted that formula into my 'form Link' column sheet and got the #UNPARSEABLE error again?? Do I need to change something on that formula?

Is what I am trying to achieve is actually possible? Once the formula is right, and I copy it into each farmer's draft email in Outlook - how is Smartsheet going to know what row to pull the data from for that particular farmer? Or do I need to send the form from within Smartsheet somehow to make this work?

SusannahAmuri

Just another thought - does my link to the form/formula need to include cell references to get it to populate the specific data from each row?

Paul Newcome

My apologies. I forgot a quote there before https

=SUBSTITUTE("https.......

You will need to have this formula populate on each row. When you do this, the output is going to be different on each row, so you will need to make sure you are sending the correct form link to the correct person.

SusannahAmuri

Hi @Paul Newcome - I'm pasting in this formula below:

=SUBSTITUTE(“https://app.smartsheet.com/b/form/06a1e7297b1a4d6a80a9796e06d7062b?Shareholder Name="+[Shareholder Name]@row + "Property Name="+[Property Name]@row, " ", "%20)

But it's still saying #UNPARSEABLE.

I'm in New Zealand - I saw somewhere that some formula elements change based on region - "Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma." Although I tried swapping the commas and it didn't make a difference.

Regarding having the formula populate on each row - what do I need to do anything to make this happen? I'm intending to paste the formula above (if I could get it to work!) into my 'Form Link' column for each row. But then was just going to copy it out of that cell and into the Outlook email when we were ready to send the emails. I'm so sorry if I'm being quite dense about this - I just don't understand how that link will know which row to auto-populate from if I do it this way. How do I know I am sending the correct form link?

Thank you SO much for your time - I cannot tell you how much I appreciate it.

Paul Newcome

These quotes are going to be the death of me. Sorry. One more after %20 there at the very end before the last closing parenthesis.

I also notice that the first one is slanted. You can't use Word or similar programs to write formulas because they use a different type of quote. Type it directly in Smartsheet or in a text editor such as Notepad (notice the rest are straight up and down?).

Once we get this formula set, you will want to apply it as a column formula. Where you have [Column Name]@row, the formula will output whatever value is in that cell.

Row 1: [Column A] = "Test"

Row 2: [Column A] = "Not Test"

Whenever you apply the formula, wherever you have [Column A]@row, it will be output as

http..............................."Test" on row 1

and

http..............................."Not Test" on row 2

When you copy those URL's into the email, you should see that "Test" or "Not Test" is in the URL itself.

SusannahAmuri

Thanks so much @Paul Newcome! That mostly worked, I could suddenly see how it was collecting the information, although the Shareholder Name field when populated said 'Susannah Amuri PropertyName@row'... no big deal , I could probably figure that out... but then pasting the link into an Outlook email didn't work. I could paste it in but then it wasn't a link anymore. So I have given up on this method, it's just taking up too much time.

However, I have found a workaround that I am sharing here for anyone else struggling with this.

I am using Update Request after all. This has meant carefully wording my column headings to fit within the character limit. Then to avoid the ugly inclusion of the data fields in the email sent directly from Smartsheet, I am sending the Update Request to myself, then copying the link to the form, and pasting that into my Outlook email.

I then have an attractive cover email sent from my Outlook account, and a tidy 'Open Update Form' link for my farmers to click on. The form looks attractive when they open it, I can use drop downs and checkboxes, and I can still include attachments and receive new ones. It does what I need it to do with only two restrictions - a) the limited characters for the 'question' field, and b) that extra step of sending the request to myself, then pasting into the email to the farmer. This works for me with only 130 emails to send, but wouldn't be practically for larger numbers.