1 Reply Latest reply on Nov 7, 2009 5:40 PM by david_lalonde@d-cogit.ca

    Calculating a unique ID including text



      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.




        • 1. Re: Calculating a unique ID including text

          I used to be a FoxPro programmer 16 years back. I have not looked back since using FileMaker Pro. Definitely less powerful... much more approachable.


          The unique ID you are proposing is very limiting. You have a potential of 67 600 combinations, a small amount for any solution that hopes to have a long life. If, on top of that, you need to change it as you suggest is a possibility, then I do not envy your task at that time. There will be related records that will also need changing.


          May I suggest you make a hidden unique ID? You can use a simple numeric serial number. On the other hand, you could use an alphanumeric serial number like I do. This can be handy to add context to a key.


          As for your need to add a visible unique ID:


          1. You will need a calculation field that extracts the first two characters of a person's last name.


          2. You will need a self-join relationship that links from the person's record, using the calculated two character identifier as a key field, to the same table using the same field.


          3. You will need to attach a OnObjectKeystroke script trigger to the last name field.


          4. The target field will need to look at the number of characters and, when there are two characters or more, look up the existing count of similarly named record to calculate the numeric portion of the key. The script would then complete the visible key, commit the record, then place the cursor back to the same position in the last name field before the script was triggered.


          Hope this helps!