Using URL queries to pre—fill forms seems to be catching on a bit more lately. There are a lot of resources out there, but they can be kind of spread out, so I figured I would start a thread that has some of the lessons learned, tips, and tricks that I have discovered along the way.
Tip #1
The first one that I find makes things more efficient is that for most of the special characters that need to be encoded, you can wrap the whole thing in a SUBSTITUTE function instead of using separate SUBSTITUTE functions for each field for the same character(s).
One of the most common is a space which needs to be swapped out with “%20”. Instead of swapping this out for all of the fields I want pre-populated individually, I can manage all of them at once using something along the lines of
=”form_url?” + SUBSTITUTE(“Field Name 1=” + [Field 1]@row + “&Field Name 2=” + [Field Name 2]@row , “ “, “%20)
You can do this for multiple special characters by nesting SUBSTITUTE functions like so:
=”form_url?” + SUBSTITUTE(SUBSTITUTE(“Field Name 1=” + [Field 1]@row + “&Field Name 2=” + [Field Name 2]@row , “ “, “%20), “@”, “%40”)
Tip #2
There are a few special characters though that you don’t want to swap out of the entire string. We want to keep =, %, and & in place because they are part of how a URL query works. To accommodate these special characters, we do need to wrap each specific field in a SUBSTITUTE function, but we only need to do this for fields that will potentially have these.
Using the above example, if we want to remove “=” from [Field 1], would use an individualize SUBSTITUTE function that works like this:
=”form_url?” + SUBSTITUTE(SUBSTITUTE(“Field Name 1=” + SUBSTITUTE([Field 1]@row , “=”, “%3D”) + “&Field Name 2=” + [Field Name 2]@row , “ “, “%20), “@”, “%40”)
Tip #3
One of the other quirks I have found was with date. One of the unintended consequences that came from the recent form updates was that dates had to be loaded into fields a very specific way. I think I read somewhere that it was fixed, but just to be on the safe side, I use a bit of a formula within a formula to fill my date field with the date in the “yyyy-mm-dd” format. This has worked very consistently so far. That piece ends up looking like this:
"&Date Field=" + YEAR(Date@row ) + "-" + MONTH(Date@row ) + "-" + DAY(Date@row )
Below is a list of a good bit of the special characters and their percent encodings.
Character | % Encoding | | Character | % Encoding | | Character | % Encoding |
---|
space | %20 | | • | %95 | | Ê | %CA |
line break | %0D%0A | | – | %96 | | Ë | %CB |
% | %25 | | — | %97 | | Ì | %CC |
& | %26 | | ˜ | %98 | | Í | %CD |
= | %3D | | ™ | %99 | | Î | %CE |
! | %21 | | š | %9A | | Ï | %CF |
# | %23 | | › | %9B | | Ð | %D0 |
$ | %24 | | œ | %9C | | Ñ | %D1 |
' | %27 | | ž | %9E | | Ò | %D2 |
( | %28 | | Ÿ | %9F | | Ó | %D3 |
) | %29 | | ¡ | %A1 | | Ô | %D4 |
* | %2A | | ¢ | %A2 | | Õ | %D5 |
+ | %2B | | £ | %A3 | | Ö | %D6 |
, | %2C | | ¤ | %A4 | | × | %D7 |
- | %2D | | ¥ | %A5 | | Ø | %D8 |
. | %2E | | ¦ | %A6 | | Ù | %D9 |
/ | %2F | | § | %A7 | | Ú | %DA |
: | %3A | | ¨ | %A8 | | Û | %DB |
; | %3B | | © | %A9 | | Ü | %DC |
| %3C | | ª | %AA | | Ý | %DD |
| %3E | | « | %AB | | Þ | %DE |
? | %3F | | ¬ | %AC | | ß | %DF |
@ | %40 | | ® | %AE | | à | %E0 |
[ | %5B | | ¯ | %AF | | á | %E1 |
\ | %5C | | ° | %B0 | | â | %E2 |
] | %5D | | ± | %B1 | | ã | %E3 |
^ | %5E | | ² | %B2 | | ä | %E4 |
_ | %5F | | ³ | %B3 | | å | %E5 |
` | %60 | | ´ | %B4 | | æ | %E6 |
{ | %7B | | µ | %B5 | | ç | %E7 |
| | %7C | | ¶ | %B6 | | è | %E8 |
} | %7D | | · | %B7 | | é | %E9 |
~ | %7E | | ¸ | %B8 | | ê | %EA |
€ | %80 | | ¹ | %B9 | | ë | %EB |
‚ | %82 | | º | %BA | | ì | %EC |
ƒ | %83 | | » | %BB | | í | %ED |
„ | %84 | | ¼ | %BC | | î | %EE |
… | %85 | | ½ | %BD | | ï | %EF |
† | %86 | | ¾ | %BE | | ð | %F0 |
‡ | %87 | | ¿ | %BF | | ñ | %F1 |
ˆ | %88 | | À | %C0 | | ò | %F2 |
‰ | %89 | | Á | %C1 | | ó | %F3 |
Š | %8A | | Â | %C2 | | ô | %F4 |
‹ | %8B | | Ã | %C3 | | õ | %F5 |
Œ | %8C | | Ä | %C4 | | ö | %F6 |
Ž | %8E | | Å | %C5 | | ÷ | %F7 |
‘ | %91 | | Æ | %C6 | | ø | %F8 |
’ | %92 | | Ç | %C7 | | ù | %F9 |
“ | %93 | | È | %C8 | | ú | %FA |
” | %94 | | É | %C9 | | û | %FB |
| | | | | | ü | %FC |
| | | | | | ý | %FD |
| | | | | | þ | %FE |
| | | | | | ÿ | %FF |
https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values