How can I generate a human-readable unique ID (reference number)

Ask for Help
1

I need to create unique human-readable reference numbers for each item in Table C. Preferably around 8 digits. Something like watch reference numbers.

Fake example:
Table A contents: Car companies.
[1] Ford

Table B contents: brand, type, color, year, qty
[1] Ford [2] Truck [3] White [4] 1998 [5] 90,000

Table C contents: Combinations
[1] Ford [2] Truck [3] White

Table B is where I can edit and add my data. If a white Ford truck was made at 90,000 qty each month in 1998, I will have 12 identical records. Records are fluid as more data comes in, even about past dates.

My key here is Table C, which is a google sheet with 1 array formula, looking up the unique combinations of brand, type, and color, from table B. So in this case the 12 records mentioned above will populate 3 columns and one row:
[1] Ford [2] Truck [3] White

I have hundred of these combination and need to assign a human readable reference number. Row ID’s do not work for me because as Table B changes, the list of combinations in Table C update from the array formula but there is no movement of rows. So [1] “Ford, Truck, White” might have ID “8yd79whodhu9w”, and later it moves down to row 2 and now “Ford, Truck, Black” has ID “8yd79whodhu9w”. This ruins any plans of pre-generating random strings as I can only have computed columns, correctly computing my fluid data.

So my uniqueness lies in concatenation of the unique combinations. I made a templet column to merge the 3 values as a text: “Ford, Truck, White”. I made a SHA256 columns with an input of this value. Then I used a Random Number computed column with the SHA256 seed.

This could absolutely work for me. I set the random number precision to “1.0000”.
With only an ability to generate 10,000 values I was a bit worried, as I have 300 currently and growing. Unfortunately on checking the results getting about halfway through my list I found two different combinations in which the same random number was generated from their unique SHA256. Of course it’s not actually the same but when limited to 4 decimal places it is the same.

So this will not work for me. Any ideas are very appreciated.

Screenshot 2023-01-06 at 20.49.14
Screenshot 2023-01-06 at 20.49.141858Ă—636 77.2 KB

2

In normal circumstances, this should never happen. If a RowID is becoming disconnected from the rest of the data in that row, then singing isn’t right. How is the table populated? Also consider using the Unique ID special value when creating the rows of you can.

But anyway, back to your question…I would probably structure your ID’s similar to VIN numbers, where each character in each position represents a particular aspect about that vehicle. Maybe create a table with 1-10…A-Z in one column, then have columns for Brand, Chassis, Color, Year, etc. Then in your other tables, maybe you could create a series of relations to that table with a Lookup to return the associated number/letter, then use a template column to join each lookup together into one ID.

It would be quite a few columns in each table, but like you indicated, I think you run to much of a risk by generating a random number. Trying to get billions of possible SHA hashes to fit into only 10k possible numbers is definitely going to lead to duplicates.

2 Likes
3

what about to use SHA256 on RowID column?

4

slice Row ID column for fewer characters. However, it results in a higher probability of duplicate value, if using 5 characters slice duplicate is probability is 1 in 10.000 rows. Solution thanks to the help of Darren Humphries

1 Like
5

For even more Human-readability. Use a template column to replace all the normal and capital letters with random numbers. To create a unique value consisting only out of numbers.