Jump to content

Help finding best solution


This topic is 2896 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.








Link to comment
Share on other sites

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);







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?

Link to comment
Share on other sites

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.





Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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


Allergens::gResultDate = Results 2::Date


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.

  • Like 1
Link to comment
Share on other sites

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.




Link to comment
Share on other sites

This topic is 2896 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.