Skip to main content
Question

Finding Next Sequence number in a field

  • September 15, 2017
  • 0 replies
  • 31 views
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.  
      This topic has been closed for replies.

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

      For example

      "T1-" & Right("000" & ToText([Record ID#]),3)
      • MCFNeil
      • Registered
      • September 15, 2017
      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
      Terms & ConditionsAccessibility statement