I'm working on a database where a single company might have several contacts. Each contact has a serial, generated automatically (ID-10000 +1 etc.), and I need each company to have a single serial.

In other words, the company serial can't be generated in the usual simple automated manner known to me, because then I get a new company serial for each of the contacts from a single company.

Any help would be appreciated. I'm a graphic artist being misused for this, I'm not the programming/math type, though I can think logically in a pinch , so keep it simple if you can.

Also I'm using the German version, so bear with me if my terminology isn't quite correct.

Pilot,

Do you really have to use a significant numbering system? If so maybe you could entertain the idea of having a 2nd numbering system to maintain record relationships but still have your significant numbering scheme as an alias.

I'm not sure what you mean by significant numbering... which is a big part of the reason why the rest of your statement remains a bit of a mystery to me as well...

Maybe I should elaborate.

The ID numbers are being used in concert with user accounts to create relations allowing for automatically adressed and signed form faxes, letters etc., which have to be individually personalised for each contact.

The company serials would be used in relation to project-datasets and especially billing, which is why I need a single serial for each company to keep confusion in the accounting area to a minimum.

Significant, meaning that the number itself has some significance. For instance, take the number (string) T4914R. If this were a stock number for a lawn mower for instance, the T might stand for a "tow" type, the 49 might stand for 49 horse power, the 14 might be blade diameter, and the R might mean that it is red in color.

In general, significant numbering systems are both a blessing and a curse. A blessing in that a person can tell much about something by just knowing the syntax of the string. However, they can also be a curse when something changes. For instance, maybe your company decided not to offer the color red anymore and now only offer brown. So the tail end of the significant number should now be a "B". Since your company has published the T4914R number all over the place they don't want to change the number. Now you have to remember that the R in this case doesn't really mean red now it means brown.

To alievate situations like this sometimes software will have a hidden numbering scheme that is used by the system for making the relationships and have a user controlled numbering scheme for user interaction. The software system (non-FileMaker) that my company uses for its core system uses exactly this sceneario. For every record there is an internal number and an external number. The users are only aware of the external number. As an administrator, I am aware of the internal numbers because all of the tables are joined based on the internal numbers.

I've been in business for about 25 years now and I've seen significant numbering systems run amuck more often than I care to so I like to caution people in their pitfalls before they get past the point of no return.

It sounds like you may be creating a new record in your Company table for each new contact?? If so, you want to create a seperate table for contacts and relate that table to the Company table based on Company::ID_Company = Contact::ID_Company.

sbg2, yes. Thanks for setting me on the right track, should have figured it out myself...

It's not quite as simple as you're saying, because it has to be user freindly for a bunch of graphic artists who don't want to think about going to a separate table to create a new company and then back to the contacts etc., etc., but you got me going in the direction I needed.

Ted S, thanks for your explanation of significant numbers and their pitfalls. The numbers are significant, but in a way that will never change. Each has a letter code at the start to indicate client-number, job-number, ID-number etc., but the letter codes are universal, not specific to any other criterion.