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

Options

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

Tags:

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    @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!

    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.

«1

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Options

    @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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @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!

    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.

  • cjg
    cjg ✭✭✭
    Options

    Hi Andree,

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @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.

    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.

  • cjg
    cjg ✭✭✭
    Options

    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
    cjg ✭✭✭
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    @cjg

    I see the issue.

    I'll get back to you!

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @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!

    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.

  • cjg
    cjg ✭✭✭
    Options

    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
    cjg ✭✭✭
    Options
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    @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!

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @cjg

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


    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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")