Prefill Form with a Date Column

Nate Roylance

Hey Smartsheets Community!

I can't find any information on this so I'm hoping y'all can help! We are making a panel application form and we're attempting to make a form that will fill with pre-arranged dates but only allow for a single selection of said dates. When someone selects a date in the smartsheets form, we would like it to remove that date option from the next person who fills out the form.

Currently the column is manually filled with the approved dates and the column has the property of "Dropdown (Single Select)". Dependencies are turned off as we don't have the need for a time window.

If a user selects one of the options, we want to remove that option from be selected again.

Example:

User1 opens the form to apply and is presented with the dates "8/10/20", "8/17/20", "3/17/21", and "8/17/22". User1 selects date "8/10/20" and finishes the rest of the form. The form submits and we can make an actionable item off of the line in smartsheets

User2 opens the same form after User1 has submitted their information. User2 is presented with all possible dates minus the date that User1 has selected - ie: "8/17/20", "3/17/21", and "8/17/22".

Aside from manually updating the column each time, is there a way to automate this process inside of Smartsheets?

Bonus Q: Can we make a drop down selection when the column property is set to "date"?

Sean Morgan

Hello @Nate Roylance ,

As of now, there isn't currently a way to have dynamic dates, in which they don't appear once already chosen from a list. I love this idea and myself and others can definitely understand the use case scenario for this. If possible, please can you raise an Enhancement Request using the quick links on the right hand side of this post.

I feel there are a few solutions that could work in this scenario. The main one that springs to mind is to have a Flag Column that triggers when more than 1 of the same data entry (a duplicate) is entered into the Sheet. From here, you could then create an Automated Update Request so that when a Flag is enabled, it will send the Update Request to the email address within a Contact Column.

Please see my below example:

The formula I used to identify duplicates was: =IF(COUNTIFS([Date Requested]:[Date Requested], [Date Requested]@row) > 1, 1). This looks at the Date on the same row as the Flag, and then checks the entire column to identify if the Date appears more than once.

From here, I then created the following automation:

You can also customize the message of the Update Request, and have the user either change their Date, or re-submit the Form.

Here are the resources I used to create this solution:

• IF: https://help.smartsheet.com/function/if
• COUNTIFS: https://help.smartsheet.com/function/countifs
• @row: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
• Automatic Update Requests: https://help.smartsheet.com/articles/2479266
• Symbol Columns: https://help.smartsheet.com/articles/2480316

With regards to having a Drop Down of Dates, again unfortunately this isn't currently available. The best solution to this would be to list the Dates for a set period E.G Month in a text Drop down, and then manually enter these into an adjacent Date Column.

Please let me know if you have any questions!

Regards

Sean