3 Replies Latest reply on Apr 5, 2016 7:51 PM by ThinkSoft

    Filter a Table Occurrence for a link to the Same Table


      I have a table ( Contacts ) containing Patients, GPs and Specialists, indicated by a Contact Type

      In the Patient section of the table - the patient will have a reference to a "Usual GP"  and the patient will have a reference to "Usual Specialist"

      The Database is

      CONTACTS  - Primary Key is CONTACT_ID_PK  { Get(UUID) }

      REFERRALS - Primary Key is REFERRAL_ID_PK  { Get(UUID)  }         

      PATIENTS        (TO of CONTACTS)  - where contact type is "Patient"

      GPS                  (TO of CONTACTS)  - where contact type is "GP"

      SPECIALISTS  (TO of CONTACTS)  - where contact type is "Specialist"

      The relationships I desire are:

      A GP has many Patients - conversely a Patient has a single USUAL GP

      A Patient may have a relationship with 1 or more Specialists

      Through Referrals - many GPs may have relationships with many Specialists

      GP -------< PATIENT(s)

      PATIENT ------------< SPECIALISTS

      GPS >------- REFERALLS --------< SPECIALISTS


      I would like to have a Value List for GPs and a Value List for Specialists so that in the Patient detail layout, the user can select from the lists.


      My questions are:

      Is it possible to create a Table Occurrence that is "filtered" to create a value list?

      What is best practice in FMP to create this type of database?


      In SQL based DBMS this is simple through a filtered "view" created on the fly.  How is this done in FMP 14?


      Many thanks in advance.

        • 1. Re: Filter a Table Occurrence for a link to the Same Table

          Two possibilities:



          • in any table, create two global calc fields, type text, with the result of "Specialist" and "GP", respectively

          • create two new TOs of Contacts and relate each to the table with the globals, matching the appropriate global to the appropriate contact type

          • create two VLs using fields from each of the new Contacts TOs (1. primaryKey, 2. fullName, show second only), using related values, starting from the table with the globals.



          • in the Contact table, create two new calc fields, type text, say, cFullNameWhenGP and cFullNameWhenSpecialist, as

          Case ( type = "GP" ; cFullName ), and Case ( type = "Specialist" ; cFullName )

          • create two VLs using fields from Contacts (1. primaryKey, 2. cFullNameWhenGP or cFullNameWhenSpecialist, show second only)

          • 2. Re: Filter a Table Occurrence for a link to the Same Table

            This is how I would do it:

            In most applications, I have a 1-record utility table named GLOBALS, that contains most global fields the app might need, and probably some fields with general settings. In this table I would also create two calculated fields with the simple value ="GP" and ="Specialist", respectively, i.e. constant values. I would then create two extra TOs (Table Occurrences) from the CONTACTS table, called 'Globals_ValuelistGP' and 'Globals_ValuelistSpecialist', and connect them to GLOBALS like this:

            GLOBALS::const_GP = Globals_ValuelistGP::ContactType

            GLOBALS::const_Specialist = Globals_ValuelistSpecialist::ContactType

            GLOBALS now has two filtered relationships with CONTACTS.

            Now, create the 2 value lists:

            'GPs' is defined as field Globals_ValuelistGP::ContactName (or whatever you named it), select 'Include only related values starting from:' and select GLOBALS from the popup menu;

            'Specialists' is defined likewise, but uses values from Globals_ValuelistSpecialist::ContactName.

            BTW, this TOG (Table Occurrence Group) does not need to be connected to other groups, or the 'main' group, if you have one. The value lists will work in other TOGs as well.

            It's often preferable to connect patients and doctors not by name, but by their unique ID. In that case, define the value lists as:

            Use values from first field: Globals_ValuelistGP::CONTACT_ID_PK (left column);

            Check 'Also display values from second field' and select Globals_ValuelistGP::ContactName in the right column;

            Like above, select 'Include only related values starting from:' GLOBALS, but check 'Show values only from second field', in order to hide the UUIDs (that are not very informative), and set the sorting on the second field.

            With this technique, while selecting the human-readable name from a value list of GPs or Specialists, you actually assign the ID to the field that stores a patient's usual GP or specialist.

            HTH, regards,

            Peter Bouma

            • 3. Re: Filter a Table Occurrence for a link to the Same Table

              Both responses are correct - sadly I can't click on both of them


              Many thanks for the support - and the fast responses.

              I really like the answers from both respondents.   Peter Bouma's solution will certainly help in the final version, however erolst's solution is very quick for a prototype that I have to deliver quickly ... I like option 2 for it's simplicity.


              Best regards