serial number re-starting automatically?

[veganboyjosh]

in a nutshell:

i'm looking for a way to serialize records with numbers, and having the numbers start over from 1 at the beginning of each month.

does anyone know of a way to do this?

out of the nutshell:

we have several files related to this problem:

1. clients: client info...duh.

2. employees: employee info.

2. jobs: job info like date, which client and which employee worked it.

i would like, in the job file, to have a "count field" that's a running total of the jobs worked for that client in the job month. so if a client had 12 jobs in one month, this "count field" for the first record of the month would state "1 of 12", and the 4th "4 of 12", 8th "8", etc...

the following month i would like for the counter to reset back to zero until they had one job, and it would go to "1 of 1".

i realize this is going to take more than one or two fields to accomplish,. but if someone could point me in the right direction. i'm sure someone has asked/posted about this problem before, but i don't know what to look for.

thanks for any help.

josh

[LiveOak]

How about building a self relationship based upon:

Key (calculation, text, stored, indexed) = Month(Status(CurrentDate) & " " & Year(Status(CurrentDate)

Match Key <--> Key

and set the next serial number to:

Max(Relationship:serial number) + 1

-bd

[veganboyjosh]

i'm a little hazy as to how this works. i tried the max function. i think it's in the direction i want to go, but it's not quite it.

i've gotten it to return the max job number for the current client.

and i've gotten it to return the max job number for the current month.

now. i dunno how to combine the two of these into the max job number for the current client in the current month. make sense?

thanks again...

josh

[Mike D.]

Josh,

To add to LiveOak's recommendation, I would include the Client ID as part of the key calculation. Assuming of course that you are using a numerical ID for each client and not just the clients name.

Key (calculation, text, stored, indexed) = ClientID & " " & Month(Status(CurrentDate) & " " & Year(Status(CurrentDate)

HTH, Mike

[veganboyjosh]

awesome. it totally worked, and it was way more simple than i thought it would be. as usual. thanks so much, kids.

josh