How to include default values for new tasks in a Task List?
I am creating a task list for my team in Smartsheet, with a few filterable fields, assigned owners/doers, and date tracking. Given that "Task List" is the second template option when creating a new Smartsheet, I assumed it would be able to handle this use case quite well, but I have spent all day trying and failing to set up what seem like pretty basic functionalities for a shared task list.
I won't get into every issue but the prime offender is default values. My list has several mostly predictable fields like task owner, request date, and priority. To be efficient, any new record should autofill with some reasonable best guesses, and let the user change only those values that need to be changed.
Bafflingly, as far as I can tell this is not possible in Smartsheet. I have spent several hours reading community posts, discussions and feature requests related to this, tried many things, and am still not finding a suitable solution even with creative workarounds. Here's a quick summary of approaches I have tried:
"Created By" and "Creation Date" built-in fields
These are fine if you know for sure that EVERY field will be entered by the task owner, and that EVERY task will be entered on time, but those are not good assumptions. These could be useful for creating the defaults but they have to be changeable when needed, and the built-in fields are immutable. We'll come back to these later.
Automation that populates values on row creation
This sounded like a perfect solution, but in practice it doesn't work because automations are so slow. Triggers take as long as 10-15min to be activated, and for defaults to be useful they need to populate instantly when the row is created. I suppose I could try to teach everyone on the team exactly which columns need to be populated and which ones will fill themselves in with the appropriate defaults later (likely well after they have finished entering the task and moved on to something else) but that just doesn't sound like it will work well in practice.
Smartsheet Form for creating entries
Forms have defaults! Huzzah, surely that will work!
...but they don't. First of all, forms can only do static defaults, so filling in today's date or the name of the person filling in the form isn't possible. Also, fields with names don't auto-populate from the company contact list so filling in names becomes so much more arduous ("How do I spell that guy's last name again?") that forms are basically disqualified for this use even if the defaults otherwise worked perfectly.
Formulas update instantly, but there is no way (that I can find) to assign a column a default formula while allowing it to also be overwritable. If I make it a "formula column" then it is immutable and has the same problem as the built-in columns. If I allow the formula to be changed then it won't work for the next row that is created, because for a formula to propagate to a new row it must be used in the preceding two rows.
Formula + Automation
I tried to get very creative about combining the features of formulas and automations to get the behavior I want. Unfortunately for every clever workaround I came up with, Smartsheet had a hidden limitation that blocked me at every turn:
- I could use a couple of "formula rows" to enforce the default formula rules and have an automation move them to the end after every new entry. I'd have to have another automation to lock in the dates that rely on TODAY() in the formula later. But...formulas can't reference the current user.
- Oh! but I can use the locked "Created By" column, hidden, as a reference. Except...those built-in columns don't populate until the spreadsheet is saved so it's going to start out blank. Also that built-in column references an email rather than a person in the contact list, and there doesn't seem to be an efficient way to convert from one to the other with a formula.
This isn't a comprehensive list of the things I tried but I think it hits the major methods/obstacles. Has anyone else tried this or come up with any solutions I am missing? It seems like "default values" should be one of the first things anyone tries to set up with a task list or many other lists, so it's hard to believe Smartsheet can do so many advanced things like notifications and dashboards while lacking such basic ease-of-use capabilities.