Topic: How to generate automatic unique ID number for patients?

I'm trying to build a patient out-patient registration database. Each patient needs to have a unique ID number (PID) that auto-updates with each new record. I have tried to place a record_counter field in the database and linked it to a textbox captioned PID, in the form OPD Registration. But this field does not auto-update when a new record is created. I also tried to use a calculated field as PID in the table and linked the same to the PID field of the form, but with no success. PID may be like A000001, A000002...., which may be possible with a calculated field. Please help.

Post's attachments

Attachment icon PHOTOS.zip 101.14 kb, 231 downloads since 2018-12-04 

Re: How to generate automatic unique ID number for patients?

Hello.


Check out this example

Post's attachments

Attachment icon CounterEx.zip 6.37 kb, 286 downloads since 2018-12-04 

Dmitry.

3 (edited by eyeman303 2018-12-05 15:17:33)

Re: How to generate automatic unique ID number for patients?

Thank you for the program. I have created an invisible EditCounter in OPD_Regidstration form and modified the script according to my Form name. However, PID number is not auto-generated when "Add New Patient" is clicked. Must have done some mistakes. Please see the script and help.
var
  sHello: string;

procedure OPD_Registration_Ed_PID_OnChange (Sender: TObject);
begin

end;

procedure HelloWorld (s: string);
begin
  ShowMessage(s);
end;

begin
  // just for example
  sHello := 'For The Joy Of Vision!';
  HelloWorld(sHello);
end.

procedure OPD_Registration _OnShow (Sender: string; Action: string);
var
    id : integer;
begin
    if Action='NewRecord' then
    begin
        id := StrToInt(OPD_Registration.EditCounter1.Text);
        OPD_Registration.Ed_PID.Text:= Format('SL-%.4d', [id]);
    end;
end;



begin

end.

Post's attachments

Attachment icon PHOTOS 05DEC18.zip 240.44 kb, 212 downloads since 2018-12-05 

4 (edited by derek 2018-12-05 21:40:36)

Re: How to generate automatic unique ID number for patients?

Hello Eyeman,
The PID is not being formatted as you want because your code has not been inserted in the correct place in the script.
Each time you want to perform a specific action in a script, the instructions must be contained within the 'begin' and 'end'  of a procedure that relates to that action (ie clicking a button, displaying a form etc).
At the very end of your script there is a 'general purpose' 'begin' and 'end' where you can add addtional instructions that simply execute when the program runs, rather than any specific action.
Anything that you insert AFTER this final 'begin' and 'end' of your script will simply be ignored;  this is what's happening with your script.
Perhaps it would help if you uploaded your program so that someone in the Forum could correct it - then you would be able to get a better understanding of scripts and what is required.
In the meantime, to give you an idea, I have attached an old program of mine that I have quickly changed to be more like your project (obviously, lots of it will be incorrect for what you actually need, but the principle is the same and it should give you more ideas).
Hope this helps,
Regards,
Derek

Post's attachments

Attachment icon eyeman303.zip 965.33 kb, 268 downloads since 2018-12-05 

Re: How to generate automatic unique ID number for patients?

Thank you for the guidance and program. I am trying to implement them in my database. Will report the outcome soon.

Re: How to generate automatic unique ID number for patients?

Hi! Merry Christmas to all.
I have got stuck in my project. Could not generate auto-incremental PID or OPD ID. Must have done some scripting error. I need help here. Also, there are too many databases in my project. I tried to aggregate dropdown objects in FORM2_OPD_REGISTRATION  in a new database SUBFORM2_OPD_REGISTRATION for reducing the number of forms. However, now the drop-down shows many empty spaces. How do I avoid them? In the FORM3_OPD_VISIT_RECORD I created a field called Name which is to be formed by the combination of First Name, Middle Name and Last Name registered in FORM2_OPD_REGISTRATION. How can it be done? How the Time Out field can be auto-generated? Is it possible to add new first name/ middle name/ last name automatically as the same is typed in the corresponding field? I also need that field namely, PID, OPDID, Name, Age, gender in the FORM3_OPD_VISIT_RECORD auto-populate when "Add New Visit" button is clicked in FORM2_OPD_REGISTRATION. Please Help.

Post's attachments

Attachment icon EYE_OPD_EMR.zip 377.46 kb, 203 downloads since 2018-12-23 

Re: How to generate automatic unique ID number for patients?

eyeman303 wrote:

