>I may not be understanding the full scope of the problem, but it >seems to me that you've already laid out the essence of the solution: >3 component fields, with a calculation field to generate the ID: > >ID = > Prefix & > If ( IsEmpty ( Prefix ), "", "-" ) & > SerialNumber & > If ( IsEmpty ( Suffix ), "", "-" ) & > Suffix > >
Sorry Richard. In my 'anger' about another useless change dictated from 'above' I forgot to mention the essential part. I have to create new records (documents) with a script. This is no problem for the first two parts : prefix and number. The problem is to tell FileMaker which suffix to use if needed. If I have a document with prefix 'VUE', with a self-join relationship I can find the next number (Max( relation::field) +1, it doesn't need a suffix. The document with 'AFA' (among others) as prefix needs a suffix A....Z. So the range would be: AFA-01-A AFA-01-B ... AFA-01-F or AFA-02-A AFA-02-B AFA-02-C ... A new record in the AFA range will depend on the number + suffix. In the AFA-01 range, it should be AFA-01-E, and in the AFA-02 range it should be AFA-02-D. My problem is to find a way so FM can recognize the situation and add the right suffix.... On top of that, I'm afraid that sooner or later a new 'chief of department' will come up with his own preferred method to 'mark' his presence. But that will be something to deal with later. I hope this is clear now.... In Kindness JW
