Forms URL query String, IS there a limit to how many queries can be used?
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
Best Answer
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
@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?
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andree,
No, that didn't work either. Same result as before.
-
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.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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,
-
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.
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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?
-
Thank you.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives