Yearly Resetting Sequential ID Generator - Multi-Prefix Support

In this article, one of our library developers, Tamás, walks through the development decisions behind the Yearly Resetting Sequential ID Generator - Multi-Prefix Support building block app available in the library.

Why I built it this way

I designed this logic block to solve a very practical problem: generating IDs in a way that is reliable, easy to reuse, and fast to deploy across many apps.
My goal was for the ID itself to contain everything needed to calculate the next one.
Each part of the ID has a clear role:

• the prefix tells which sequence the ID belongs to
• the year separates the sequence over time
• the number shows the order within that year

Because of this structure, I didn’t need to introduce any reset logic. The latest existing ID already contains all the information required to generate the next one.
As long as the last ID is valid, the next ID can always be calculated in a predictable way.

I included the year directly in the ID because it naturally separates records over time. It also allows numbering to restart automatically each year without needing any scheduled reset or manual update.

The prefix acts as a boundary between different sequences. This makes it possible to run multiple independent numbering sequences within the same table. Changing the prefix simply means continuing a different sequence.

I added validation because the whole approach depends on a consistent ID structure. If an existing ID doesn’t follow the expected format, continuing the sequence could lead to confusing or inconsistent results. I’d rather stop early, make the issue visible, and avoid generating incorrect IDs.

I chose a connector-based lookup so the logic block can work with different tables without changing the core logic. Once the connector is configured, you only need to provide the tableId and the prefix, and the same logic block can be reused in multiple places.

For me, the goal wasn’t just to generate IDs, but to create a reusable pattern where the ID format itself defines how sequencing works. That’s what keeps the logic simple, predictable, and easy to reuse across apps.

What this logic block does

This logic block generates sequential IDs that reset yearly with the following structure:
prefix + year + running number

Example:
TEST20240001
TEST20240002

When the year changes, numbering automatically restarts from 1:
TEST20250001

Multiple prefixes can exist in the same table. Each prefix maintains its own independent sequence:
ORD20240001
ORD20240002
INV20240001
INV20240002

The logic block ensures:

• consistent ID format
• automatic yearly reset
• independent numbering per prefix
• early detection of malformed historical IDs
• early detection of numeric overflow

Why the connector is part of the design

Before generating the next ID, the logic block needs the latest existing ID for the given prefix.
The connector performs a filtered lookup:

• filter records where ID starts with the prefix
• sort descending by _sequenceNumber
• return the latest record

Because the prefix is part of the filter, each prefix has its own running counter even inside the same table.
This keeps sequences isolated and predictable.

How the build is structured

The actual implementation uses three triggers.
That structure is intentional.
I wanted the logic block to be easy to follow from top to bottom, while still keeping the main logic separate and reusable. So instead of spreading the logic across many editable triggers, I organized it into three stages:

1. USER CONFIG: INPUT STAGE
2. MAGIC validation + ID generation
3. USER CONFIG: OUTPUT STAGE

This gives a good balance between usability and control.

• The first trigger is where you define the inputs.
• The second trigger contains the internal logic that normally does not need to be edited for the logic block to work.
• The third trigger is where you decide what to do with the generated result.

This means you normally do not need to work inside the core logic trigger. They only need to configure the input side and the output side.
That was one of the key design decisions: make the logic block reusable by exposing only the parts that usually need to change.

Trigger 1 – USER CONFIG: INPUT STAGE

This is the first trigger you interact with.
Its purpose is to define the input values the logic block needs before any ID can be generated.
In practice, this trigger is used to define:

• The prefix: _configPrefix
• ID length: _configIdNumberFormat
• the table ID passed into the connector: tableId

What each value means

_configPrefix
This is the text placed at the beginning of every generated ID.
You use it to define which sequence you want to generate.

Examples:

• ORD
• INV
• TEST

If _configPrefix = ORD, the logic block only looks at existing IDs that start with ORD, and it continues only that sequence.
So this value does two things at the same time:

• it defines the visible prefix in the final ID
• it defines which historical IDs the connector should use as the source for the next number

That means different prefixes in the same table produce different sequences.
_configIdNumberFormat

This defines the length of the numeric part of the ID.
You set it as a zero-filled string.
Examples:
0000
000000
00000000

The number of zeros defines the width of the numeric section.

Examples:
0000 → 0001, 0002, 0003 ... 9999
000000 → 000001, 000002 ... 999999

This value does not set the actual number. It defines the format the number must fit into.
I use it for two purposes:

