Utilizing query strings, formulas, and webforms to have a weekly training tracker

L_123

Objective: Have a dynamic form autosent and already filled out (mostly) on a weekly basis for a large rolling number of teamembers, while all being managed by the lowest tech savvy group in my organization.

In order to complete this I have 3 sheets.

1. training files database. The date field is filled out as far as 2100 and locked to prevent any changes. All the employee has to do is attach the training, and name it. I even use a helper column with a catcher to highlight any cells with special characters in red.
2. Personnel tracker sheet. This sheet is where all personnel are managed, and the workflows are built.
3. historical submission sheet. Basic sheet with no automation. Where the users submit their completion.

So the personnel tracker sheet is where the issue is and where all the complexity is. The personnel sheet has the following formula on it:

="https://app.smartsheet.com/b/form/rest of the url?Training=" + SUBSTITUTE([Current Week Training]@row, " ", "%20") + "&Department=" + Department@row + "&Name=" + SUBSTITUTE(Name@row, " ", "%20") + "&DueDate=" + DueDate@row

Then in the update request, I use {{Form1}} to pull out the separate user forms along with the publish access to the training files database. My issue is that {{Form1}} about 50% of the time doesn't pull the full url, and just points the user to the blank form. I don't let the user see any options other than training and datecomplete, all the other fields are empty.

Anyone know what the issue could be? clicking on the links in the page always works, it is just when I use curly brackets to post the values from the cells into the email alert that they get cut off, and even then only half the time.

Paul Newcome

Have you noticed an patterns with the URL's that get cut off?

L_123

Not yet. All unique trainings and people seem to have some that work.

Paul Newcome

Could it be a certain number of characters or a specific character that is only present some of the time? Do you have a way of compiling a list of all of the ones that have failed vs all of the ones that have not?

L_123

I don't think it could be a specific number of characters, though that did cross my mind as they do get fairly long. But the failures are spread out throughout many different lengths of name.

I should have exported it, but I corrected it so that users could see their submissions are being tracked. I can look up my actions on the sheet, but it would be painful to pull it apart in an easy to analyse manner.

Paul Newcome

You specify "Name". Is it the name field that gets cut off or a portion of the name field? I haven't tested a double space in a URL before, so is it possible that there could be "john<space><space>doe" in a field and the resulting SUBSTITUTE function outputs "john%20%20doe" which in turn causes the issue?

I do remember when I was messing around with the dynamic URL's for forms I ran into a little trouble with spaces and special characters to the point where I got the most consistent results when I spread each piece out in their own columns.

"&Name=" + [First Name]@row + "%20" + [Last Name]@row

instead of

"&Name=" + SUBSTITUTE([Full Name]@row, " ", "%20")

Same thing with email addresses. I had a field for the prefix and a separate field for the domain and used concatenation to manually join them together instead of using a SUBSTITUTE function to replace the @ with the appropriate text string.

EDIT: Missed a set of quotes...

L_123

I found the issue. It is in the required login. There seems to be a glitch(?) where if you force a login it pushes you to the login page and forgets the query string when it directs you back to the form, pushing you to a blank form instead of one that is pre-filled out.

@Genevieve P Do you know of a specific reason for this to happen? Is it intended behavior?

Paul Newcome

But you also said...

"...it is just when I use curly brackets to post the values from the cells into the email alert that they get cut off..."

Is the forced sign-in redirect part of the same thing? Based on the above, it sounds like the full URL isn't being pulled by the placeholder, or was that just another way of saying that the form isn't populating?

L_123

Yes, it is all the same process. The url is generated on one sheet using concatenation. Then I use the curly brackets to pull the url from the applicable row into an update request where the user can select it. This all works fine. The url is pushing the user to the correct location, but I've required logins in the form settings to get a consistent name from the user rather than have them type it out, also to prevent submissions on behalf of others

After they login, the query strings are being "forgotten", and the user is being brought into a raw form. Now that they are logged in however, if they reopen it, the query string works. So the issue isn't in the update request, formulas, or sheet itself. It is in the login system for accessing the form.

Paul Newcome

And that would be why it works for some and not for others. Because some are already logged in and others are not. Got it. I'm kind of curious myself to see if this is expected or not.