I am a graphic designer currently developing an FM7 database to track clients, jobs, invoices etc. In this system, each client / vendor has a two-letter code (unique) associated with them. This code is currently used in a simple calculation, combining it with a three-digit auto generated serial number to create a job number. For example, the first record would generate AE001 and the next FY002. In this simple model, the client code is variable, but the serial number always goes up by one.

What I'd like to do is have the job number auto generate in increments of one, based upon which client is selected. For example, if I created a new record for client code AE, the job number would be AE001. Then, regardless of how many jobs for other clients were generated after that, the next job number for client code AE would be AE002.

I hope this makes sense. I'm sure it's a simple solution, but I just can't seem to figure it out. Any help would be greatly appreciated.

Best Regards,

David

Here is a rough sketch out of memory of one approach.

You need following fields:

clientCode : like AE

jobCode = clientCode & serial

previousSerial

serial

A self relationship Item is based on clientCode = clientCode

What you would like is to make serial the max of the related serial + 1.

But FM will yell that that is a circular. Sort the relationship by serial

Schlick,

it sounds similar to another question i answered, perhaps you might take a look at that, see if it's helpful?

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=159362&an=0&page=0#159362

sincerely,

J__