Script vs. formula
In the "Contacts table of my solution I use "Short Name" as a code and match field for several other tables. This five character name code is also a convenient shorthand in a few other layouts. Although it works fine, I'm beginning to see that as the database grows, it might be advisable to automate the creation of Short Names. I currently make them up according to the usual practice. Here's the bare bones (only) of a formula that would automate the process:(field would be stored as text-auto enter)
Case(Length(Last Name) ≥ 4; Left(Last Name;4)& Left(First Name;1); Length(Last Name) = 3;Left(Last Name;3)& Left(First Name;2);Length(Last Name) = 2; Left(Last Name;2)& Left(First Name;3))
This would return "SMITJ" for John Smith, "SIMJE" for Jean Sim, and "KOSTE" for Stephanie Ko. The snag is due to the validation requirement that each Short Name be unique of course. As far as I can tell, there's no use for Get(LastError) in a formula, so I couldn't check validation automatically and capture the error. I am aware that I can trigger a script On Commit that would accomplish what I'm trying to do. I was wondering if there's a way to avoid this. To be clear, "John Smith" would evaluate identically to "Joan Smith". By capturing the error (504) a script would eventually evaluate these as "SMITJ" and "SMJOA". Is there a way to accomplish this as an auto-enter operation?
I'm tempted to leave things as they are. I tend to remember character combinations I create myself. However this could change with the addition of a couple of hundred names. Given that a five character alpha only code can produce over 11,000,000 combinations, it seems that a five-character code, with its creation well automated, is more than adequate.