4 Replies Latest reply on Sep 1, 2010 12:25 PM by AdamHalstead

    Value List - Need to show multiple fields for second value

    AdamHalstead

      Title

      Value List - Need to show multiple fields for second value

      Post

      FM Pro V11

      For a pop-up menu control I need to show in the menu from which the user selects, a string comprised of multiple fields, not just one field other than the first field.

      More specifically, I am showing in the popup menu values from a table called 'Location'

      Location contains a primary key 'idlocation', which I specify as the (first) value.
      The other columns in location are a foreign key for another table 'buildings', and 'room number'.

      I want the user to see in the menu: room number, building name, street address
      e.g. RM 101, Orwell Towers, 1984 George Street
      The building name and street address come from the building table.
      I seem only able to select one field to show, which isn't enough to identify the location unless the user knows the ID number itself.

      Thanks,

      Adam

        • 1. Re: Value List - Need to show multiple fields for second value
          philmodjunk

          It would be nice if filemaker value lists supported more than two columns like some competing products do.

          You can define a calculation field that combines your text into a single field. Define a value list with this expression:

          RoomNumber & ", " & BuildingName & ", " & StreetAddress

          Make this calculation field your column 2 field.

          Note: If some of the above fields might be blank in some cases, you can use If functions to keep the extra commas from appearing if you wish.

          • 2. Re: Value List - Need to show multiple fields for second value
            AdamHalstead

            Thanks for the quick response.
            I'm almost there now.

            For my value list I'm using two tables.

            Location:
            idlocation
            building_id_building
            room_number

            Building:
            idbuilding
            name
            street_address

            for the first field I specify Location : idlocation

            For the second field I added a calculation field 'option text' to location table and specified (from the context of location):
            room_number & Building::name & Building::street_address

            this doesn't work get '....will not work because field 'option text' cannot be indexed. ...'

            If I instead make a calculation field in Building using name and street_address this will work.
            However, I need the room_number from one table and the other fields from Building.

            Can't see where my logic is wrong.
            If I try just room_number is the calculation, this fails.
            I get MySQL error: ...syntax to use near FROM SIS.location where idlocation IS NOT NULL ORDER BY 2, 1'

            I must have an issue with the schema. Been a long week. But if you can see something I'm missing on the FM side I be greatful to know.

             

            • 3. Re: Value List - Need to show multiple fields for second value
              philmodjunk

              I'm not sure where MySQL comes in to this, that's a detail left out of your first post.

              In filemaker, you can't combine values from two related tables, because the result, by definition, is unstored. Value lists that draw values from fields in a table, use the field's index to build the value list. Since unstored fields don't have an index, you can't use the result for a value list.

              Two options--neither will do exactly what you prefer here, but they can get the job done.

              1) Define a lookup field in one of the two tables that uses looked up values to copy the data from the related record. You now have all the values in one table so your calculation will work. Of course, you've now denormalized your data (you have duplicate copies of the info in more than one place) and you'll have to manage any updates to the looked up values so that the copies are alos updated. This may be something you can manage with a script trigger.

              2) look into using conditional value lists to reduce the number of values in your value list at any one time. In your case, you might have a conditional value list that only displays data from the building table for a given location. Since all the values in the list are from the same location, you no longer need those fields in the value list itself.

              • 4. Re: Value List - Need to show multiple fields for second value
                AdamHalstead

                Thanks for all your help. I got a clearer idea of how FM handles values list.

                I found an alternative solution, which was to create my aggregated data in a MySQL view (I'm using an external database), and then use the field from the view to populate the value list.