1 Reply Latest reply on Oct 2, 2014 5:56 AM by philmodjunk

    Searching based on multiple tables.



      Searching based on multiple tables.



      I have the following table relationships:

      Client:clientID --> <--Diagnosis:clientID--> <--referrals:clientID 

      I'm trying to create a layout that can search for a particular diagnosis for a particular referral.

      Clients can have more than one diagnosis obviously.  Also, on the diagnosis portal when they add a diagnosis to a person there is sort of a delete option.  When they click the X it pastes text in a field which says diagnosis deleted on date by account name.  Then the portal they see just hides records that have text in that field.  They think they deleted it but for other reasons we need to keep that entry rather than truly deleting it.  

      What I would like to do is is create a layout where the person can search for any given diagnosis for any referral type.  So all diabetics that were referred to Centre A.  I want it to ignore those where the diagnosis was "deleted".

      My layout is based on the referrals table, with fields on it about client and diagnosis via the related tables above.  On the referrals table I created a field called diagnosis which is a calculation listing the diagnosis field from the diagnosis table.  Works but leads to two problems.  First one being that they may not have a diagnosis at the time the referral is made, may be made at a later date....and two it includes the ones that have been "deleted" which I don't want it to do.  

      Is there a better way to search for this than what I'm doing or is there something I've left out to eliminated those deleted diagnosis records and having to re lookup all the time?

        • 1. Re: Searching based on multiple tables.

          "Omit" requests can omit records from the found set that match specific criteria such as your "deleted" text. "Omit" requests can be set up manual or scripted finds.

          But if each referral is specific to just one record in Diagnosis, it would appear that you are not using the correct match fields to link diagnosis and referral records. I'd Match a DiagnosisID in Diagnosis to a DiagnosisID in Referrals if there might be one Diagnosis to possibly more than one referrals. If the reverse might be true, I'd use a ReferralID.

          You may find this thread of Scripted Find examples a useful source of ideas: Scripted Find Examples

          Caulkins Consulting, Home of Adventures In FileMaking