1 Reply Latest reply on Dec 13, 2015 4:53 PM by Mike_Mitchell

    How can I duplicate only certain data from a table for a value list?


      I have licensees (table 1) that take continuing education (table 2).


      I show the education classes taken by a licensee in a portal. We choose from a value list of all continuing education classes from

      the classes table. 

      when classes are added to the CE Classes table, they have a beginning date and an expiration date. 

      I need this drop down value list of classes to only show classes that are not yet expired.

      Do I need to create a duplicate of the classes table and somehow filter it?  Just not sure how to only pull current classes for the value list.

      Thanks for any guidance!


        • 1. Re: How can I duplicate only certain data from a table for a value list?

          Hello, Jane. There are a few ways to accomplish this task. In ascending order of complexity:


          1) Create a field in the parent table (where the value list is to appear) that corresponds to the information you want. In your example, you could use a date field corresponding to the current date. Then, create a new TO (table occurrence) corresponding to the table in which the values appear. Create a relationship between the current table and the new TO, based on the comparison field - for example, current date >= beginning date AND current date <= ending date. Now, you're in a position to create the value list using only the related values in the target table.


          2) Create a text field in a single-record or sessions table (where each record corresponds to a user). Populate that field with the values you want using a script (such as OnFirstWindowOpen). Base the value list on that field (it will automatically parse any return delimited list into values when you use that field).


          3) Use a Virtual List technique to create the values in a variable (or series of variables). Display the Virtual List in a portal (perhaps displayed in a popover). Allow the user to click on a value and use a script to populate the value.


          Of course, this is all predicated on keeping all your classes in the same table (expired and current). Depending on your data model and searching needs, it might make sense to archive off the expired classes to another table and just have the current ones in the main table. This has the advantage of faster processing for value lists and searches, but the disadvantage of needing to search a different table if you needed to search all classes. The "best" method will depend on your business needs.