URL Query String for a Multi Select Drop Down Field

Trang Turtletraxx

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?

jmyzk_cloudsmart_jp

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.

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

Prefilled form for the 3rd row.

jmyzk_cloudsmart_jp

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.

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

Updated demo sheet with new formula

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

The formula works in both the checkbox and dropdown.

Paul Newcome

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?

Paul Newcome

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

jmyzk_cloudsmart_jp

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.

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

Prefilled form for the 3rd row.

Trang Turtletraxx

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

Trang Turtletraxx

@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

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.

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

Updated demo sheet with new formula

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

The formula works in both the checkbox and dropdown.

Trang Turtletraxx

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

jmyzk_cloudsmart_jp

Happy to help!😁

Trang Turtletraxx

@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

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

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

Trang Turtletraxx

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?

Trang Turtletraxx

@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

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:

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

Trang Turtletraxx

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

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.

Trang Turtletraxx

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