5 Replies Latest reply on Nov 29, 2013 1:53 PM by PrisonProfessor

    Drop down list from other table field in portal



      Drop down list from other table field in portal


           Being new to FMP 12 Adv, I'm finding out how much I do not know and probably asking a 'duh' so bear with me.

           I have a table named 'Facility' that has a field name 'Facility Name'

           I want to build a portal in the layout for the table 'Soldiers' that will have within the portal a drop down of all 'Facility Name' entries in the 'Facility' table.


        • 1. Re: Drop down list from other table field in portal

               Open Manage | Value Lists

               Click New

               Name your value list and select the "use values from a field" option

               In the dialog box that pops up, select the name of a Tutorial: What are Table Occurrences? of your Facility Table and click a field in the list that appears below the drop down to select that field as the source of values for your value list.

               You may find that you need to specify a "Facility ID" field as the first field and then select the Facility Name as the "second field".

               Once you have defined your value list, you can select a value list format (drop down list, pop up menu, check boxes... ) for a field on the Inspector's data tab and then select this value list to use with that value list format.

          • 2. Re: Drop down list from other table field in portal

                 Hi Phil;

                 Really appreciate your quick responses. I'm still not getting the results I desire. Perhaps a little more detail in case I did not fully explain.

                 I'm building a dB for prison ministry and I have a layout name 'Profile' that profiles the inmates. It is quite common an inmate will get transferred from facility to facility. Within this profile I want to have a portal that tracks a history of the facilities he has been incarcerated. The portal will be many facilities to one inmate.

                 The fields I want to display in the portal are 'Facility Name' from the Facility table and 'Date Enter' & 'Date Exited' that are not related but indpendent.

                 I want the 'Facility Name' to be a drop=down with the values of all 'Facility Name' in the 'Facility' table

                 With your last suggestion I fist tried linking 'Facility Name' from the 'Profile' table to the 'Facility' table and get a drop down list of the all "Facility Name" I'm able to successfully enter one record but subsequent records though the full list is displayed once I commit it defaults to the first entry.

                 I then went and tried linking in addition to 'Facility Name', Facility Id' between both tables. When I attempt to enter first record I again get all values of 'Facility Name' but once I commit I get a error dialog 'This field cannot be modified until 'Facility Id' is given a valid value.

                 When I look at the 'Facility' table their is a 'Facility Id' associated with the 'Facility Name'. I also noted that each time I attempt the latter a record is added to the 'Facility' table populating only the 'Facility Id' & 'Facility Name' fields.

                 I hope that all makes sense. Though I love to get resolved ASAP, I see it is late 11:20 PM CT and I do have many other tasks that can keep me busy might mean more issues though frown) so if you want to respond after Thanksgiving that's cool. 

                 Happy Thanksgiving!!! 

            • 3. Re: Drop down list from other table field in portal



                   You can  include all the value from facility on valuelist .

                   This will show all the values

              • 4. Re: Drop down list from other table field in portal

                     It sounds like your value lists are working but that your tables and relationships are not set up correctly. What you describe is a many to many relationship. an inmate can be sent to many different facilities and a facility will have many different inmates. Thus a direct link from profile to facility won't work. You need a third table that functions as a "join" table between Profile and Facilities.

                     Start with these relationships:


                     Profiles::__pkProfileID = Profile_Facility::_fkProfileID
                     Facilities::__pkFacilityID = Profile_Facility::_fkFacilityID

                     You can place a portal to Profile_Facility on the Profiles layout to list and select a Facilities record for each given Profiles record. Fields from Facilities can be included in the Portal to show additional info about each selected Facilities record and the _fkFacilityID field can be set up with a value list for selecting Facilities records by their ID field.

                     For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                • 5. Re: Drop down list from other table field in portal

                       Thanks Phil; It still took some head scratching but things appear to be working the way I wast them to. The mention of foreign key triggered my memory. I had an Access class when it was first introduced in the nineties and I did some development work back then but most of them brain cells are long dead cool