Skip to main content

Gapless sequential counters

Issue a human-facing sequence — invoice numbers, ticket codes, protocol IDs — that stays contiguous. Allocate numbers yourself, not from the auto-generated record id.

Prefer the native sequence action on a unique Number field. Bobuild keeps an internal per-key counter, locks it in the same transaction as the save, and never reuses a number after rollback or after you delete a business row.

Why not use {id}?​

Every record gets an auto-assigned sequential id. That works for internal links and for codes like P{id}, but the sequence can leave gaps: rolled-back creates, failed submissions after the id was reserved, deleted drafts, and similar cases skip numbers. When fiscal or operational rules need no holes, allocate a business number separately.

When to use this​

  • Invoice / receipt numbers that must be contiguous
  • Protocol or case numbers without skipped values
  • Numbers that restart every year (or per branch, document type, …)
  • Any public counter where auditors expect n, n+1, n+2 with nothing missing inside each sequence

If gaps are acceptable, a textual auto field such as INV-{id:lpad(5, '0')} is simpler.

Data model​

ObjectKey fields
Invoice (example)number (Number, Unique alone), plus your usual business fields

Or, to restart per year (or another section):

ObjectKey fields
Invoiceyear (Text or Number), number (Number) — mark both Unique together

The uniqueness group is the sequence key:

  • number unique alone → one sequence for the whole object
  • number unique with year (or branch, …) → one sequence per partner value

See Uniqueness. Do not invent a separate Counter object for new apps.

Steps​

  1. On Invoice, add Number field number and enable Unique (alone, or together with year / other key fields).
  2. Keep number (and key partners such as year) read-only on the create form so users cannot type over allocated values.
  3. Optionally derive a public label with an auto text field, for example {year}/{number} or INV-{year}-{number:lpad(5, '0')}.
  4. On the create form (or API create task), add an Update this record (or include the fields on a Create) task action that sets:
    • each key partner (year, …) — constant, formula, or copy from the form;
    • number → to next sequence value.

Partner fields must be set in the same action (or already present on the row for an update). Form tasks run in the same database transaction as the save, so concurrent creates serialize on the matching internal counter and rollbacks do not consume numbers.

The first issued value is 1. Different uniqueness keys (for example 2026 vs 2027) get independent sequences.

Choosing the key​

NeedUniqueness group
One never-resetting sequencenumber unique alone
Restart every calendar yearUnique on number + year (FORMAT(invoice_date, 'Y') or FORMAT(NOW(), 'Y'))
Fiscal year (e.g. Apr–Mar)Unique on number + fiscal-year field
Per branch / officeUnique on number + branch (or branch + year)
Several series (invoices vs credit notes)Unique on number + Text key such as CONCAT(..., '-INV')

Manual counter objects (legacy)​

Before the native action existed, the same behaviour was built with an explicit Counter (or YearlyCounter) object and two task steps: increment the counter, then stamp the invoice. That still works if you already use it; for new apps prefer native sequence above.

One sequence (global counter)​

ObjectKey fields
Counter (global)number (Number) — last issued value
Invoicenumber (Number), plus business fields
  1. Create global Counter with Number number (default 0); optionally seed the starting value.
  2. On create, Update Counter set number to {number}+1, then Update this record and set Invoice number from the Counter.

Sequences per year (YearlyCounter)​

ObjectKey fields
YearlyCounter (not global)year (Unique), number
Invoiceyear, number (unique together)
  1. Create YearlyCounter for the year (number ← 0); unique year reuses the row if it exists.
  2. Update that YearlyCounter: number ← {number}+1.
  3. Update this record: stamp year and number from the counter.

Tips​

  • Prefer to next sequence value on a unique Number field over maintaining a visible Counter object.
  • Do not reuse auto field {id} for the same public sequence — keep internal id and business number separate.
  • Deleting a business row does not free its number for reuse (fiscal-friendly).
  • The same patterns work on API Create endpoints when records are created outside the UI.