1 Reply Latest reply on May 8, 2012 3:17 PM by philmodjunk

    Autoenter looked up value

    mark_d2x

      Title

      Autoenter looked up value

      Post

      Hi 

      In my prostate cancer databse i want to look up the PSA value for a particular follow up for the patient.  The dates may not be exactly the same.  In this database the relationships are:

       

      Tumour -> one to many -> PSA (kp_TUM_GEN = kf_PSA)

      Tumour -> one to many -> Follow up (kp_TUM_GEN = kf_FOLLOW_UP)

      I have set up a new entity of the PSA and Follow up tables  so that PSA2::kp_TUM_GEN = FOLLOW_UP2::kp_TUM_GEN and PSA2:DATE_SAMPLE = FOLLOW_UP2::DATE_OF_FOLLOW_UP. (The kps are alphanumeric - kp_TUMOUR_000000000001 etc)

      In the FOLLOW_UP table want to look up from the PSA table the PSA result or the one with the next lowest date.

      So, the lookup is for FOLLOW_UP::PSA - 

      Starting with table: FOLLOW_UP2 |looked up from related table PSA2 | PSA | If no match, then copy next lower value

      I think I'm almost there but not quite.

      Where there is no match I am just getting a "?" not the next previous date value.

      In a different way:

      01/01/2012 PSA = 10

      01/02/2012 PSA = 5

      01/03/2012 PSA = 12

       

      If a patient was seen on

      02/01/2012  PSA  = 10

      02/02/2012  PSA = 5

      02/03/2012  PSA = 12

       

      Any suggestions?

        • 1. Re: Autoenter looked up value
          philmodjunk

          Instead of:

          PSA2::kp_TUM_GEN = FOLLOW_UP2::kp_TUM_GEN and
          PSA2:DATE_SAMPLE = FOLLOW_UP2::DATE_OF_FOLLOW_UP.

          Try this:

          PSA2::kp_TUM_GEN = FOLLOW_UP2::kp_TUM_GEN and
          PSA2:DATE_SAMPLE < FOLLOW_UP2::DATE_OF_FOLLOW_UP.

          make it a sorted relationship where PSA2 is sorted by Date_sample in descending order.

          At first, it seemed odd that your samples were taken one day prior to the follow up, but I'd guess that's because the lab work was on a different date and the follow up is the appointment where the results of the PSA are part of the discussion with the patient.