8 Replies Latest reply on Sep 9, 2014 11:57 PM by LadyBear

    Using lookup values as a drop-down list

    LadyBear

      Currently we have a situation where student courses are part of a value list. We use that value list as a drop-down list. Whenever a new course is added it is put into the value list.

       

      It was suggested to me that I would be better creating a layout with all the course information and adding in new courses to the new layout, rather than the value list.

       

      Then to use the course field in the new layout to populate the drop-down list. Is this an alternative without using value lists? All our drop-downs come from value lists. If it is possible, is it necessarily better practice?

        • 1. Re: Using lookup values as a drop-down list
          keywords

          Probably what was meant was:

           

          1.     Create a table for courses, where each record is a course

          2.     Define your value list to display the list of courses in that table (probably use the course ID as the primary field and also display the course name, but configure the list to show the name only)

          3.     Set the value list to display on a course ID field on the layout where the course selection is made; this way when a course is selected, the (hidden in the list) course ID is posted into that field, establishing the relationship link

           

          With the list onstructed as per step 2, each new course added to the table is automaticaly added to the list (or conversely removed, if a course is deleted).

           

          Hope that makes sense.

          1 of 1 people found this helpful
          • 2. Re: Using lookup values as a drop-down list
            LadyBear

            It makes great sense.  I am just wondering from a more technical point of view if it is actually better. 

             

            Adding the course to the value list versus adding the course to a layout?

            • 3. Re: Using lookup values as a drop-down list
              Mike_Mitchell

              How much do you want to be involved in data entry?  

               

              You don't add records to a layout; you add them to a table. What keywords is describing is often referred to as a "domain" table (because it defines the "domain" a particular value can take). Often, in a very simple system with few users, there's not much difference from a maintenance perspective. However, once the number of records starts to grow, and especially once the number of users starts to grow, you can be overwhelmed with the need to keep the value list up to date. That takes you away from doing things that really do require your time.

               

              Another big problem with managing things via direct editing of the value list is version control. If you make your upgrades happen in a development environment, then move the old database out and the new one in, you'll always have to remember to capture the value list(s) from the old database via copy-and-paste if you use static lists. On the other hand, if you use tables with records, you can manage those tables just like you manage any other data during migration.

               

              The solution, therefore, is to provide the means for users to do it themselves. You give them the value list, then give them a button to "Add new course" or "Edit list". This then pops up a list view of all possible options (which is the domain table). They make their edits, then those edits become part of the value list from then on. The key: You don't have to get involved.

               

              Of course, you'll want to provide some traps to prevent bad things from happening - like duplicate entries, deletions, or whatever business rules dictate. Often, these value lists are built by "DBAs" or "Super Users" - people who have elevated privileges and have been trained how not to screw things up.

               

              Hope that answers the question.

               

              Mike

              • 4. Re: Using lookup values as a drop-down list
                keywords

                Here here to all of Mike's post. A variation on Mike's opening question, and broadly the focus of his final paragraph, would be: "How much do you want to CONTROL data entry?" My point here being who can add courses? what naming conventions do you want to follow? etc

                 

                To my way of thinking it is easier to control and manage a table than a value list. For simple value lists that are unlikely to change and are purely for data entry purposes (such as Yes/No or the like) then a custom value value list is fine, but I would definitely look to a table-based list. This table may, in due course, simplify other processing such as course level statistics, etc in addition to your immediate need to generate a list of courses.

                • 5. Re: Using lookup values as a drop-down list
                  beverly

                  True. Easy to maintain a table of values. And I do for some lists. However... We do have a 'sort-by-valuelist' option in the sort dialog. And sometimes you need a sort that is NOT alphanumeric. These must be in the VL definitions.

                  I have clients with lists of measurements. (in. ft. yd. for example) We don't want the feet to sort (alpha) before the inches! And in the fields used to select these values, the order is most important.

                   

                  Just keep that in mind when determining where to store your values.

                   

                  -- sent from myPhone --

                  Beverly Voth

                  --

                  • 6. Re: Using lookup values as a drop-down list
                    keywords

                    Totally agree. I have a situation where, in a cashbook report I want Income fields to sort before expenditure fields, and the simplest way to accomplish this is a codes value list which is created for this specific purpose.

                    • 7. Re: Using lookup values as a drop-down list
                      Mike_Mitchell

                      This is a good point. There are some tricks you can use to get around this, but they're likely to be arcane for the average user to manage. So if you need the non-alphanumeric sort, a custom list may be the best option.

                      • 8. Re: Using lookup values as a drop-down list
                        LadyBear

                        Thank you.  This is just what I was looking for.  Thanks to keywords as well.  There definitely needs to be some control as historical data is still valid if students want their old Certificates re-printed or to know what the name of the course they studied was.  I also can't have anybody changing the data, only people doing specific roles.