Autoincrement - idnumbers ....

[Peter.Greulich]

RE: Autoincrement - idnumbers ....

Gary,

Something like this?

padl(trim(str(val(tablemax("tablename.dbf",'"+trim(left(lastname,6))=var->lastname)+"',right(trim(idnumber))))+1)),4,"0")

Haven't tested it - may have a syntax error.

What I don't like is that you're allowing the first part of your id to be 6 characters or less!

Peter

[drgarytraub]

RE: Autoincrement - idnumbers ....

Here is a simple solution that works in every case that I have tested thus far:


dim tid as c
dim newid as c
tid=UPPER(alltrim(left(lastname.value,6)))
newid=tid+"0000"

dim tn as n
dim tnstr as c
while exist(newid,"patients","idnumber")
tn=tn+1
tnstr=padl(alltrim(str(tn)),4,"0")
newid=tid+tnstr
end while
idnumber.value=newid


Thanks for everyone's help.


Gary

[JohnZaleski]

RE: Autoincrement - idnumbers ....

The success of this method depends upon the index tag expression that you are using for the index tag "idnumber".This is somewhat closer to what I suggested in a prior response to this thread.I'm not sure anyone can validate what you are doing unless they know what the index expression is.

[drgarytraub]

RE: Autoincrement - idnumbers ....

John,

The index tag is simply the field name: idnumber, character, 10

[JohnZaleski]

RE: Autoincrement - idnumbers ....

Gary,
That looks like it should do the trick.
I think you mentioned that you do this in the on save event.If that's the case is there a test to only allow this to be done in the enter mode??
John

[drgarytraub]

RE: Autoincrement - idnumbers ....

John,

In my New button, I set a shared variable called newpt to "Yes". Then, in my save Button, if newpt is "Yes", the idnumber script runs just before the parent.commit(). I then reset the variable newpt=""

Does this sound like the best way to go?

Gary

[Al Buchholz]

RE: Autoincrement - idnumbers ....

Shouldn't the test be if a value is needed and it's not there, then assign it?

btw, I still don't see how this is an improvement over a simple id field, but what the heck.

[drgarytraub]

RE: Autoincrement - idnumbers ....

Hi Al,

The point of creating an idnumber field this way is so that the user can easily determine which patient it is for. Sure, the name can change and then the idnumber is not informative, but by far in most cases, the name won't change. I would think this is certainly more informative than a simple number.

This is my reasoning. If you know of a better way, please tell me. I am open to all suggestions. Thanks.

Gary

[Steven Spencer]

RE: Autoincrement - idnumbers ....

Hi Gary,

