Populate AutoNumber Before Update

Detrie Zacharias

I use a form to populate an intake sheet.

My intake sheet has an auto-number column that gets populated when a new record is created.

I am looking for a way to present the Auto-Number on the form at the time the data is being entered so that it can be included in the naming of a file being attached to the record.

I thought about a formula field that =autonumber + 1 but, It could mess things up if another user creates a record before another user completes and submits their form.

Open to ideas

Thanks

JCluff

Unfortunately there is no way to reference any existing data in the sheet via Forms. Any formula set as a default value will simply just input that formula in the cell when the form is submitted.

Alternatively, another option would be to create a dashboard and imbed the form with the web content widget and include a metric widget referencing a calculated field that finds the next auto-number from the sheet. This isn't a pretty fix, but it would solve your need.

Andrée Starå

@Detrie Zacharias

Excellent!

Happy to help!

Try something like this.

=VALUE(RIGHT([Work ID]@row, 5)) + 1 

Did it work?

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

JCluff

Unfortunately there is no way to reference any existing data in the sheet via Forms. Any formula set as a default value will simply just input that formula in the cell when the form is submitted.

Alternatively, another option would be to create a dashboard and imbed the form with the web content widget and include a metric widget referencing a calculated field that finds the next auto-number from the sheet. This isn't a pretty fix, but it would solve your need.

Andrée Starå

Hi @Detrie Zacharias

I hope you're well and safe!

To add to JCluff's excellent advice/answer.

You could add a process to check out the next available number.

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Detrie Zacharias

This is a good workaround

My auto-number is formatted... DMR-01234

I've added a helper column to house the next value and used this formula but it only adds a "1" to the end of the auto-number rather than adding 1 to the value

=[Work ID]@row + 1

Also tried ..

=RIGHT([Work ID]@row, 5) + 1

Andrée Starå

@Detrie Zacharias

Excellent!

Happy to help!

Try something like this.

=VALUE(RIGHT([Work ID]@row, 5)) + 1 

Did it work?

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

Detrie Zacharias

Hi @Andrée Starå

VALUE did it.. Thanks for the help all

Andrée Starå

@Detrie Zacharias

Excellent!

You're more than welcome!