What is the purpose of this user ID? For unique identification, it's simpler to set up a field as an auto-entered serial number and use it. And you can pair that ID number with the person's full name in value lists to get something that is appropriately user friendly.
A self join relationship can be used to supply the text you specify with an appended sequence number n the case of duplicates.
Divide the data into two fields, one for the JSMITH and one for the added sequence number. Say you call the text field for the name, NameID, and the number field for the sequence number Sequence. Define UserID as a text field with an auto-enter calculation to combine the values in a single field: NameID & Sequence, select a unique values validation rule for this text field.
Then this relationship:
Users::NameID = Users|SameName::NameID
can be used. Users|SameName is a added table occurrence with the same data source table as Users.
Your script then becomes:
If [ Count ( Users|SameName::NameID ) > 1 //duplicates exist ]
Set field [ Users::sequence ; Count (Users|SameName::NameID ) - 1 ]
This kind-of works, the problems I encountered when I use it are.
1: The record is not committed until you click out of all box's or close the screen so I had to change >1 to ≥ 1 and take the -1 from the end of the sequence adder.
2: Once you add 1 to jsmith it becomes unique as JSMITH1 and so the next Jsmith only sees 1 previous incarnation and sets the result to JSMITH1 again
so I had to combine your's and mine to make a working script. It works great :-) may be a tad slow if we end up with a ton of John Smith's or the like but that I can deal with
To repeat, why do you need it?
I would not use this method except for
a) specific "labeling requirements" where info printed from this database must be human readable but fit a very small space. (In all other cases, I'd just use the person's full name and or a unique ID number.)
b) to support "legacy" systems already in use by the client.
I would not ever use such a field as the Primary Key for linking to other tables in relationships.
Once you add 1 to jsmith it becomes unique as JSMITH1 and so the next Jsmith only sees 1 previous incarnation and sets the result to JSMITH1 again
That's why you separate the values into two fields, the NameID field only has the text JSMITH and thus matches to all records with JSMITH, the "1" is the value assigned to the separate sequence field. Thus records with values of JSMITH1, JSMITH2, JSMITH3... all match to each other as the NameID field has exactly the same text in all three records.
Sorry forgot to mention the Why.
Our company uses the first initial, and last name combo to create the logon ID for the computer domain, Email etc etc. this is why it HAS TO BE unique for each person in the system, as I had a unique UserID I didn't see the point in a second "serial Number" type index which just confuses the point especially when users all get searched by their ID anyway.
It's not a matter of "confusing the point". It's a matter of good database design. As I said before, you may have a good use for this identifier, but don't use it as your primary key for linking records in your database. Not only are names not unique, they are vulnerable to data entry errors and people change their names. If you get a bunch of records in your database linked by JSmith2 and than June Smith becomes June Jones or James Smith tells you that there was a mistake, his name is "James Schmidt", then changing this value screws up the links to the other records that still expect that linkage.
This can be avoided by using a serial number--which users should never know even exists, to link your records in relationships. Unique Identifiers like this can be used, but put them in a field of the employee or user table so that the value can be used in searches and sorts and don't use it to match to other records in relationships.