Serial Numbers (With a Twist)

[Corey Martin]

I have a field called Invoice Number that needs to increment. FM is able to do a basic increment, but I need something a little more advanced. Each record is assigned a Grant ID by the user, a number which is either 593 or 597. Invoice numbers combine with grant IDs to create record identifiers: 593.1, 597.1, etc. If someone inserted an invoice for the first time in to the system, the record identifier would be a combination of the Grant ID, a period, and 1 (ex. 597.1). If someone else went in and inserted an invoice with a Grant ID of 593, the record identifier should be 593.1 because it is the first invoice being filed under the 593 Grant ID.

If I made the Invoice ID a simple incrementing number and just stuck the Grant ID and a period next to it to form the record identifer, a problem occurs. Say I entered a record for the first time in to the system. It would work fine, returning 593.1. But then when I went in the second time to insert an invoice under the 597 Grant ID, it would come up as 597.2, when I'd want it to come up as 597.1 because it is the first record with a 597 grant ID that I'm entering in to the database.

Is there away around this problem?

Thanks!

Corey Martin

[Kurt Knippel]

Create a self-relationship (i.e. DB1 to DB1) by grant ID. Then you can take the Max (Self by GrantID::SerialNumber) and add 1 to get the next number for that GrantID.