Hi all,

In a single file (table) I keep records of documents and I need to assign
unique Serial, but depending on a Year (date).

Let' assume I have 15 records for 2006 and 10 records for 2007. What I want
is when I enter a new record for i.e. 12/31/2006, FMP to give 16 as serial
number and if I enter a record for 1/1/2007 to give 11 as serial and so on.

FMP 8.5 - Mac OS 10.4.9


Any help for this will be appreciated.

Nick Trimmis

Have a small table with serial numbers held. One record for each
year. Do not use the internal function of assigning serial numbers.
Use a script to add a record, getting the year first and then looking
up the last serial number from the table +1. Replace that number back
into the table for next usage.
Thomas


On May 5, 6:04 am, Nick Trimmis <ntrim... (AT) hotmail (DOT) com> wrote:

In article <C2623F5A.122AE%ntrimmis (AT) hotmail (DOT) com>, Nick Trimmis
<ntrimmis (AT) hotmail (DOT) com> wrote:

First you need to know the year for each record by a Calculation field
using the Year function.
eg.
RecordYear Calculation, Number Result
= Year(DateField)

Now you can use this field for defining a Relationship to group
together all the records of the same year.
eg.
rel_SameYear Match records when
RecordYear = SameTable::RecordYear

Most people's first thought is to then use the Count function to know
how many records for the year exist and add 1 to it ... BUT that's a
mistake. If you later delete any record you will end up with two
records using the same "serial number".
ie.
If you have records numbered 1, 2, 3, 4, and 5
You delete record 3
When you create a new record, the count is only 4 records
so the new record becomes 5 giving a duplicate serial number

Instead you need to use the Max function to work out what the maximum
current "serial number" for the year is, and then add 1 to it.
eg.
SerialNumber Number, Auto-enter by Calculation:
= If (IsEmpty(RecordYear),
"",
Max(rel_SameYear::SerialNumber) + 1)

The If (IsEmpty(RecordYear), "" part is used to force FileMaker NOT to
calculate the "serial number" until the record has a date entered and
the Year calculated.

If there are not any existing records for the year, then the Max
function will return 0 meaning the first record for the year will be 1.

All done.

The only real problem can be when you delete the LAST existing record
for a year and then create a new record for that year - it will be
given the same serial number the deleted record had. There are a couple
of ways around this, but it gets more complicated and usually doesn't
matter since people want the sequence of serial numbers to not have
gaps in them anyway.



The next problem could be that currently existing records will not be
numbered. If there are only a few, it's probably easiest to number them
manually.

If there are quite a few records, then it easier to export the records,
delete them and the re-import them making sure to turn on the "perform
the auto-enter" option ... as long as there are no Container fields
storing images / sounds / etc. If there are Container fields, then you
can do the same thing, but instead of exporting the records you can
duplicate / Save As the file, delete the records in the current file
and import them from the duplicate.


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

On 5/6/07 6:47 AM, in article
060520071547224472%helpful_harry (AT) nom (DOT) de.plume.com, "Helpful Harry"
<helpful_harry (AT) nom (DOT) de.plume.com> wrote:


I did It your way and it works great.

Thanks a lot guys