For every user in my user-profile table i want the user to be associated with a “short” 6-8 character unique code ( read as affiliate ID ) which can be used by new users as referral.
a. I initially thought of using the unique username the user registers with to be used for this purpose but i do give an option for the user to change the username in future so this fails
b. i tried to see if the UniqueID generator can be used but this is a very long string and i want something short of 6-8 chars max ( alpha-numeric )
c. Tried to use randbetween function and then wrapping it up with DEC2HEX to generate the string. But i need to to this to a field/cell only when a new user registers and it should remain static once generated for a given user - Struggling with it ( got a GSheets script too which generates this code)
But wondering how to populate this value only when a new user is registered - all the IF , IFBLANK, kind of conditions in sheets are not going though the way i want.
Apparently Glide doesnt have an option of generating random string with length as constraint
The amount of search i have done and not yeilded results mean that there might be a simpler way of doing which am not able to figure out in this struggle.
I believe an arrayformula with some string manipulations here works best. To keep it 6 characters you can possibly use the first 6 characters of the rowID (using LEFT), last 6 characters of the rowID (using RIGHT) or include the row number in there.
Tell me how you want it to be, I will construct the formula for you.
But how do i ensure that this column gets populated only when there is a new entry in the users table? The moment we say formulas we are talking about GSheets right and the row ID is generated in GlideTable primarily ( i know we can see this value in GSheets also ).
For your use case I would steer clear of a spreadsheet solution and instead use the Glide computed columns approach that I offered in that other thread. The problem with the spreadsheet solution is that values will change if a row is ever deleted.
With my approach, the values are locked in once they are set. The only downside is that you have to “kick start” it by setting the first value manually.
Sorry, I was on my mobile just now, so a bit fiddly to provide a direct link.
This is the approach I’m referring to:
This is done purely in Glide - no spreadsheet formulas required. And the same general approach can be used to generate any variation of an alphanumeric code that you like (with the numeric part auto-incrementing). For example, you could have something like:
The “USER” part would be fixed, with the numbers auto-incrementing.