How to keep custom settings in Open EMR but delete all patient records?

Day-to-Day Users
#1

Hello. Is there a way to keep the custom settings but delete the entire patient database? I can only see how to delete one patient at a time? Thanks.

#2

You can truncate the patient_data table. That should work. Things like the insurance_data, billing_table, etc all have counters so truncating the patient_data table shouldn’t cause too many issues .

#3

You really don’t want abandoned data floating around.
Suggest you check out the interface/patient_file/deleter.php script. Recm modifying a cloned version to batch delete patients by iterating from top level of script.
Then go back into database and reset you auto increment columns or truncate the appropriate tables.

1 Like
#4

Thanks, Daniel. Can you please explain how to truncate the patient_data table?

#5

Thanks, Jerry. Can you please point to me where I can find that script and how to run it?

#6

I have a few questions.
How many encounters are associated with the patients? Do you want to delete the encounters too?
Do you have insurance data associated with these patients?
Do you care about any of the data associated with the patients?

#7

@sjpadgett offered great advice with checking out the interface/patient_file/deleter.php script which could be called by a simple script to loop through all patients in the db.

#8

Thanks. I have 9000 patients with many encounters each. I have no insurance information and no data with any of the patients that I want to save. Basically, we have another doctor starting in another clinic, and I want to create a blank database for her using the customized settings that were created for us in our current clinic.

#9

Can you elaborate when you refer to “customized settings”? Are you referring to just the Administration-> Global settings or are you referring to custom Layout Based Forms, forms, the address book, users, etc.?

The deleter.php script does take care of most the tables related to patients, but may be incomplete for what you are looking for. In the openemr_600 database there are 77 tables that store pid values, but not every table that has patient data has a pid column (i.e. lbf_data).

A few of the tables that are missed by the deleter.php script (I searched the script to find these strings and could not find them):
amc_misc_data
amendments
ac_activity
audit_master
ccda
…

and there are quite a few more.

I think the following should be through enough:

  1. Query the patient data.
  2. For each pid, run the deleter script.
  3. Truncate the remaining tables that contain a reference to pids. (Note: some tables have columns like ct_pid, pc_pid, etc.)
  4. There are tables that hold the next value of things like encounters and such. It might not be necessary to reset these, but maybe Jerry or Stephen can elaborate.

@stephenwaite @sjpadgett - Is there anything that I am missing here?

#10

Actually, I am not entirely sure as an IT consultant set up our system so I don’t know exactly what he customized. So I am thinking of at least the global settings and the layout based forms. I don’t need the address book nor users.

Sorry I am a very basic user. I have no idea how to run the deleter.php script. Can you explain how to do that or point me to a site? Thanks.

#11

I think that Jerry and Stephen (and myself) assumed that you are a developer that has experience with php or mySQL. Currently I’m not aware of a script that will do exactly what you are looking for so there really isn’t an easy button for this task.

Are you looking for the version of OpenEMR with an empty database to be used in a production environment or is this just for training purposes? If this is being used for training purposes I can write a quick set of instructions that will get you what you need with no guarantee that data issues won’t happen in the future. This solution should not ever be used in Production.

If this instance of OpenEMR will be used in a production environment it will need more attention to detail and coding will be required, probably 4 - 6 (max) hours of coding and testing.

1 Like
#12

Thanks, Daniel, just need a quick set of instructions to create an empty database for training. Thank you.

#13

Tom,

Do you have access to phpMyAdmin? This would be easiest. Also, are you running this on LAMP or XAMP?

#14

Before reading this, make a backup of your current database.

Once you get a second instance of OpenEMR going, the following instructions should be fine for training purposes only. Here are a few things that this does:

  1. We remove all patient demographics, billing, and insurance data.
  2. We remove all form data and encounter data. The encounter increment is not reset to prevent unexpected behavior.
  3. We remove all calendar events associated with patients, keeping things like staff schedules.
  4. We load the sample_patient_data.sql info using the info provided. The current sample_patient_data.sql file has issues. This should work for you.

When you have confirmed that you are in the training database, you can put the following in phpMyAdmin screen. If you have not yet made a backup, read the first line of this post.
copy and paste in its entirety everything below this line.

Truncate table patient_data;
truncate table insurance_data;
truncate table billing;
truncate table form_encounter;
delete from openemr_postcalendar_events where pc_pid > 0;

INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Ms.’, ‘english’, ‘’, ‘Farrah’, ‘Rolle’, ‘A.’, ‘1973-10-11’, ‘111 Main Street’, ‘92101’, ‘San Luis’, ‘CA’, ‘’, ‘456789123’, ‘’, ‘(619) 555-2222’, ‘(619) 555-3333’, ‘(619) 555-1111’, ‘’, ‘married’, ‘Joe’, ‘2003-11-17 12:06:00’, ‘Female’, ‘Phil Martin’, ‘’, NULL, ‘frolle@pennfirm.com’, ‘Latina’, ‘’, ‘’, ‘3’, ‘3200’, ‘’, ‘2021-01-01 00:00:00’, ‘10’, 5,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2)VALUES ( ‘Mr.’, ‘english’, ‘’, ‘Ted’, ‘Shaw’, ‘’, ‘1947-03-11’, ‘222 1st Avenue’, ‘92101’, ‘San Diego’, ‘CA’, ‘’, ‘920-24-2256’, ‘Landscape Architect’, ‘(619) 555-7568’, ‘(619) 555-9867’, ‘(619) 555-7567’, ‘’, ‘married’, ‘Marion Shaw’, ‘2003-11-17 13:47:42’, ‘Male’, ‘Phil Martin’, ‘’, NULL, ‘info@pennfirm.com’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘2021-01-01 00:00:00’, ‘24555’, 1,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mrs.’, ‘english’, ‘’, ‘Eduardo’, ‘Perez’, ‘’, ‘1957-01-09’, ‘789 Third Avenue’, ‘’, ‘San Diego’, ‘CA’, ‘’, ‘234567891’, ‘Manager of Transportation’, ‘(619) 555-4859’, ‘(619) 555-7821’, ‘(619) 555-7823 x251’, ‘’, ‘’, ‘’, ‘2004-01-20 15:43:30’, ‘Male’, ‘Ynez Jones’, ‘’, 4, ‘info@pennfirm.com’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘2021-01-01 00:00:00’, ‘789456’, 4,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mrs.’, ‘spanish’, ‘’, ‘Nora’, ‘Cohen’, ‘’, ‘1967-06-04’, ‘155 First Avenue’, ‘92101’, ‘San Luis’, ‘CA’, ‘’, ‘891-23-4567’, ‘’, ‘(213) 555-5555’, ‘(213) 555-4444’, ‘(818) 555-5555’, ‘’, ‘married’, ‘’, ‘2004-01-16 10:28:22’, ‘Female’, ‘Phil Martin’, ‘’, 5, ‘’, ‘Latina’, ‘’, ‘’, ‘3’, ‘1500’, ‘’, ‘2021-01-01 00:00:00’, ‘8’, 8,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mr.’, ‘english’, ‘’, ‘Brent’, ‘Perez’, ‘’, ‘1960-01-01’, ‘1234 1st Avenue’, ‘92101’, ‘San Diego’, ‘CA’, ‘’, ‘123456789’, ‘Airline Mechanic’, ‘(619) 696-5050’, ‘(888) 480-5050’, ‘(619) 696-5050’, ‘’, ‘married’, ‘Maria Perez’, ‘2004-01-14 17:30:09’, ‘Male’, ‘Phil Martin’, ‘’, 5, ‘bperez@pennfirm.com’, ‘Latino’, ‘’, ‘’, ‘3’, ‘2200’, ‘’, ‘2004-04-02 00:00:00’, ‘1001’, 41,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mr.’, ‘’, ‘’, ‘Jim’, ‘Moses’, ‘’, ‘1945-02-14’, ‘’, ‘’, ‘Los Angeles’, ‘CA’, ‘’, ‘545676767’, ‘’, ‘(323) 555-4444’, ‘(909) 555-6767’, ‘(310) 555-1212’, ‘’, ‘’, ‘’, ‘2004-01-15 13:45:08’, ‘Male’, ‘Administrator Administrator’, ‘’, 1, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘2021-01-01 00:00:00’, ‘17’, 17,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mr.’, ‘’, ‘’, ‘Richard’, ‘Jones’, ‘’, ‘1940-12-16’, ‘400 West Broadway’, ‘92101’, ‘San Diego’, ‘CA’, ‘’, ‘555-11-1111’, ‘’, ‘(619) 555-5555’, ‘(619) 333-3333’, ‘(619) 444-4444’, ‘’, ‘married’, ‘’, ‘2004-01-16 10:02:41’, ‘’, ‘Ynez Jones’, ‘’, 4, ‘richard@pennfirm.com’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘2021-01-01 00:00:00’, ‘18’, 18,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mr.’, ‘english’, ‘’, ‘Ilias’, ‘Jenane’, ‘’, ‘1933-03-22’, ‘145 N. East Street’, ‘92111’, ‘La Mesa’, ‘CA’, ‘’, ‘111-11-2222’, ‘retired - school teacher’, ‘(619) 555-2222’, ‘(619) 555-3333’, ‘(619) 555-1111’, ‘’, ‘single’, ‘’, ‘2004-01-16 10:23:47’, ‘Female’, ‘Ynez Jones’, ‘’, 4, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘2021-01-01 00:00:00’, ‘22’, 22,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mr.’, ‘english’, ‘’, ‘Jason’, ‘Binder’, ‘’, ‘1961-12-11’, ‘100 West Sepulveda’, ‘92020’, ‘Los Angeles’, ‘CA’, ‘’, ‘222-11-8888’, ‘Real Estate Agent’, ‘’, ‘’, ‘’, ‘’, ‘single’, ‘’, ‘2004-01-16 10:13:50’, ‘Male’, ‘Ynez Jones’, ‘’, 4, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘2021-01-01 00:00:00’, ‘30’, 30,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mr.’, ‘english’, ‘’, ‘John’, ‘Dockerty’, ‘D’, ‘1977-05-02’, ‘800 West Way’, ‘92101’, ‘San Diego’, ‘CA’, ‘’, ‘111-11-1111’, ‘Physical therapist’, ‘(567) 789-7891’, ‘(567) 789-1234’, ‘(567) 567-5678’, ‘’, ‘single’, ‘’, ‘2004-01-16 10:21:05’, ‘’, ‘Phil Martin’, ‘’, 5, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘2021-01-01 00:00:00’, ‘25’, 25,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mr.’, ‘english’, ‘’, ‘James’, ‘Janssen’, ‘’, ‘1966-04-28’, ‘111 North Street’, ‘90205’, ‘Irvine’, ‘CA’, ‘’, ‘555-11-1111’, ‘Office Manager’, ‘(818) 555-2222’, ‘(818) 555-2323’, ‘(5555) 555-1111’, ‘’, ‘single’, ‘’, ‘2004-01-19 14:21:25’, ‘Male’, ‘’, ‘’, 1, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘2021-01-01 00:00:00’, ‘26’, 26,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mr.’, ‘english’, ‘’, ‘Wallace’, ‘Buckley’, ‘’, ‘1952-04-03’, ‘123 West Street’, ‘90400’, ‘Barstow’, ‘California’, ‘’, ‘999-99-1111’, ‘Accountant’, ‘(555) 111-1111’, ‘(555) 111-1234’, ‘(555) 555-5555’, ‘’, ‘domestic partner’, ‘Mike Hart’, ‘2004-01-16 10:18:28’, ‘’, ‘Ynez Jones’, ‘’, 4, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘2021-01-01 00:00:00’, ‘40’, 40,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mr.’, ‘english’, ‘’, ‘Robert’, ‘Dickey’, ‘’, ‘1955-04-12’, ‘111 North Kearny’, ‘91040’, ‘Torrance’, ‘CA’, ‘’, ‘214-22-1111’, ‘Project Manager’, ‘(808) 555-1234’, ‘(808) 555-4444’, ‘(808) 555-1111’, ‘’, ‘married’, ‘’, ‘2004-01-16 10:31:45’, ‘’, ‘Phil Martin’, ‘’, 5, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘2021-01-01 00:00:00’, ‘34’, 34,’’,’’,’’,’’);
INSERT INTO patient_data
(title, language, financial, fname, lname, mname, DOB, street, postal_code, city, state, drivers_license, ss, occupation, phone_home, phone_biz, phone_contact, phone_cell, status, contact_relationship, date, sex, referrer, referrerID, providerID, email, ethnoracial, interpretter, migrantseasonal, family_size, monthly_income, homeless, financial_review, pubpid, pid, genericname1, genericval1, genericname2, genericval2) VALUES ( ‘Mr.’, ‘english’, ‘’, ‘Jillian’, ‘Mahoney’, ‘’, ‘1968-08-11’, ‘444 North State Street’, ‘90204’, ‘Santa Ana’, ‘CA’, ‘’, ‘222-11-1111’, ‘’, ‘(808) 555-4444’, ‘(808) 555-3333’, ‘(808) 555-5555’, ‘’, ‘married’, ‘’, ‘2004-01-19 12:14:06’, ‘Female’, ‘Ynez Jones’, ‘’, 4, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘2021-01-01 00:00:00’, ‘35’, 35,’’,’’,’’,’’);

#15

Thanks, Daniel. Yes, I do have access to phpMy Admin. And I am running this on XAMP. I am going to need some time to work through your instructions-thank you very much.