Fiscal Year ID Number

[sawindel]

Hi --

I have a database in which the end user wants the following:

1.) A unique serial number that is a one-up serialization based on our company's fiscal year. (starts Oct 1st).

2.) They want the one-up to restart at the beginning of each fiscal year.

(i.e. id number at the end of FY 2003 might be: 2003-097, the next record created in FY 2004 would be 2004-001)

3.) A third request is that they are able to enter in FY2004 data PRIOR to FY2004 yet give it a 2004-xxx ID number.

This database is being used to track ISO9000 & ISO14000 projects so identifying the projects based on the fiscal year is important.

I have been trying a couple of workarounds but haven't gotten very far. I can have a field FISCAL YEAR automatically change from 2003 to 2004 based on a calculation, but am getting stuck at how to reset the one-up serialized number back to 001 at the correct point in time and also am stuck on how to allow the user to enter data PRIOR to the actual fiscal year.

Any help would be greatly appreciated.

thanks

Scott

[stanley]

Scott:

I use a simple db for tracking my own invoices, and this is how I handle fiscal years:

On the entry screen, aside from having fields for the date entered, date invoiced, date paid, etc., I've got a pull-down for the fiscal year, so I can easily mark which FY the record belongs in. There is another field which is hidden from view, which holds the FiscalYearItemNumber data, which is filled by a script when I hit the "Enter Record" button - the script does a find for that fiscal year, then enters Status(CurrentFoundCount) into FiscalYearItemNumber. A further field, FYID, is a calculation combining the two fields, so I end up with something like "2003 - 104" for a unique number.

Two caveats about how this system works:

1. You cannot delete entries once they are made - if you have 100 records in FY 2003, and delete the first one, then the next one you enter ends up with "2003 - 100", just like the previous one. No good. But if you are being deeply responsible about audit tracking, the user shouldn't be able to delete anything once it has been entered anyway.

2. On a networked multi-user system, this will not work, as another user's half-finished entry will be counted as well, and make a mess of things.

You could certainly make a more robust version of this - just wanted to let you know how I've done it (a simple built-in-twenty-minutes solution) so you can work your way through your problem.

Oh, one other thing. When I first begin an entry, the fiscal year is auto-entered into the field, as most of the time I'm working in the current year.

-Stanley

[sawindel]

Stanley --

Thanks -- I'll try what you mentioned here. This is a networked file, but typically the entries are made during a group goal setting session and then not deleted.

thanks again

Scott