4 Replies Latest reply on Dec 16, 2016 10:31 PM by ThinkSoft

    Selecting a unique list AS a related TO

    ThinkSoft

      G'day Friends I have a dilemma.

      the design of the database is this:  Where Contacts are abstracted as Patient, Doctor and Specialist.

      Screen Shot 2016-12-17 at 3.56.44 pm.png

      A Patient may present to a doctor for multiple cases

      Each case may have multiple meetings

      Each meeting MAY or MAY NOT have the same Specialist in attendance.

      Specialists do not get involved with the case until the meeting.

      Conversely this means that a specialist attends meetings that are part of cases for patients.

      At this point I can connect to display all the patients that the specialist has attended in the TO SpecialistPatients ( this should be name SpecialistCasesPatients - to be pedantic ) - however, this may ( sorry, does )  contain duplicate entries.

       

      My request of all you wonderfully smart folk out there is how to create a new TO that shows a unique list of patients attached to the specialist.

       

      These are relevant current relationships:

      All current relationships are single conditions.

       

      Contacts::CONTACT_ID

       

      SpecialistMeetings::MEETING_ID
      SpecialistMeetings::Patient_ID  --> Contacts::CONTACT_ID 

      SpecialistMeetings::Doctor_ID  --> Contacts::CONTACT_ID

      SpecialistMeetings::CASE_ID

       

      Specialist Cases::CASE_ID

      Specialist Cases::Doctor_ID  --> Contacts::CONTACT_ID

      Specialist Cases::Patient_ID  --> SpecialistPatients::CONTACT_ID

       

      SpecialistPatients::CONTACT_ID

       

      ==============================================

      Please do not tell me to change the structure of the database - this is how it is...

       

      Yours in wild eager anticipatory gratitude.

       

      Tony

        • 1. Re: Selecting a unique list AS a related TO
          erolst

          ThinkSoft wrote:

          a new TO that shows a unique list of patients attached to the specialist.

          If Case is a child of Meeting Meeting is a child of Case with just an added specialist, you could use a chain of

           

          Contact_Specialist::id --< Case::id_specialist / id_meeting >-- Meeting::id / id_patient >-- Contact_Patient::id

           

          Contact_Specialist::id --< Meeting::id_specialist / id_case >-- Case::id / id_patient >-- Contact_Patient::id

           

          Just in case you would store the patientID again in Case Meeting (but you shouldn't), you could leave out the Meeting Case hop.

          ThinkSoft wrote:

          […] all you wonderfully smart folk out there […]

          Flattery will get you anywhere

           

          EDIT: corrected the relationship of Case and Meeting.

          • 2. Re: Selecting a unique list AS a related TO
            ThinkSoft

            Many thanks - my mood after only two hours sleep in the last 39 is generous….

             

            so…

             

            Unfortunately;  Meeting is a Child of Case

             

            I.e.

            One Case ( created with a Doctor and Patient ) has one or more meetings ( which will then include the Specialist )

             

            there is no direct relationship between CASE and SPECIALIST

             

            ++++++++++++++++++++++++++++++++++++++

             

            No intent on flattery - just the acknowledgement that after nearly 9months of Filemaker usage - having built 6 applications, integrated it with XERO, written a video conferencing package and integrate FM data onto a  Video conference screen - writing data back to the web and building two FIAS apps that are up on Apples’s App store - I still feel as dumb as the day I started - and that I can always learn from others more experienced than I

             

            Tony

            • 3. Re: Selecting a unique list AS a related TO
              erolst

              ThinkSoft wrote:

              Unfortunately; Meeting is a Child of Case

               

              I.e.

              One Case ( created with a Doctor and Patient ) has one or more meetings ( which will then include the Specialist )

               

              Right, so I confused the two tables; I'll edit that.

               

              But it doesn't matter; just turn it around:

               

              • filter all meetings of a specialist by Contact:id = Meeting::id_specialist

              • filter all cases of those meetings by Meeting::id_case = Case::id

              • filter all patients of those cases by Case::id_patient = Contact::id

               

              Once you're (back) in a Contact table occurrence, you'll have by definition a de-duped list.

              • 4. Re: Selecting a unique list AS a related TO
                ThinkSoft

                many thanks - my brain hurts so after the hip hop concert with 4 grandsons - i’ll try this out…

                 

                heading off now…

                 

                Tony Harris, Director

                 

                 

                 

                Mobile       0432 326 505 <tel:0432326505>  

                Int'l           61432 326 505 <tel:61432326505>

                Email     tony.harris@thinksoft.com.au <mailto:tony.harris@thinksoft.com.au>

                Web          www.thinksoft.com.au <http://www.thinksoft.com.au/>

                Facetime Tony Harris <facetime://tony.harris@thinksoft.com.au>

                Skype     tony.thinksoft <skype:tony.thinksoft>

                Zoom     Video Conference <https://zoom.us/j/8597114441>

                 

                Copyright © Australian Custom Software Pty Ltd t/a ThinkSoft

                Please consider the environment before printing this e-mail and attachments.

                Email Policy: www.thinksoft.com.au/email_policy <http://www.thinksoft.com.au/email_policy>