• to apply adding leading zeros to the generated number
• to validate whether the next number still fits the allowed length

tableId

This defines which table the connector should query.

In practice, this is what makes the logic block reusable across different tables. You can copy the same logic block and point it to another table by changing only this value in the configuration trigger.

The logic block does not search tables on its own. It passes tableId and _configPrefix to the connector, and the connector returns the latest matching ID from that table.

How to use them together

These three values define the behavior of the generator together:

• tableId tells the logic block where to look
• _configPrefix tells it which sequence to continue
• _configIdNumberFormat tells it how the numeric part should look

Practical setup guidance

When you configure the logic block, make sure:

• tableId points to the correct table
• _configPrefix matches the prefix you want to continue
• _configIdNumberFormat matches the number length you want to enforce

If you change the prefix, you are not continuing the old sequence anymore. You are starting or continuing a different prefix-specific sequence.
If you increase the number format length later, the logic block will expect that wider format when validating existing IDs in that table and prefix scope.
The connector retrieves the latest ID matching the prefix and stores it in _stateLastId.
If no ID exists yet, LINK() ensures the generator can still produce the first valid ID.

Why the connector is part of the design

The connector is not just a technical detail. It is part of the reuse strategy.
I use the connector to read the latest existing ID from the target table. The connector receives the table ID as input, applies the prefix filter, sorts the records by _sequenceNumber in descending order, and returns the latest matching record.

This is important for two reasons.

First, it allows the logic block to continue numbering from the real state of the table.

Second, it makes the logic block much easier to reuse. If you copy the button into another app, you do not need to rewrite the logic. You only need to point the connector to the correct table and provide the needed configuration.
This is the practical pattern behind the build:

• create the button once
• set the table input in the connector
• define the prefix and number format
• reuse it wherever needed

That was a deliberate choice to support faster app building.

What does the connector do in the logic block?

What happens here:

• the connector receives the table ID
• the connector receives the prefix
• the connector filters records by prefix
• the connector sorts records by _sequenceNumber in descending order
• the connector returns the latest matching ID

The connector also supports a very important use case: multiple entities in the same table.

If the same table contains records like:
ORD20240001
MAT20240011

then the logic block should not treat them as one shared sequence. Each prefix should continue only from its own latest value.

So the connector filters by prefix first. That means the next values become:
ORD → ORD20240002
MAT → MAT20240012

I chose this model because different entities can live in the same table, but that does not mean they should share the same running number.

One setup requirement is also worth stating clearly: the connector needs authentication, so a token has to be generated and added to the connector authentication settings. I treat this as setup work, not as part of the logic block’s logic itself, but it is still an important part of making the logic block usable.

Trigger 2 – MAGIC: validation + ID generation

This trigger is the core logic of the logic block.
It does three things in order:

1. checks whether the latest matching ID has the expected structure
2. checks whether the next number would still fit the configured numeric length
3. builds the next ID

I grouped these steps into one trigger because they belong to the same decision flow. Before creating a new ID, I want to answer three questions:

• is the previous ID structurally safe to use?
• would the next number still fit the format?
• if yes, what should the next ID be?

Format validation

Expression

REGEX_REPLACE(
LINK(Variable._stateLastId.id, Variable._configPrefix + DATETIMETOTEXT(App info.Current Date and Time,'YYYY') + Variable._configIdNumberFormat),
'^' + Variable._configPrefix + '[0-9]{4}' + '[0-9]{' + LEN(Variable._configIdNumberFormat) + '}$',
'ok'
)

What this expression does

This expression checks whether the latest ID matches the exact structure the logic block expects.
It works in this order:

1. LINK(...) takes Variable._stateLastId.id when the connector returned a value.
2. If no ID exists yet,** LINK(...)** uses a fallback value built from:
   • Variable._configPrefix
   • DATETIMETOTEXT(...,'YYYY')
   • Variable._configIdNumberFormat
3. That fallback is included so validation still has a valid structure to evaluate even when this is the first generated ID.
4. Variable._configPrefix defines the exact prefix the ID must start with.
5. DATETIMETOTEXT(...,'YYYY') is not used as a match value inside the regex itself, but it is part of the fallback structure so the fallback looks like a real ID shape.
6. LEN(Variable._configIdNumberFormat) defines how many digits the numeric portion must contain.
7. REGEX_REPLACE(...) then tests the full string against this pattern:
   • prefix
   • exactly 4 digits for the year
   • exactly N digits for the numeric part
