Re: [Gnumed-devel] Open Source Health Informatics, London, 30th Sep 2010

[Jim Busser]

On 2010-08-13, at 3:31 PM, Luke Kenneth Casson Leighton wrote:

>> request_id can be purely "owned" by GNUmed and returned by whichever path 
>> labs will handle it.
> 
> in which field? ORC002 or ORC004?

My focus lapsed... request_id would be returned in ORC 004

        *when* the ordering provider was a GNUmed id
        AND
        *where* what was requested to the lab included this GNUmed-initiated 
number

as opposed to some other praxis or hospital that might have supplied some other 
number on account of this doctor doing other work elsewhere... it is a reason 
why GNUmed should aim for a unique-from-other-systems request_id.

>> lab_request_id is perhaps what is on the German labels and perhaps allows 
>> the clinician at the point of blood/urine-taking to know what the path lab's 
>> "internal id" is going to be. This maybe achieves to gain the exact same 
>> value (if Germany would adopt HL7) in ORC 002.

>  ok, then the wiki page needs updating with the correct logic, taking
> into account that lab_request.lab_request_id is the "first priority"
> to check (being gnumed-generated and ending up in ORC002 or is it
> ORC004?  need to know!  i know you say ORC 002)  but then the "second
> line of defense" is to check ORC003 - against
> clin.lab_request.request_id.

I misspoke...

        lab_request.lab_request_id

*happens* in Germany to be upstream (GNUmed) "able to be entered" despite that 
it is not actually assigned by GNUmed but determined by what the lab supplies 
as bar codes and by the individual bar code label that the doctor happens to 
select from among those supplied to the praxis.

In Germany it qualifies as the first priority for the match. In Canada it is, 
for practical intents and purposes, unavailable except through importing it 
with the incoming result auto-creation.

> if the entry already exists (with the right lab_request_id) then its
> request_id field gets updated/overwritten... whoops can't have even
> _created_ the lab_request because it has a non-NULL field and has a
> unique key constraint on it.

It is only request_id (not lab_request_id) that is non-NULL, unique so we just 
need a way to supply a unique value maybe

        CONCATENATE ("gm-", NUM_TO_TEXT (pk_item))

        ... or "gm-" could instead be some configurable per-praxis value.

> so we're screwed.

if above and below does not resolve...

From my understanding, the bar code (below) is determined from what is 
available from among what German labs uniquely supply, and so we just need to 
check with Karsten that it makes sense that this bar code should simply go into 
lab_request_id. Notice that Karsten may have been working ahead of available 
clarifications and use cases, since HL7 is not even yet used in German test 
results.

In an environment where multiple labs exist, and where each is able to generate 
their own reference ID, then we cannot expect lab_request_id 
(externally-assigned) is always going to be unique and therefore we cannot 
require the constraint

        unique(fk_patient, lab_request_id)

in spite of it having been envisioned below

> i believe that the names / purposes of
> clin.lab_request.lab_request_id and clin.lab_request.request_id are
> the wrong way round.  or, more to the point: see gmMeasurements.sql
> 
> create table clin.lab_request (
>    unique (fk_test_org, request_id)
>    -- FIXME: there really ought to be a constraint like this:
> --  unique (fk_patient, request_id)
> 
> * request_id should be what the barcode goes into.  if there isn't a
> barcode, a unique number less than 20 digits needs to be created.
> * ORC 003 should be going into lab_request_id.
> * unique(fk_test_org, request_id) is wrong
> * unique(fk_patient, request_id) is right
> * unique(fk_patient, lab_request_id) is right *IF* lab_request_id is
> not-NULL.  i assume postgres can do conditional constraints like this?
> if lab_request_id not NULL, check it's unique?
> l.

-- Jim