2 Replies Latest reply on Aug 20, 2014 10:45 PM by NsaA

    Value List sourced from two fields



      Value List sourced from two fields



           In my medical database (FM 12),

           I have a Contacts layout which shows two portals. The two portals are from two tables:

           1.     PhoneNumbers

           2.     EmailAddresses

           Each portal has two fields in the Contact Layout:

           The PhoneNumber portal has two fields in the contact Layout, Type and Number. Type is a drop-down list which can be edited and contains values like Home, Work, care of.

           The EmailAddresses portal is set up similarly and has a separate drop down list for Type.


           I recently included a field in Contacts Table: Preferred Appointment Confirmation Method.

           Please help me set this up for the above field--

           I want to be able to select the type of phone, email from a drop-down list sourced from Type of Phone as well as Type of Email. Is this possible? Basically my patient has to choose how I may contact him using any one phone number or email address.

           If this is possible, can I go one step further and have the chosen number/ email auto-saved in a separate field? Then I could set up auto-email, text too.


        • 1. Re: Value List sourced from two fields

               You can't draw values for a value list from two tables. But I don't think that will be a problem here as there is a simpler way to do what you want.

               Presumably, you have relationships like this:


               Patients::__pkPatientID = EmailAddresses::_fkPatientID
               Patients::__pkPatientID = PhoneNumbers::_fkPatientID

               Of course, your field and table names are probably different. But the one to many relationships between Patients and the other two tables appear to be what you have here.

               But here's a simple way to select a "preferred confirmation method":

               Put a button in the portal row of both portals that you will click to select that one phone number or email address as the preferred method.

               The script in the phone number portal would work like this:

               Set Field [Patients::_fkPreferredContact ; PhoneNumbers::__pkPhoneNumberID ]
               Set Field [Patients::PreferredContactType ; "Phone" ]

               The script for the button in the email portal is nearly identical but set's the preferredcontacttype to "email".

               Then you add two more Tutorial: What are Table Occurrences? of Phone and Email to your relationships and match fields like this:

               Patients::_fkPreferredContact = PhoneNumbers|Preferred::__pkPhoneNumberID
               Patients::_fkPreferredContact = EmailAddresses|Preferred::__pkEmailID

               Note that "PhoneNumbers|Preferred" and "EmailAddresses|Preferred" are simply the names that I've given to two new table occurrences in Manage | Database | Relationships.

               Then you can define a calculation field in Patients like this:

               If ( PreferredContactType = "Phone" ; PhoneNumbers|Preferred::PhoneNumber ; EmailAddresses|Preferred::EmailAddress )

               be sure to select "text" as the calculation field's result type.

               PS. I normally put email addresses and PhoneNumbers all in one table to begin with and then this all get's a bit simpler to set up as I don't need the calculation field, only one script with one script step and just one added table occurrence to make this happen.

          • 2. Re: Value List sourced from two fields

                 I managed this finally! smiley

                 Your instructions are very clear, but i kept making silly mistakes and took some time.

                 Thank you so much!!!!