How can I reset on a daily basis, a field that is defined as an auto-enter serial number?

Each day I want this field to start with 1 and go up to n, and then the next day start over again at 1 and go to n, repeat ad nauseam.

This will be used in part of a calculation of another field to build a record ID number.

Assuming you have a creation date field, create a relationship to itself and sort it by the serial number descending. Then make serial an auto-enter calculation of Case( IsEmpty(selfreldate::serial), 1, GetField("selfreldate::serial") + 1 )

where selfreldate is the name of the creation date self-relationship.

Thanks for the reply.

What I have in mind is something like this:

recID = userID & julianDate & seqNumber

where seqNumber is the above calculation that you suggested.

Example:

03-2004167-001

but without the hyphens. Any issues/pitfalls with this calculation that I should be awares of?

This will mainly be used to keep a log of user records as the sequence number will show any gaps in record numbers.

Now as you may have noticed I need the sequence number to be applied to and be relevant to each user.

For example

user01 creates three records today and user04 creates two records on the same day, the recID numbers would be something like:

01-2004170-001

01-2004170-002

01-2004170-003

04-2004170-001

04-2004170-002

So do I need to define one of your Case calculation fields for each user?

Are your usernames actually user01, user02, etc.? If so, then create an auto-entered text calculation userdate of Right( Status(CurrentUsername), 2 ) & "-" & DateToText(Status(CurrentDate)). I'm not sure how you'd convert it to Julian right now. Then create a self-relationship on this field and sort it by serial descending. Make your serial field an auto-enter of Right( "00" & Case( IsEmpty(selfrel::userdate), 1, GetField("selfrel::serial") + 1 ), 3 ). Finally, create a calculation of userdate & "-" & serial, for your recID.

-Queue- said:

Are your usernames actually user01, user02, etc.?

Of course not silly.

The users of the database have a three digit number as their userID based upon their login group. Long story, but basically the reason for this is because not all computers(we still have a couple of old StarMaxes and even an old PPC6115) have mulit-user capability or are not set up as mulituser so I cannot really use Status(CurrentUsername). Which brings me to my next question.

Can I do this without using the Status functions?

Oh, and one more thing, should the fields be defined as text with the option of auto-enter calculation OR should they be defined as plain old calculation fields?

Replace Status(CurrentUsername) with your userID field then. If you can replace Status(CurrentDate) with your date field, just make sure it's updated daily. They should both be auto-entered text fields.

Thanks again for your help.

It solves the problem as it was defined but causes other problems. More specifically when a record is duplicated, so is the value in recID which sort of defeats the purpose. I need to rethink the problem through a little more.

I will go away now and not bother you anymore.

If you are scripting duplication, then you can add Set Field [serial, Right( "00" & selfrel::serial + 1, 3 )] to update the serial.