How to include default values for new tasks in a Task List?

BESSjd

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

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.

Philip Robbins

Hi @BESSjd,

A couple of ideas for you on this one:

• If you want to have selectable contacts in a dropdown via a form, pre-populate 'Add contacts' box when setting up the column. These will then be available for people submitting the form to select from.
• For any formula-based defaults, if you add a helper column as an override, you can adjust your formula to pick up the override if it is populated, or go with the default if not. Something along the lines of: =IF(Override@row<>"",Override@row,"Default value").

BESSjd

@Philip Robbins,

Thanks for the input! Your first point was something I looked into - it's a bit high maintenance to manually update contact lists on multiple fields as the company grows, but that is a potentially functional workaround. It still leaves the problem that Forms can't use dynamic defaults like today's date or the current user's name, though.

The idea of an override column is interesting, I hadn't thought of that. So the "official" column would be an immutable formula, and there would be a second column for optional input to change that column? Assuming I am understanding that correctly it does sound like it would work, but would be a little clunky and unintuitive for the user to have to enter information in a different column. I don't mind doing overly complicated things on the backend, but once you start requiring all the users of a sheet to understand and correctly apply a non-obvious approach it can get messy. Unless I'm missing something, and there's a way to make this transparent to the user?

Thanks,

JD

Darren Mullen

@Philip Robbins Good suggestions. I have used that sort of logic MANY times!

@BESSjd As far as keeping contact lists up to date as the company goes there are some solutions for that.

1. Smartsheet Data Shuttle or Bridge, if you have access to those apps
2. Otherwise, the dropdown list manager add-on will do the trick, https://www.smartsheetguru.com/smartsheet-dropdown-list-from-another-sheet/