Next Available Number

👶 Basic Configuration

The simplest method is to just use sequential numbers. Here, we calculate the next available number (1 more than the highest number that has already been used).

Highest number in use:

1321

⁠

Formula: [Opportunities].[Docket Num].Max()

Next available number:

1322

⁠

Formula: [Highest Number in Use] + 1

🤓 Advanced Configurations

Preventing Duplicates

One issue with custom fields in sync tables is that you have to refresh the table in order to see the changes (whether manually, or on the automatic refresh frequency you’ve set). Regular fields update right away without a refresh, but not custom fields.

This can throw off the “Highest number in use” calculation, because numbers that were recently assigned don’t yet show up in the table, and so don’t bump up the highest number total.

To fix this:

Add an additional column to the Opportunities Sync Table called “Docket Num Cached”. This will be a regular Coda column that we can write to immediately.

When updating the Opportunity Number on a new Opportunity, put the new number into both columns

Update the Highest Number in Use formula to find the max of the max of these two columns:

Max(

[Opportunities].[Docket Num].Max(),

[Opportunities].[Docket Num Cached].Max()

)

Specify Base Number

Perhaps you want to have the numbers have a new starting number each year (so that 2021 projects are 21XXX, and 2022 projects are 22XXX.

Base number:

24000

⁠

Formula: Today().Year().Slice(3,4)*1000

// Take this year (2022), use just the last 2 digits of it (22), multiply by 1000 (22000)

Next available number:

24001

⁠

Formula: Max( [Highest Number in Use] + 1, [Base Number] + 1 )

// Go with whatever’s higher: the highest number in use + 1, or this year’s base number + 1. When we roll into a new year, the base number is going to be higher than anything we did last year, so it’ll use that.

Prefixing

Maybe you want to start any project numbers with “P”, e.g. “P1320”, to easily differentiate from other numbering systems in your company (e.g. invoice numbers, customer numbers, etc). In that case we need to strip off the “P”, find the highest number, and add the “P” back on.

Next available number with prefix removed:

1322

⁠

Formula:

Opportunities.[Opportunity Number].FormulaMap(

currentValue.RegexReplace("[^\d]", "")

.ToNumber()

).Max() + 1

// Go through each existing opportunity number (FormulaMap), strip out anything that’s not a digit (by using the regular expression [^\d] to replace any non-digit characters with blank “”), and tell Coda to think of the result as a number rather than a string of text (ToNumber()). Find the largest one (Max()), and add 1.

Next available number prefixed:

P1322

⁠

Formula: Concatenate(”P”, [Next Available Number with Prefix Removed])

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.