Unique Record Identifier

I want to assign each ‘Person’ record a unique identifier based off their information. Put simply when a person is entered into my database I’d like to create a record (I’m calling it ‘Person ID’) that is generated using the first three letters of last name + the first three of their first name and a number.

First Name: John
Surname: Smith
Generated ID Record: SMIJOH0

Just wondering if anyone has any suggestions or scripting that might achieve this?

Hi @Joel1

If I was doing this I’d be looking for a no code option of which there are two that I can think of. Before I share, you may need to consider people that don’t have three letters in their name. I have a Chinese client called “Li”. :thinking:

So… you could either use Integromat or natively do it in Knack with a few additional fields. I’m going to explain the latter.

If you don’t already, add an “Auto Increment” field so you have a unique sequential number.

I presume your user name is in a “Name” field, not short text, as it should be👍

If so, add two new text formula fields: “NameFirst” and “NameLast”. Use the formula getNameFirst and getNameLast to populate these:

Depending on how many records you have in your table it may take some time for this formula to update. To test, simply update one record in your table (object) by clicking into a field (in the builder) and submitting. This will force a record update and you will see the two new fields have split the name into first and last.

Now add two more text formula fields called “FistNameLeft” and “LastNameLeft”. Use the “left” text string formula to extract the first three letters, left(String, End Position)


Do the same for “NameLast”.

You will need to force a record update again and should now have the first three letters of both names.

Finally, add another text formula “Person ID” and concatenate the strings (join them together).
The formula will be:

(LastNameLeft)(FirstNameLeft)(Auto Increment).

Once again, force an update on one of your records and this should work. :+1:

If you want to force an update on all records, and your table doesn’t have thousands of records I’d add a multichoice field called “UPDATE”. Leave all the options as standard and do an update on the table changing the field to first, second or third choice and run it. This will force a record update which can still take some time depending on quantity of records and server load.

Text formula fields don’t update instantly and are queued to run when server time allows. This can take a few minutes, or longer. I always test on one record and force the update manually until I’m satisfied with the result. Then either force a bulk update and go do something else, in Knack or elsewhere. Or just let it queue and come back to it later, depends on the urgency. :man_shrugging:

1 Like

Fantastic Carl!! This is brilliant, really appreciate it!

Glad that it helped :+1: