URL Query String for a Multi Select Drop Down Field

Trang Turtletraxx

Oh you are GOOD! :)

That worked perfectly! Thank you SO MUCH! You're are the BEST!!!

Trang Turtletraxx

I will have to make sure not to use "&" and spell out the word AND instead, right?

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

Trang Turtletraxx

Sounds good. I'll add this to my "Formula Sheet" so I remember this exception as I go through my fields. Thanks again for all the time spent on this issue.

Paul Newcome

Happy to help. 👍️

whitney22189

@Paul Newcome Hi Paul, I'm still on unclear about how to make the multi-select options separate in the pre-populated form. I don't see it addressed here. %0A%0D will make them populate, but creates them as one line, therefore a new option. I need them to appear as separate in the chance that they both selected.

What am I missing?

Paul Newcome

@whitney22189 When you clicked on the form link to open the form in the second screenshot, did you maybe accidentally click on the wrong row? I see the first screenshot has that "English Spanish" string in it.

whitney22189

Nope, it's the correct link. The "English Spanish" string was a submission to confirm that it will submit exactly how it appears in the form when it appeared like that in the form. Any idea how to make sure it's broken up or do I have to have individual helper columns?

Trang Turtletraxx

@whitney22189 you don't need individual helper columns, but you do need to make sure your formula has the field names as they are seen ON THE FORM, not the column name and the right nested SUBTITUTE().

whitney22189

@Trang Turtletraxx then can you help me out on how my formula is wrong? The column name and field name are the exact same so there's no question there, but what am I doing wrong about the nested substitute?

Paul Newcome

@whitney22189 Thank you for confirming.

I remember there was something a little different that we had to do with multi-selects, so please bear with me as I try to remember and work my way through this…

When pre-populating a multi-select field, you have to repeat the field name multiple times. So if your multi-select column has 3 options selected, your string for that would actually be

………….."&Field Name=" + "first_option" + "&Field Name=" + "second_option" + "&Field Name=" + "third_option"………….

The challenge here is that we want this to be dynamic, so we can't just hardcode 3 options in. So let's see if I can explain this in a way that it makes sense. Basically we want to take what is in the field (so [Field Name]@row) and for every line break (CHAR(10)) we need to replace it with "&Field Name=".

So if your cell has "A", "B", and "C" selected, we can use a SUBSTITUTE function around just the field like so:

SUBSTITUE([Field Name]@row, CHAR(10), "&Field Name=")

Now we go from

A

B

C

to

A&Field Name=B&Field Name=C

Since we used the SUBSTITUTE function, that is now dynamic to allow for a single selection or any number of selections to get your string.

Now we can drop this into our formula generating string like so:

………….."&Field Name=" + SUBSTITUE([Field Name]@row, CHAR(10), "&Field Name=")………….

NOTE: This SUBSTITUTE function is field specific. It is not something that goes wrapped around the main string. It gets nested into the string for that specific field.

Here is an example formula with the multi-select portion in bold:

="https://app.smartsheet.com/b/form/……………….?" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("First Field=" + [First Field]@row + "&Multi-Select=" + SUBSTITUTE([Multi-Select]@row, CHAR(10), "&Multi-Select=") + "&Third Field=" + [Third Field]@row, " ", "%20"), "-", "%2D"), "@", "%40"), "[", "%5B"), "]", "%5D")

whitney22189

Brilliant. THANK YOU! I knew you had an answer, I just needed it spelled out a little bit for me. Appreciate you taking the time to work through it again!

Paul Newcome

@whitney22189 Happy to help. 👍️

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