Finding Next Sequence number in a field

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
We are using a alpha numeric field in a table similar to : T1-001.  How could I ensure that for a new record created, next available sequence number is available to a user in this 'required' field.  I know using Record Id is much more simple solution but sometimes people need to such unique fields.  
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 2 years ago

  • 0
  • 1
The easiest way is to make a formula which uses the Record ID#

For example

"T1-" & Right("000" & ToText([Record ID#]),3)
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
If the prefix changes however (T1 to T2, or P1, etc.)

You can create a table with all of those records, and then create a relationship between the table.  Then use a filtered report to only show the "Next Sequence" that hasn't been used yet.  

Thus you can have a very complex or simple sequence of IDs