Calculating a unique ID including text
I need to calculate a unique ID per record with the following considerations.
1. Must start with first letters of the last name. Ex. "HA" from Hardy.
2. A 2-digit number will be added beginning with "01". That makes the completed ID "HA01"
3. For another last name that starts with "HA" (another Hardy or a Hamilton, for example) the digit needs to become "02".
I can think of ways to do this theoretically, but am new enough to Filemaker that I cannot determine the details or the most efficient way. So far, the way I would choose would be to somehow sort on ID, determine if there is a record with the first 2 letters I need to use, if so, determine the highest number of the last 2 digits and add 1.
Also, I need to calculate this as soon as they enter their last name in the record, but before it is saved. I won't go into the reasons for that unless I need to change it.
I may be going about it all completely wrong. I welcome any additional ideas. I was an experienced FoxPro programmer for a few years, so I understand databases, but the particulars of FileMaker are taking some time, so any particulars would be helpful.