Generating OpenMRS Numbers

classic Classic list List threaded Threaded
3 messages Options
John Bowers John Bowers
Reply | Threaded
Open this post in threaded view
|

Generating OpenMRS Numbers

I am working on migrating our Excel data into the OpenMRS system.

Since our patient data is pretty bad and for over half of the patients  
only name is given (which isn't one of our identifiers) I'm creating  
an Import Identifier that will just be the import date and record  
number.

I have created a patient manually in mysql to get started and am about  
to import the entire Excel sheet.

What I want to know is: can I go back after the fact and have OpenMRS  
generate the internal OpenMRS identifier for each patient that doesn't  
have it (i.e. all the patients that were imported)?

Thanks,


John Bowers
E [hidden email]
T +256 77 930 4659

Volunteer Technology Coordinator
Kagando Hospital
Kagando Village, Uganda

Owner
espressoSoft LLC
www.espressosoft.com
Tallahassee, FL, USA

_________________________________________

To unsubscribe from OpenMRS Implementers' mailing list, send an e-mail to [hidden email] with "SIGNOFF openmrs-implement-l" in the  body (not the subject) of your e-mail.

[mailto:[hidden email]?body=SIGNOFF%20openmrs-implement-l]
Elaine Baker Elaine Baker
Reply | Threaded
Open this post in threaded view
|

Re: Generating OpenMRS Numbers

Hello John

Important things to note:
- The person_id field is an autoincrement so you cant make up your own
- The patient_id field is always supposed to be the same as the person_id
field
- The identifier you see in the openmrs screens is in patient_identifier
table

The approach we followed in Tanzania was

a) using the OpenMRS interface (API) create a user called Import User or
something like that.  Go to the openmrs back end and look up the users table
to see your new user's user_id.  This is called YourImportUserID in the
queries below.  Again using the OpenMRS interface, create a location.  Then
in the back end look up the location id in the table location.  This is
called YourLocationId in the queries below.  Again using the OpenMRS
interface, create a new identifier type.  Look up the back end in the table
patient_identifier_type to get the id for this type.  This is called
YourIdentifierTypeID in the queries below.

b) in the back end, create a field in the person table, called
PatientID_Temp  (you may delete this field at the end of the process, it is
used temporarily)

c) append your list of patients data into the person table.  Don't append
anything into the person_id field as it is on autoincrement so will be
filled in automatically.  Append with the birthdate, gender, dead (yes/no)
and deathdate.  Put in your Import User user_id as the creator and today's
date as the date_created.  Fill your patient identifiers in the temporary
field (PatientID_Temp)

INSERT INTO person ( gender, birthdate, dead, death_date, creator,
date_created, PatientID_Temp )
SELECT YourGenderColumn AS Gender, YourDateOfBirthColumn, Not
IsNull([YourDateOfDeathColumn]) AS Dead, YourDateOfDeathColumn,
YourImportUserID AS creator, Date() AS date_created,
YourPatientIdentifierColumn
FROM YourPatientTable;

d) append your list of patients into the patients table.  The patient_id
should be the same as the person_id in the person table.

INSERT INTO patient ( patient_id, creator, date_created )
SELECT person.person_id, YourImportUserID AS creator, Date() AS date_created
FROM person
WHERE (((person.PatientID_Temp) Is Not Null));

e) Put your patient names in the person_name table

INSERT INTO person_name ( person_id, creator, date_created, preferred,
given_name, family_name, voided )
SELECT person.person_id, YourImportUserID AS creator, Date() AS
date_created, 1 AS preferred, YourPatientFirstNameColumn,
YourPatientSurNameColumn, 0 AS voided
FROM person INNER JOIN YourPatientTable ON person.PatientID_Temp =
YourPatientTable.YourPatientIdentifierColumn
WHERE (((person.PatientID_Temp) Is Not Null));

f) put your patient ids in the patient_identifier table

