Jump to content
Sign in to follow this  
kevinc

Serialized Field

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.