8. If the full string matches, the whole value is replaced with ok.
9. If it does not match, the original value remains unchanged.
10. The trigger then checks whether the result equals ok. If it does not, execution stops.

So this expression is not extracting values for calculation. Its role is to verify that the ID has the expected structure before any numeric logic is applied.

Why I chose this

I chose to validate the complete ID structure before doing any numeric logic.
I built it this way because the rest of the trigger assumes fixed positions:

• prefix at the start
• year immediately after the prefix
• number immediately after the year

If the previous ID does not follow that contract, incrementing from it would be unreliable.

I also chose to include the LINK(...) fallback here so the first generated ID does not need separate validation logic. The same validation path works whether an older ID exists or not.
I intentionally stop the trigger instead of trying to repair malformed IDs. Automatic correction would hide data problems and could create a sequence that looks valid but is built on the wrong input.

Overflow protection

Expression

LEN(
IF(
MID(
LINK(Variable._stateLastId.id, Variable._configPrefix + Variable._configIdNumberFormat),
LEN(Variable._configPrefix),
4
) = DATETIMETOTEXT(App info.Current Date and Time,'YYYY'),
RIGHT(
Variable._configIdNumberFormat
+ TOTEXT(
TEXTTOINTEGER(
REGEX_REPLACE(
LINK(Variable._stateLastId.id, Variable._configPrefix + Variable._configIdNumberFormat),
'^' + Variable._configPrefix + '[0-9]{4}',
''
)
) + 1
),
LEN(Variable._configIdNumberFormat)
),
RIGHT(
Variable._configIdNumberFormat + '1', LEN(Variable._configIdNumberFormat))
)
)

What this expression does

This expression calculates what the next numeric portion would be and then measures its length.
It works in this order:

1. LINK(...) uses the last ID when one exists. If not, it falls back to Variable._configPrefix + Variable._configIdNumberFormat.
2. MID(..., LEN(Variable._configPrefix), 4) reads the 4 characters immediately after the prefix. This is treated as the year part of the previous ID.
3. DATETIMETOTEXT(...,'YYYY') provides the current year for comparison.
4. IF(...) decides whether the numeric part should continue or reset:
   • same year → increment
   • different year → start from 1
5. In the increment branch, REGEX_REPLACE(...) removes the prefix and the 4-digit year from the start of the previous ID so only the numeric portion remains.
6. TEXTTOINTEGER(...) + 1 converts that numeric text to a number and increments it.
7. TOTEXT(...) converts the new number back to text.
8. Variable._configIdNumberFormat + ... places the configured zero-format in front of the value.
9. RIGHT(..., LEN(Variable._configIdNumberFormat)) keeps only the required number of digits.
10. In the reset branch, RIGHT(Variable._configIdNumberFormat + '1', LEN(...)) creates the first allowed number for a new year.
11. LEN(...) measures the length of the resulting numeric portion.

The trigger compares this length to the allowed length from Variable._configIdNumberFormat. If the next numeric portion would be too long, execution stops.

Why I chose this

I chose to calculate the next number the same way the ID is actually generated, instead of using a separate formula to guess when the number would exceed the allowed length.

I built it this way because I wanted overflow checking to follow exactly the same rules as real ID generation. That reduces the chance of the validation logic and generation logic drifting apart later.

Stopping here is safer than continuing. If the next numeric value no longer fits the configured length, the resulting ID would break the structure you configured.

For example, if the numeric format is 0000, then 9999 is valid but 10000 is not. I want the logic block to catch that before record creation, not after.

ID generation logic

Expression

Variable._configPrefix
+ DATETIMETOTEXT(App info.Current Date and Time,'YYYY')
+ IF(
MID(
LINK(Variable._stateLastId.id, Variable._configPrefix + Variable._configIdNumberFormat),
LEN(Variable._configPrefix),
4
) = DATETIMETOTEXT(App info.Current Date and Time,'YYYY'),
RIGHT(
Variable._configIdNumberFormat
+ TOTEXT(
TEXTTOINTEGER(
REGEX_REPLACE(
LINK(Variable._stateLastId.id, Variable._configPrefix + Variable._configIdNumberFormat),
'^' + Variable._configPrefix + '[0-9]{4}',
''
)
) + 1
),
LEN(Variable._configIdNumberFormat)
),
RIGHT(
Variable._configIdNumberFormat + '1', LEN(Variable._configIdNumberFormat))
)

