Forms URL query String, IS there a limit to how many queries can be used?

cjg

Hi, I am a novice with Smartsheet, but am learning.

I have a form that I need to pre-populate so we can send it out to technicians to complete a field report.

I can get five fields to populate, but no more than that.

="https://app.smartsheet.com/b/form/d2cda8ab647e4c11b40e503977eb916b?Part%20Number=" + [Part Number]@row + "&Serial%20Number=" + [Serial Number]@row + "&Customer%20Name=" + SUBSTITUTE([Customer Name]@row, " ", "%20") + "&Model%20Number=" + SUBSTITUTE([Model Number]@row, " ", "%20" + "&Install%20Date=" + [Install Date]@row + "&Notes%3A=" + SUBSTITUTE([Notes:]@row, " ", "%20") + "&Test%20One=" + SUBSTITUTE([Test One]@row, " ", "%20") + "&Test%20two=" + SUBSTITUTE([Test two]@row, " ", "%20"))

Any ideas?

Thank you,

Clint

Andrée Starå

@cjg

Excellent!

You're more than welcome!

Here's an excellent helpful article detailing the special characters that might need to be used to make the form work.

✅Remember! 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!

Lucas Rayala

@cjg, honestly I think you just taught me something new—I didn’t know you could pre-populate data in a form so I’m going to play with this tomorrow. I guess my question is this: if you can pre-populate the form, is it possible to have a formula column on your target sheet that would populate the data once your initial entry is completed?

Andrée Starå

Hi @cjg

I hope you're well and safe!

There's no limit that I know of, and I've developed client solutions using a lot more fields than five, so there has to be an issue in the formula somewhere.

I'll take a look and see if I can see it at a quick glance.

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!

Andrée Starå

@cjg

Try something like this.

="https://app.smartsheet.com/b/form/d2cda8ab647e4c11b40e503977eb916b?Part%20Number=" + [Part Number]@row + "&Serial%20Number=" + [Serial Number]@row + "&Customer%20Name=" + SUBSTITUTE([Customer Name]@row, " ", "%20") + "&Model%20Number=" + SUBSTITUTE([Model Number]@row, " ", "%20") + "&Install%20Date=" + [Install Date]@row + "&Notes%3A=" + SUBSTITUTE([Notes:]@row, " ", "%20") + "&Test%20One=" + SUBSTITUTE([Test One]@row, " ", "%20") + "&Test%20two=" + SUBSTITUTE([Test Two]@row, " ", "%20")

Did it work?

✅Remember! 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!

cjg

Hi Andree,

No, that didn't work either. Same result as before.

Andrée Starå

@cjg

Hmm. It works for me.

Can you share some screenshots of the column names in the sheet? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

cjg

Hey Lucas,

Yes, this form is connected to another sheet. Once our customer fills out the service request, I set up an automation to another sheet we are trying to auto-populate. This form will go to our technician. The tech will fill out the fields that pertain to them and once they submitted it will be on my sheet. That is the hope, if I can get it to work,

cjg

Hi Andree,

The second URL was me testing it again this morning. I have had some issues that if the field was not on the original URL it would not display.

Andrée Starå

@cjg

I see the issue.

I'll get back to you!

Andrée Starå

@cjg

Try this one.

="https://app.smartsheet.com/b/form/d2cda8ab647e4c11b40e503977eb916b?Part%20Number=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Part Number]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Serial%20Number=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Serial Number]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Customer%20Name=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Customer Name]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Model%20Number=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Model Number]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Install%20Date=" + [Install Date]@row + "&Notes%3A=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Notes:]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Test%20One=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Test One]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Test%20two=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Test Two]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23")

Did it work?

✅Remember! 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!

cjg

That worked. Obviously, what you wrote up is much more in-depth than what I had. Where can I look to learn what you did?

cjg

Thank you.

Andrée Starå

@cjg

Excellent!

You're more than welcome!

Here's an excellent helpful article detailing the special characters that might need to be used to make the form work.

✅Remember! 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!

Andrée Starå

@cjg

I don't know what happened, but I noticed the link wasn't included. Here it is again.

https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

Paul Newcome

Here is my suggestion for helping to keep these URL formulas a little bit tidy (or at least as tidy as they can be).

Think of it in three sections. You have one section that contains the fields and data to populate, you have a second section that will be swapping out your characters for the correct % encoding, and another section that is the "base" of the url.

I work my formulas in that order.

="Field Name 1=" + [Field Name 1]@row + "Field Name 2=" + [Field Name 2]@row

The above generates the string that will take care of the fields and their data. Now I start wrapping the whole thing in my SUBSTITUTE functions to take care of the % encoding.

=SUBSTITUTE(SUBSTITUTE("Field Name 1=" + [Field Name 1]@row + "&Field Name 2=" + [Field Name 2]@row, " ", "%20"), "/", "%2F")

Then I will add my base URL at the beginning.

="URL?" + SUBSTITUTE(SUBSTITUTE("Field Name 1=" + [Field Name 1]@row + "Field Name 2=" + [Field Name 2]@row, " ", "%20"), "/", "%2F")

.

Doing it this way allows me to focus on each of the main pieces on at a time and also helps keep me from having to repeat the same SUBSTITUTE functions multiple times through the string which helps cut down on all those parenthesis and syntax issues as well as troubleshooting finding specific fields and whatnot. It also makes it easier to adjust for different characters that need swapped out because I can just add a SUBSTITTUE to the beginning (after the base URL) and then take care of the character swap at the end. And you don't have to try to remember to always type "%20" everywhere there is a space in a field name or anything like that.

.

That turns this:

="https://app.smartsheet.com/b/form/d2cda8ab647e4c11b40e503977eb916b?Part%20Number=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Part Number]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Serial%20Number=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Serial Number]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Customer%20Name=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Customer Name]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Model%20Number=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Model Number]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Install%20Date=" + [Install Date]@row + "&Notes%3A=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Notes:]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Test%20One=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Test One]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23") + "&Test%20two=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Test Two]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23")

Into this:

="https://app.smartsheet.com/b/form/d2cda8ab647e4c11b40e503977eb916b?"+ SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("Part Number=" + [Part Number]@row + "&Serial Number=" + [Serial Number]@row + "&Customer Name=" + [Customer Name]@row + "&Model Number=" + [Model Number]@row + "&Install Date=" + [Install Date]@row + "&Notes:=" + [Notes:]@row + "&Test One=" + [Test One]@row + "&Test Two=" + [Test Two]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23")

Paul Newcome

It keeps freezing when I try to edit my post above. I had a small typo.

"&Test Two="

should be

"&Test two="

="https://app.smartsheet.com/b/form/d2cda8ab647e4c11b40e503977eb916b?"+ SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("Part Number=" + [Part Number]@row + "&Serial Number=" + [Serial Number]@row + "&Customer Name=" + [Customer Name]@row + "&Model Number=" + [Model Number]@row + "&Install Date=" + [Install Date]@row + "&Notes:=" + [Notes:]@row + "&Test One=" + [Test One]@row + "&Test two=" + [Test Two]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23")