AnsweredAssumed Answered

Filter a Table Occurrence for a link to the Same Table

Question asked by ThinkSoft on Apr 4, 2016
Latest reply on Apr 5, 2016 by ThinkSoft

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)




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.