What this expression does

This is the expression that builds the final ID.
It works in this order:

1. Variable._configPrefix adds the configured prefix at the start of the ID.
2. DATETIMETOTEXT(App info.Current Date and Time,'YYYY') adds the current 4-digit year.
3. LINK(...) uses the last ID returned by the connector when one exists. If the connector does not return an ID, it uses Variable._configPrefix + Variable._configIdNumberFormat as the fallback structure.
4. MID(..., LEN(Variable._configPrefix), 4) reads the 4 characters immediately after the prefix. These are treated as the year part of the previous ID.
5. DATETIMETOTEXT(...,'YYYY') is used again here so the expression can decide whether the previous ID belongs to the current year.
6. IF(...) controls the two possible paths:
   • same year → continue numbering
   • different year → reset to 1
7. In the same-year branch, REGEX_REPLACE(...) removes the prefix and the 4-digit year from the start of the previous ID so only the numeric part remains.
8. **TEXTTOINTEGER(...) + 1 ** increments that numeric value.
9. TOTEXT(...) converts the incremented value back into text.
10. Variable._configIdNumberFormat + ... places the configured zero-format in front of the new value.
11. RIGHT(..., LEN(Variable._configIdNumberFormat)) keeps only the required number of digits, so the numeric part always has fixed length.
12. In the reset branch, RIGHT(Variable._configIdNumberFormat + '1', LEN(...)) creates the first number of a new yearly sequence.
13. The prefix, current year, and formatted number are concatenated into the final ID.

So the final output always has this structure:
prefix + year + fixed-length number

Why I chose this

I chose to derive the next ID entirely from the latest real ID in the table.
I built it this way because the previous ID already contains everything the logic needs:

• which prefix the sequence belongs to
• which year it belongs to
• what the last numeric value was

That means I do not need a separate counter table, a stored year field, or a scheduled yearly reset.

I also chose fixed-length formatting because it keeps the structure predictable and easier to validate.

The trade-off is that the expression depends on a strict ID structure. That is why the validation step comes first. I accepted that trade-off because a strict and readable contract is more useful here than trying to support many loose input formats.

Trigger 3 – USER CONFIG: OUTPUT STAGE

This is the last trigger in the logic block.
Its purpose is to define what should happen with the generated ID once _stateOutput contains a valid value.

The important point here is that this stage is intentionally left open for you.

If _stateOutput is not blank, you can decide what the logic block should do next.
In the current setup, the trigger shows a guidance message that explains the next steps:

• add the configured table to the app
• replace the message step with a Create Record trigger
• use _stateOutput as the new record ID

I built it this way because output behavior can vary between use cases.

You may want to:

• create a record immediately
• store the ID in another variable first
• use the ID inside a longer workflow
• validate additional conditions before writing to the table

Instead of hard‑wiring one fixed output behavior, I kept the output stage configurable.

That is why the user-facing configuration exists on both ends of the logic block:

• the input structure is configurable
• the output behavior is configurable
• the core logic stays in the middle

Variable naming approach

Internal variables use underscore prefix:

• _configPrefix
• _configIdNumberFormat
• _stateLastId
• _stateOutput

This helps you quickly distinguish between configuration values and internal processing variables.

Design boundaries

The logic block assumes this structure:
prefix + year + number
Each prefix maintains its own independent sequence.

The logic block does not support one shared counter across multiple prefixes.

If the year or numeric sections are moved within the ID, the expressions must be adjusted accordingly.

Summary of build decisions

If I reduce the whole logic block to the core decisions behind it, they are these:

• I wanted fast reuse, so the logic block is configuration-driven.
• I wanted the table to be the source of truth, so the next ID is always based on the latest real record.
• I wanted the logic to stay maintainable, so the calculation is centralized in one place.
• I wanted multiple entities in one table to work cleanly, so each prefix has its own independent sequence.
• I wanted the ID structure to stay consistent over time, so the year is part of the ID and the sequence resets automatically when the year changes.
• I wanted to protect the table from bad patterns, so format validation and overflow checks stop incorrect IDs before a record is created.
• I wanted the trigger flow to stay readable, so responsibilities are separated into clear steps: configuration, validation, generation, output.

That is why the logic block looks the way it does.
It is not only an ID generator. It is a reusable pattern for controlled ID creation that tries to balance simplicity, reuse, and data protection, while keeping yearly segmentation built directly into the structure of the ID.

Get Involved