3 Replies Latest reply on Jul 26, 2015 6:10 PM by Sciman

    Limit Value List

    Sciman

      I have a value list that uses values from a field in a table.

       

      The value list is currently populated with the values for that field from all of the records in the table.

       

      I would like to limit the value list to only those values that correspond to "active" records in the table (as maintained in a field called "Status" in the same table, which is set to either "Active" or "Inactive").

       

      Thanks in advance,

       

      Sciman

        • 1. Re: Limit Value List
          Hudi

          Hi Sciman,

           

          There are several ways to do this but I'll give you what i think is the simplest.

           

          You have two tables, a parent, where you are currently viewing records, and a child table which is where your value list is coming from.

           

          1.In the parent table create a calculation field called active__unstored and set the value to "Active" (Type the word active in quotes into the calculation dialogue) . Make sure the result is 'Text'.

           

          2. Creat a new table occurance in manage database based on the child table and call it something like CHILD__filter_active

           

          3. Make a relationship to the CHILD__filter_acitve table from the parent where the relationship is PARENT::active__unstored = CHILD::status

           

          4. Create a value list from the status field of the chld table and make sure to check "Include only related values from" and select the PARENT table.

           

          That's all. In a nutshell, the calculated field you created in the parent table is ensuring that you can only see active records through that relationship.

           

          HTH

          • 2. Re: Limit Value List
            Sciman

            HTH,

             

            Thank you, this worked great!

             

            On a note to others who may read this in the future: When I first tried this, I obtained the message "no values defined" when trying to select from the value list.  Fortunately, HTH's reminder to make sure the calculated result is "Text" for the "active_unstored" field, prompted me to check for this also in my "status" field (which was also a calculated field).  Sure enough, it was still set to "Number".  Everything worked fine after changing this field to have the calculated result set to "Text" also.

             

            Sciman

            • 3. Re: Limit Value List
              Sciman

              One additional note for those who may read this in the future:

               

              While the above works great for accessing the filtered value list in browse mode, the "no values defined" is still obtained when attempting to access the filtered value list in find mode.  This was resolved from changing the calculated field from "unstored" to "Use Global Storage" in storage options.