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

L_123
L_123 ✭✭✭✭✭✭
edited 08/24/20 in Formulas and Functions

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.

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!