INSERT INTO patient_identifier ( patient_id, creator, date_created,
preferred, identifier, identifier_type, voided, location_id )
SELECT person.person_id, YourImportUserID  AS creator, Date() AS
date_created, 1 AS preferred, YourPatientTable.YourPatientIdentifier,
YourIdentifierTypeId AS identifier_type, 0 AS voided, YourLocationID  AS
location_id
FROM YourPatientTable INNER JOIN person ON YourPatientTable.
YourPatientIdentifierColumn = person.PatientID_Temp
WHERE (((person.PatientID_Temp) Is Not Null));



Best wishes
Elaine


University Computing Centre Ltd
P.O.Box 35062
Dar es Salaam
 
Telephone: +255 22 2410645
Fax: +255 22 2410690
Email: [hidden email]

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf
Of John Bowers
Sent: 05 December 2008 12:05
To: [hidden email]
Subject: [OPENMRS-IMPLEMENTERS] Generating OpenMRS Numbers

I am working on migrating our Excel data into the OpenMRS system.

Since our patient data is pretty bad and for over half of the patients  
only name is given (which isn't one of our identifiers) I'm creating  
an Import Identifier that will just be the import date and record  
number.

I have created a patient manually in mysql to get started and am about  
to import the entire Excel sheet.

What I want to know is: can I go back after the fact and have OpenMRS  
generate the internal OpenMRS identifier for each patient that doesn't  
have it (i.e. all the patients that were imported)?

Thanks,


John Bowers
E [hidden email]
T +256 77 930 4659

Volunteer Technology Coordinator
Kagando Hospital
Kagando Village, Uganda

Owner
espressoSoft LLC
www.espressosoft.com
Tallahassee, FL, USA

_________________________________________

To unsubscribe from OpenMRS Implementers' mailing list, send an e-mail to
[hidden email] with "SIGNOFF openmrs-implement-l" in the  body
(not the subject) of your e-mail.

[mailto:[hidden email]?body=SIGNOFF%20openmrs-implement-l]



_________________

University Computing Centre - 'Professionalism, Customer Care and Technological foresight'

_________________________________________

To unsubscribe from OpenMRS Implementers' mailing list, send an e-mail to [hidden email] with "SIGNOFF openmrs-implement-l" in the  body (not the subject) of your e-mail.

[mailto:[hidden email]?body=SIGNOFF%20openmrs-implement-l]
Andrew Kanter Andrew Kanter
Reply | Threaded
Open this post in threaded view
|

Re: Generating OpenMRS Numbers

You guys might want to to check with JJ (cc'd) as I think he has been working on a module to do this....

-Andy
 
--------------------
Andrew S. Kanter, MD MPH

- Director of Health Information Systems/Medical Informatics
Millennium Villages Project Earth Institute, Columbia University
- Asst. Prof. of Clinical Biomedical Informatics and Clinical Epidemiology
Columbia University


Email: [hidden email]
Mobile: +1 (646) 469-2421
Office: +1 (212) 305-4842
Skype: akanter-ippnw
Yahoo: andy_kanter



From: Elaine Baker <[hidden email]>
To: [hidden email]
Sent: Friday, December 5, 2008 4:57:54 AM
Subject: Re: [OPENMRS-IMPLEMENTERS] Generating OpenMRS Numbers

Hello John

Important things to note:
- The person_id field is an autoincrement so you cant make up your own
- The patient_id field is always supposed to be the same as the person_id
field
- The identifier you see in the openmrs screens is in patient_identifier
table

The approach we followed in Tanzania was

a) using the OpenMRS interface (API) create a user called Import User or
something like that.  Go to the openmrs back end and look up the users table
to see your new user's user_id.  This is called YourImportUserID in the
queries below.  Again using the OpenMRS interface, create a location.  Then
in the back end look up the location id in the table location.  This is
called YourLocationId in the queries below.  Again using the OpenMRS
interface, create a new identifier type.  Look up the back end in the table
patient_identifier_type to get the id for this type.  This is called
YourIdentifierTypeID in the queries below.

b) in the back end, create a field in the person table, called
PatientID_Temp  (you may delete this field at the end of the process, it is
used temporarily)

