Generating the next number in a series - ideas?

Ask the Community Get Answers
1

Just looking for thoughts here: each case our company takes on has an alphanumeric case code. The code is composed of a three letter client stem, like “ABC-” followed by a three digit number. So Acme Industries might be “ACM-” and the fourth case for Acme Industries would be “ACM-004.” Currently that Case Code is a short text field in our Knack app.

We have been assigning these codes manually, which requires looking up the previous case codes and figuring out the last number used. So for the example above, we would need to verify that “ACM-003” was the last code assigned, before we gave the next case “ACM-004.” (The system only accounts for three digits. As we haven’t yet had a client with 999+ cases, it isn’t a problem. Yet.)

I am trying to figure out if there is a way to automate the assigning of that next code in sequence when a new case record is created. Any thoughts from the Knack Forum Brain Trust?

2

Hi @Joseph7, great use case!
I mention a very similar solution in Leigh’s post here.

In your Clients table, include the following fields:

  • Prefix (short text) - a prefix for the client (e.g. ACM)
  • Current ID (number) - defaults to 0
  • Next ID [equation] - {Current ID} + 1
  • Padding (short text) - contains three conditional rules:
    • Next ID [equation] is less than 10, set to 0
    • Next ID [equation] is less than 100, set to 00
    • Otherwise set to blank
  • Next ID (text formula) - {Prefix}-{Padding}{Next ID [equation]}
    image
    image345×374 19.8 KB

You can use the same principle in my video in the other post to then update the Case with the next available ID, and then update the Current ID field back in the Clients table.

prefix

Let me know how you go!

3

Amazing stuff. Thank you, Stephen!