New serial start of year

[LaRetta]

I am creating a serial table.  The purpose is similar to incrementing an invoice number which must start over at the first of the year.  I do not want to use script (even server-side script) because it puts the dependency upon a process and I want it automatic.  I want to use a serial table (I think) because it protects from collisions (record-locking or two users claiming same serial).

 

I've attached what I've come up with so far.  I am wondering if there is simpler way or if anyone sees any potential unidentified problems with this method.  Thank you for considering this file.  :-)

 

I have been unable to break it in multi-user.  When I create the record and leave the cursor in the field and open a new window and create another, it identifies the first one even without a commit.  I just want to be sure that 1) it will not break in multi-user and 2) there aren't simpler calcs to do same thing.  

YearlySerials.fp7.zip

[comment]

I have been unable to break it in multi-user.

 

I have been able to break it in single user, single window:

 

Show All Records

Show Omitted Only

New Record

[LaRetta]

 

Of course I knew that the table could not be sorted and all records must show because of the GetNthRecord() but it's a developer table after all.  I thought I might also prohibit sort and find.  Do you have another suggestion for safe serial which starts over first of year?  I know we can't use a relationship here.  

 

Thank you, Michael!  BTW, you know I wouldn't use this for keys but so others know, this is NOT used for keys in which relationships are based.

Edited December 12, 2014 by LaRetta

[comment]

I would go with scripting the reset at the beginning of each year. That is assuming that I couldn't talk the client out of it entirely.

 

In the worst case, if the script fails then a few records at the beginning of the year will have serials that continue those of last year. You didn't say what these serials represent, but I am assuming that would be preferable to having duplicates.

[LaRetta]

I understand scripting at beginning of year - that was my first suggestion but client wishes to avoid that dependency.   This is used to generate a unique PermitNumber in a Permits table where sometimes they enter their own Permit Number but if it is left empty, system generates result similar to:  2014-serial.  Validation unique would be on the serial table as well as Permits::PermitNumber.

 

Thank you for offering your opinion on the process.  I appreciate it.

[LaRetta]

client wishes to avoid that dependency

 

So let's compare 'dependencies':

 

Serial reset beginning of year via server-side script:

Script fires once a year

 

Serial table:

Validate Permits::PermitNumber (would exist either way)

Serial table (would exist either way)

Validation on serial

Table must be protected from sorts and searches

 

...

[comment]

I don't fully understand your second scenario: how will the number get from the Serial table into the Permits::PermitNumber field? It seems you are forcing new permits to be created by script only (that's what I would do too). If so, the same script can verify that the serial number (in the Permits table) has been reset* - and if not, reset it all by itself, with no need to depend on a server script running once a year.

 

--

(*) Of course, you would need to leave a trail of resetting the serial number, e.g. by setting a field in the Preferences table to the current year. And prevent the (highly unlikely, but still) possibility of duplicate resetting by requiring ownership of the Preferences record before proceeding.

[Josh Ormond]

Would you be able use FM's serial number auto-enter?  You can prepend it with the year. And then test for the year at creation time. If the applied serial number is "2014-" set the "next serial value" to "2015-2" and set the current record's ID to 2015-1. The next record created would obviously then be 2015-2 and the script doesn't touch the FM next serial value.

 

If you test for the number of Open records, you should also be able to trap for other possible scripts trying to reset the serial number also.

 

Edit: I just re-read your earlier post. I see that they sometimes enter their own Permit number. Hmmmm...what is the use case for that? I suppose I see potential business use for it, however, I think the added complexity is often not worth the value of the preference.

[LaRetta]

Thank you both very much for the suggestions.  I have been testing but have not yet decided which is safest and best method overall.  As soon as I have (it may take up to a week before I can finalize it), I'll post with what I see as the risks of each method.  I really appreciate your ideas!!