UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Custom Autonumbers    
 
   
jleach
post Oct 2 2010, 06:22 AM
Post#1


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


Often we find ourselves in situations where we want to have some custom autonumber-like functionality, but getting it isn't as easy as you might think. You shouldn't use an actual Autonumber field, as per best practices they shouldn't ever be seen by the user, and DMax has it's problems as well...
One of the issues with using DMax in a situation like this arises in multi-user situations where the next number is obtained at the start of the record. If person A starts a record, and person B starts a record before person A gets around to saving theirs, then there's going to be some conflict in what you need to be a unique number. Also, DMax, being an aggregate function, will be a bit on the slow side when you start having many many records as each call to it will have to look at all of those records to get the highest one.
This code works by storing the next available number in a table which contains a single field and a single record. There is a seperate table for each incrementing requirement. When a new number is needed, this table is opened, locked, and the number is incremented, saved to that table, and then the table is closed and unlocked and the number is returned by the function.
There is no form interface for this sample. There are four tables (tblANumMsgs, tblANumQuotes, tblMessages and tblQuotes) and three modules (modELookup, modSleep, and basAutoNumbers). To demonstrate the functionality call the GetAutoNum() function from the immediate window.
Format 2000 for download. Created and testing in 2003, though it should work in any version. This only works on linked access tables! Extra verifications and locking methods will be required for other data sources.
Cheers,
Edit: 2010/10/12
Uploaded instructions
Instruction Supplements:
1) If you tables contain spaces you must wrap all table names in the code with square brackets to avoid a table not found error.
Attached File(s)
Attached File  AutoNum2k.zip ( 30.03K )Number of downloads: 1624
Attached File  Anum_Instr.doc ( 25.5K )Number of downloads: 1303
 
Go to the top of the page
 
argeedblu
post Oct 2 2010, 08:34 AM
Post#2


Remembered
Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada


Jack,
reat addition to the archive. However, I am encountering a problem with this form of the call: ? getautonum(dsAutoNumQuote ,True) which errors out in A2010.
Glenn
Go to the top of the page
 
datAdrenaline
post Oct 2 2010, 10:39 PM
Post#3


UtterAccess Editor
Posts: 18,007
Joined: 4-December 03
From: Northern Virginia, USA


Hello Glenn (and Jack)
The error occurrs because fldID in tblQuotes is defined as a TEXT data type, and thus is raising an error in ELookup() ... it should be Number/Long.
Go to the top of the page
 
datAdrenaline
post Oct 2 2010, 11:08 PM
Post#4


UtterAccess Editor
Posts: 18,007
Joined: 4-December 03
From: Northern Virginia, USA


I am going to be a devils advocate to some extent due to your comment:
quot;You shouldn't use an actual Autonumber field, as per best practices they shouldn't ever be seen by the user"
That is a very debatable subject, and a detailed discussion is probably best left for another thread, and I beleive it to be so debatable that a consenses would not be reached in support of your claim. I personally have shown the user the Autonumber for some primary table in almost every app I develop. I consider it a "best practice" to consider what is best for the application's scope and purpose as well as how a replacement of an Autonumber will differ from the one Access (Jet/ACE) already provides you. The code you present is basically an "exposed code" Autonumber ... one that falls victim to all the same arguments on both sides of the fence regarding showing or hiding autonumbers. In short, you are implementing a lot of code as well as db objects in order to duplicate a functionality that comes standard with Access (Jet/ACE). In doing that, the process of uniquely identifying each record gets "closer" to the user, and thus increased your chances of your data becoming "unclean".
Go to the top of the page
 
argeedblu
post Oct 3 2010, 04:17 AM
Post#5


Remembered
Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada


Thanks Brent,
didn't have time to track it down. Until Jack has time to make the correction, anyone downloading the demo should note the correction and update accordingly.
Your comment about the 'autonumber issue' is well-taken. It seems to be a topic that is almost as contentious as the discussions of real vs surrogate keys. Consensus is indeed unlikely.
That said, I think that Jack's demo still has merit and could be useful for people who want to generate unique number series for control documents, such as invoices and purchase orders where continuity and integrity of the sequence can be important. Autonumbers can't be counted on to provide that assurance.
Glenn
Go to the top of the page
 
