• 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)
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.
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.