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
-
Andrée Starå ✭✭✭✭✭✭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 bymarking 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:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
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 bymarking 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:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Andrée Starå ✭✭✭✭✭✭
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 bymarking 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:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
cjg ✭
Hi Andree,
No, that didn't work either. Same result as before.
-
Andrée Starå ✭✭✭✭✭✭
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:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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å ✭✭✭✭✭✭
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Andrée Starå ✭✭✭✭✭✭
Try this one.
="https://app.smartsheet.com/b/form/d2cda8ab647e4c11b40e503977eb916b?Part%20Number="+替代(替代(替代(替代([标准t 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 bymarking 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:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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å ✭✭✭✭✭✭
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 bymarking 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:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Andrée Starå ✭✭✭✭✭✭
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
= "字段名称1 = " +(字段名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="+替代(替代(替代(替代([标准t 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.
"&TestTwo="
should be
"&Testtwo="
="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 + "&Testtwo=" + [Test Two]@row, " ", "%20"), ",", "%2C"), "&", "%26"), "#", "%23")
Categories
<\/p>
Attempt two: can you update your trigger into \"When Rows Are Changed\"<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/5Q6HHHJHRFET\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\nThen, delete all of these erroneous rows and hit save.<\/p>
Finally, add \"No Show\" into like three rows. Save your sheet, and reload (automation takes a second to run completely). <\/p>
Is your \"Attendance\" column set up as a dropdown or as a text?<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":112207,"type":"question","name":"access to sheet","excerpt":"I want specific fields but I don't want a link to the sheet.... If someone has the link to the sheet would they even be able to access it?","snippet":"I want specific fields but I don't want a link to the sheet.... If someone has the link to the sheet would they even be able to access it?","categoryID":321,"dateInserted":"2023-10-26T15:25:53+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T16:09:50+00:00","insertUserID":166642,"insertUser":{"userID":166642,"name":"JenaW","url":"https:\/\/community.smartsheet.com\/profile\/JenaW","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T17:45:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":142964,"lastUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-10-26T16:55:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":33,"score":null,"hot":3396672343,"url":"https:\/\/community.smartsheet.com\/discussion\/112207\/access-to-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112207\/access-to-sheet","format":"Rich","lastPost":{"discussionID":112207,"commentID":401859,"name":"Re: access to sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401859#Comment_401859","dateInserted":"2023-10-26T16:09:50+00:00","insertUserID":142964,"insertUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-10-26T16:55:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/GCNDC3R4VSAE\/h.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/GCNDC3R4VSAE\/h.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/GCNDC3R4VSAE\/h.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/GCNDC3R4VSAE\/h.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/GCNDC3R4VSAE\/h.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/GCNDC3R4VSAE\/h.png"},"alt":"h.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T16:01:38+00:00","dateAnswered":"2023-10-26T15:58:49+00:00","acceptedAnswers":[{"commentID":401852,"body":"
Hi @JenaW<\/a>,<\/p> You have the option of using \"Message only\", and then displaying your two fields within the message using double curly brackets like this:<\/p> The tradeshow named {{Association\/Tradeshow Name}} will be held on {{Tradeshow Date}}.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":112167,"type":"question","name":"After approval, update request","excerpt":"1 week after approval, I need an update request to go out. Is that possible? How can this automation be set up?","snippet":"1 week after approval, I need an update request to go out. Is that possible? How can this automation be set up?","categoryID":321,"dateInserted":"2023-10-25T21:01:48+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T14:47:56+00:00","insertUserID":166642,"insertUser":{"userID":166642,"name":"JenaW","url":"https:\/\/community.smartsheet.com\/profile\/JenaW","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T17:45:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":166642,"lastUser":{"userID":166642,"name":"JenaW","url":"https:\/\/community.smartsheet.com\/profile\/JenaW","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T17:45:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":35,"score":null,"hot":3396601784,"url":"https:\/\/community.smartsheet.com\/discussion\/112167\/after-approval-update-request","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112167\/after-approval-update-request","format":"Rich","lastPost":{"discussionID":112167,"commentID":401829,"name":"Re: After approval, update request","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401829#Comment_401829","dateInserted":"2023-10-26T14:47:56+00:00","insertUserID":166642,"insertUser":{"userID":166642,"name":"JenaW","url":"https:\/\/community.smartsheet.com\/profile\/JenaW","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T17:45:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T21:35:00+00:00","dateAnswered":"2023-10-26T14:42:15+00:00","acceptedAnswers":[{"commentID":401825,"body":"
