Change External ID start #

system · October 15, 2013, 7:40pm

hamdpa wrote on Tuesday, October 15, 2013:

Hi to everyone,

Using OpenEMR 4.1.2 (1) appliance.
I am trying to change the start # for new patients from 1 to 11275. Tried following the instructions in this thread (http://sourceforge.net/p/openemr/discussion/202505/thread/5919a3e0/#b7fb) by going to administration/other/database/ then phymyadmin page 6 patient_data/pid/change, then change default from 0 to 11275, tried this on 4.1.1 demo, but its not working.

What am I doing wrong?

Thank you,

Henry

system · October 15, 2013, 8:24pm

mdsupport wrote on Tuesday, October 15, 2013:

You should reset pid default back to its prior value and try the next suggestion in the thread.

system · October 15, 2013, 8:47pm

hamdpa wrote on Tuesday, October 15, 2013:

Hi MD Support,

Which field do you put “ALTER TABLE PATIENT_DATA AUTO_INCREMENT = 17500;” into the extra field does not allow a change. Does it go into default? Is the semicolon included?

Thank you,

Henry

system · October 15, 2013, 10:47pm

mdsupport wrote on Tuesday, October 15, 2013:

No field specification is permitted.

http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

system · October 16, 2013, 12:48am

hamdpa wrote on Wednesday, October 16, 2013:

Hi MD Support,

I’m sorry, I don’t understand your answer. I believe your saying that the field can’t be changed. If that is so, does that mean there is no solution to this, other than letting the system assign an incorrect # then going back and editing it?

Thank you,

Henry

system · October 16, 2013, 2:53am

mdsupport wrote on Wednesday, October 16, 2013:

You cannot specify the field because mySQL allows only one field to have that property.

system · October 16, 2013, 3:11am

yehster wrote on Wednesday, October 16, 2013:

The next pid and also pubpid (external id) is generated based on the current max pid in the patient_data table

github.com

openemr/openemr/blob/master/interface/new/new_patient_save.php#L24


}


require_once("$srcdir/pid.inc");
require_once("$srcdir/patient.inc");


//here, we lock the patient data table while we find the most recent max PID
//other interfaces can still read the data during this lock, however
sqlStatement("lock tables patient_data read");


$result = sqlQuery("select max(pid)+1 as pid from patient_data");


// TBD: This looks wrong to unlock the table before we have added our
// patient with its newly allocated pid!
//
sqlStatement("unlock tables");
//end table lock
$newpid = 1;


if ($result['pid'] > 1) {
$newpid = $result['pid'];
}

github.com

openemr/openemr/blob/master/interface/new/new_patient_save.php#L43


$newpid = $result['pid'];
}


setpid($newpid);


if ($pid == null) {
$pid = 0;
}


// what do we set for the public pid?
if (isset($_POST["pubpid"]) && ($_POST["pubpid"] != "")) {
$mypubpid = $_POST["pubpid"];
} else {
$mypubpid = $pid;
}