In the FORM3_OPD_VISIT_RECORD I created a field called Name which is to be formed by the combination of First Name, Middle Name and Last Name registered in FORM2_OPD_REGISTRATION. How can it be done?

Post's attachments

Attachment icon test.7z 4.44 kb, 179 downloads since 2018-12-23 

Re: How to generate automatic unique ID number for patients?

Hi Eyeman303,
As a general 'rule of thumb', you should try to not hold the same piece of data more than once in your database;  creating a new field to hold information that you are already holding (firstname, middlename, lastname) introduces data redundancy.  I would suggest using a calculated field instead (please see attached).
I have had a quick look at your project and the database has a number of tables that seem unnecessary (middle_name, last_name, age_years, years, age_months, unit_price, subform2_opd_registration).  Also, some of the relationships do not look correct.
If I have some time, I can go through it and suggest an alternative schema that has no redundancy and is less complex, if this would be of help;  this should also help in reducing the number of forms that you currently have.  One suggestion might be to use 'editable tablegrids' for your 'look-up' forms (salutation, gender, next of kin etc - these can all be done on one form;  your project would load a lot faster with fewer forms.
Derek.

Post's attachments

Attachment icon joinednames.zip 336.38 kb, 210 downloads since 2018-12-23 

Re: How to generate automatic unique ID number for patients?

Hi Derek and Sibprogsistem,
Thank you for the guidance.I am trying to implement the suggestions. The  SUBFORM2A_OPD_REGISTRATION_LIST is actually meant to be the Lookup form with an editable TableGrid. I entered data in this form using SUBFORM 2A OPD REGISTRATION FORM to store in the database SUBFORM2_OPD_REGISTRATION. The combo boxes Middle_Name, Last_Name etc in FORM2_OPD_REGISTRATION are linked to the database SUBFORM2_OPD_REGISTRATION. However here the drop-down shows lots of empty spaces. Why does it happen? This does not happen if the combo boxes are linked to separate Lookup databases like Middle_Name, Last_Name, Occupation etc. Using 4-5 Lookup databases and forms would have been very convenient if the drop-downs were without empty spaces. Still to create few lookup databases for the form FORM3_OPD_VISIT_RECORD. How to reduce the number of database tables and forms?

10 (edited by sibprogsistem 2018-12-23 19:58:39)

Re: How to generate automatic unique ID number for patients?

tables

Post's attachments

Attachment icon tables.7z 3.12 kb, 184 downloads since 2018-12-23 

11 (edited by sibprogsistem 2018-12-23 20:19:54)

Re: How to generate automatic unique ID number for patients?

данные для записи нового пациента нужно брать из других таблиц.
SUBFORM2_OPD_REGISTRATION table contains patient data

Re: How to generate automatic unique ID number for patients?

https://downloader.disk.yandex.ru/preview/958e187e54381b2ed4e39e270791de10e559a6568aff8627a938ee0d68228d67/5c201a63/kOa8RABzsH3eXyZkX2tFb5EhSIgrR3jGdwqIipeVhFKid7azhgdtt9tfScCycIGinm4UqgohYZomvBXi3WrD_A%3D%3D?uid=0&filename=ugijhpok.jpg&disposition=inline&hash=&limit=0&content_type=image%2Fjpeg&tknv=v2&size=2048x2048

Re: How to generate automatic unique ID number for patients?

Hi everybody! Happy new year!
I have progressed somewhat in my project. Is it possible to create an action button to duplicate data fields, when more information needs to be added?  In my forms, there are many data fields ( eg. in Clinical Features page). A patient may have clinical findings in 1 or 2 fields only. Maybe as in Tabsheet2, I create 1 or 2 lines of data fields, and have an option to add more lines of data fields at the click of a button? Similar changes can be made in Chief complaint and History pages also. In that way, the form will look simpler and easily manageable. There is a screenshot of a similar programme attached. Something like that may simplify data entry. Please guide.

Post's attachments

Attachment icon eye_opd_emr.zip 462.86 kb, 228 downloads since 2019-01-04 

Re: How to generate automatic unique ID number for patients?

Hello!
I am having few roadblocks in my project.
1. PID number and OPD ID are not auto-generated. Please correct the script.
2. How to best record the clinical features etc in OPD Visit record, so that a prescription can be generated easily featuring patient details, clinical features, diagnosis, management, next appointment in a  A4 page?

Post's attachments

Attachment icon Eye_EMR_23JAN19.7z 314.05 kb, 222 downloads since 2019-01-23 

15 (edited by eyeman303 2019-11-13 18:33:09)

Re: How to generate automatic unique ID number for patients?

Hi Derek,
Based on your advice,I have redesigned my programme which builds upon your programme eyeman303. There are about 15 forms that capture inputs about each patient visit. Creating 15 tablegrids in the AA_Form_007_OPD_VISIT_REC will clutter the form. I have created a button in the AA_Form_007_OPD_VISIT_REC  to show AA_Form_007D_OPD_VISIT_REC_MENU containing buttons to show other forms, eg. DB_Form_015_LIST_OPD_VISIT_REC_CHIEF_COMP, This last form shows tablegrid from table_ChiefComplaints and has buttons to add new record, by opening AA_Form_018_REC_Chief_Complaints. There is foreign key id_visits in table_ChiefComplaints.Now when the inputs are saved in AA_Form_018_REC_Chief_Complaints, the tablegrid in DB_Form_015_LIST_OPD_VISIT_REC_CHIEF_COMP shows all records from the table_ChiefComplaints although tablegrid settings allow only child records.Is the OPD_ID from AA_Form_007_OPD_VISIT_REC not carried through AA_Form_007D_OPD_VISIT_REC_MENU to AA_Form_018_REC_Chief_Complaints? How to best design forms where there are multiple child forms linked to a main form?

Post's attachments

Attachment icon forms.7z 357.39 kb, 132 downloads since 2019-11-13 

Re: How to generate automatic unique ID number for patients?

Hi,
It's hard to work out exactly what's going on although it does sound like one of the keys is either not being passed through or not being saved.
Can you attach the latest version of your project and I'll try and have a look.
Derek.

17 (edited by eyeman303 2019-11-14 16:35:25)

Re: How to generate automatic unique ID number for patients?

Hello Derek,
I have attached the latest version. Please examine it. One thing I have noticed is that OPD_ID is not auto-updating in this version.

Post's attachments

Attachment icon My_Eyes_40.zip 466.58 kb, 130 downloads since 2019-11-14 

18 (edited by derek 2019-11-15 11:16:15)

Re: How to generate automatic unique ID number for patients?

Hi,
AA_Form_007D_OPD_VISIT_REC_MENU neither receives any data nor passes any on, so it breaks any relationships that MVD maintains automatically for you.  You could manually obtain the necessary keys by script but it's not playing to MVD's strengths.
I understand what you are trying to achieve using a 'menu' form to keep AA_Form_007_OPD_VISIT_REC uncluttered but I think a better approach might be to use a page control with a number of tab sheets (one tab sheet for each of your menu items).  This keeps the relationships intact and should then result in only the relevant child records being displayed in the tablegrid in AA_Form_006_PATIENT_REG_REC which was your original concern (see the attachment as a suggestion).
However, for information, there are a number of points that I would mention.
1.  When you close your application, it stays running in the background - this is due to the way you use your login form, I suspect.
2.  I would probably question any application that has 107 tables and 141 forms - I don't fully understand your requirement but I'd have a rethink of the data structure.  In my experience, if that's not correct then it's never going to work properly.
3.  With such a large number of tables and forms, I would also be worried about how slow your application will run once you have some meaningful data loaded and, I'm presuming from the nature of what you've done, it will be multi-user as well.
Anyway, that's just my opinion and you and others may have a different view.
Hope this helps,
Derek.

Post's attachments

Attachment icon My_Eyes_40a.zip 550.14 kb, 175 downloads since 2019-11-15 

Re: How to generate automatic unique ID number for patients?

Hi Derek,
I can never thank you enough for your advice. Now, I know that 007D Menu form actually breaks data transmission. I shall follow your advice and use page controls in AA_Form_007_OPD_VISIT_REC form. Yes, the "Chief Complaints" tab is only showing child records now.
I designed the login form based on the examples in the Forum. I am not sure how to log off completely so that the application stops completely after closing the form AA_Form_002_SELECT_MODULE. May I ask for your advice? The number of tables and forms are large, but the majority of the tables are dictionaries and won't add data regularly. I am also concerned about how fast would the application respond after a few months.  The database is about patients( registration, visits, appointments, bills), staff & doctors ( details, attendance, leave, holidays). Maybe separate databases for Staff and doctors would be better.
Regards,
eyeman303

20 (edited by derek 2019-11-15 21:23:55)

Re: How to generate automatic unique ID number for patients?

Hello,
Your application doesn't close completely because the login form is only hidden (AA_Form_001_LOGIN.hide - see line 71 in the script).
The easiest fix is to create an 'on close' event for AA_Form_002_SELECT_MODULE and insert the instruction  AA_Form_001_LOGIN.close;  in the script (see attached screenshot). 
So now, when AA_Form_002_SELECT_MODULE closes, it automatically closes AA_Form_001_LOGIN at the same time;
Derek.

Post's attachments

Attachment icon screenshot1.jpg 472.88 kb, 41 downloads since 2019-11-15 

21 (edited by eyeman303 2019-11-16 18:45:46)

Re: How to generate automatic unique ID number for patients?

Hi Derek,
Thanks again. I created the OnClose event as you illustrated, and the application is closing perfectly. I have redesigned AA_Form_007_OPD_VISIT_REC form with page controls and now it is looking good and clutter-free( please see the attached image). The button style you used really made the page control look better. Now the only issue I find is that the OPD_ID is not auto-incrementing in the same form (although earlier it used to and the script is unchanged). Can you please check the script?
Regards,
eyeman303

Post's attachments

Attachment icon New Form_2.JPG 80.18 kb, 39 downloads since 2019-11-16 

Re: How to generate automatic unique ID number for patients?

Hi Eyeman,
It's not the script.
If you have a look at the raw data (screenshot1 in the attachment) you can see the 'null' values.
So I suspect the problem is with the 'save' button on AA_Form_007_OPD_VISIT_REC not saving editcounter1 (look at screenshot2.jpg).
Derek

Post's attachments

Attachment icon eyeman.zip 314.19 kb, 152 downloads since 2019-11-17 

Re: How to generate automatic unique ID number for patients?

Hello Derek,
Thanks once again for lighting the candle of knowledge and showing me the way. Somehow during recreating the AA_Form_007_OPD_VISIT_REC the EditCounter1 was not included under the Save button and it produced the Null values in the table. I have included the EditCounter1 object under Save button action and updated the last Null value to the next integer in the SQLiteStudio. Now the opd_id is auto-updating. However, in the data table, there are many records with the same OPD_ID. Why is this so? Does clicking the save button more than once does so? There are some records at the end done for the purpose of testing. Trying to delete the record in the SQLiteStudio and application elicits the error message "Foreign key constraint failed". How these records can be deleted?
Regards,
eyeman303

Post's attachments

Attachment icon errors.zip 844.34 kb, 142 downloads since 2019-11-17 

24 (edited by derek 2019-11-17 18:26:33)

Re: How to generate automatic unique ID number for patients?

Hello,
OPD_ID is auto-incremented whenever AA_Form_007_OPD_VISIT_REC is opened.  But if you don't close AA_Form_007_OPD_VISIT_REC once the record has been saved, then OPD_ID can't auto-increment until the next time AA_Form_007_OPD_VISIT_REC is opened (see screenshot3)..
As a general rule (just my personal preference), I would always try to close any form once the data has been saved because it's too easy to accidently create duplicate records, blank records etc.  You could always check for this happening (by script) but it's extra work and not playing to MVD's strengths.
When you get 'foreign key constraint failed' error messages, it's because you have data in other tables that is dependent on the row of data you're trying to delete (if it's allowed to happen, you get an 'orphan record'  - ie it's 'parent' no longer exists, and the integrity of a relational database is compromised).
In your case, I imagine you're trying to delete a record from AA_002_REC_Visits but there are still related records in AA_003_REC_Chief_Complaints.  What you need to do is note the ID of the AA_002_REC_Visits record that you can't delete and then find the record(s) in AA_003_REC_Chief_Complaints where the foreign key (ID_AA_002_REC_Visits) is equal to the ID that you've noted. 
Delete all of the relevant rows in AA_003_REC_Chief_Complaints and then go back to AA_002_REC_Visits and the deletion should now work.
Derek.

Post's attachments

Attachment icon screenshot3.jpg 206.35 kb, 41 downloads since 2019-11-17 

Re: How to generate automatic unique ID number for patients?

Hello eyeman, Derek

Good to hear you Derek !

As Derek says, the best way to avoid an erroneous auto-increment is to open the form, save the entries, close it, and re-open it for new entries. As a result, the integrity of the base is ensured.

But if you do not want to do this (close the form and reopen it, ie do not check the option shown by derek), you can do otherwise by code and forbid the saving of an empty record:

procedure Form2_buttonSave_OnClick (Sender: string; Action: string);
begin
    Form2.buttonSave.dbDoCloseForm := False; // Prevents closing the form
    Form2.buttonSave.dbDontResetID := True;
end;

JB