Medical Tests Reports

There are many medical lab reports that users may wish to upload. My first solution is show at the links below.

The solution was to extract the information into chunks.

A better solution is to get the actual reported values entered. At this point, we encounter the problems of different methodologies at different labs. Technically, the count of one lab cannot be reliably compare to the count at a different lab. There are some ways to mitigate this issue which is outside of the scope of this set of posts (coding).

Setting Up

For each of these labs we need to know what bacteria is reported — this would be used done by this new table. We add DisplayOrder so the bacteria will appear the same as on the printed or pdf report (makes entry easier!)

The rest results may be in any of the following presentations

To handle this, we add another table with verbal descriptions and links to images (optional)

Last thing is to enter the value for each. For simplicity, I opted to define ranges of value.

So, to recap this last table defines the range for a specific bacteria on a specific test that would be deem to be have some label. The data needs to be entered for each bacteria in each test.

Our current database schema

In today’s code example, I enclose a download from my site of the lab tests with their taxons (which may be incomplete or incorrect). I did this by putting into a text file the taxons,displayorder, and then naming the file {labName}.{LabTestName}.txt. They will all be dropped into a /data/ folder.

This makes it easy for lab tests to be revised/updated (and hopefully, with a pull request for the updated date — this is open source, some payback to the project is expected behavior).

After the upload you should see

Select * from [dbo].[LabTestStandards]
SELECT * from LabTests
SELECT * from labs

When the Levels and Ranges are added, things becomes a little more complex for processing. If you execute the following and save it as an .xml file (in the /data/ folder), I will write a demo program when I get a pull request on the data. Do it one by one for each LabTestName (GI-Map is shown below) –> GI-Map.xml

SELECT * from  LabTests T (NOLOCK) 
			JOIN LabTestStandards S (NOLOCK)
			ON T.LabTestId=S.LabTestId
			LEFT Join LabTestStdLevel L (NOLOCK)
			ON L.LabTestStandardsId = S.LabTestStandardsId
			LEFT JOIN LabTestLevel M (NOLOCK)
			ON L.LabTestLevelId=M.LabTestLevelId
			WHERE LabTestName='GI-Map'

The result is XML to be dropped into a file

   .... etc ...

There is actually a stored procedure that does it for you:
ExportLabSettingsForSharing @LabName varchar(50)=’GI-Map’

There is also one that generates the data to use for data entry:

DisplayNewEntryForm @LabName varchar(50)=’GI-Map’

Bottom Line

This post definitely has a home work assignment — obtain the level data and names from existing reports that you have available (or can borrow from friends).