Replacing all special characters in form query string parameters with a formula

pris

I was attempting to use the formula below to replace special characters in form query strings parameters but my form is not populating with any information.

I attended Engage 2024 and was very excited to apply to our form but something is preventing it from running correctly.

="https://app.smartsheet.com/b/form/9858be5e35cc4de288dec6cf32c565bc?" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("Row ID=" + [Row ID]@row + "&Training Name=" + [Training Name]@row + "&Trainer Name=" + [Trainer(s)]@row + "&Partial Credit Permitted=" + [Partial Credit ]@row + "&Total Hours Attended=" + [CEU Total]@row, "%", "%25"), " ", "%20"), "#", "%23"), "$", "%24"), "&", "%26"), "||", "&"), "=", "%3D"), "|^", "="), ":", "%3A"), "<", "%3C"), ">", "%3E"), "[", "%5B"), "]", "%5D"), "{", "%7B"), "}", "%7D"), "+", "%2B"), "@", "%40"), "/", "%2F"), ";", "%3B"), "?", "%3F"), "|", "%7C"), "~", "%7E"), "'", "%27"), ",", "%2C"), CHAR(10), "")

Paul Newcome

The problem is with you swapping out these two characters… "%", "=" and "&". The URL needs both of them in there.

http://app.smartsheet.com………………….some%20text

In the above "%20" represents a space between "some" and "text", but since you are using the SUBSTITUTE function to swap out "%", you end up with

some%2520text

instead.

Same concept with the & and =.

Your URL needs

……….&Training%20Name=…………

but it is actually

……….%26Training%2520Name%3D…………….

To correct this, you would need to remove those characters from the general substitution and specifically call them out in only the fields where they may be found.

………………..("Row ID=" + [Row ID]@row + "&Training Name=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Training Name]@row, "%", "%25"), "&", "%26"), "=", "%3D") + "&Trainer Name=" + …………………………….

pris

thanks Paul! Is it possible to add a redirect to the query string once the form is submitted? So use the query string to populate a form with the fields populated and once they submit, it redirects them to another link? Using the query string, not the redirect function in Smartsheet?

Paul Newcome

Happy to help. 👍️

Not that I am aware of. The only redirect I have been able to find so far has been through the form settings.

pris

Hi Paul, I was able to make the changes suggested and it is functioning correctly, however, this error message is popping up for me now. Do you know what the issue is?

Paul Newcome

The error is basically saying you need to cut back on your formulas. There are a number of articles here in the community that dive into that particular error.