On Sun, 29 Nov 2009 20:50:03 -0500, Jason L DeLooze <[email protected]> wrote:
>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)? > The creation of records is via script. In short I redirect the user to a screen where they can enter data, at the end they click a 'Create' button. I separated the three parts in three global fields: prefix number suffix because I 'gamble' on the fact to compare each part separately: prefix - to find the prefix range and the next number prefix + number to find the max number in combination with the prefix The user knows (out of a 'reglamento' issued by the government department - ever found your way in such a document) which prefix to use in which case. In my case I have to use 14 different prefixes, but in total there are 254 different possible prefixes. Depends on the business. >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)]. Those are in part also questions we asked the department. As answer we got that, even if we have already a range with a suffix, there are 'exceptions' where there is no need for a suffix. But for now we skip that part. Indeed if we have already a range of AFA-01-A, AFA-01-B....the next suffix, if the user enters AFA-02, should be 'A' to give AFA-02-A, and the following in the AFA-02 range would be 'B', to give AFA-02-B. If the user enters AFA-01, it should be 'C' to form AFA-01-C, even if AFA-02 already exist.... That's why I separated the 3 parts in prefix, number and suffix. I know that it is nearly impossible to cover all the possibilities, but if I can manage to cover 70 to 80 % of the cases, I will be happy (until they issue another 'reglamento'. > >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. The range will go over 100 > >And one further question: what happens when the suffix for a series hits "Z"? What follows that, "AA" or error? Also one of our questions, but we did not get an answer. Combine the 'over 100' with the letters and you see that it doesn't fit. So I suppose they have to allow AA, AB etc. There's no other way. > >None of these are insurmountable obstacles. Get the interface (interaction) tight and the solution will follow. > I was thinking about a sort of workaround where FileMaker could 'propose' a next 'number/suffix' to the user, based on existing data. That would bring us half way. JW
