View Full Version : unique number required


10-29-2007, 05:40 AM
hello, i am new in alpha5 and planning to create a database for my clinic. I would like to create sequential number such as 10000-001, 10000-002 etc. The first digits are from patient id or record no. While the last digits are from patient's visit frequency times. I have browse through the forum, could not find any answer.
Any help would be appreciated.

Stan Mathews
10-29-2007, 09:31 AM

alltrim(patient_id)+"-"+increment_value(padl(alltrim(str(tablecount("yourtablename","patient_id ="+quote(patient_id)),3,0)),3,"0"))

Substituting your actual tablename, assuming the patient id field is named patient_id.

If you want a version that can be applied retroactively to existing records.

ALLTRIM(PATIENT_ID)+"-"+INCREMENT_VALUE(PADL(ALLTRIM(STR(TABLECOUNT("yourtablename","patient_id ="+QUOTE(PATIENT_ID)+".and. recno()<"+RECNO()),3,0)),3,"0"))

These would be used as the expression for a calculated field.

10-30-2007, 05:11 AM
Hello Stan,

Thanks for the help. However, the script in the calculated field gives an error of "argument is incorrect data type".
I create 2 simple table, one with Patient_id and another Treatment table with treatment_id and treatment_code fields. I would like to generate a treatment_code = patient_id-treatment count(visit), such as
1001-01, 1001-02, 1001-03 etc

I just bought the license Alpha5 a week ago, and trying my best to learn the program and its Xbasic from the help and some tutorial from Alphasoftware website.

Any help is appreciated.

Stan Mathews
10-30-2007, 09:21 AM
However, the script in the calculated field gives an error of "argument is incorrect data type".

What is the type of


I can't see why any would be numeric since you aren't going to perform any math calculations with them.

Try entering treatment records in my example attachment

10-30-2007, 12:49 PM
Hi Stan,

Thanks a lot. I manage to solve the problem after i use character type in the field.
However, i am not able to view your zip files, as it is a php type. When i browse using the browser, url not found. How to view the attachments? I did download the PHP file from PHP.net website, its quite complicated. Any other way?

Thanks again for your great help.

Stan Mathews
10-30-2007, 01:00 PM
When I click on the attachment link I am offered the option to open, save, or cancel. When I choose open, I am presented with the contents of the compressed file. When I choose save, I can choose where to save it. After saving it you need to expand the zip, note where you save the uncompressed version, open Alpha, navigate to the saved location, open the database.

10-30-2007, 01:30 PM
Hi stan,

Thanks again. Now i really know how to view other attachments from the forum.
I hope to create a clinic management software to replace my old one which was created with clarion 5.
Meantime, i will continue learning and practicing the Alpha 5 and its Xbasic codes.
Do you know any software or program that can convert my database files (.tps type) into other format? (apart from the clarion software and drivers which i don't have.)

Stan Mathews
10-30-2007, 01:51 PM
Googling ".tps" led me here (http://en.allexperts.com/q/Clarion-2368/TPS-file-conversion.htm).

10-30-2007, 02:01 PM
Hello Stan,

I have seen this site before. Probably i have to contact softvelocity myself to get the Topscan files.

Thanks again.

10-30-2007, 07:18 PM

If you find a way to use your TPS files please post it--I checked into this a while back and the only sure way was to purchase the Clarion TPS Drivers--about $600. US dollars at the time. I found a TPS reader that allowed me to see a very small amount of the .tps files but not good enough to export from. The program I use luckily was updated and now allows me to export or report to a HTML file--I can then import this into Excel and from Excel into Alpha. Luckily it will only be a one time import once I complete my current application that is going to replace the one built with Clarion.

10-30-2007, 08:27 PM
Hi MikeC,

At present the only way to convert my TPS files is to post the files to the programmer / developer who promised me to convert it. However, I have to wait until my Alpha database is ready. Hopefully by that time, they still around. Otherwise i have to pay for the Topscan driver from Softvelocity.
I have been using their clinic management program since 1998. Last month, they told me that they wanted to stop their service and support. After searching the internet, i found Alpha 5.
If i found any other way to convert these files, i will post it here.

Steve Wood
10-31-2007, 02:18 AM

Not my program but I would not create an ID like that. This is a Visit record table, so I would have a VisitID and a PatientID. The VisitID would just be a meaningless sequential number, and the PatientID would be whatever patient was visiting. You don't lose anything by going this way. You can still count up how many visits they have made, list them in order, give them "Visit Numbers" such as Visit 1, Visit 2, etc on a report. The method you are chasing may come back to bite you because it HAS TO BE maintained with much more precision than what I suggested.

10-31-2007, 04:51 AM
Hi Steve,

I accept your suggestion.
In my clinic setup, patientID = Registration iD (eg 1000). When patient entering the consultation room, his/her ID would be 1000-01 or 1000-12. A doctor who see this number would immediately know that the patient is either a new case or his 12th visits. If this is a new case, doctor would not bother to look at his past treatments. Most of the time, patient sit near the doctor and he saw the screen and contents. I would rather prefer having display 1000-01 than visit no or a new case etc.
I use the ID for treatment_code so that i sort the record by code rather than by date / patientID.
When invoicing to panel company, doctor and the company's administrators can immediately figure out how many times their employee visit the clinic.
In fact, this ID help doctor save his time, spend more time with patient rather than computer and more importantly know his patient better.

Of course PatientID or Registration No is the most important.

Thanks for the suggestion.