Form Query String - Using Multiple Values

Options

I've been doing some research on how to append values from a sheet, to a form link, so that when the user clicks on the link, certain fields are pre-populated. I've run into two issues that I'm looking for some help on:

  1. Can you add more than 2 values in the query string? I'm referencing fields in a sheet, so they're not hard coded, but it seems like when I get to the 3rd parameter, the URL still executes, but the 3rd field won't populate. I tested the parameter is properly formatted by removing the 2nd parameter, and it does work, it just seems like if I go beyond 2 arguments/parameters, it doesn't work.
  2. I'm also having trouble getting drop-down values to show up at all. Is that possible?

Thanks for any help

Mike

Answers

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

    Hi @Mike Rini

    I hope you're well and safe!

    If I remember correctly, I've used it for more than 3 values and dropdown list options.

    Can you share the URL-query formula?

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

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

    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.

  • Mike Rini
    Options

    Thanks for the quick response @Andrée Starå

    Here's the basic formula - removing the actual form url for purposes of showing it here: ="formurlgoeshere?Request%20ID=" + [Request ID]@row + "&Campaign%20Name=" + [Campaign Name]@row + "&Creative%20Differentiator=" + [Creative Differentiator]@row

    This gives me the first two populated correctly, but not the third. The third one is actually removed when the URL resolves after being clicked on (I noticed after looking in the address bar of Chrome).

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

    @Mike Rini

    Happy to help!

    I'd be happy to take a quick look.

    Can you share a copy of the sheet with at least one row?

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    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.

  • Abraham Sanchez
    Options

    @Mike Rini

    As andree mention I would be good to see the actual data sample or the final url.

    I had issues when encoding the url from fields, because of spaces and special charecters blocks the parameters, and there is no url encoder function in Smartsheet that I am aware of, so you can use substition funtion to replace those cases. On yur formula I see that Campaign Name, could be problematic, because it can have spaces or special caracters. if it is, that could be the reason why your third parameter never gets read.

    Here is a sample I used, instead of the plain field, you can add more special cases depending on your data.

    SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Campaign Name]@row, " ", "+"), ",", "%2C"), "&", "%26"), "#", "%23")

    Regards,

    Abraham

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

    @Abraham Sanchez

    Yes, that's where I was going as well.

    I recently developed an advanced client solution where we use the URL-query feature to create unique QR codes tied to specific tasks/projects.

    @Mike Rini

    Let us know how it goes.

    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.

  • Mike Rini
    Options

    @Abraham Sanchez that makes sense. So it's possible it's the value being passed through in the string that's the issue then? In other words, my reference to the cell may be fine, but the value it's pulling in may have an additional space, ', etc. which is causing the URL to not parse that?


    Thanks for the quick feedback, I'll start taking a look and see if I can pinpoint the issue.

    Much appreciated

  • Paul Lopez
    Options

    I'm having the same issue after the first query, no problem pre-populating the first field. Then the rest are empty. I've tested each query separately and they work with no issues, but when I add them to one link, only the first one works.

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

    Hi @Paul Lopez

    I hope you're well and safe!

    Can you share the formula?

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic weekend!

    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!

    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 Lopez
    Options

    ="https://app.smartsheet.com/b/form/XXXXXXXXXXXXX?Start%20Date=" + [Start Date]@row + "&Project%20Name=" + SUBSTITUTE([Task Name]1, " ", "%20") + "&End%20Date=" + [End Date]@row

  • Paul Lopez
    Options

    I figured out what it is, it's the time after the date that's stopping the query. How do I remove time from the date format?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 02/19/21
    Options

    @Paul Lopez

    Excellent!

    You'd use the DATEONLY function.

    Did it work?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support 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!

    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.

  • Mike Rini
    Options

    I appreciate all the help here, and was able to figure out most of the challenges with my query strings. One question I have is with passing all the values in a multi-select through. I know that smartsheets has a delimiter of CHAR(10) for separating the selections, and if I'm using the SUBSTITUTE(), what should I be replacing it with?

    I assumed the function would look like this SUBSTITUTE([Field 1]@row, CHAR(10),"%0A") given that 0A is the HEX conversion, but it doesn't work when pulling in the next selection for the multi-select.

    Any thoughts on how to tackle this part?

    Thanks,

    Mike

  • Mike D.
    Options

    I'm able to get the formula to work but is there any way to make it appear as a one word link on the sheet? Since it looks like a formula and not a hyperlink, the Hyperlink function doesn't work. I basically want people to be able to basically click a "link" that says "Update" that then takes them to the form...