Why do you want to include the initials? What problem does that solve for you that a simple auto-entered serial number plus the patient's full name does not?
Fullname is not necessary and can be very long in some instances. Also, there is Hipaa compliance and its better to not have a fullname as a chart name (especially if being emailed, etc). Maybe I will go with the first 3 letters of the last and 2 letters of the first. My main concern is that I set this up correctly, not too worried about the naming convention at the moment. I used a text field, with an auto enter calc using the serialincrement function for this. Not sure if this is the best method, haven't been able to test it much.
The auto-entered serial number won't produce a series of values that starts over at one for each patient. Is that an issue?
What you describe can be done and as long as you don't need the values to be unique to any given patient, the letters you choose to include won't make much difference in terms of database function one way or another. But getting the series to start at 1 for each patient with no gaps takes a bit of work--especially in a database where multiple users might be adding new records for new charts all at the same time.
Thanks. People entering new records for the same patient shouldn't be an issue because a patient only gets a new case every so often.The auto-entered serial number won't produce a series of values that starts over at one for each patient. Is that an issue?Yes, this will be an issue because my goal is to have this auto entered chart number be used when users enter new cases into another system (an EMR). So each pt would have multiple records at some point, say, JDO1, JDO2, JDO3 for the 3 charts within John Doe's chart. I'd like it to match what is in the EMR and also make sense.
"shouldn't be" isn't the point. If it is even remotely possible, and it is as far as I can tell, then steps need to be taken to be sure that it doesn't happen--especially since these are part of a patient's medical record.
As long as JD001 for John Doe and JD001 for Jim Denver does not create a problem...
You can set up a relationship from your patients table to a "Charts" table:
Patient::__pkPatientID = Charts::_fkPatientID
Then this script can create a new Charts record and assign a chart number to it:
Set Variable [$PatientID ; value: Patient::__pkPatientID ]
Set Variable [$ChartNo ; value: Left(Patient::FirstName ; 1 ) & Left ( Patient::Lastname ; 1 ) & Right ( "000" & Max ( Charts::ChartNumber ) ; 3 )]
Go to Layout [Charts]
Set Field [Charts::_fkPatientID ; $PatientID]
Set Field [Charts::ChartNumber ; $ChartNo ]
Go to Layout [original layout]
To ensure against two users simulaneously generating a chart record for the same patient and gettting identical chart numbers, add a text field to Charts, ValidChartNo, with this auto-enter calculation:
_fkPatientID & "|" & ChartNo
Put a unique values validation rule on this field. Then if the very unlikely happens, one of the two users will get this error message and they can revert the record and try again.
EP, I've been writing medical record dbs for a long time and I have never run into a situaltion that requires anything other than one unique ID number per patient, and it always accompanies the patient for life. In those cases where we grandfathered an existing system into a newer one the patient ID stayed the same. Now every sub record, lab tests, visits, etc, gets a unique record ID and timestamp that is accompanied by the patient's ID.
Taking the intitials and numbering for John Doe, JD001, fails as soon as Jerry Davis and June Dugger become a patients, and what will you do when June gets married to Jason Smith?
FMP allows you to start with a really big serial number and increment it for every new patient and keep the same ID number forever.
Also I don't believe that HIPAA considers email safe for patient info, but FAXs are OK. Go figure.
Thanks for all the great feedback. My situation is a bit different, as the patient isn't being assigned the unique ID, rather the "case" is being assigned an ID. For example, John Doe begins a new "case" with the medical office. This case would be given an ID. If John Doe comes back in a year, a new case would be opened (sparate chart from the old one). Ths is for a rehab facility that keeps a new chart per "case." So even if I scrap the initials idea, a new number would still need to be assigned "per case" even if it is for an existing patient.
Ok, after testing I am convinced (I guess I had to find out the hard way). Here is what I've come up with:
-An auto enter serial starting at 00001 and incrementing by 1.
-I want to add the region in front of the number. For example PA00001 for a pennsylvania client. I am considering using a calc field that references the 2 letter designation of the patients address (PA in this case) and slapping it in front of the actual serial as a merge field. This way the serial is purely meaningless, but the user will see the meaningful letters before it.
Is this a better way to go?
Try to consider this from another perspective. Your scenario is no different than a basic Customer>Invoice<Products data model. Three tables in its most basic form. In this case the customer is the patient, you will have one table that holds the customer information that is available for reuse if needed, the invoice is the "new case" and the products will be what ever services comprise this case. Each invoice/case will have its own ID number that will differentiate it from the others. There is no reason to have to reenter the patient information every time they have a new case, and given the foibles of human data entry many not to. If a patient never returns so be it, but if they have multiple cases you need only pull up their ID number and their information in good to go. Staff will appreciate this point, no one likes duplicating previously entered data.
Although the invoice/case record requires a unique serial number, you could have a field that auto increments for that patient based on the number of case records already in the file.
If you are not familiar with this data model, search the forum, Philmodjunk has a link to a very basic FMP file that demonstrates it perfectly.
Thanks Bumper. This is exactly what I want to do. I should have elaborated more on my current setup. I have a Parent key for patients and foreign key for cases, linking each case to the appropriate patient. I also want to create a serial for each case, to identify each case and this can also be the parent key for other functions/relationships if needed. What I've come up with is (from my earlier post):
Hi E P
I am not a developer, I am in the medical profession as well, I am building uopn a database for our facility use that I inherited, my predecessor began building it. It uses anchor bouy format...which I didn't realize right away, but I didn't know any different, and I think if you gave it a look, it might make some sense. I think I am understanding your perceived dilemma, because I had the same hard time trying to understand the format. I kept thinking tha everything had to related back to the client.... I had a very hard time understanding the relationships between the tables....and I still do. But all of a sudden it started to sink in.... Bumper is absolutely right, each patient is assigned a unique ID that follows them for life. And if you have a serial number key on your "case" table, every case will have its own unique identifier as well. I'm not understanding why you need the initials or the state abbreviations ..I'm thinking that you are trying to wrap your head around how these things link together... I think I understand what you mean about needing a "new case" each time but it most certainly is not the standard of practice in the US at least, to treat every patient as if they had never been there. ( I know that is not what you are doing but it is what you are describing) Medical histories are updated, but you need to start there..find the patient and add cases to the patient. I'm pretty sure you wouldn't have a situation where each "case " could have many patientsWe do a brand new admission for each of our patients , opening a new "case" each time, and many of them have been admitted many times so they have many cases, my agency has a sister affliation with a rehab also... we are in the same boat. You really don't need to "come up with" a numbering system, it is all in how you link the tables.
Sorbsbuster was kind enough to upload a sample database for me. I think you could still download it and study how he set it up. http://www.4shared.com/file/gTtuMbd9/ClientVisits.html . I read some other posts of yours (I think they were yours) related to serial numbers., which is why I think you are struggling with this..I'm not at all versed in the anchor bouy method, but as I have expanded our databse It is starting to come together. It's like each table has the opportuniy to act like the parent table ...with other table occurances for other relationships.. Stop thinking so hard about it, it will begin to make sense. Also google "six fried rice method" and some articles will come up.