Serialized Field

[kevinc]

My db has a field that has YMM-XXX where Y = year number (I know, 1 digit can cause problems, but it's an inherited db and I'll get to this issue soon), MM=month number, and XXX represents the serial number of the entry for that month (we typically create 70-110 new entries each month). I want to have this be an automated entry that resets the XXX to 001 on the creation of the first record created on the first day of a new month. I've played around with different approaches, but none provide consistent results. Any ideas?

[Søren Dyhr]

It's pretty straight forward, since this is figure of meaning to the user and not a relational key. This means it with ease could be an unstored value - since it only needs a value when rendered on a layout:

Right( Year( theDate ); 1 ) & Right( "0" & MonthOf; 2 ) & "-" &

Right(

    "00" &

    ValueCount(

        Left(

            List( SelfJoin::Serialnumber );

            Position(

                ¶ & List( SelfJoin::Serialnumber ) & ¶;

                ¶ & Serialnumber & ¶;

                1;

                1

            )

        )

    );

    3

)

What is used here is a selfjoin from MonthOf as key on both sides, which should be stored in order to work. It gets it's value by this:

http://www.filemaker.com/help/Functions%20Ref19.html

--sd

serialize.zip

[comment]

this is figure of meaning to the user and not a relational key.

One would hope so. However, since this has SOME meaning to the user, and presumably will be used for something, perhaps it should be pointed out that in the proposed method the assigned number might shift, when for example an entry has been deleted or a date has been corrected.

[Søren Dyhr]

True, it just a matter how strictly consistency should be interpreted? It's somewhat against database theory where one fact per field is the ideal.

I can't help thinking by such deeply codified gestures, who's cheating who and why? - another way to approach this matter is:

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

Even here is integrity jeopardized, what if an issued document have gone to the printer, but later deleted again from the serialization - suddenly have two documents the same ID.

Resent revelations of a scam in my neck of the woods are fake leasing contracts the work of keen photoshop jockeys deeds. There is in our legislation that says if a contract is of a duration less than 3 month, shouldn't it be mentioned in the balance as a liability so a lot of contracts were then an attempt to cheat the accoundants and investors to believe another solidity than the actual, allowing extensive credits without any bearing at all.

So each leasing contract originally were made with long term leases, but in photoshop changed to fly under the radar, to disguise the commitment.

--sd

[kevinc]

Thanks for the ideas. I think my original post was misleading/incomplete.

What I'm hoping to achieve is something like this:

810-103 (last record on the last day of the month (Oct 2008)

811-001 (1st record on the first day of new month (Nov 2008)

811-002

811-003

...

811-099 (last record on last day of the month (Nov 2008)

812-001 (1st record on the first day of new month (Dec 2008)

812-002

812-003

...

812-107 (last record on last day of the month (Dec 2008)

901-001 (1st record on first day of new month (Jan 2009)

901-002

901-003

...

We currently have to manually reset the serial number to 001 on the morning of the first day of each month (before anybody creates a new record on that day). We also have to manually reset the serial number to the next number in the sequence if a record is mistakenly created and deleted. I'd like both of those operations to be automatic.

Once a record is created, this field value should not change for it's record even if a previous record is deleted. This field represents a quote number. It's record has a directory on the network as well as hard copy folder associated with it; both use this quote number for reference. Also, when customers call or send in purchase orders, they reference this quote number, so it must be static once it is created.

Sometimes a record will be created when only a subset of records is showing, and sometimes it will be created when all records are shown.

I tried looking at the quote number of the last record created, but it seems to be associated with the current found set and gives inconsistent results.

[comment]

The best way to do this, IMHO, is not to. The only way to make sure an ID is unique is to make it completely meaningless - such as an automatically entered serial number.

Although you could have an opening script check if today's month is different than the month being used, and if so, reset the serial number generator back to "001", the process would be still vulnerable (as is every scripted process), and the complexity required to make sure two users starting up at the same time do not collide is hardly worth the result.

The requirement to have consecutive numbers even if a record is deleted is even more problematic. What if another record has been created in the meantime?

BTW, a simple calculation of y & mm & last 3 digits of a serial number has exactly the same chance of producing a duplicate as the current method.

[kevinc]

Thanks for your comments. I realize this is not an ideal situation, but I still want to automate what is now a manual process at the beginning of each month and when a record is deleted. There must be a way to do this.

[Søren Dyhr]

at the beginning of each month and when a record is deleted.

Well why not stuff my calc' in a scripted replace fired just after a "Show All Records"

--sd