URL Query String for a Multi Select Drop Down Field

Paul Newcome

It looks like you have an extra "PortfolioURL@row" tucked in where you are swapping the "-" with "%2D". Lets try removing that to see if it starts working.

Trang Turtletraxx

Now I'm getting the #UNPARSEABLE error

Paul Newcome

Hmm. It looks like you may have accidentally removed the comma when you removed the extra "PortfolioURL@row".

Once we get that fixed, I see another issue that is going to cause this one to fail. You should have an = after each field name.

....................................SUBSTITUTE("Submitter Email=" + [Submitter Email]@row.........................

Trang Turtletraxx

I added the missing comma and the ='s to each referenced field. Now, the first field in the form is pre populated PLUS the name of the next field (see screen shots below). This was very odd.

I thought about the actual Substitute syntax. The first variable after SUBSTITUTE( is supposed to be the column or group of cells I want to search for and the next is the old text, but we have the first variable as actual word text with a plus sign. Not sure how that all works as it relates to the actual syntax.

Paul Newcome

I forgot we need an "&" before each field name after the first. Sorry about that. so the first field name starts with a question mark, every field name after that starts with an "&", and all field names are followed by an equals.

="form_url?" + "first field=" + [First Field]@row + "&second field=" + [Second Field]@row.....................

The actual syntax for the SUBSTITUTE function is

The first field is actually a string. It is perfectly fine that the first variable is "words" + [Column Name]@row. We are creating a string for the SUBSTITUTE function to evaluate.

Trang Turtletraxx

Still doesn't work. I tried it the way you instructed with the first field "Submitter" after the SUBSTITUTE string WITHOUT the "&" and I get the same outcome as my previous response. I added the "&" to the first field "Submitter" and didn't get any outcomes in any of the fields. The screen shots below is the outcome from your instructions with the "&" except for the first field.

I really appreciate your time in this matter and you always lending your super brain in solutioning everyone's questions. I don't want to take up too much of your time fixing this, but will continue to accept any assistance with it. I just don't want to keep you from other things if there is a solution for my issue with the use of the helper columns. I'm sure there is a way to put the nested SUBSTITUTE into the query string and eliminate the helper columns, but it doesn't seem as if luck is on my side for it to work.

Paul Newcome

Ok. So we know that we have an issue with the "Owner Email" field. Double check that you have an exact match in the formula to the field name. Are there any hidden spaces anywhere? Smartsheet will only ever show a single space even if you put 15 spaces between words. Are you using a custom field name, or is that the actual column name being used?

Trang Turtletraxx

Submitter Email and Owner Email are the names of the columns and so are not customized on the form. They are both identified as an email validation field in the form.

Trang Turtletraxx

Also, I don't know if the Owner Email field is the only issue. Usually when a query sting hits an issue, none of the fields below where the issue occurs will populate. We just know Owner Email is the first issue the query string is running into.

Paul Newcome

Ok. So it should definitely be "&Owner Email=" with just a single space. You are also correct in that once it hits an issue it just stops working, but we won't know until we get through this piece. I have done this quite a few times in the past including with email fields (and email validation turned on), so right now I am just trying everything I can think of.

What if you pull up the form using the generated URL but with [Submitter Email] as blank? I wonder if there could possibly be something going on with that field that is throwing things off after.

I see that you don't have the periods percent encoded. Try another SUBSTITUTE function to swap the "." for "%2E".

Trang Turtletraxx

I added the SUBSTITUTE for "." with no change. I removed Submitter Email from the query and the "&" before Owner Email as it moves to the first field position, the issue moves down to the Owner Email field.

If I add the Submitter Email back to the query string and there the Submitter cell is blank (SubmitterURL), but there is an Owner Email, the form populates "&Owner Email" in the Submitter Email field.

I really think it has to do with how we have the nested SUBSTITUTE string set up because it doesn't read the way SUBSTITUTE functions SUBSTITUTE(search text, old text, new text,...)

SubmitterURL is a blank cell = no Submitter Email

Paul Newcome

I use this nested SUBSTITUTE method successfully every time, and there are quite a few documented cases of it being used successfully elsewhere throughout the community. There is something going on with the email fields. Are they set to allow multiple selections in a single cell or are they restricted to a single contact in the column properties?

Trang Turtletraxx

They are text fields with email validation on the actual form.

Paul Newcome

I threw together a quick test sheet using the nested SUBSTITUTE method including the text fields with email validation and got it to populate successfully. I know this should be working for you. We just need to figure out why it is not.

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?