Each Quote must have a unique ID number.
This number does not need to visible to users.
There could be another quote ID number that is visible to users.
It is the one that is visible to others that I am having trouble with as this will be the one that Jack, Jill and Pail will reference to me when discussing the quotes. I do not want each of them to know how many quotes are on the system. It would be easy to set a field whereby the CreatorName of the quote was added to the next sequential number, but if Jack had the first two quotes on the database they would be JackQuote1 and JackQuote2 but if Jill had the next quote she would have JillQuote3, which to Jill would not mean anything.
I would like to be able to add the CreatorName to a sequential number relevant to that CreatorName, regardless how many other quotes are on the database.
I'm no expert on this aspect, but it seems that by using Accounts and Privileges properly Jill would never see any records created by Jack and vice-versa. The UserName concatenated with a serial value would work. However, should Jill delete "JillQuote4" I believe the next quote created would be 5 so the serials won't always be consecutive if they're created automatically. The name could be created OnRecordCommit with a script as well. Under no circumstances should this field be a match field. As David said, the serial used as the match field needn't (shouldn't) be visible to the user.
In general, it is very difficult to maintain several indepedent series in the same table and to do so reliably. However, this case seems to be an exception, because the two main dangers do not apply here, namely:
• two users cannot be creating a new record in the same series at the same time;
• a record cannot be re-assigned to another series.
So it would seem quite safe to:
1. Define a field AccountName as Text, Auto-enter Creation Account Name;
2. Define a self-join relationship matching on AccountName;
3. Define a field PrivateSerialID as Number, Auto-enter Calculation, Evaluate Always =
Max ( Quotes 2:: PrivateSerialID ) + 1
Be warned, however: just because I cannot foresee any danger of getting a duplicate with this method, it doesn't necessarily follow that it doesn't exist - it could be just a limitation of my predicting abilities. In any case, you should maintain a common QuoteID serial number and use it exclusively for relationships and such.