jleach
post Oct 3 2010, 05:25 AM
Post#6


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


The original attachment has been replaced with a version that doesn't require user input to discern the datatype.
rent - interesting argument, and I can definately see where you're coming from with it. Probably I shouldn't have used that particular wording in the original post as it is debatable, so I apologize for that. One of the driving factors for me in doing this was those cases where an autonumber can start returning negative numbers... I can't give a customer an invoice number of -487796, and this function would keep that from happening. The rest of your points on the matter are very well taken though.
Cheers,
Go to the top of the page
 
BananaRepublic
post Oct 3 2010, 11:22 AM
Post#7


Dungeon Cleaner
Posts: 1,520
Joined: 16-June 07
From: Banana Republic


Personally, the biggest issue for me WRT showing autonumbers to end users is that they will then add meanings that should never have been added to the autonumbers. By and by, I'll have to field questions like "why aren't those sequential?", "where is XXX now?", "We have 3129 invoices but there's only 3043 invoices. Where's the rest?". Yes, I can train and educate the users but why? Doesn't it really mean that we're costing the client more money for fluff and why should they be paying for fluff? Even if I were just an employee sharing the product with other employees, it still costs the company some money in terms of lost payroll hours to train & educate the users when I can entirely sidestep the whole issue by not showing it to start with.
ome business insists on some kind of code such as sequential numbering appended to a YYYY-MM notation or whatever. I generally tend to regard this as a holdover from existing systems such as filing cabinets where such thing was necessary to keep it well-organized. With an electronic database system, we have a query engine so we can do away with the manual code. However, even so, there may be other requirements that makes the code necessary and in such scenarios, it really does makes sense to roll out our custom auto-numbering to follow along with the business rules governing the structure of code. I should point out that it's plausible that we can save the added complexity by just using normal autonumber as part of the code (e.g. YYYY-MM-XXX being a calculated field of say, Year([InvoiceDate]) & "-" & Month([InvoiceDate]) & "-" & [InvoiceID] ) but I would haphazard a guess that it's rare because non-sequential numbering tends to be intolerable even if it was initially understood to not be sequential.
Glad to see a great submission, and I agree that using a table (which is very similar to a Oracle or PostgresSQL sequence) to generate the number can be much more superior compared to DMax().
Go to the top of the page
 
datAdrenaline
post Oct 6 2010, 02:23 PM
Post#8


UtterAccess Editor
Posts: 18,007
Joined: 4-December 03
From: Northern Virginia, USA


