Auto Populate Fields

[Oirish]

Hi All,

 

This is my first post on the forum, but I've been hammering my head off the table the last few days trying to get this working.

 

System: Patient database

 

Rough Concept:  The data on each patient can change with each presentation (i.e. they may have stopped a particular medication etc.) I have created a table (admission DB) that is unique for each admission and auto populates with certain data from another table (Patient DB) about the patient once their PatientID is entered (Name, Sex, martial status, DOB etc.). 

 

I created an investigation ordering system (Another table called Investigations DB) which is related to the admission table via PatientID. When you select an investigation from the drop down list in Admission DB is goes to the Investigation DB and creates a new record (investigation episode) and the data is meant to auto populate the order form to save time.

 

The investigation history is viewed as a portal in the admission layout - so we can see all the scans a patient has had

 

Problem: Old data populating new order fields

 

Example:

 

John Test attended in January 2014, was on bisoprolol 2.5mg. Its his first visit and we order an Echocardiogram (ECHO). I create a new episode in the admission DB and select ECHO in the investigation drop down list. The form auto fills in his information including his current medications, reason for scan etc. John buggers off home after the scan.

 

John returns to the clinic in September, his family doctor has changed his medication and he is now on a dose of 5mg. John needs another ECHO. I create a new Admission episode in the Admission DB, saying he is now on 5mg bisoprolol. I go to the investigation section and choose ECHO. However the form auto populates with the information from his first admission (i.e. Bisoprolol 2.5mg)

 

Attempted Solutions: (and I've tried many)

1. Script to copy medications from Admission DB (Can be up to 20 per patient) and paste into fields in Investigation DB. Worked but very cumbersome.
2. Tried another unique identifier that copies from the admission DB (admission ID) into the investigation ID - however this messes up the portal view

I would be extremely grateful if someone/anyone had any ideas on where I'm going wrong. 

 

Sorry about all the etc.

 

Many Thanks

[comment]

(Another table called Investigations DB) which is related to the admission table via PatientID

 

Here's your problem. If Investigations are a child of Admissions, then the relationship must be based on AdmissionID. By using PatientID you end up with an Investigation being related to all of the patient's Admissions - and the auto-enter uses data from the first related record (that means first in creation order, unless the relationship is sorted).

[Oirish]

Members,

 

Many thanks for the quick reply (I owe you a pint if ever I meet you)

 

I tried the solution, I created a new field in the Admission DB (Admission ID) which automatically generates a number when creating a new admission. When I go to the investigation section in the Admission DB and select the desired examination and submit the request, a script runs which roughly does the following:

 

If (exam type = ECHO)

Copy Admission DB::AdmissionID

Go to Layout ECHO

Create new record

Paste AdmissionID

 

This works great, the medications are correct etc and when I return to the main table I can see a brief order summary in the portal. Happy days

 

BUT (as there is always a 'but')

 

I am only seeing the orders for this admission, whereas before I was able to view orders for all previous admissions. I tinkered a bit with the portal filter:

 

1. I set the filter to Admission DB::admissionID = InvestigationDB::AdmissionID - as you would expect this will only display the matching admission ID's and not the previous also
2. I set the filter to Admission DB::PatientID = InvestigationDB::PatientID (even though no relationship exists between the tables via patientID) and same result
3. I set the filter to admissionID and PatientID and it displayed no results at all.

I'd like to get this feature working as being able to view all scans ordered for the patient (both current and previous admissions) would be a great time (and money) saver as it would prevent unnecessary reordering of scans. 

 

Any ideas would once again be appreciated and thanks a million for taking the time to help

 

Oirish

[comment]

A point of good practice: instead of copy and paste, use Set Variable[] and Set Field[]. There are several reasons why that's better, one of them is that the user's clipboard is not wiped out in the process.

 

Re the other point, I am a bit confused: are "orders" the same thing as "investigations"? If so, and you want to have a portal on the Admissions layout showing all patient's Investigations, you will need another relationship between the two tables (using another occurrence of the Investigations table). This relationship can be based on matching PatientID - provided you populate a PatientID field in the Investigations table, either as part of your script, or by a lookup.

 

--

P.S. What you call "filter" is a predicate. We use the term filter to describe either adding another predicate to an existing relationship ("filtering a relationship") or setting a portal to show only some of the related records ("filtering a portal").

[Oirish]

Members,

 

Once again many thanks for replying. I added the second occurrence in the table and it all seems to be working well , so a huge thank you to you.

 

Apologies for my misuse of 'filter', the terminology sounds right in my head but obviously can lead to confusion. 

 

Can't thank you enough for your help.

 

All the best.

 

Oirish