3 Replies Latest reply on Jul 28, 2014 3:59 PM by nihmbrisby

    Pop-up field of names from same table

    notJames

      Title

      Pop-up field of names from same table

      Post

           I have a table of students with two billing designations: primary and secondary (pop up field that lets you choose between the two). I would like to add a pop-up or drop-down field to that table that lists all the students with the "primary" designation.

           Will this involve a self-join relationship? Or creating another table that automatically adds records based on what students are designated as "primary"?

        • 1. Re: Pop-up field of names from same table
          nihmbrisby

               Pop ups and drop downs get their menus from value lists.  A value list can "use values from a field" (this is the first option in the edit value list window). Furthermore the context for the evaluation of this field can be specified by checking 'include only related values starting from:'  The only thing we now need is a relationship that provides the proper context.

               In your case a self join should do the trick.  I believe the relationship should look like this:

               Table::Primary key -----x-----SelfJoinTable::Primary key  ("x"  means cartesian join.  It will returns 'all records')

               AND

               Table::c_primary ------ = ------ SelfJoinTable::billing_designation (ie only records that are of billing type 'primary')

               Table::c_primary is a constant.  By this I simply mean a field which always has the same value for all records.  In this case, c_primary just equals the word "primary."  This field can be auto-entered to equal primary (using the 'data' checkbox in auto-entry).  For records that already exist, simply add Table::c_primary to the layout, go to any one record, click show all, type in 'primary' into the field, and then use the "Replace Field Contents" option in the records menu.  Once you've done this you can remove the Table::c_primary field.

               Now you can choose the value list to be equal to values from SelfJoinTable::Primary key and check "Include only related values starting from "Table"

               Hope this helps.

                

          • 2. Re: Pop-up field of names from same table
            philmodjunk

                 Table::Primary key -----x-----SelfJoinTable::Primary key  ("x"  means cartesian join.  It will returns 'all records')

                 AND

                 Table::c_primary ------ = ------ SelfJoinTable::billing_designation (ie only records that are of billing type 'primary')

                 Can be simplified to just be:

                 Table::c_primary ------ = ------ SelfJoinTable::billing_designation (ie only records that are of billing type 'primary')

                 The cartesian join pair does nothing for this conditional value list.

                 There is also an alternative method that you can use for this that does not require a relationship to do. See "option 1" in this tutorial on conditional value lists:

                 Forum Tutorial: Custom Value List?

            • 3. Re: Pop-up field of names from same table
              nihmbrisby

                   "Can be simplified to just be:"

                   Thanks Phil- I had wondered about that as I was writing my reply.