John, How does the User specify which type of ID to create? As an example, if there exists records (documents) with IDs
AFA-01-A AFA-01-B ... AFA-01-F and AFA-02-A AFA-02-B, how would the User specify they wanted the new record (document) to be numbered with either (a) the next serial in the AFA-01 series (AFA-01-G), (b) the next serial in the AFA-02 series (AFA-02-C), or (c) the next serial in the AFA series (AFA-03-A)? On the other hand, for the "VUE" series, which has no suffix, a User request for a new "VUE" record would number it with the next serial number ... VUE-01, VUE-02, etc., whatever is next in the series. Because of this, I suspect the User could request either "AFA-01", "AFA-02", or "AFA" to specify the (a), (b), or (c) cases listed above; that is, the User wants the next serial in the "AFA-01" series, the next serial in the "AFA-02" series, or the next serial in the "AFA" series. [Of course, this leads to questions about handling User errors, like wanting the next serial in the "AFA-89" series when "AFA-88" has not been used ... or requesting the next serial in the "VUE-01" series (suggesting a suffix is used - but VUE has no suffixes)]. I'm also wondering about the "serial number" part of the ID; that is, the "01", "02", etc part. What happens when the serial number reached "99"? Will it continue to "100", or will it return an error? If it returns "100", then a sorting of these records by the ID will not sort properly; that is, AFA-100-A will sort before AFA-11-A. And one further question: what happens when the suffix for a series hits "Z"? What follows that, "AA" or error? None of these are insurmountable obstacles. Get the interface (interaction) tight and the solution will follow. Best wishes, Jason L. DeLooze Annapolis, MD USA "You must be the change you want to see in the world" On 11/29/09 at 4:52 PM -0500, John Wenmeekers wrote: > >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