if ($_POST['form_create']) {
$form_fname = ucwords(trim($_POST["fname"]));
$form_lname = ucwords(trim($_POST["lname"]));
$form_mname = ucwords(trim($_POST["mname"]));

So if you change the pid of an existing row in the patient_data table to 11275 (using phpmyadmin or MySQL Workbench), the next new patient will have the value 11276 for pid and pubpid (external id).

I will defer on explaining in detail why changing the default value didn’t work.

system · October 16, 2013, 3:16am

blankev wrote on Wednesday, October 16, 2013:

Sorry to interfere, but I just tried this on Demo V4.1.1 and V4.1.2 it takes a new number for ID, but does not start with incrementing, but goes back to the old last registered ID.

In V1.1.1 it was number 5. Probably have to clear Coockies or another file that registers the last ID too.

system · October 16, 2013, 3:27am

yehster wrote on Wednesday, October 16, 2013:

I suspect that you set the PUBPID to the large value, and not the pid.

system · October 16, 2013, 3:55am

yehster wrote on Wednesday, October 16, 2013:

Cookies/cache have nothing to do with this…

On the 4.1.2 demo, External ID is presently being generated from 12001. As I suspected, you had not forced the PID to a larger number, all you did was create a new patient with a big PUBPID.

system · October 16, 2013, 3:58am

yehster wrote on Wednesday, October 16, 2013:

The instructions in the other thread are completely incorrect because
PID and PUBPID are not created by autoincrement.

system · October 16, 2013, 4:00am

yehster wrote on Wednesday, October 16, 2013:

Actually Sherwin’s instructions in the other thread are correct, he saying the same thing I’m saying to do. The instructions about “ALTER TABLE” are what are wrong.

system · October 16, 2013, 5:52am

hamdpa wrote on Wednesday, October 16, 2013:

Hi Kevin,
I assume your saying that all I have to do is set the new patients # to 11275 and from then on it will sequentially give one # higher to each new patient. Is that correct?

Thank you,

Henry

system · October 16, 2013, 9:45am

blankev wrote on Wednesday, October 16, 2013:

the Ext-ID 12001 started after my wrong input 12000 and later correction to 11290 of the original Ext-ID. I also could see that the same Ext-ID is accepted.

Where is that number for sequence stored, if it is not a Cookie?

system · October 16, 2013, 10:11am

blankev wrote on Wednesday, October 16, 2013:

And to correct myself: The Ext-ID is taken from the PID or has to be changed manually.

You have to make the PID row in sequence starting with 11275 it gets the correct the Ext ID from PID.

Next new patient will follow the PID in the first field of the table, and if you are changing, make the first field KeyID also compatible with the other ID fields all in sequence plus 1 for every row starting with 11275 Good luck and perseverance.

I just corrected Demo V4.1.2.Patch2 and it takes off at next ID, PID and Ext-Id are the following somewhere after 11311

system · October 16, 2013, 4:19pm

yehster wrote on Wednesday, October 16, 2013:

Yes, but you must update the PID of the new patient using PhpMyAdmin. You cannot simply change the external id using OpenEMR’s interface. Emphasis on needing to do update the PID, and not the pubpid/external id.

system · October 16, 2013, 4:21pm

yehster wrote on Wednesday, October 16, 2013:

It’s stored in the PID column in the database per the code I referenced in github.
Incidentally, the ONLY cookie OpenEMR uses is a cookie to manage the Php session.

system · October 17, 2013, 3:19am

hamdpa wrote on Thursday, October 17, 2013:

Hi Pimm,

Which field in PID is the one that needs to be changed? Sorry you need to be so explicate.

Thanks,

Henry

system · October 17, 2013, 5:24am

mdsupport wrote on Thursday, October 17, 2013:

Hmm - did not know pid logic for new record. Anyway, to set the pid to your needs run this sql (assuming MAX(pid) is less than 11275:

UPDATE patient_data p JOIN (SELECT max( pid ) max_pid FROM patient_data) p2
SET p.pid =11274 WHERE p.pid = p2.max_pid

If the explanation above is correct, next pt will be 11275.

system · October 17, 2013, 9:11am

fsgl wrote on Thursday, October 17, 2013:

Go to 4.1.2 Demo/Database/page 5/patient_data, choose first patient, click Change, change ** both pubpid and pid** (changing just pid won’t work), click Go and check to see if changes are correct.

See attachments.

It is not necessary to change the id column as shown in the first attachment. My first attempt at this effort included changing all 3 columns; but I found out subsequently it is only necessary to change pubpid and pid.

As you can see, the next 2 patients had been assigned 11276 & 11277.

Wait until the Demo has reset, 6:31 am Pacific Time, and then experiment.

Did you get the scheduling problem resolved? Feedback is useful because it helps me know if the advice had been apt or off base.