Auto-Number Reset

Loucas Tsiartas

I'm using a custom auto-number field in combination with a Date and a Time fields to create a sort of priority list with which requests are processed. I'm facing a problem with requests that are already in the queue and users go in and and change the Date or Time (this is a valid action) but since the auto-number doesn't change those changed requests are processed using the same order they came in. I need these changed requests to get a new auto-number and go to the bottom of the list for processing. One caveat is that the auto-number is assigned in my intake sheet and requests are moved automatically to various other sheets depending on the date. I created a workflow to move changed requests back to the intake sheet and tried the Clear Field action before the move but, it doesn't allow my to clear an auto-number field. Any workaround? Any help would be greatly appreciated. Thank you.

Genevieve P.

Hi @Loucas Tsiartas

Instead of using an Auto-Number field, what about using a Created Date system column? Then you can Sort the sheet by the "Date" column for when the request is due, and secondly by the "Created Date" column This would organize your sheet first by when tasks are due, and second to when the submission came in.

When you update the due "Date", then re-sort the sheet, the Created Date will stay the same but the row will move down to where the new due date fits in. You can still use the Auto-Number as unique identifiers to keep track of specific tasks, but (as you've found) this number is locked in when the row is created.

If I'm misunderstanding your process, it would be helpful to see a screen capture of your intake sheet (but please block out any sensitive data).

Cheers,

Genevieve

Loucas Tsiartas

Thank you for your response @Genevieve P. However, switching to the Created Date field does not solve my problem because when the date for a request is changed by a user the Created Date will not change and the request will end up in the original order in the queue instead of going to the bottom. I tried using the Modified Date instead but that too is problematic because when the date for a request is changed by a user, my sheet re-calculates all the requests for the specific date in order to calculate how many slots are available for each day and that causes the Modified Date for all requests in the same date to update :(

Genevieve P.

Hi @Loucas Tsiartas

How about using a "Record a Date" workflow in a new Date column? You could set the trigger to be when a Row is Added or Changed, then have it set to when the due Date column has a date in it.

This way it will date-stamp the row with the date of submission/creation of the row, but it will also update that date if the "Date" in the other column changes.

See: Set the Current Date with Record a Date Action

Loucas Tsiartas

Thank you @Genevieve P. This works partially in that requests with different dates will get ordered correctly when their date changes. However, because this new Record Date field is a short date (without time), requests in the same date will not be re-prioritized. Is there any way to include time in addition to the date?

Andrée Starå

Hi @Loucas Tsiartas

I hope you're well and safe!

To add to Genevieve's excellent advice/answer.

This might help.

Please have a look at my post below with a method I developed. You could add multiple helper columns to get the time for each change.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Loucas Tsiartas

Thank you @Andrée Starå. I don't see the solution in the post you referenced. Do you want me to email to get the solution?

Andrée Starå

@Loucas Tsiartas

Happy to help!

Yes, please send me an email at andree@workbold.com, and I'll share it with you.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!