Hi Everyone please i need help as we used to record our patient like ACH0001 and so on. We want to import the the data with csv to openemr DB , please help to that these will reflect both on the patient dashboard and in all as the same.
Reference the images before and what i want.
Hello Natt
You can put the desired value in âExternal IDâ field in the demographics WHO section.
External ID value is displayed at top near patient name.
Hope this helps.
Thank You
Infeg Team (services@infeg.com)
1 Like
thanks, but what to do because there is no external ID in the database because i have to import over 1000 patients,
Database column name used in OpenEMR for External Id is - âpubpidâ
1 Like
under patient_data there is no pubpid it only has , âIDâ and uuid.
So which table to find it
i dont know which colum to find the pubpid in the database
pubpid is there in patient_data table. Highlighted below
mysql> desc patient_data;
±---------------------±-------------±-----±----±---------±---------------+
| Field | Type | Null | Key | Default | Extra |
±---------------------±-------------±-----±----±---------±---------------+
| id | bigint(20) | NO | MUL | NULL | auto_increment |
| uuid | binary(16) | YES | UNI | NULL | |
| title | varchar(255) | NO | | | |
| language | varchar(255) | NO | | | |
| financial | varchar(255) | NO | | | |
| fname | varchar(255) | NO | | | |
| lname | varchar(255) | NO | | | |
| mname | varchar(255) | NO | | | |
| DOB | date | YES | | NULL | |
| street | varchar(255) | NO | | | |
| postal_code | varchar(255) | NO | | | |
| city | varchar(255) | NO | | | |
| state | varchar(255) | NO | | | |
| country_code | varchar(255) | NO | | | |
| drivers_license | varchar(255) | NO | | | |
| ss | varchar(255) | NO | | | |
| occupation | longtext | YES | | NULL | |
| phone_home | varchar(255) | NO | | | |
| phone_biz | varchar(255) | NO | | | |
| phone_contact | varchar(255) | NO | | | |
| phone_cell | varchar(255) | NO | | | |
| pharmacy_id | int(11) | NO | | 0 | |
| status | varchar(255) | NO | | | |
| contact_relationship | varchar(255) | NO | | | |
| date | datetime | YES | | NULL | |
| sex | varchar(255) | NO | | | |
| referrer | varchar(255) | NO | | | |
| referrerID | varchar(255) | NO | | | |
| providerID | int(11) | YES | | NULL | |
| ref_providerID | int(11) | YES | | NULL | |
| email | varchar(255) | NO | | | |
| email_direct | varchar(255) | NO | | | |
| ethnoracial | varchar(255) | NO | | | |
| race | varchar(255) | NO | | | |
| ethnicity | varchar(255) | NO | | | |
| religion | varchar(40) | NO | | | |
| interpretter | varchar(255) | NO | | | |
| migrantseasonal | varchar(255) | NO | | | |
| family_size | varchar(255) | NO | | | |
| monthly_income | varchar(255) | NO | | | |
| billing_note | text | YES | | NULL | |
| homeless | varchar(255) | NO | | | |
| financial_review | datetime | YES | | NULL | |
| pubpid | varchar(255) | NO | | | |
| pid | bigint(20) | NO | PRI | 0 | |
| genericname1 | varchar(255) | NO | | | |
| genericval1 | varchar(255) | NO | | | |
| genericname2 | varchar(255) | NO | | | |
| genericval2 | varchar(255) | NO | | | |
| hipaa_mail | varchar(3) | NO | | | |
| hipaa_voice | varchar(3) | NO | | | |
| hipaa_notice | varchar(3) | NO | | | |
| hipaa_message | varchar(20) | NO | | | |
| hipaa_allowsms | varchar(3) | NO | | NO | |
| hipaa_allowemail | varchar(3) | NO | | NO | |
| squad | varchar(32) | NO | | | |
| fitness | int(11) | NO | | 0 | |
| referral_source | varchar(30) | NO | | | |
| usertext1 | varchar(255) | NO | | | |
| usertext2 | varchar(255) | NO | | | |
| usertext3 | varchar(255) | NO | | | |
| usertext4 | varchar(255) | NO | | | |
| usertext5 | varchar(255) | NO | | | |
| usertext6 | varchar(255) | NO | | | |
| usertext7 | varchar(255) | NO | | | |
| usertext8 | varchar(255) | NO | | | |
| userlist1 | varchar(255) | NO | | | |
| userlist2 | varchar(255) | NO | | | |
| userlist3 | varchar(255) | NO | | | |
| userlist4 | varchar(255) | NO | | | |
| userlist5 | varchar(255) | NO | | | |
| userlist6 | varchar(255) | NO | | | |
| userlist7 | varchar(255) | NO | | | |
| pricelevel | varchar(255) | NO | | standard | |
| regdate | datetime | YES | | NULL | |
| contrastart | date | YES | | NULL | |
| completed_ad | varchar(3) | NO | | NO | |
| ad_reviewed | date | YES | | NULL | |
| vfc | varchar(255) | NO | | | |
| mothersname | varchar(255) | NO | | | |
| guardiansname | text | YES | | NULL | |
| allow_imm_reg_use | varchar(255) | NO | | | |
| allow_imm_info_share | varchar(255) | NO | | | |
| allow_health_info_ex | varchar(255) | NO | | | |
| allow_patient_portal | varchar(31) | NO | | | |
| deceased_date | datetime | YES | | NULL | |
| deceased_reason | varchar(255) | NO | | | |
| soap_import_status | tinyint(4) | YES | | NULL | |
| cmsportal_login | varchar(60) | NO | | | |
| care_team_provider | text | YES | | NULL | |
| care_team_facility | text | YES | | NULL | |
| care_team_status | text | YES | | NULL | |
| county | varchar(40) | NO | | | |
| industry | text | YES | | NULL | |
| imm_reg_status | text | YES | | NULL | |
| imm_reg_stat_effdate | text | YES | | NULL | |
| publicity_code | text | YES | | NULL | |
| publ_code_eff_date | text | YES | | NULL | |
| protect_indicator | text | YES | | NULL | |
| prot_indi_effdate | text | YES | | NULL | |
| guardianrelationship | text | YES | | NULL | |
| guardiansex | text | YES | | NULL | |
| guardianaddress | text | YES | | NULL | |
| guardiancity | text | YES | | NULL | |
| guardianstate | text | YES | | NULL | |
| guardianpostalcode | text | YES | | NULL | |
| guardiancountry | text | YES | | NULL | |
| guardianphone | text | YES | | NULL | |
| guardianworkphone | text | YES | | NULL | |
| guardianemail | text | YES | | NULL | |
| sexual_orientation | text | YES | | NULL | |
| gender_identity | text | YES | | NULL | |
| birth_fname | text | YES | | NULL | |
| birth_lname | text | YES | | NULL | |
| birth_mname | text | YES | | NULL | |
| dupscore | int(11) | NO | | -9 | |
| name_history | tinytext | YES | | NULL | |
| suffix | tinytext | YES | | NULL | |
| street_line_2 | tinytext | YES | | NULL | |
| patient_groups | text | YES | | NULL | |
| prevent_portal_apps | text | YES | | NULL | |
| provider_since_date | tinytext | YES | | NULL | |
| created_by | bigint(20) | YES | | NULL | |
| updated_by | bigint(20) | YES | | NULL | |
| oth_mile | text | YES | | NULL | |
±---------------------±-------------±-----±----±---------±---------------+
@Infeg_Services Can you tell me the steps to do. because i have enabled the external ID in the demographics , and i entered the number but when i watched the DB it gives user ID 0.
So what to do to achieve this ?
Natt
The external ID field can be used to track your physical patient ID. And you can see that at active patient widget at top panel. The ID in the patient_data table is unique identifier of that table and that is used in whole application to identify the paitent. So we should not disturb that. Instead you can use pubpid for your reference with physical record of the patient. Use can also search patient using pubpid in UI.
We couldnt understand what exactly you are trying to get clarified. How you are importing the data to the database? do you have a script?
Can you please explain more. So it will be helpful for the people here in community - to point you to the possible ways to achieve.
Thank You
Infeg Team (services@infeg.com)
@Infeg_Services thanks very much, what i need i want to import our patient DB which is in csv which we want to bring to mysql database. But if we import that in the current sql database there is no Colum if we want to import our patient DB to the current DB. If there is a Colum for external ID in the DB it will be simple
Hi @Natt_SL_Limited
Congratulations, looks like you found a neat hack to import records and give them their PubPID. But once you have them imported you will need to set up the EMR to automatically assign them in the proper format for each new patient.
Just in case you havenât done that yet, this wiki page has the steps to do that.
https://www.open-emr.org/wiki/index.php/Quickie_External_ID_Mask_Tutorial
Best of the Holiday season to you.
- Harley
1 Like
@htuck Thanks very much. What i did was to first export the current DB Sql in csv and i used it as template to insert all the patients and include all the ID extensions like ACH001,ACH002 and so on as the PUBID and i dropped all the tables and import the new sql. All works fine. Now i am seeking how i can make the external ID to autogenerate.
Hi @Natt_SL_Limited
Yes, the process described in the wiki page will take the mask you create and autogenerate the pubPID from that point on.
- HT