Combining Form URL query strings in Alert Someone workflows?

TomW

Hi,

I've got a sheet (Deals) which tracks the status of various deals and when the Status of the deals is marked as "Complete" it triggers an automation to send the owner an alert. The alert tells the owner they need to add a record to a different tracker (Live Deals) and includes a link to a Form to capture data for this. I want to be able to pre-populate the "Deal Name" fields in this Form.

I've followed the guidance on URL Queries for Forms: https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

And I'm aware that in the Alert Someone workflow I can customize the message with placeholders in {{ }}, for example {{Deal Name}}

I've tried combining these two concepts by including the following url in my alert message:

https://app.smartsheet.com/b/form/xxxxxxxxxxxxx?Deal%20Name={{Deal Name}}

When I test this automation, the url I receive in the alert works OK if the value in the Deal Name column is one word, e.g. "Test", then this is populated as the default value in the form. Nice.

However, if the Deal Name is two or more words, e.g. "Test Deal", then only the first word is captured. I assume this is cos the Smartsheet automation doesn't automatically add %20 to replace spaces when its reading the {{Deal Name}} placeholder.

Is there a workaround for this?

Is there another approach for pre-populating a default value in a form that I've overlooked?

Jeff Reisman

@TomW

What about including the URL in a sheet column instead of in the message body? Then include just that column in your alert?

I have a system that constructs custom event registration form URLs for each event set up by a user, and then emails that URL back to the user for distribution to potential attendees. I have a column containing the base URL for the registration form, and use a series of helper columns that gather all my pre-filled form values from that row, and run SUBSTITUTES on them to replace the spaces and other characters with html.

I start with a ClassInfoString field:

=IF(ISTEXT([Sponsoring Distributor]@row), ("Class%20Name=" + [Class Name]@row + "&Class%20Location=" + Location@row + "&Sponsoring%20Distributor=" + SUBSTITUTE([Sponsoring Distributor]@row, "&", "%26") + "&Start%20Date=" + StartDate@row + "&Start%20Time=" + [Start Time]@row + "&End%20Time=" + [End Time]@row), ("Class%20Name=" + [Class Name]@row + "&Class%20Location=" + Location@row + "&Sponsoring%20Distributor=N/A&Start%20Date=" + StartDate@row + "&Start%20Time=" + [Start Time]@row + "&End%20Time=" + [End Time]@row))

This is basically two options, one if there's a sponsor, another if there is not. If there is a sponsor, I include a SUBSTITUTE for any ampersands right off the bat. I lay out the field names with %20 already in there, along with ampersands before each new field name, and add in my class info field values, resulting in:

Class%20Name=Applications and ZCS 1 and 2&Class%20Location=123 Main Street, Anytown, USA&Sponsoring%20Distributor=Shlomo's Deli %26 HVAC Supply&Start%20Date=04/27/2023&Start%20Time=9:15 AM&End%20Time=5:15 PM

You can see there's still an issue due to spaces, slashes, apostrophes, etc.

So I go down the line, from column to column, subbing out the characters:

and so on.

Then I combine by base form URL with my EventID and the value from SubApostrophes, and there's the custom URL.

TomW

Thanks @Jeff Reisman, this looks like a genius way of doing it and works really well.

Luckily I only have one or two columns to auto-populate in the form and don't have many special characters to substitute.