Try:
(
"19-"
& RIGHT(
(
"0000"
& (
NUMBER(
INDEX(
SPLIT(
INDEX(
SORT(
SELECT(
Employees[EmpID],
AND(
ISNOTBLANK([EmpID]),
(LEFT([EmpID], 3) = "19-")
)
),
TRUE
),
1
),
"-"
),
2
)
)
+ 1
)
),
4
)
)
-
SELECT(Employees[EmpID], ...) gathers a list of existing employee IDs that match the given criteria (...; see (2)).
-
AND(..., ...) matches only rows that meet both criteria (..., ...; see (3) & (4)).
-
ISNOTBLANK([EmpID]) requires that the EmpID column of the current row have a non-blank value.
-
(LEFT([EmpID], 3) = "19-") matches only if the current rowโs EmpID column valueโs leftmost three characters are exactly, 19-.
-
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.
-
INDEX(..., 1) extracts the first item (per 1) from the sorted list of employee IDs (...; from (5)), giving the highest-numbered employee ID.
-
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.
-
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.
-
(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.
-
("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.
-
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.
-
("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.
LinkedIn
Twitter
