Formula for a field

Question

How can I create the following formula field (with API name Username) on Contact?

First letter of FirstName + LastName + Number, such that if the combination is not unique, the number would make it unique.

For example, if I have a contact with FirstName being equal John and LastName being equal Parker the Username would be equal JParker then.

And if I will add a contact James Parker later on its Username would be JParker1 (given that there were no Contact with the FirstName starting with the letter J and LastName Parker added between).

Is it a good idea to use a formula field for Username at all? If so, how could I create such a field?

Thank you.

And I would like to preserve the number at the end of Username (e.g. if I delete JParker, the JParker1 should not change to JParker).

Answer 1

You cannot do this with a formula field. It's really a variant of the common "filtered autonumber" problem, where there's a desire to put an incrementing number after some field, but have multiple sequences running in parallel.

One approach is to use a List Custom Setting. You'd populate one instance of the custom setting for each first initial + last name combination that's created, and store the most recent digit used in that instance.

You'd need to write a before insert trigger, which would (for each inbound Contact) look up the Custom Setting for the desired username, using a FOR UPDATE query to lock that record. If the custom setting instance weren't found, you'd know that the user name had not been utilized before, and insert a new instance to record that it's been taken. If it were found, you'd increment the number stored there, use it in the username for the current Contact, and update the Custom Setting.

You'd also need to include some logic to handle, for example, the situation where two users with the same first initial and last name are inserted in the same transaction, and ensure that your queries are bulkified appropriately.