Database theory.. (no, I have no Comp Sci, but I've been programming a while :-)

For simplicity...
Keys to files are best as simple, numeric, auto-incrementing unless you are importing existing data, all the same length, and should generally not attempt to be descriptive.

(btw.. an example of a numeric key that attempts to be descriptive is SS# .. descriptive attempts will always lead to things like uneven length, stale #'s with incorrect info, OR, simply, longer than they need to be)

(I winced when I saw the AlphaSports keys, things like V010)

If you want, you can set up a separate ID field/refernce #/searchfield for particular purposes, then if the person changes their name, or whatever, you can decide whether it is appropriate to change their ID ... it won't effect accessing the data by key a whit :-) .. you still may have some auto-incrementing difficulties as above, but it won't effect your primary key..

However, in most environments you can simply crossreference by an alpha search on last name, first name and/or company to "tag" the record you want... quick and easy... If a person is gonna read you SMITH0027 over the phone, they could just as easily read you #23798

We may get some folks here who disagree with the above, though :-)

[forskare]

RE: Autoincrement - idnumbers ....

When I was in the 8th grade, there were 3 Gary Johnsons We called them: Johnson1, Johnson2 and Johnson3. Should work, Yah?

kenn

[Al Buchholz]

RE: Autoincrement - idnumbers ....

Steven,

Very well stated. Degrees (I have math & computer) aren't as important as experience. They just get you started on the path.


Gary

Linking fields should be numeric and practically hidden to end users.

Look up fields should be non linking fields and based on real data and indices (for performance).

Sets should be used to show the patients name and address type information for any child data.

Methods that work 'most' of the time fail in a user's perspective and confidence over time.

I just spent a great deal of time fixing a more elaborate scheme in someone else's system that generated a unique number - 99.9% of the time. It became a series of credits applied to the improper client, or the improper invoice, or calculating an improper balance somewhat randomly. All because it worked most of the time.

In fact the error rate was in the range of 1 in 1000, but over a matter of 5 years and 800000 records it became quite a problem.

Never build a house on a poor foundation.

If you want to use your scheme, at most have it as an alternative identifier for a master record where the patient can be found by name, address, ss#, birthdate, etc.

Good luck and happy holidays!

[Steven Spencer]

RE: Autoincrement - idnumbers ....

Gary
Linking fields should be numeric and practically hidden to end users.

Steven
I almost put that in my explanation...

However, it seems that in the practical world, the linking primary key field often makes simply the best Customer# or Vendor#, etc... in other words it is in fact used in business communication..

As stated, there are many instances where printing or showing the number is optional or irrelevant or just an encumbrance..

Example is a browse on your items that are in 100 warehouses, you have limited real estate (browse space on screen) so you show the first 10 characters of the warehouse name and omit the 3 digit key#.... or similar with the customer #...

However in many cases, for quick data entry, it is good that your users, (e.g. office people) do ultimately become familiar with the key#, as they get to know the short #'s of the principle customers, they will save lookup time, ...but if they don't your beautiful tailored alpha or identity search will get it for them chick-chuck...

This is about the only place where I might "cheat" on the ideas we have shared... if I was starting a whole new system for an existing business, I might assign the really big customer #00100, rather than having every number auto-increment semi-randomly and he gets #00174, but that is of minimal significance.

[drgarytraub]

RE: Autoincrement - idnumbers ....

Thanks guys for alll this information. What an education! I really appreciate it and it all makes sense.

Here is my dilemma though. I started out by using the built-in autoincrement feature, using just simple sequential numbers, and this worked just fine. However, the reason I even thought of changing to the alphanumeric scheme is that I am importing large amounts of data from another commerically sold program. They have sold over 20,000 copies of their software. So I had 2 thoughts. If they used alphanumeric linking field/id's, then that was the "right" way. Second, they were using alphanumeric fields, I thought I have to also so as to be consistent. For example, if I import 2000 patients with 1000,000 transactions, that have key field/id's as alphanumeric, and then I siwtch to numeric autoincrement, wouldn't that create a mess?

What are tour thoughts?

Thanks in advance.

Gary

[Steven Spencer]

RE: Autoincrement - idnumbers ....

Gary
>Thanks guys for alll this information. What an education! >I really appreciate it and it all makes sense.

Steven
Welcome, as a newbie I can't offer much on Alpha details, but I can chime in on database design :-)

Gary
> I am importing large amounts of data from another
> commerically sold program. .. If they used alphanumeric > linking field/id's, then that was the "right" way.

Steven
nope :-)

Gary
> Second, they were using alphanumeric fields, I thought I > have to also so as to be consistent. For example, if I
> import 2000 patients with 1000,000 transactions, that
> have key field/id's as alphanumeric, and then I siwtch
> to numeric autoincrement, wouldn't that create a mess?
> What are tour thoughts?

Steven
Bring over their key field into a separate field that you make an "alternate index" for search purposes, (and you can print it out on paperwork as well), but simply number the patients from e.g. 1000 to 3000 (in a five digit, up to 99K patients.. or six digit 999K, numeric key field, first character(s) 0) and ADD that key as a brand new field in each transaction at the time of data import....

voila !, every patient and every transaction now has a short key, the other balagon (Hebrew for beaurucratic mess) key field is then relagated to simply an auxiliary search field, you don't even want it in the transaction records.

Hope that helps..

Steven

[Steven Spencer]

RE: Autoincrement - idnumbers ....

Gary
>Thanks guys for alll this information. What an education! >I really appreciate it and it all makes sense.

Steven
Welcome, as a newbie I can't offer much on Alpha details, but I can chime in on database design :-)

Gary
> I am importing large amounts of data from another
> commerically sold program. .. If they used alphanumeric > linking field/id's, then that was the "right" way.

Steven
nope :-)

Gary
> Second, they were using alphanumeric fields, I thought I > have to also so as to be consistent. For example, if I
> import 2000 patients with 1000,000 transactions, that
> have key field/id's as alphanumeric, and then I siwtch
> to numeric autoincrement, wouldn't that create a mess?
> What are tour thoughts?

Steven
Bring over their key field into a separate field that you make an "alternate index" for search purposes, (and you can print it out on paperwork as well), but simply number the patients from e.g. 1000 to 3000 (in a five digit, up to 99K patients.. or six digit 999K, numeric key field, first character(s) 0) and ADD that key as a brand new field in each transaction at the time of data import....

voila !, every patient and every transaction now has a short key, the other balagon (Hebrew for beaurucratic mess) key field is then relagated to simply an auxiliary search field, you don't even want it in the transaction records.

Hope that helps..

Steven