Problem /w/ Combo create & reset for calculated field[s]

[Andrew Greenfield]

Greetings,

I am running into a problem solving the business rules by the owner which I hope FM8 will solve in a clean way:

He wants every when a person creates a "job" to be comprised of:

2 digit year + 2 digit month + incremental job# that resets each 1st of a month.

Ergo for today it would be: 0605034

or 06 [yr] + 05 [mth] + [we are on job#] 034.

Yes he wants/needs the zeros as well... ....

I am running into problems doing the correct calculation formula. I can get the month/yr/date easily enough, but my chief problems are:

FM needs to know what the next number in the job sequence-- a lookup to see what value it should be at

or -- If I use serial gen for a Job ID field, I need to have it reset itself every month [say at midnight?] as he wants to start job codes at 001 each month. Trigger? Background Script?

And being multi-user environment, I need to do a bit of intelligence so that each time someone opens a job, it grabs the next sequence -- this I assume is the "creation" rather than commit checkboxes...

I figure it will be at least 3 fields;

ID, Date, and then Jobcode:

Where ID would be the 3 digit Job ID, that needs to be smart about incremental [last job] and also the day of the month [should I reset?].

Date would have the timestamp of the job being opened, and useful for the final calc field of job code which has the combination of the prior two fields.

I appreciate your help and wisdom in solving this problem,

Sincerely,

anDREW J

:

Edited May 22, 2006 by Guest

[Søren Dyhr]

Trigger? Background Script?

No not quite, take a look at this template... it's done in fm3 or fm4 so you need to drag and drop it upon the icon of you FMPro app. to make it convert/migrate.

http://www.filemakerpros.com/Serialize_by_Category.zip

--sd

[Andrew Greenfield]

Thanks for the first bit of help! This gives me a direction for the first part --

--> But how do I accomplish the logic for the 1st of the month reset of counter?

Thanks again in advance!

--anDREW J