special formed ID field

[Gabe]

Hi

I am trying to set up a calculated index field where the id's of my case files are formed something like mmyy-#### where #### is a serial number that is reset to 1 at the beginning of every new month.

Any ideas how I might go about getting that to work? I just need to know a good way to reset the serial number.

[BobWeaver]

If it's a single user database, you can set up a selfjoin relationship (using the x operator to match all records) and use this autoenter formula:

serialno=Let([

LastSN=Last(selfjoin::serialno);

MMYY=Right("00"&Month(Get(CurrentDate));2)&Right("00"&Year(Get(CurrentDate));2)];

Case(IsEmpty(LastSN) or Left(LastSN;4)<>MMYY;MMYY&"0001";

SerialIncrement ( LastSN ; 1 )))

serialno should be a text field.

If it's multi-user, then this can cause duplicates if two users attempt to create a new record at the same time. So, it's best in that case, to script the operation and check for duplicates.

[Gabe]

Well, it is a multi-user database, but its only like 2 or 3 users. The chances of them creating records at the same time is small, but it could happen. So, I'd rather not take the chance.

Is there a way I could use a validation calculation to check if the value already exists and simply increment the current one again to make it unique?

[BobWeaver]

I haven't done a lot with FM7's field validation features yet. I'm sure you can set it to validate only unique serial numbers, but you would likely have to perform some manual operation to reassign the serial number when a duplicate is found.

[Gabe]

Ok. I went ahead and scripted the calculation, however, I am still having a minor problem.

The ID field has to start with a 1 for civil suits, and 2 for small claims. The final result will look like

1-0405-0001 or 2-0405-0001

I modified the calculation you gave me to simply tack on a 1 or a 2 at the beginning of the ID. When I do that, however, the calculation you gave me doesn't want to give back unique results for type 2 ID's. I can't figure out why, which I'm sure is partly due to the fact that I've been staring at this thing for way too long.

Can you give me a little help getting this calc to cope with the 1 and 2's?

[BobWeaver]

That's not a simple change. You are going from a single series of numbers to multiple independent series of numbers. You now have to have a different relationship to find the last number in whichever series you are looking at. You will need a field SuitType for the suit. Then change the relationship to be a selfjoin on field SuitType. Then the serial number formula will change to this:

Let([

LastSN=Last(sjSuitType::SerialNo);

MMYY=Right("00"&Month(Get(CurrentDate));2)&Right("00"&Year(Get(CurrentDate));2)];

Case(IsEmpty(SuitType);"";

IsEmpty(LastSN) or Middle(LastSN;3;4)<>MMYY;SuitType & "-" &MMYY& "-" &"0001";

SerialIncrement ( LastSN ; 1 )))