Is there a way (i.e. a formula) that will allow us to automatically
assign a consecutive job number to each new record? The catch is the
format of the job number: the first two numerals represent the year
(currently 05), the next two numerals represent the month (i.e. June
would be "06," October would be "10") and the third two numbers
represent the consecutive job number beginning with "01" at the start
of each month. We would never do more than 99 jobs each month.

Create a SERIAL field, an Auto-Enter serial number.

Create a calculation field SERIAL ID (returned as TEXT) with a formula
such as:

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

&

If ( Month ( Get ( CurrentDate ) ) < 10; "0" & Month ( Get ( CurrentDate
) ) ; Month ( Get ( CurrentDate ) ) )

&

If ( Serial < 10; "0" & Serial; Serial)

Written for FM7; if you're on 6 or earlier, change "Get" to "Status".

Matt


On 6/8/2005, Jo wrote:

In article <0dVpe.14168$MX2.5144@trndny03>, "Matt Wills" <I'm (AT) Witz (DOT) end>
wrote:
That would usually work, but Jo wants the serial number to reset to
"01" at the start of each month.

There's two ways to do that (that spring to mind, there may well be
others too):

A. Create a Relationship link within the file / table to group together
all the records for the same month and year. Then you can use
Count(Relationship::Field) to obtain the number of records within
the same month instead of using the Serial field.

B. Use Matt's method above, but also add a Global Date field and an
opening script that checks the system's CurrentDate against the
Global Date and if it's a new month you can reset the Serial field.
This one will only work if the database is quit each day (or at
least each month) and if your records use CurrentDate rather than
a manually entered date (ie. no back-dating or pre-dating of
records). This also won't work in older versions of FileMaker that
don't have the Set Next Serial Value script command.

Let us know if you need more help with either of these. )



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)