Job Number Counter

[jsissov]

I am developing a 'Job Tracking System' for a print department.

We number our jobs as follows MMYY.001..002..003, etc. For example

the first job recorded in July 2005, would have the job number '0705.001'

Corresponding folders on our server are named likewise.

Currently I have a 'Serial' counter that just starts at, for example, 0705.001.

This works fine but I have to update it at the start of each new month.

Is there a way to create a 'Serial' counter that will extract the MMYY so I don't have to update this every month?

Regards,

Johnny

[RalphL]

I posted a couple of tech note numbers that may help:

http://www.fmforums.com/threads/showflat...true#Post165221

[J__]

To obtain the MMYY each month, you can create a calculated field. to do this for you.

Year ( Get(CurrentDate) ) returns the Year ( ex 2005 ), so you can use Right ( Year ( Get (CurrentDate)); 2) to return "05"

To get the Month, you can use Month( Get ( CurrentDate ) ), which will return 7 - no leading 0's, so you'll have to check for that in the calculation of your field. You can do it like this:

Something like this inside of the calculationi for the field

If ( Length( Month( Get( CurrentDate) )) = 1

;

"0" & Month ( Get (CurrentDate) ) & Right ( Year ( Get (CurrentDate)); 2)

;

Month ( Get (CurrentDate) ) & Right ( Year ( Get (CurrentDate)); 2)

)

I'm sure there is a more efficient way, but this gets you 07 for july and 12 for december, it only puts in teh leading 0 if length is < 2 for the Month.

So, now, you have a way to extract year and month as the days go by, rather than having to go in and change it at the beginning of each month.

I would also recommend breaking up the serial number you are describing into 3 separate fields

year, month, print_number, you could then create a calculated field, which is the concatenation of those three fields. print_Serial = year & month & "." & print_number

Why break it up? Well because you can then use Filemaker's autoincrement serial mechanism to increment the numbers for you.

for year, i would make this calculated ; Year = Right (Year ( Get ( CurrentDate ) ); 2)

(so you get 05 in stead of 2005)

for month, again calculated fields works: Print_Month =

If ( Length( Month( Get( CurrentDate) )) = 1

;

"0" & Month ( Get (CurrentDate) ) & Right ( Year ( Get (CurrentDate)); 2)

;

Month ( Get (CurrentDate) ) & Right ( Year ( Get (CurrentDate)); 2)

)

as described above.

for the Print_Number, you can increment the field serial, just type in 000 and each time you add a new record, you'll get 001, 002, 003 and so on.

Now create a calculated field to get all of them stuck together: Print_Serial = Print_Month & Print_Year & "." & Print_Number

The hard part is then resetting it at the beginning of each month. I have done something like this with a script and attached it to a button. is that a possible approach? If so, let me know and I can give you some tips on how to go about this (probably)

hope that helps,

sincerely,

J__

[LaRetta]

Or maybe like this (BAD DEMO FILE REMOVED) ...

You'll never have to reset the serials - the whole thing will run on its own just fine. The Job Numbers will just restart at 001 when the month changes. The JobNumber increments will be provided by the matched MMYY records in the new 'self-join'.

CTRL-N to create several records (which is currently based upon the global date in the header for month-change test purposes). Then change the global date to a future month and create several more records.

In your system, you would want to replace gTestDate to Get(CurrentDate) in the MMYY calculation. I believe it's simple to grasp and implement.

LaRetta

[LaRetta]

I deleted the demo above. I had based it upon Count() but I realized that, if a Job was deleted, system might assign duplicate Job Numbers. I noticed two people had downloaded the prior demo. I hope they grab this one instead.

There is a serial number in this file. But it's purpose is NOT to provide the incremental serial. Incrementing will be handled by Max(self-join; 'last three digits of Job Number).

DEMO FILE NEVER RE-ATTACHED ... THANKS TO COMMENT'S VALUABLE CONTRIBUTION BELOW. The process is simply not worth the risk.

LaRetta

[comment]

Here is another demo - showing why such schemes shouldn't be used in a multi-user environment.

BadCustomSerialDemo.fp7.zip

[LaRetta]

That's ummm, just what i was discovering and why i hadn't placed a new demo. Although my tests weren't nearly so obvious nor entertaining, they were showing troublesome aspects for sure.

I kept thinking that it's bad to 'make meaning' from an ID. I kept thinking resetting the serial was dangerous ... and that it was a bit wonky. For once, I should have listened to myself.

Well, I got some great ideas from your 'demo' aspects of your demo.

Update: Oh. I was so embarrassed, I forgot to say thank you, Michael.

[LaRetta]

As an aside ...

How many times have I created a calculation simply to display a message result?

Well ... now I feel ridiculous. I just spent 10 minutes trying to figure out how in the world comment got that red DUP to display in the CheckDup field in his demo when it is straight number without calculation! I even wasted time looking in Script Maker, Specify Button and Custom Functions.

Lord, what a pretty little resource-saving, time-saving use! Now I will go through my solution removing (approx) 30 unnecessary boolean calculations. Why take the time? Penance ... and to sink the concept deep. By replacing all of them, it will become habit to consider this idea first BEFORE I create a new field.

And no, I won't tell anyone ... you can look for yourself in his demo. But it's sweet! And powerful. There are so many gems throughout Forums ... planted there for us to find. I feel like I'm on a treasure hunt every time I sign on ... and I am.

Used too frequently by me ... but still just as true: Thank you SO MUCH.

LaRetta

[-Queue-]

If you disable creation of new records via menu and script each record to be committed after creating it, I think you are far less likely to produce duplicates. But I would only use such pseudoserials for visual purposes anyway; the auto-entered serial should still be used for relationships.

I would use a simpler setup though, as shown in the modified attachment.

CustomSerialDemo.zip