Pre-populate a form

skarkhoff

Trying this for the first time, but having trouble getting a dropdown list (single select) to populate in the form. I have 3 fields that I'm trying to pre-populate, the text and contact fields work fine, but the dropdown doesn't show data.

FORMULA: (the dropdown column is Territory Manager Name, NPI Number is text and Email is a contact field.

="FORMLINK?NPI%20Number=" + SUBSTITUTE([NPI Number]@row, " ", "%20") + "&Email=" + SUBSTITUTE(Email@row, " ", "%20") + "&Territory%20Manager%20Name=" + SUBSTITUTE([Territory Manager Name]@row, " ", "%20")

I also tried this, thinking maybe since it was a dropdown, it didn't show the responses as having a space?

="FORMLINK?NPI%20Number=" + SUBSTITUTE([NPI Number]@row, " ", "%20") + "&Email=" + SUBSTITUTE(Email@row, " ", "%20") + "&Territory%20Manager%20Name=" + [Territory Manager Name]@row

No luck! Do I need to format different because of the dropdown?

Debbie Turner

Hi,

I got this to work perfectly to pre-populate a form, but is there any way to stop people from updating the prepopulated fields without hiding them? I am passing the information over (project ID, Requestor, Project Description) so that the person filling out the form knows what they are being asked about, but they are updating the project description field on me. I am sending the form out to multiple people and I want separate records in a new sheet, so I don't think an update request is going to work for me.

I had another thought.. can you use substitution in the help text for the form like you can in the email automation so I could place all the passed information in there and keep the fields hidden?

Thanks

Andrée Starå

Hi @Debbie Turner

I hope you're well and safe!

Unfortunately, as far as I know, it's not possible now without hiding them, and the substation idea isn't possible either at the moment, but both are excellent ideas!

Please submit an Enhancement Request when you have a moment.

Here's a possible workaround or workarounds 

• As a possible workaround, you could hide them but add a couple of so-called helper columns that reference the other values and show them in the form instead.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic day!

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!

liz.mayeux

My only suggestion is to include labels or change the descriptions for the form w/ instructions for not updating this field.

Andy B

New to SS, but decent at excel. I thought I'd ask this question in this thread as I believe it pertains.

I'm creating a simple request workflow for payment. I'm creating a form to feed my sheet, and I'd like to have a drop down as the first option for users to have an option of choosing an existing vendor or "free form". If they choose an existing vendor, it would automatically populate certain fields. If they choose "free form", they would have to populate. Any suggestions?

I'd like to have this all in one form. As I read, am remember I'm new, this thread talked about creating different URL's that will prepopulate. I'd rather have one form, with a drop down, that would populate fields, and users could change the values if they so choose. I could have the data to populate this form in a standing data file/spreadsheet.

thanks in advance!

liz.mayeux

If I'm understanding you correctly, your initial form would need to work with logic.

Depending on how they answer the 1st question - Use existing vendor or create new. Existing vendor would allow for a drop down list to appear as the next question and the information can be back filled behind the scenes (either by data mesh - if you have that optional upgrade OR by creating vlookup formulas for that column). If they choose create new then the cells would appear that they are responsible for filling out.

I hope this helps.

Paul Newcome

@Andy B I suggest creating two columns for each entry. One that is a dropdown and the other that is text/number. If you include every column in the form, you can use conditional logic in the form to either show the dropdowns or the free text fields based on the initial selection.

From there you could create yet a third set of columns for each entry point that would pull the non-blank values so that all of the data is included in the same column (useful for reports/metrics/easier to read).

Hiding the columns that are actually filled in through the form will help keep the sheet from becoming too cluttered.

Andy B

To all: In my perfect world, the if the user selected for example, "Amazon" from the first field called vendor, it would automatically, in real time, populate all the other fields in the form.

@liz.mayeux Thanks. Question, if I put in a vlookup in my sheet, for example if the answer to pull in info as the user populated/choose a vendor, would it prepopulate the form as the users moves through the fields. I tested and it doesn't look like but I might be missing something.

@Paul Newcome So in your example, if the user chooses "Amazon" as the vendor, how would I tell the next field, let's say 'bank routing info' to use the data for "Amazon" from the first field. My limited understanding of logic is it can only effect what fields show up after a choice is made. If I have 20 vendors do I need to create 20 unique columns for every category?

thanks!!!!

Paul Newcome

@Andy B It seems I misunderstood what you were needing. You are going to want something along the lines of what Liz suggested (although I recommend an INDEX/MATCH instead of a VLOOKUP).

And you are not missing anything. You will not be able to "auto-fill" fields in the form as the user is filling it out. The information would be grabbed once the form has been summitted.

liz.mayeux

@Paul Newcome You are correct with the INDEX/MATCH formula suggestion. Using my MSExcel habits, VLOOKUP formulas are my goto. A real time form autofill would be fantastic and one of those upgrade requests they ask for us to provide to them.

Dynamic View would allow for some sort of real time updates, but that is a premium option and not always available to everyone. You would still need an INDEX/MATCH formula running to have it work as well.

Paul Newcome

@liz.mayeux Dynamic View or a report (depending on data security needs). The report option wouldn't cost anything additional but has the downside of less data security. The Dynamic View is more secure but (as you said) a premium option/costs more.

Andy B

Ok, thanks guys. Let me wrestle with it for a few days. Good help, I appreciate it.

Steve Gebbie

What's the best way to direct a user to the URL generated by the formula after submission? My thought is that after submit, you could direct the user to the URL generated by the responses in the form that would auto pre-fill certain fields so they wouldn't have to repeat typing them in again for another submission of the same form.

Would this work? I tried adding it to the "send user to a link" after submission but that did not work. Is there an easier way to accomplish this?

Andrée Starå

Hi @Steve Gebbie

I hope you're well and safe!

I developed a solution using two forms (or the same form by using conditional logic) that can be used to submit the main information and then the other information on a new row for each submission after the first.

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

liz.mayeux

@Andrée Starå is this solution something you can share? I'm struggling w/ the same situation with needing the same information entered multiple times, but don't want to turn off the person submitting the form.