Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Recursive CF to generate unique usernames

Danny Dawson

Recommended Posts

Danny Dawson

Hi I have been struggling with this for some time with no success and as I have a deadline looming I wondered if there are some cleverer people out there who could help me solve my problem.


I have a DB of about 40000 people. I need to create a unique UserName for each person. I am creating the username by cocantinating the persons surname, first letter of there first name and grad year to produce a username such as dawsond08. However with a large group of people there are duplicates. I also have a field that counts the number of times each username occurs. The CF I am trying to write would use recursion to use more letters of the first name until it generates a unique value. So in the situation where there were 3 danny dawsons who graduated in 2008 username 1 would be dawsond08, username 2 would be dawsonda08 and username dawsondan08. I am positive this is achievable but I could really do with some help to get there.


Any help would be really gratefully received. Thanks in advance.


Danny Dawson

Link to post
Share on other sites

I don't think it's possible to do just with a CF or calc. I believe you will need a script to do this.


Have your script generate the first possible account name, then test that against the other records using a relationship. If there's already a record with that account name, generate the next iteration and test that. Keep going until no duplicates are found. Also make sure your CF/calc accomodates duplicate accounts where all letters of the name have been attempted:






make the second one:




or something.


The script would have two loops. One for looping through the records that need to be processed, and one for looping through the duplicate check/fix.

Link to post
Share on other sites
Danny Dawson

Hi, Thanks for the reply. I am also sure that this can be done with a script but I believe this is exactly the kind of thing that a recursive function could do and I think it would be a more elegant way of doing it. It may well be that I end up going down the script route but I am very keen to try the CF approach if it is possible.


Once again thanks for the reply.

Link to post
Share on other sites

CFs usually only deal with the values that you pass in as parameters. This is a functional programming design that works well for keeping the code clean. But in this design, it's hard to work with records other than the one you're on.


An alternative, is to use a global variable to remember all the account names as you generate them. Then for the next record that needs an account name, you can just check it against the list.


Here's my CF for this:


//AssignAccountName(nameFirst;nameLast;iteration;suffix) =
//By Mike Hackett

//When used with a Replace[] or looping Set Field[], generates a unique account name for each record in the set.
//The $$accounts variable stores the list of accounts that are generated and checked against.  

//Should be called with iteration value=1

username = Lower(nameLast & Left(nameFirst;iteration) & suffix);

Case(PatternCount($$accounts; username); 
AssignAccountName(nameFirst;nameLast; iteration + 1; suffix); 
Let($$accounts= $$accounts & ¶ & username; 


You'll still need something to call this. I'd recommend a short script:

Set Variable[$$accounts;""]

Replace Field Contents[No Dialog; AssignAccountName ( NameFirst ; NameLast; 1; Right(GradYear;2))

Set Variable[$$accounts;""]


If you need to be able to work on subsets of records, but include ALL records in the $$accounts, you should instead initialize that varable with a list of the existing account names.

Link to post
Share on other sites
David Head

I would probably do this by script to be better able to control when the identity was generated.

But just to show it can be done with a recursive custom function:


GenerateIdentity (p1; p2; n; p3; similar )
David Head, uLearnIT © 2008

p1, p2 and p3 are the fields used to construct the identity
n is the number of characters to take from p2
similar is a list of identities for similar names (from a relationship matching p1, p2 and p3 )

Let ([
identity = Lower ( p1 )  & Lower ( Left ( p2; n ) ) & Right ( p3; 2 ); // generated identity
identity_is_unique = not Position ( similar ; identity ; 1 ; 1 ) // identity does not appear in similar (list)
If ( identity_is_unique ; identity; GenerateIdentity (p1; p2; n + 1; p3; similar ) )


The value is the identity field is generated as an auto-entered calculation:


If (Exists ( first name ) and Exists ( last name ) and Exists ( graduate year ); 
GenerateIdentity ( last name ; first name ; 1 ; graduate year ; List ( NameIdentity__samenameyear::identity ) )


I have used another CF, Exists, which corresponds to "not IsEmpty".

The NameIdentity__samenameyear relationship matches first name, last name and graduating year.


The attached sample file shows both methods - script and CF. Enjoy!

Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

Terms of Use