AnsweredAssumed Answered

Calculating a unique ID including text

Question asked by whardy7 on Nov 7, 2009
Latest reply on Nov 7, 2009 by david_lalonde@d-cogit.ca

Title

Calculating a unique ID including text

Post

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.

 

Thanks!

Wayne 

Outcomes