Resetting Serial Sequence Each Month

[abequinn]

Hi all ...

I know I've seen similar questions raised before, but I didn't see an
answer that quite met my needs.

I've put together a database to track work orders, and I want to be
able to auto-generate a serial number. The numbering system we use
follows a YYYY-MM-XXX pattern, in which XXX is a three-digit serial
that starts over at the beginning of each month. For example, the third
work order received in the month of November would be 2004-11-003,
while the first order of December would be 2004-12-001.

I'm stumped as to how to script this. And, unfortunately, changing the
numbering system isn't an option. Any ideas?

[Marc-André Paiement]

"abequinn" <abeq...@gmail.com> a écrit dans le message de news:
1102551616.4...@f14g2000cwb.googlegroups.com...

Hi,

There may be other and better ways to do this, but my first idea would be to
set up a self-join relationship based on the left part (yyyy-mm) of the
serial_number, and then set new ones to max (selfjoin: number part of the
serial number) + 1

First, create two new calculted fields:
- Left_part: to store the left part of the serial number, something like
Left(serial_number,7). Text result.
- Right_part: to store the number part, something like
TextToNum(Right(serial_number,3). Number result.

To define the relationship, you will also need a global field (text) for the
left side and of course the Left_part calculated field is used on the right
side.

Finally the script would look something like thatL
- set field (the global field, year (status(current date)) & "-" & month
(status(current date)))
- set field (serial_number, global field & "-" & Right("00" &
Max(selfjoin::Right_part) + 1,3)

I didn't test this, so I'm not sure whether you need a case statement for
the first order of each month (when the relationship is empty).

Let me know if this is not working or not clear enough, that is if somebody
doesn't come up with a better way in the meantime...

Marc-André Paiement

[Helpful Harry]

In article <SZPtd.278576$0f.7...@charlie.risq.qc.ca>, "Marc-André
Paiement" <map...@cgocable.ca> wrote:

OUCH! That looks painful. ;o)

If the database is a simple-use one, it's probably easier to simply
have three fields and a short opening script rather than muck about
with Relationship links. (Files that are hosted on FileMaker Server or
web-hosted would require something more complicated.)

The first field is a normal Number field (SerialNum) with an auto-enter
option of a serial number.

The second field is a Calculation with a Text result to give the
InvoiceNumber, which you've probably already got:
Invoice Number = Year(Status(CurrentDate))
& "-"
& Right("0" & Month(Status(CurrentDate)), 2)
& "-"
& Right("00" & SerialNumber, 3)

The last field is a Global Date field that stores the date of the last
time the serial number was reset (g_SerialDate).

Then the script only needs to be something like:

If [Month(g_SerialDate) = Month(Status(CurrentDate))]
Exit Script
Else
Set Field [g_SerialDate, Status(CurrentDate)]
Set Next Serial Value [SerialNum, 1]
End If

Using the Preferences: Document option you can have this script run
every time the database is opened. If "this month" is not the same as
the month of the last reset, then the serial number is set back to 1
... as long as you computer's date is working properly of course.

Making the script test just the month rather than the 1st of the month
means you don't have to worry about months where the 1st of a month
being a weekend or public holiday where the file isn't opened. The
serial number will simply reset the next day that the database is
opened instead.

Notes:
1. If you're using FileMaker 7 then you'll need to replace all the
"Status(CurrentDate)" functions with the new "Get(CurrentDate)"
function.

2. The two "Status(CurrentDate)" functions in the Invoice Number
field will need to be replaced with an "InvoiceDate" field if
you're manually entering invoice dates instead of using
"today's" date when the invoice record is created.

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

[Marc-André Paiement]

"Helpful Harry" <helpfu...@nom.de.plume.com> a écrit dans le message de
news: 101220041826450045%helpfu...@nom.de.plume.com...

You're right Harry, this is mnch simpler. I must admit I'm still working
mostly with Filemaker 5 and Set Next Serail Value is not available.

[Helpful Harry]

In article <R3bud.294219$0f.2...@charlie.risq.qc.ca>, "Marc-André

Paiement" <map...@cgocable.ca> wrote:
>
> You're right Harry, this is mnch simpler. I must admit I'm still working
> mostly with Filemaker 5 and Set Next Serail Value is not available.

I haven't got version 5 (I think I skipped over that one), so I'll
believe you on that. It is a function in FileMaker 5.5, so maybe it's
time for a half-step upgrade ... although FileMaker don't sell 5.5 any
more. :o\

Without that command you do have to resort to some gymnastics with
relationship links to reset a "serial" number ... either that or always
have a fresh clone file with serial number of "1" and script the import
process to transfer existing records.

[smar...@kspress.com]

Harry-- I am trying to utilzed the process you mentioned above but have a few questions I hoped you could help with? I am wanting to do much the same thing as abequinn but the difference is I need to do this for each of our different products. I learned of the following way to make is to auto serial for each different product I have but cannot incorporate what you noted so that for each different product it re-calcualtes for each month (the order number is product code- year- month- order # (this is the piece that needs to serial).

Define two new tables: product1Serials and product2Serials
 
Define each with the same two fields:
ForeignKey (number)
CategorySerial (auto-entered serial number, you can include the "C" or "D" in the next serial value setting)
 
Let's call your main table "main" and set up relationships:
Main:: PrimaryKey = product1Serials::ForeignKey (enable creation of records via this relationship for product1Serials)
Main:: PrimaryKey = product2Serials::ForeignKey (enamble record creation for product2Serials)
 
Now, when you are ready to assing this label to a new record, run this script:
If [Main::Category = "product1" ]
  Set Field[product1Serials::ForeignKey; Main:: PrimaryKey]
  Set Field[Main::CategorySerial; product1Serials::CategorySerial ]
Else
  Set Field[product2Serials::ForeignKey; Main:: PrimaryKey]
  Set Field[Main::CategorySerial; product2Serials::CategorySerial ]
End If
 
Main::CategorySerial is where you'll be able to see the category serial number.

[Helpful Harry]

In article <dd087c54-54f7-46c7...@googlegroups.com>,

smar...@kspress.com wrote:
>
> Harry-- I am trying to utilzed the process you mentioned above but
> have a few questions I hoped you could help with? I am wanting to
> do much the same thing as abequinn but the difference is I need to
> do this for each of our different products. I learned of the
> following way to make is to auto serial for each different product
> I have but cannot incorporate what you noted so that for each
> different product it re-calcualtes for each month (the order number
> is product code- year- month- order # (this is the piece that needs
> to serial).
>
> Define two new tables: product1Serials and product2Serials
>
> Define each with the same two fields:
> ForeignKey (number)
> CategorySerial (auto-entered serial number, you can include the "C"
> or "D" in the next serial value setting)
>
> Let's call your main table "main" and set up relationships:
> Main:: PrimaryKey = product1Serials::ForeignKey (enable creation of
> records via this relationship for product1Serials)
> Main:: PrimaryKey = product2Serials::ForeignKey (enamble record
> creation for product2Serials)
>
> Now, when you are ready to assing this label to a new record, run
> this script:

> If [Main::Category =3D "product1" ]

> Set Field[product1Serials::ForeignKey; Main:: PrimaryKey]
> Set Field[Main::CategorySerial; product1Serials::CategorySerial ]
> Else
> Set Field[product2Serials::ForeignKey; Main:: PrimaryKey]
> Set Field[Main::CategorySerial; product2Serials::CategorySerial ]
> End If
>
> Main::CategorySerial is where you'll be able to see the category
> serial number.

I don't understand why a product serial number would need to restart each
month or why an order number changes with the products. Normally products
would be given a unique ID number when the product record is created and
remain the same "forever". An order number is more likely to be a possible
candidate for restarting.

If it's the order number that needs to restart each month, then that's the
same process as above - either mine if the database is Quit each night (so
that the script can be run when the database is re-opened the next day),
or the more complicated self-join relationship system if the database is
left running or hosted on a server.

Either way, it's unlikely you need those two new tables or that
relationship and script ... which doesn't appear to do anything useful
(although it depends on how the rest of the database is set-up and how it
works).


If for some strange reason it is the Product that needs to have a
restarted serial number, then it will probably require a system using a
relationship, but exactly how it needs to be set-up depends on the
database's structure, fields, etc. and what you're trying to achieve.

Basically you'll likely need a relationship that groups together the
records for the same product and year-month, and then counts the existing
number of records and adds one to it for any newly created record.

It's unlikely it can be done using the script system I posted since that
would require a separate serial number field for each individual product,
which would get very messy very quickly unless there's only a few
products.

Helpful Harry :o)