3 Replies Latest reply on Jun 25, 2013 10:13 AM by philmodjunk

    Should this be a table or value list?

    brett_

      Title

      Should this be a table or value list?

      Post

           I'd like to create an interest attributes for customers.  For example, interests might be:

           - Web Design
           - Entrepreneurship
           - Time Management

           I'm not sure if those values should go into a table or value list.  I would like to search under "Web Design" and get a listing of everyone who has that value associated to them.

           Customers can have more than one value associated to them.

           Any suggestions about how this should be setup?

        • 1. Re: Should this be a table or value list?
          philmodjunk

               It should be both. Put the list of interests in a table and use it as the source of values for a value list. Since multiple people will likely have the same interest and a given purpose will likely want to list multiple interests, you have a many to many relationship:

               People----<People_Interest>-----Interests

               People::__pkPeopleID = People_Interest::_fkPeopleID
               Interests::__pkInterdstID = People_Interest::_fkInterestID

               A portal to People_interest placed on the people layout can be use to list and select interests for a given person. A portal to the same table on an Interests layout can be used to list all people who have selected that Interest. And summary reports listing multiple interests and the people who selected them can be produced from a layout based on People_Interest.

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

          • 2. Re: Should this be a table or value list?
            brett_

                 Thanks.  I have the tables setup.

                 If I want to enter several interest for a person on the person layout, how should that be setup? 

                 Once those interest are entered, I'd like them to display as I navigate from person to person.

            • 3. Re: Should this be a table or value list?
              philmodjunk

                   See the demo file. The basic approach is to place a portal to the join table (people_Interest) on the people (Person as you've named it) layout. Then you create records in this portal, one for each interest that links the current person to a selected interest.

                   Design details that facilitate this (and all found in the demo file):

                     
              1.           "Allow creation of records via this relationship" is enabled for the join table in the peopl to join table relationship.
              2.      
              3.           The foreign key field, _fkInterestID is placed in this portal row and formatted with a value list of Interest ID's and names from the Intrests table.
              4.      
              5.           A Name or description field from Interests is added to this portal row.
              6.      
              7.           Then, selecting an Interest from the value list, creates a new record in the join table where both the _fk fields are populated with the correct ID numbers and the name/description field then displays text from the selected Interests record.

                   After you have the basics working, you might try the check box list example in the demo file as a faster, more user friendly way to create the records in the Join Table.