1 Reply Latest reply on Apr 19, 2011 10:36 AM by philmodjunk

    Failing to find from a related database field



      Failing to find from a related database field


      I am having trouble getting a find to work.

      Two databases, linked many to many by medical record number (MRN). They are called in the relationship graph (both are external and not related to any tables in the current DB).

      The layout shows db1 with related fields from db2; most have NO match in db2. I can sort to find the records with something from db2 showing up. 

      If I search on field in db1, I can find all records on a service (Ortho). 

      If I search on field from db2, I get no records matching, even though I see them. The field I am searching on is not a calculation, but rather the MRN that makes the match.

      This does not work as a single search (Orhto AND MRN>0) or as a series 

      Service=Ortho, then constain set for MRN > 0. 

      Any idea why this doesn't work? I know it won't work if I'm using an unstored calculation, but this isn't. 

      Thanks for your help,

      Mark (infectiologue)

        • 1. Re: Failing to find from a related database field

          You would appear to have a problem right at the start here:

          Two databases, linked many to many by medical record number (MRN).

          How did you relate these two tables? If you used:

          db1::MRN = db2::MRN

          You don't have a many to many relationship, you have a one to many relationship and this might be at least part of the problem here.

          For a true many to many relationship, you'd need either a join table linking these two tables or possibly a multi-value key in either db1 or db2. (I don't recommend the multi-value key for this.)

          A search where you enter "ortho" in the text field of a db1 field and a  lone * in db2::MRN should find all records with ortho in the text field and where at least one related record in db2 exists. This can be done in a single find request.

          That said, the criteria you describe should also work as far as I can tell without running a few tests.