I am trying to figure out the best method to use for creating a customer record id. I would really value the input from you folks on the forum.

I have now created test Sesame applications using different methods of creating record ids. The old Q&A @number style and using a combination of layout elements.

In Q&A I used a sequential numbering system to create unique record ids. The reason for this was that it was an indexed field used as the main method of searching. In Sesame we do not use indexing so this reason is not valid.

In an article in Inside Sesame it describes using a combination of elements to create the record id such as first 3 letters of last name plus last 4 of zip. This has some benefits like being able to figure out id quickly and it is easy to create and maintain.

However it is very conceivable that you will have duplicates. I ran a test on some existing data and have many duplicates even using part of first, entire last plus entire zip.

On record entry this is easily dealt with by using a pop-up list to determine the correct customer but if I use this numbering system and plan to use it in my xlookups or with posting wont this be a problem.

Am I missing something?

If I plan on using features like Xlookup and Posting do I need to use the unique numbering method?

Thanks

Thanks for the fast reply 

This information is so critical to understand and comprehend so the correct decisions are made in the design stages of planning for my particular needs. 

I now feel a little more confident with my methodology.

Unique Number creation is always very important part of application. Practically all different systems of creating unique number has some limitation and sometimes one can combine the different systems to deal with the problem.  I have to create multiple unique# to accomodate my needs.  I will describe how do I produce various Unique numbers in my applications.

1.  I use @number only when Unique Number created in Mass Update because
     a.  Reading it does not make any sense
     b.  The number changes when you go in @add mode and escape it, system takes it as if it was used but in reality that number was not used and consequently it will create a break in the sequence and cause error in loops.
     c.  You can use only one number counter per application.

2. I have to deal with patients. Most of the Insurance company and Medicare assign SS# as ID, so for me in patient file SS# is one of the Unique numbers. What about Medicaid patient? Fortunately in Illinois they are assigned 9 digits recipient number, so that takes care of problem there. How about young children who do not have SS# ?  I take birthdate @num(YYYY/MM/DD) that makes 8 digits and the last one to allow another 10 patient with the same birthdate, starting from 1, 2..to 0. I have not gone past two as of yet in the last 14 years. It flags me if the number was already assigned, so I increase the end digit by one.  I believe, one can apply this to name + zipcode +1 to 0 (and if you allow alphabets that will create another 26 new possibilites. ) that should take care of duplicates.

For the other Unique number for the same file I use for today's date @num (YYYY/MM/DD) + 001 for the first patient for that date and increase sequentially, this number becomes the reference number for billing and is also utilized for giving sequential# for that date. It helps automatically poping up the information about next patient when patient goes to nursing station or examination room etc. for this I have to use @xlookupr ("data\application.db", 999999999999, "Q", "Q") Q being the unique field, on form entry event.

I still have need to produce one more unique number for the day in the other application, I can see one patient many times but I see the patient once a day only so ID + @date (YYMMDD) is the other unique number, that basically deals with patient data on the particular day.

I practically ran into problem when using variable integer because it could not hold value high enough to do @xlookupr command for data retrival but Mark bailed me out with that suggesting that Double variable does not have such limitation.

One can use globalvalue( ) to create multiple uniuqe number but looking at them would not make any sense.

My explanation and use might not be easily understandable and I might not be making it any easier to understand but they have been working perfectly for several years for me.  Please excuse me for not being very clear and rambling kind of way to present myself.

Quote:


I believe most of them have Medicare and medicare ID is the same as SS# plus one or two more alphbet and digit or mixed like A, M1, C2, D, ( I do not use them in ID, but they have a separate element assigned to it).  The day medicare stops using ss#, they will have to assign some unique ID. Insurance companies are also using SS# for ID and they will have to assign some number if they decide not to use SS#.  May be two digits from Insurance company plus assigned ID will make unique#.  We will have to decide howmany digits unique number should contain after they establish some guidelines about their Non-SS# ID.

Quote:


Command Tree is definitely not as user friendly as the menu system. I plan to have a menu system for employees.  I haven't put everything together yet, and I am glad I did not because if there is a problem with @xlookupr (@fn, 999999999999, "ID", "ID") command has a problem in networked application, I would not have gone much further and along the way had many Maalox moments.  I hope Sesame Development come out with some menu system before version 2.0 else we will have to deal with the work-around menu system using a form in "add" mode and using @selectTreeItem ( ) and hiding command tree from them. I believe it is workable until something better comes out.