URL Query String for a Multi Select Drop Down Field

Trang Turtletraxx
Trang Turtletraxx ✭✭✭
edited 02/15/24 in Smartsheet Basics

Hi,

I've read posts with similar questions. What I can gather is I have to parse out each of the drop down options into my query string but the posts I've read all have the solution incorporated after the URL? the adding & and list the field again with the second value and so on.

My formula is all split up into individual fields and @row cell references, so I'm not sure where/how to incorporate the multi= and the parsed out options. I have included my query string I've created with just the normal @row reference for the multi select field, the field it's pulling from showing all the pick list options chosen and the pre populated form displaying only the first option.

Can anyone help guide me please and thank you?

Best Answers

«13

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/14/24 Answer ✓

    Hi @Trang Turtletraxx

    To prefill to a Multiple Dropdown List Columm form field, I would concatenate a separate list constituting a Multiple Dropdown List. (&[form labe]+Value1+&[form label]+Value2+&[form label]+Value3 , , , )

    In the image below, the first sheet de-composes the multiple lists to separate lists, n1, n2, and so on.

    Then, the following formula concatenates multiple prefill values.

    [URL] =FormUrl# + "?List=" + [n1]@row + "&List=" + [n2]@row + "&List=" + [n3]@row + "&List=" + [n4]@row + "&List=" + [n5]@row

    So, in your case, you first need to separate each list making up the "Cross Cutting Portfolios" like "Business System," "ECS," and so on.

    Then, concatenate those prefill values like this;

    "&Cross%20Cutting%20Portfolios="+ "Business%20System"+"&Cross%20Cutting%20Portfolios="+ "ECS"

    You can access the demo dashboard with the following link. The first sheet is editable. So, you can add or change the list items and use the URL to input list choices to the second sheet with a prefilled form. (You need to refresh the dashboard several times to see the input reflected.

    Prefilled form for the 3rd row.


  • @jmyzk_cloudsmart_jp Thank you SO SO very much for being so detailed in your response. The way you explained it made so much sense and you wrote it out as if you were teaching a lesson. This worked perfectly for my formula and pre populated form. You are the best!

  • @jmyzk_cloudsmart_jp I'm running into an issue with the formulas. I've created all the helper columns to concatenate the the list. Options where a 2 word choice is chosen ("Business Systems" or "User Productivity") only the first word appears. The URL Query is pulling the option appropriately, but in the actual form, it is only showing the first word "Business" or "User" and if the 2-word options has other Portfolios AFTER them, the other Portfolios won't appear. If "Business Systems" is NOT chosen and there are multiple other Portfolios they appear perfectly, except for User Productivity, but I think it is because User Productivity is the last on the drop down list, but it still only shows the first word. Any thoughts?

    The screen shots I've provided examples of with this issue are Rows 4 and 8.


  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/17/24 Answer ✓

    Hi @Trang Turtletraxx

    I would use a formula like this to populate items with more than two words having spaces between words.

    =FormUrl# + SUBSTITUTE("?List=" + [n1]@row + "&List=" + [n2]@row + "&List=" + [n3]@row + "&List=" + [n4]@row + "&List=" + [n5]@row, " ", "%20")

    Instead of substituting space with "%20" item by item, I substitute the whole query string's space or other special characters with the URL percent encoding.

    Updated demo sheet with new formula

    The formula works in both the checkbox and dropdown.







  • @jmyzk_cloudsmart_jp you are a GENIUS! Thank you SO much! Everything works perfectly.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Happy to help!😁

  • @jmyzk_cloudsmart_jp Sorry to bother you again. Please help!

    I'm running into an issue to our above solution. It seems any fields AFTER the multi select part will not populate (see first screen shot below). If I move the multi select portion of the formula to the END, it will not populate the multi select options (see second screen shot below). Any thoughts on why this is happening?


  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @Trang Turtletraxx

    Some of the special character substitutions are inconsistent in your formula for Portfolio(s). ("Portfolios") So, this may be causing 'any fields AFTER the multi select part will not populate'

    As for the second formula, I could not find any obvious error, but since the formula "will not populate the multi select options", I would check the part just before.


    I updated my demo sheet to include "( ) ?/."













    https://app.smartsheet.com/b/publish?EQBCT=ec7074b516924201853f70b57baba47a


    For consistency, I used the SUBSTITUTE function except for ( and ). That way, we can avoid the inconsistency errors.

    =FormUrl# + "?" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("list%28s%29=" + [n1]@row + "&list%28s%29=" + [n2]@row + "&list%28s%29=" + [n3]@row + "&list%28s%29=" + [n4]@row + "&list%28s%29=" + [n5]@row + "&name?=" + name@row + "&date d/m/y=" + date@row, " ", "%20"), "?", "%3F"), "/", "%2F")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You do not need multiple helper columns to pre-fill a multi-select dropdown. You would pull in the regular cell and use another SUBSTITUTE function to swap CHAR(10) for "%0D%0A".


    =FormUrl# + "?" + SUBSTITUTE("MultiSelect Field Name=" + [Multi-Select]@row, CHAR(10), "%0D%0A")

  • Similar to my other issue re: the URL Query line break. Can you educate me what exactly the CHAR(10), "%0D%0A" actually does/says as it relates to the function in the formula?

  • @Paul Newcome This is a bit different than the line break since there aren't any line breaks when there are multiple selections. The output is like a long string of words with wrap text. I may not be understanding your suggestion to this multi selection issue. Should I be adding the Substitute part in the column where I'm converting the cell to CHAR() code or into the URL Query String replacing all the references to the helper columns? I've tried both and neither worked.

    Screen Shot 1-3: Added Substitute(...CHAR(10), "%0D%0A") to my URL conversion column, removed reference to all those helper columns and referenced the single cell reference with CHAR(10) included. The form collected the multi select choices as a single choice (screen shot 2)


    Screen Shot 4: When I add the substitute(...CHAR(10), "%0D%0A") to my query string and get #UNPARSEABLE

    Thoughts?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    There are a number of special characters that have to be "percent encoded" to output a valid URL. Things like spaces being swapped out with "%20" and whatnot. Instead of manually doing this, you can use a series of SUBSTITUTE functions to just grab all of them at one no matter where they fall.

    ="base url" + "?" + "First Field=" + [First Field]@row + "Second Field=" + [Second Field]@row


    The above will output the base URL followed by a question mark and then the first and second fields to be pre-populated. The problem is that there are spaces in the field names, and there could potentially be spaces in the cell data. To go ahead and take care of all spaces at once, we can use a SUBSTITTUE function like so:

    ="base url" + "?" + SUBSTITUTE("First Field=" + [First Field]@row + "Second Field=" + [Second Field]@row, " ", "%20")


    But there could be other special characters such as line breaks that also need percent encoded, so we use another SUBSTITUTE function:

    ="base url" + "?" + SUBSTITUTE(SUBSTITUTE("First Field=" + [First Field]@row + "Second Field=" + [Second Field]@row, " ", "%20"), CHAR(10), "%0D%0A")

    This does the same thing with line breaks that we did with spaces.

    CHAR(10) is the easiest way to indicate "line break" in a formula. There are quite a few different numbers you can put in both the CHAR and UNICHAR functions to get all kinds of different symbols, special characters, etc., but that would be for another post.


    You can use this nested SUBSTITUTE method to take care of many different special characters including parenthesis and hyphens if you are wanting to output a phone number, the "@" in the event you are pre-filling an email address, so on and so forth. There are a number of articles here in the Community that dive into URLs as well as the below linked help article:


  • I know there is a query string solution I've ready in the majority of these community posts where

    I have a nested Substitute formula for each of the fields I reference in the query string. Each time some new character comes up I need to cover I add another Substitute. For Example: for our company email addresses, I had to add the @ and _ CHAR().

    When you responded to my line break issue, I added the CHAR(10), "%0D%0A") to the nested formula

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Cross Cutting Portfolios]@row, "'", "%27"), "-", "%2D"), ",", "%2C"), " ", "%20"), "?", "%3F"), "/", "%2F"), "&", "%26"), "_", "%5F"), "@", "%40"), CHAR(10), "%0D%0A")

    2nd column and to the right in the screen shot below, are what I refer to my URL columns that contain the above Substitute formula for fields that will be pre populated. The "Approval Form" column is where the URL query string is where I focus on how the fields are labeled and inputting the CHAR() code + referencing the various URL columns.

    ="URL?Submitter%20Email=" + SubmitterURL@row + "&Owner%20Email=" + OwnerURL@row + "&Event%20Type=" + TypeURL@row + "&Portfolio=" + PortfolioURL@row + "&Name%20of%20the%20Effort=" + NameURL@row + "&Initiative%20Category=" + ICategoryURL@row +...

    So I have to add the nested SUBSTITUTE string into my query string? I thought by having separate URL columns with nested SUBSTITUTE formulas would convert the fields for those possible special characters and situations. Now I have to add that long nested SUBSTITUTE string into the query string as well?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm not sure I follow which screenshots you are referring to.


    My suggestion with the SUBSTITUTE method is in place of all of the extra columns suggested by jmyzk. The way I understand your posts and screenshots thus far, I feel like maybe the solution has become overcomplicated. You shouldn't need extra helper columns to convert anything before joining everything together into the URL. Using the nested SUBSTITUTE method, you should be able to just reference the cells that contain the original data without having to use the helper columns to percent encode first.


    Note the below screenshot. You do not need a helper column to swap out spaces in Fields A, B, or C. We can just do that in one formula. Of course you would expand this with more nested SUBSTITUTEs for all of your special characters, but this at least shows how you do not need any helper columns at all.


  • @Paul Newcome I get what you're saying now. You're saying instead of my process of breaking out my fields into individual column with the nested SUBSTITUTE formula for each field, I should build the nested SUBSTITUTE formula into the query string. I tried to mimic your screen shot with my fields and when I open the form, the fields are empty. Can you see what the issue might be from the screen shot of my formula?