Employee ID based on previous numbers

I’m trying to create/auto increment new employee numbers based on year & number.
For example, our employee IDs are 19-0054. When I create a new employee entry/new form entry, I’d like the new employee ID to increment by one from the previous number.

Any thoughts would be greatly appreciated.

Cheers!
Tim

Maybe someone has a different approach, but due to multi user capabilities, sequential keys are not encouraged. It might possible using valid_if checking to ensure uniqueness, but not absolutely sequential. I am sure others may have a better approach…

1 Like

Does the employee ID must be the key? If not, you could have a spreadsheet formula to give each row an incremented ID.
The downside in this approach is that you would have to sync to get the ID, that’s why you won’t be able to use it as a key for example.

1 Like

@Gil - you are right. I just “assumed” the ID was a key…

The Employee ID doesn’t have to be the key… right now it is as that is a unique # / employee (row).
Part of my problem is I can’t seem to figure out how to increment 19-00xx… I tried ROW as well as just A1+1…neither seems to work.

You could write something like… =“19-”&RIGHT(“0000”&row(),4) but if someone delete one row from the spreadsheet, it will ruin your IDs. Deleting a user from the app doesn’t do that because it keeps the blank row.

@Aleksi - I can’t get appsheet to recognize ‘row’… ??
And were you thinking this would go in the Auto Compute / App Formula section or …?
I had this set as a number initially, but I’m not entirely sure it shouldn’t be text (I’m assuming the hyphen (19-xxxx) plays havoc with numbers vs text).

I’m not worried about anyone deleting rows as
a) I’m the only one that uses this right now and
b) no one gets deleted, just terminated

I was thinking to do that on the spreadsheet as Gil proposed.

ah…ok… I missed Gil’s suggestion…
I’d really like the ID # to be in the app as I’ve got a workflow that emails the login information & Employee IDs to the user when the app is sync’d…

hmmmm

Try:

(
  "19-"
  & RIGHT(
    (
      "0000"
      & (
        NUMBER(
          INDEX(
            SPLIT(
              INDEX(
                SORT(
                  SELECT(
                    Employees[EmpID],
                    AND(
                      ISNOTBLANK([EmpID]),
                      (LEFT([EmpID], 3) = "19-")
                    )
                  ),
                  TRUE
                ),
                1
              ),
              "-"
            ),
            2
          )
        )
        + 1
      )
    ),
    4
  )
)
  1. SELECT(Employees[EmpID], ...) gathers a list of existing employee IDs that match the given criteria (...; see (2)).

  2. AND(..., ...) matches only rows that meet both criteria (..., ...; see (3) & (4)).

  3. ISNOTBLANK([EmpID]) requires that the EmpID column of the current row have a non-blank value.

  4. (LEFT([EmpID], 3) = "19-") matches only if the current row’s EmpID column value’s leftmost three characters are exactly, 19-.

  5. SORT(..., TRUE) sorts the list of matching employee IDs (...; from (1)) in descending (Z-to-A, 9-to-0) order (per TRUE). Assuming all employee IDs conform to the convention of the 19- prefix followed by four numeric digits, this sorting will put the highest employee ID at the start of the list.

  6. INDEX(..., 1) extracts the first item (per 1) from the sorted list of employee IDs (...; from (5)), giving the highest-numbered employee ID.

  7. SPLIT(..., "-") splits the single employee ID extracted from the sorted list (...; from (6)) around the minus sign (-), producing a two-item list containing the prefix and the employee’s serial number. This step assumes there will only ever be one minus sign in an employee ID.

  8. INDEX(..., 2) extracts the second item (per 2) from the two-item list produced by splitting the employee ID (..., from (7)), giving only the employee serial number.

  9. (NUMBER(...) + 1) converts the textual employee serial number (...; from (8)) to its numeric value and adds 1, producing an employee serial number one greater than the highest in use.

  10. ("0000" & ...) converts the numeric employee serial number (...; from (9)) back to text and prefixes it with four leading zeros ("0000"). The numeric serial number doesn’t have leading zeros; this how we include them.

  11. RIGHT(..., 4) extracts the rightmost four characters (per 4) from the new employee serial number (...; from (10)). This gives us a four-digit serial number with leading zeros if needed, dropping excess leading zeroes.

  12. ("19-" & ...) prefixes the new four-digit serial number (...; from (11)) with 19-, producing a complete employee ID.

WARNING

It is possible that multiple users of the app could add the same new employee ID! Each individual device isn’t aware of additions made by others until all devices making additions have synced. To avoid creating duplicate employee IDs, only one user should ever add new employee IDs, ideally from only a single device, and should sync immediately after the addition.

4 Likes

wow!
Just…wow!

Thanks @Steve!! And thanks SO much for the explanation. I knew there was a way to convert text to a number… NUMBER is what was missing. (well…along with the rest of it, of course), but that is a helpful function for me to remember.

I’ll let you know how it goes.

2 Likes

Thank you very much Steve.
If I have another column for JobID-XXXX, and I would like JobID-XXXX to follow that of a QuoteID-XXXX with the same running number when I add new record, will this be possible to do it?
I would like the number XXXX to follow the same as QuoteID running numbers.

Like this?

SUBSTITUTE([QuoteID], "QuoteID-", "JobID-")
2 Likes

Thank you very much for your help Steve.

1 Like

Currently, I have a Job details table where the QuoteID is a sequentially generated number (Quote-XXXX) from Quotation table. QuoteID is not the key. When I tried to substitute QuoteID-XXXX with JobID-XXXX, the JobID got the unique key from Quotation table. What I am trying to do is to add another 2 digit number from JobID-XXXX. The 2 digit is like the fabrication steps to make a Job order. Example, QuoteID is accepted quoteID from customer, thereafter create a JobID number similar to the QuoteID number, and with the JobID create sub-module to perform the task. It’s like Top level is JobID-0001, sub level is JobID-0001_1, JobID-0001_2 etc. Not sure whether this approach is correct. Seek people’s help in this. Thank you.