URL Query String for a Multi Select Drop Down Field

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

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.

https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

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

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

The formula works in both the checkbox and dropdown.

I think I see the issue. You are using a SUBSTITUTE to swap out the "&". We don't want to swap out the "&" at the start of each field name. Are there any fields that are going to contain that character within the cell data?

Great!! I wish I had noticed it sooner. I only caught it because I was going one by one to make sure all of your percent encodings were the correct swaps. I even noticed that it was swapped with the correct percent encoding and only had the light bulb moment a few minutes later. Haha.

It would be ideal if you could avoid the use of "&", but if you know which fields you will be using them in, you can use separate SUBSTITUTE functions inside of the string to cover those specific fields. An example would be below if I anticipated that character's use in [3rd Field]@row:

="form_url?" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("Submitter Email=" + [Submitter Email]@row + "&Owner Email=" + [Owner Email]@row + "&3rd Field=" + SUBSTITUTE([3rd Field]@row, "&", "%26") + "&4th Field=" + [4th Field]@row, " ", "%20"), "'", "%27"), ",", "%2C"), "-", "%2D"), ".", "%2E"), "/", "%2F"), "?", "%3F"), "@", "%40"), "[", "%5B"), "]", "%5D"), "_", "%5F")

@whitney22189 Happy to help. 👍️

@Trang Turtletraxx Does that help with cutting out some of the helper columns in your original post?

All comments

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

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"

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.

https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

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

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

The formula works in both the checkbox and dropdown.

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

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?

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

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 "( ) ?/."

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

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?

https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

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?

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.