3 Replies Latest reply on Mar 7, 2015 11:37 AM by philmodjunk

    Value list from several (related) fields?

    everyman_1

      Title

      Value list from several (related) fields?

      Post

      Problem:

      Student database with several "phone number" fields.  I'd like to designate one of them as "primary" for emergency contact.

      What I'd LIKE to do is have a separate "PRIMARY PHONE" field, with a pull-down menu consisting of:

             
      • student's cell
      •      
      • mother's cell
      •      
      • father's cell
      •      
      • mother's office
      •      
      • home
      •      
      • etc.

       

      I'm not sure this is even possible; as a fallback, I'll have to put a checkbox near each "phone" field that says, "Designate as primary."

      Solution:

      Anyone?

       

      Thanks in advance!

       

       

        • 1. Re: Value list from several (related) fields?
          philmodjunk

          It's not impossible, but it's far easier to set up a related table and a portal to list all phone numbers associated with a particular student. Then your phone numbers are all in the same field, but each in its own related record You can designate a particular phone number as "primary" by storing it's primary key in a field defined for that purpose in the Student record. This field can be formatted as a conditional value list of only the phone numbers listed in the PhoneNumber table that are linked to this student. A link to a different Tutorial: What are Table Occurrences? of your phone numbers table will then only link to this one primary phone number for any given student.

          You could also put a button in the portal row to your student phone numbers that selects the phone number record clicked as "primary" by setting the Student field to the primary key value of this field and then you don't need any value list.

          To learn more about Conditional value lists, see "Adventures in FileMaking #1 - Conditional Value Lists" This free to download file demonstrates 10 different ways to set up a conditional value list in FileMaker.

          • 2. Re: Value list from several (related) fields?
            everyman_1

            Agreed.  Under ideal circumstances I'd set up a related table for Phones (and another for Addresses) --- but most circumstances aren't ideal....  In the instant case, there wasn't a sufficient need for that much flexibility given the very regular nature of the phone numbers we collect: home (if anybody still has one), student cell, and parents' work and cell numbers.  Then there were the extra steps involved (importing years of Bento data from several similar but slightly different tables, with similar but slightly different field names) which made extra tables even less attractive.

             

            In the end I created a simple Value List ("Home Phone/Student Cell/Parent 1 Work/Parent 1 Cell/Parent 2 Work/Parent 2 Cell") and a simple calculated field (case (primaryPhone = "Home Phone" ; formatted_homeNumber ; etc.)).  It's not terribly elegant, but sometimes a crudely drawn straight line really is the shortest (if somewhat inflexible) path to the destination.

             

            • 3. Re: Value list from several (related) fields?
              philmodjunk

              Yet now that you have a functioning FileMaker database, you are not locked into your current data model. It's pretty easy to set up a script that takes the data from your current set of individual fields and builds a related set of records in a new table. that not only gives you a better option for your value list or eliminates the need for it altogether,  it gives you a much more flexible way to manage the multiple contact numbers (I include email addresses and social media URL's in the same set of fields when such are needed) typical of so many "contact manager" type projects used today.