Help finding best solution

[bmill]

Looking for best solution for a medical record application.

 

In an allergy office, patients have skin testing with a panel of allergens (bermuda grass, chocolate, etc).  The reaction on the skin to each allergen is measured and recorded as two variables, wheal size and erythema (redness).  

 

Each patient may have a full panel (up to 66 tests) or just a few, and each patient may be retested at a later date.

 

I have a table with the allergen list (about 100 records), a table of patients, and a results table with four measurement fields and test date, linked to the patient table. 

 

Results are first recorded on a paper chart and then entered into the DB. 

 

I have looked at two ways to record the data: 

    1.  Import the full allergen list into the results table, enter the data, and then delete items from the list that were not tested

    2.  Record data into the allergen table temporarily,   import each test into the results table as it is recorded (? how to do this seamlessly as data is entered), then clear the fields. 

 

To simplify data entry, I have a tab control portal filtering the allergen list by category (grass, weed, tree, food, etc),  sorted to match the written data.   

 

Any advise on which is the most efficient solution or any other ideas would be much appreciated.

 

Thanks!

 

 

 

 

 

[comment]

I believe you should have three tables: Patients, Allergens and Results, arranged as:

 

Patients -< Results >- Allergens

 

with the Results table having fields for:

• ResultID (auto-entered serial number);

• PatientID (link to the Patients table);

• AllergenID (link to the Allergens table);

• Date

• WhealSize

• Erythema

 

 

 

I have a table with the allergen list (about 100 records), a table of patients, and a results table with four measurement fields and test date, linked to the patient table.

 

Why four, when you said there are only two?

[bmill]

Thanks for the reply.  

 

I failed to mention that an additional two measurements using a stronger dose of allergen are sometimes made if the first result is negative, giving a total of four possible measurements for each pt. 

 

I have the tables and fields set up as you suggested.

 

I would like to find a way to enter the test results quickly using the keyboard  without having to to stop and activate a button to import measurements for each allergen id into the results table.  I tried using a field as a script trigger to activate import scrip and move to next portal row when you tab into it (see files,)  but this is a bit clunky.

 

  

fmp13.pdf

fmp13-2.pdf

[comment]

I am afraid I don't quite understand the issue:
 

I would like to find a way to enter the test results quickly using the keyboard  without having to to stop and activate a button to import measurements for each allergen id into the results table.

 

I thought you were entering the data from a paper form, so what is there to import?

[bmill]

My limited experience and knowledge is the problem and I it is likely that I  don't see the obvious. 

 

The allergen table is a rather large pick list (100+ items) and each allergen id is "imported" (maybe not the best term) from the list as a new record into the results table.

 

Not all patients have the same number of tests run.  The entire list of allergens can be imported into the results table from the allergen list table, measurements entered on tests that were run, and empty records deleted. I guess this would be the simplest method but creating 100 new records in the results table and then deleting the majority each time a few tests needed to be recorded seemed clumsy.

 

Another way would be to "pick" each allergen that was tested and move only these to the results table before entering the measurements, but that is a potentially time consuming step.

 

I imagined that there might be a way to do the "picking" and entering at the same time by filling in the measurements in the allergen table, like you would if you were using pen and paper.   When the measurements for a test were entered, tabbing into  the next record would trigger the movement of the recorded measurements to the results table while clearing the fields for the next entry.  A portal on the screen would show the test results for the patient as data is being entered.

 

A third way would be to enter the data into the allergen table and run a script to move only the recorded records to the results.  

 

Seems like a common data entry scenario and thought I would ask if there is a "best" way to do this sort of thing before getting too bogged down recreating the wheel.

 

Thanks again.

[comment]

Actually, the simplest method would be to create the records as you go: let the user go to the first empty row of a portal to Results, placed on a layout of Patients, select an allergen (they could narrow down the selection by typing the first few letters of the allergen and/or by selecting a group first), then enter the date and the results. The date could also be pre-entered into a global field, to save repetitive entry for a battery of results.

 

If - as it seems - you want to enter the results against a grid of all possible allergens, then you have two options:

 

1. Import all allergens into the Results table, and fill out those that have actual results and ignore the rest. It's not the most elegant solution, but it does work and it's relatively easy to set up.

 

2. Use the allergens in the Allergens table as the grid. This is a bit more difficult to set up, let me describe the simpler variant:

• Add two global fields, gPatientID and gResultDate to the Allergens table;
• Define a relationship between the Allergens table and a new occurrence of the Results table as follows:

Allergens::gPatientID = Results 2::PatientID

AND

Allergens::gResultDate = Results 2::Date

AND

Allergens::AllergenID = Results 2::AllergenID

• Enable creation of records on the Results 2 side of the relationship.
• Create a data entry layout based on a list view of the Allergens table. and place the measurements fields from Results 2 in the body part and the two global fields in the header.

With this in place, once you have populated a patient ID and a date in the global fields, you can enter the measurements against any allergen and have a record created automatically in the Results table (if it doesn't already exist).

 

 

Note: using the Allergens table itself as a "draft" table will not work in a multi-user scenario, and IMHO you should take this option off the table.

[Mark_MC]

Sorry to throw this out, I’m kind of new to the board.

 

OK, so you have a Patient Table and a Results table as the primary data tables, The Allergen Table basically acts as a Lookup table.

 

Have a data entry form; the upper part of the form reflects the patient information.  Here you can recall an existing patient, update an existing patient, or add a new patient.

 

The lower part of the form is used for results entry and contains a Result Date field created as a Global field, a result sequence (also a global field), and a tab object with the breakdown you mentioned for different tabs with filtered portals listed in the OP.  The Sequence Number would default to “1” but if there were separate set of results for a different test performed the same day you could change it to the next higher number.

 

Now write two types of scripts: ( a ) “Seed” records in the Test Table and ( b ) “Cleanup” script once the entries are made.  If there are standard sets of “panels” (i.e. like groups of samples ran together).  Running one of the “Seed” scripts will create records (without test results) in the Test Table for the Patient_ID, Date, Sequence listed on the form and of course the actual results will be blank.  Fill in the results; maybe add a few selected rows if needed.  Then run the Cleanup Script to delete any rows in the Test Table where there is an allergen with no results.

 

Over time tweek the number and specific items in the selectable Seed Scripts and they should get closer and closer to pre-grouping the results you need and reduce the need to manually creat records.

 

The “Seed” script should error trap to make sure a valid patient exists, a date has been entered for the test result, and a sequence number is present (if you use it) are present before creating any new Test Table records.  Might also be a good idea to check if the patient already has a test result for that date before allowing the seed script to create new records – don’t create duplicate records.

 

Since the portals on this page would be keyed to filter based on the date global field, you would have different layouts for general record review.

 

 

>>>>

[bmill]

Thanks so much for the great ideas!  I will give them a try and let you know how it works out.