AnsweredAssumed Answered

Calculating a unique ID including text

Question asked by whardy7 on Nov 7, 2009
Latest reply on Nov 7, 2009 by


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.