c) append your list of patients data into the person table.  Don't append
anything into the person_id field as it is on autoincrement so will be
filled in automatically.  Append with the birthdate, gender, dead (yes/no)
and deathdate.  Put in your Import User user_id as the creator and today's
date as the date_created.  Fill your patient identifiers in the temporary
field (PatientID_Temp)

INSERT INTO person ( gender, birthdate, dead, death_date, creator,
date_created, PatientID_Temp )
SELECT YourGenderColumn AS Gender, YourDateOfBirthColumn, Not
IsNull([YourDateOfDeathColumn]) AS Dead, YourDateOfDeathColumn,
YourImportUserID AS creator, Date() AS date_created,
YourPatientIdentifierColumn
FROM YourPatientTable;

d) append your list of patients into the patients table.  The patient_id
should be the same as the person_id in the person table.

INSERT INTO patient ( patient_id, creator, date_created )
SELECT person.person_id, YourImportUserID AS creator, Date() AS date_created
FROM person
WHERE (((person.PatientID_Temp) Is Not Null));

e) Put your patient names in the person_name table

INSERT INTO person_name ( person_id, creator, date_created, preferred,
given_name, family_name, voided )
SELECT person.person_id, YourImportUserID AS creator, Date() AS
date_created, 1 AS preferred, YourPatientFirstNameColumn,
YourPatientSurNameColumn, 0 AS voided
FROM person INNER JOIN YourPatientTable ON person.PatientID_Temp =
YourPatientTable.YourPatientIdentifierColumn
WHERE (((person.PatientID_Temp) Is Not Null));

f) put your patient ids in the patient_identifier table

INSERT INTO patient_identifier ( patient_id, creator, date_created,
preferred, identifier, identifier_type, voided, location_id )
SELECT person.person_id, YourImportUserID  AS creator, Date() AS
date_created, 1 AS preferred, YourPatientTable.YourPatientIdentifier,
YourIdentifierTypeId AS identifier_type, 0 AS voided, YourLocationID  AS
location_id
FROM YourPatientTable INNER JOIN person ON YourPatientTable.
YourPatientIdentifierColumn = person.PatientID_Temp
WHERE (((person.PatientID_Temp) Is Not Null));



Best wishes
Elaine


University Computing Centre Ltd
P.O.Box 35062
Dar es Salaam

Telephone: +255 22 2410645
Fax: +255 22 2410690
Email: [hidden email]

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf
Of John Bowers
Sent: 05 December 2008 12:05
To: [hidden email]
Subject: [OPENMRS-IMPLEMENTERS] Generating OpenMRS Numbers

I am working on migrating our Excel data into the OpenMRS system.

Since our patient data is pretty bad and for over half of the patients 
only name is given (which isn't one of our identifiers) I'm creating 
an Import Identifier that will just be the import date and record 
number.

I have created a patient manually in mysql to get started and am about 
to import the entire Excel sheet.

What I want to know is: can I go back after the fact and have OpenMRS 
generate the internal OpenMRS identifier for each patient that doesn't 
have it (i.e. all the patients that were imported)?

Thanks,


John Bowers
E [hidden email]
T +256 77 930 4659

Volunteer Technology Coordinator
Kagando Hospital
Kagando Village, Uganda

Owner
espressoSoft LLC
www.espressosoft.com
Tallahassee, FL, USA

_________________________________________

To unsubscribe from OpenMRS Implementers' mailing list, send an e-mail to
[hidden email] with "SIGNOFF openmrs-implement-l" in the  body
(not the subject) of your e-mail.

[mailto:[hidden email]?body=SIGNOFF%20openmrs-implement-l]



_________________

University Computing Centre - 'Professionalism, Customer Care and Technological foresight'

_________________________________________

To unsubscribe from OpenMRS Implementers' mailing list, send an e-mail to [hidden email] with "SIGNOFF openmrs-implement-l" in the  body (not the subject) of your e-mail.

[mailto:[hidden email]?body=SIGNOFF%20openmrs-implement-l]

[hidden email] from OpenMRS Implementers' mailing list