About Member IDs
The Member ID field, specifically the way it is populated, is a frequent source of confusion. The goal of this topic is strictly informational -- to explain what the field is for, how it is populated, and why it looks odd sometimes.
|1||The Member ID is a required field, and it must be unique. You can put anything you like into this field, but by default the system will assign a unique number to the field that matches the internal id of the membership record. This is done simply to save you the trouble of deciding on a unique value if you don't care about the contents of the field.|
|2||The internal system ID is a "database sequence." A sequence is essentially a running counter that is maintained internally by the database engine. It never "backs up" and there is no "undo" button. Every time a new record is created and saved, the database increments the appropriate counter and assigns that value. The next time a new ID is needed, it will increment the number again. If you subsequently delete the record, the system does not "re-use" the number.|
|3||Because internal ID numbers are just dumb counters, they cannot be relied on as a means to determine how many members are in the system. To get an accurate count of members, you should use statistics reports, or simply look at the total records on the Membership List after querying for all records.|
What Causes Confusion?
Suppose you add 10 members one after another in a new database. Their internal IDs will be 1 - 10. Likewise, the defaulted member ID numbers will be 1 - 10. At this point everything looks good.
Now let's say you realize that member number 5 actually never joined, and shouldn't even be in the database. Thus, you delete that member. Now the problems begin...
When you add another member, what should the system do? There are currently 9 records, lowest ID is 1, highest ID is 10. Should the system:
•Assign the number 11 to the new record, because that's the number immediately following the largest ID? Ok, but there really aren't 11 members, there are only 10.
•Assign the number 10, because there are currently only 9 members, and this is the 10th? Ok, but that would mean there are now two number 10s in the database, which is wrong.
•Reassign number 5, because it's an empty slot? Ok, now the count matches the actual number of members, but number 5 really didn't come before number 6, it came after number 10. Now the sequence doesn't make sense anymore. This gets even more complicated if you delete two or three members.
The only logical solution was to leave it up to you to tell us what you want to see. Therefore, if you don't want to use a random value for the Member ID, and don't want to type it in each time, we let you build your own list of "preloaded" member IDs. This enables you to define a list of IDs for the system to use. You can set up a simple list of 1 - 10000, or a more complicated list like, "13JEU4R5, 12JEU4R6, 12JEU4R7", or whatever you wish). The system won't care what the IDs are, it will just use one after another as you create new members. (See Preloaded Member IDs for more information.)
Every time you add a new member, the system looks at the preloaded ID's table and if there is anything available, uses the next value. You can even manually mark Preloaded IDs as active or used (they're automatically marked as Used when the system picks one). More importantly, if you start a new record, then cancel it, the system will go back and reset the ID it started to use, making it available again.
In addition, there is a Recycle Preloaded IDs preference that controls whether the system should recycle old IDs when the records that used them are deleted. (This is the scenario described above when record 5 was deleted.) If the preference is on, then when record 5 was deleted the system would find the entry in the Preloaded IDs table and mark it as available again. Then it would be used again by the next record.
The obvious question then is, "why don't we just do this automatically instead of expecting you, the user, to populate preloaded ID numbers?"
There are several reasons:
1 Not everyone cares about the member ID.
2 Not everyone uses numeric IDs.
3 Not everyone uses sequential IDs.
4 Some people want the ID to match the internal system ID.
Hopefully this information helps clarify how and why the Member ID field behaves the way it does.