Well .. I debated as to whether to reply as I think we are bordering on hijacking the benificial Code Archive submission ... ... But if someone is considering using a code generated number, its important to make that decision with as much info as possible ... so here goes:
>> I'll have to field questions like "why aren't those sequential?", "where is XXX now?", "We have 3129 invoices but there's only 3043 invoices. Where's the rest?". Yes, I can train and educate the users but why? <<
Are you saying that training is NOT needed if the values of a code generated identifying value is used? That is not the case at all, code generated values require just as much explanation as inherent Autonumber fields! With code generated id's your efforts of training go into explaining why no deletions, and possibly no cancellations, are allowed and that the value generated is sequential AS WELL AS contiguous, and WHY it has to be contiguous.
Possible question series between the two camps (BR's questions):
Q: Are those numbers sequential? ...
A1 (datAdrenaline camp): Yes, they are, but there may be gaps in the sequence due to canceled input or deleted records.
A2 (BR camp): Yes, they are, the number is assigned after you save the record {of course this depends are your code, but for this sample, this would be the case}, plus to keep the data values contiguous, deletions are not allowed for <entity name>. But records can be marked as "hidden" or "deleted". Hidden records can only be retreived by administrative tools and/or permissions.
Q: Where is XXX now?
A1: If a record is not found with the value you seek, then the record has been deleted or the value was used for a record that was canceled during input.
A2: If a record is not found with the value you seek, then the record has been hidden from the interface and is only accessible through the administrative tools.
Q: We have [a max invoice id of] 3129 invoices but there's only 3043 invoices. Where's the rest?
A1: Invoices are assigned a value that is unique upon invoice start, once assigned, it will not be reused. If a user cancels a new invoice, the number it was issued will not be re-issued to another invoice. In addition, if an invoice is deleted, the id of the deleted invoice is not re-used. Those scenarios create the gaps in series of invoice id's.
A2: The UI only shows 3043 invoices because that is the amount that are valid to be seen. All the other ids are hidden due to conditions of that invoice (ie: the input was canceled or the invoice was marked as invalid or 'deleted')
----
The moral of the story is that there is always training involved for every system that is deployed and the claim that money is lost by showing an ACE/Jet generated autonumber is -- well -- no so realistic. An Autonumber field is a unique identifier (incremented sequentially or randomly), nothing more, nothing less. If you add more meaning to it, or add specificification to it (ie: data must be contiguous) then you are going beyond its scope. But, if the scope of a system generated visible identifier does not go beyond that of the inherent Jet/ACE Autonumber, then I see no reason to avoid using an Autonumber for that purpose.
My definition for my visible autoids in my systems:
A system generated number used to uniquely identify the <entity title>. These numbers are sequentially generated, but the data is not garenteed to be contiguous because of canceled input or deleted records.
----
I would hate to think of a people management database that does not display a system generated number for each unique individual.
Go to the top of the page
 
datAdrenaline
post Oct 6 2010, 02:30 PM
Post#9


UtterAccess Editor
Posts: 18,007
Joined: 4-December 03
From: Northern Virginia, USA


Jack,
ossible improvement ... Use ONE table to track you seed values ...
tblSeeds
--------
SeedId (Autonumber, Pk)
SeedName (Text, indexed uniquely) {values would be the Table Name}
SeedValue (Number/Long)
Use record locking (pessimistic) when doing your incrementing and such.
By using this method, you can then use tblSeeds as well as your code for other types of incremental needs as well. Plus you would not have an "explosion" of table objects to manage.
Go to the top of the page
 
BananaRepublic
post Oct 6 2010, 02:56 PM
Post#10


Dungeon Cleaner
Posts: 1,520
Joined: 16-June 07
From: Banana Republic


Actually, no. I said no training is necessary when any numbering system is NOT used at all. Search and find records by using person's name, company's name, invoice date or title, IMHO, requires less training and explanation. Users does not have to know there's numbers being generated under the hood nor should they care. As long the RDBMS can find it, everything is good.
then went to discuss the fact that some business will insist on some kind of numbering system even though I do feel that it should not be necessary. As an analogy, we don't search for website number 183302714901 to find www.UtterAccess.com - we just type "UtterAccess" in our favorite search engine and boom, it's there. It also (in my mind, at least) makes more sense to search for a invoice that was placed on 9/3/2010 than "2nd invoice created for the month of September", and even if the customer can't remember the exact date but only that it was 2nd invoice placed in the month of September, we can search for any invoices on September, 2010 and see that 9/3/2010 invoice is the one that the customer is looking for. In other words, it's almost inevitable that people talk about data in terms of reference other than autogenerated IDs. But back to the point - some business will nonetheless insist on a numbering system, and as long we agree on some conventions (e.g. assign a number only at the save time, not as soon as it's dirtied, and prohibit deletions), sequential numbering should work well when properly implemented.
I hope this clears thing up.
Go to the top of the page
 
jleach
post Oct 6 2010, 04:05 PM
Post#11


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


Hi Brent. I intentionally set this up on seperate tables to keep table traffic to a minimum and reduce possible conflicts. During some import operations a table may be accessed repeatedly and seperate tables would greatly reduce the risk of more than one person trying to access a given table at a time.
'll add some comments on the debate when I have time... I've got a few opinions as well that I'd like to get out there but I'm on the run at the moment.
Cheers,
edit:
Ohadn't been familiar with methods to enfore record locking vs. page locking when this was originally written. I'll look into that more.
Go to the top of the page
 
argeedblu
post Oct 7 2010, 05:49 AM
Post#12


Remembered
Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada


>
Hmmm. In the physical world, an accounting system that tolerates gaps in control number sequences is suspect and an open invitation to employee fraud. Let's say I work for XYZ manufacturing. We have some cash walk-in customers. The customer pays $100.00 over the counter in payment of invoice 12366 and leaves. I pocket the $100.00 and shred the invoice. Sometime later, an auditor notices the gap. Of course I tell her, "Oh that's just a quirk of our accounting system. That invoice never really existed." End of story? I think not. What you are looking at now is an expanded audit and balooning audit expenses.
aps in Purchase Order sequence numbers open similar opportunites for fraud.
Jack mentioned ISO. I can't quote chapter and verse, but I recall a UA discussion several years ago where the OP referred to an ISO requirement for the continuity of control document sequence numbers.
Incidentally, just this week, a local woman was sentenced to several years in jail for stealing $50,000+ from the charity she worked for. Chances are that she did the deed by evading the audit trail. Equally likely is that the flawed audit trail led to her detection, arrest and conviction.
Glenn
Go to the top of the page
 
jleach
post Oct 7 2010, 05:59 AM
Post#13


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


Interesting Glenn. I have yet to face a situation where sequential incrementation is indeed a strict requirement, and I can now certainly see how it would be in such circumstance, and I suppose it doesn't surprise me that ISO would dictate this in some form or another. Thanks for the input.
Go to the top of the page
 
BananaRepublic
post Oct 7 2010, 06:44 AM
Post#14


Dungeon Cleaner
Posts: 1,520
Joined: 16-June 07
From: Banana Republic


Great points all around.
Regarding the identifier for the invoices - I don't deny that it may make things easier to be able to talk about an invoice identified by something unique. However, what I'm saying is that it more likely, at least in my mind, makes sense to have a invoice filed on 9/5/2010 to Banana Republic identified by say, "BANREP20100905" than it is to identify it as "3812031". The former is more human readable & memorable and provides some information encoded that also aids in searching & finding the relevant data for this one invoice. My whole concern from the start is that when we're talking about identifying an invoice for user (as opposed to RDBMS which we can just slap any good old number and forget about it) is that it has to work without any trainings spent toward explaining that and this is not a quirk but an expected part of process. This won't change the fact that there will be organizations that requires a control numbering and in such circumstances there's little I can do to argue beyond than pointing that people are more likely to talk about events in terms of date, name, what they bought, a price they paid but when you ask for a confirmation number, they're all "um, i wrote it on a napkin, hang on let me look it for ... [long pause]... i think it was something like.... 023.... I don't remember." I really can't call this easy & convenient.
But that's just my $0.02 ZWD. <
Go to the top of the page
 
ace
post Oct 7 2010, 06:58 AM
Post#15


UtterAccess VIP
Posts: 5,446
Joined: 26-November 05
From: Upstate NY, USA


I had a client that runs a limousine service. They actually ran their business for 10 years using a
modified version of a Microsoft template. The person they hired to 'develop' the application used
Can autonumber field named invoicenumber. It shows on every form that uses it. I could not
convince them that it needed to be changed and that it was worth paying me to do so.
Up until 2009 they were not required to pay sales tax on the services they provide. Along with
the the change in law came state audits. The very first question asked by the auditor was "why
are there gaps in the invoice numbers?"
The next day I received a phone call asking me what I could do about it...
Go to the top of the page
 
argeedblu
post Oct 7 2010, 07:06 AM
Post#16


Remembered
Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada


Ace,
I'm glad to hear that at least this audit standard hasn't change in the 15 or so years since I was involved in administration/accounting. I was going from memory so to hear that that business rule is current and general is re-assuring.
Incidentally, my solution to the issue, which I am sure you don't need, is to use autonumber for data management ids and a second uniquely indexed field for document control numbers, populating the latter with routines such as Jack's at the heart of the code.
Glenn
Go to the top of the page
 
jleach
post Oct 7 2010, 07:09 AM
Post#17


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


Interesting that the conversation is turning towards the need for sequential numbering... because even with an actual autonumber field, the values are not guaranteed to be in sequence. I haven't completely thought it through, but it seems that verifying continuity in sequencing would involve an analysis of all the existing data to make sure there are no gaps, and filling gaps when they are found. I suppose one could keep a value stored (sequence check up till number #) and you could narrow the required analysis to work from that value up.
here's no strict requirement that the date of the document sequence be synced with the ID of the sequence, right? So if 555 gets filled a month after 777, as long as 555 is there it wouldn't matter, right?
And, what about starting numbers? Do ISO and Audits require that a record be kept stating what the starting number of an ID system was? I know of a good many ID systems that don't start at 1.
Go to the top of the page
 
argeedblu
post Oct 7 2010, 08:42 AM
Post#18


Remembered
Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada


Hi Jack,
o my mind, one of the reasons for custom numbers is precisely because autonumbers do not guarantee continuity and sequence. I have to admit that was the direction my mind went in when I saw your original post. I realize now that I have taken things beyond your original intent.
I think sequences would ordinarily begin at 1 but tracking the actual beginning of a sequence would take care of the eventuality that a particular instance starts otherwise. Off the top of my head, I think that from an audit perspective, the concern is not so much that every number used since the beginning of time be accounted for but rather that the number series from the start to end of a fiscal period be in tact. So, if I am auditing (don't get me wrong, I am not really an auditor) a multi-year fiscal period beginning January 1 2005 and ending December 31, 2009, I would be interested in the last number used in 2004 and that all numbers are accounted for over the subsequent five years. If, in fact, some numbers got used out of sequence, it should still be possible to reconcile the series with actual activities.
Glenn
Go to the top of the page
 
BananaRepublic
post Oct 7 2010, 10:32 AM
Post#19


Dungeon Cleaner
Posts: 1,520
Joined: 16-June 07
From: Banana Republic


Glenn or anyone else who's familiar with auditing process (the only experience I have with auditing is one for caseworks which I don't think is in same realms as financial auditing)
o you know what would be the general response to the idea I mentioned earlier of encoding the invoice with such thing as "BANREP20100905"? Would they still pooh-pooh and insist on a proper control numbering system? I suppose it will end up depending on which auditing rules we're looking at but what I'm hoping is for a general tendencies, a measure of how common such system would be considered acceptable under various auditing system?
Also, so I know - we all agreed that gaps means accounting tracking is suspect and auditor may not like that. Does it makes any difference if the same company always generated a end-of-month report enumerating all invoices in their exact state at the end of month, and compared it with an external source (e.g. bank statement for example) to show that both does balance out? Would auditor pooh-pooh this also (in general)?
Thanks!
Go to the top of the page
 
argeedblu
post Oct 7 2010, 11:18 AM
Post#20


Remembered
Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada


ally, I find anything that has leading characters other than 0's to be more difficult to read and mentally process than 'pure' numeric digits. The specific encoding you have suggested here would be awkward if the company issues more than one invoice per day (assuming that the numeric part of the code is a coded date) so you would need even more digits to distinguish individual invoices. I think it would be more difficult to test the integrity of the sequence than it would be with a purely numeric sequence, either mechanically or electronically.
There are also performance issues given that numeric indexes are faster to process that alphanumeric.
Oalso read someone recently (sorry I can't point to the article) commenting on that sort of encoding as a hangover from manual (i.e. paper) filining systems.
I thing that would make the process more difficult (read costly) because an auditor would have to review each of these reports. Many audit techniques rely on random selection of documents and transactions for testing. As far as I know,the random values are generated independantly from the client's data. If you are using simple numeric document control values then the audit can use a simple generic process to generate the test values. Any other form of coding would require a custom selection (again read $$$) to deal with the special format.
One example comes to mind that might clarify the issue Banana, is banks and credit cards. I don't know of any bank account or credit card numbers that have the kind of alphanumeric coding you are suggesting. I mention that specific sector because of the sheer volume of transactions that the have to handle and control on a daily basis. In Canada, at least, Banks might refer to themselves with cutesy acronyms (RBC, BMO, etc.) in their marketing and advertising on their credit cards and cheques, etc. the bank, branch, and customer account are all numerically coded. I don't have any knowlege of how they identify transactions internally, of course.
Glenn
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    13th December 2019 - 11:53 PM