6 Replies Latest reply on Oct 10, 2015 3:08 PM by clayhendrix

    Value List Best Practices

    clayhendrix

      I think that it is easier to create a table with a field for value lists and create a layout where users can add, delete, and edit the value list options.

       

      That is a more professional option that having users edit value lists, in my opinion.

       

      Regardless, of whether you agree with that or not, my question is this.

       

      If I have a value list for production status and another value list for customer status with each list having different and unrelated values, should I make a table for each list? I think the answer is no, because the different records would seemingly connect unrelated values for the lists. I cannot think of when that would be a problem, but it seems like a bad practice.

       

      Please avise.

        • 1. Re: Value List Best Practices
          Mike_Mitchell

          clayhendrix wrote:

           

          If I have a value list for production status and another value list for customer status with each list having different and unrelated values, should I make a table for each list? I think the answer is no, because the different records would seemingly connect unrelated values for the lists. I cannot think of when that would be a problem, but it seems like a bad practice.

           

          The answer is, "It depends." If the two lists have different values, then combining them into a single list would introduce confusion on the part of the user. It would allow users to put invalid codes on one or the other type of record. So you would want to avoid that.

           

          Now, you can avoid that by putting a key field into the table (return delimited) that you could then make part of a relationship on which you base the value list. That way, if a status applies to both types of parent record, you can just apply both codes and it'll show up both places. However, that adds another layer of complexity (and opportunity for error) to the user's task. So you'd want to make a determination as to whether that is worthwhile or not. It would depend on how savvy the users are and whether you can trust them to make the right choices on each record.

           

          HTH

           

          Mike

          • 2. Re: Value List Best Practices
            clayhendrix

            Thanks, Mike,

            Should I just create a new table for each value list that I want to maintain through a layout? After reading your answer, I see that perhaps that is the question I should have asked in the first place. It seems like a lot of extra unrelated tables, but I suppose tables are free, right?

            • 3. Re: Value List Best Practices
              Mike_Mitchell

              I'm not sure how rewording it like that changes the question, or the answer. You're maintaining a value list by means of a table. Presumably, that table has to be edited via a layout. If you want to segregate the value lists completely from each other, then you do separate tables. If you want to avoid duplicate values that apply to multiple value lists, then you put them in the same table and segregate them via a key field that's used from the parent table.

               

              For example:

               

              Production Status               Customer Status

               

              Draft                                   Lead

              In Process                         First Time

              Billed                                 Repeat

              Paid

               

              These two lists have no duplicates, so separate tables would be the default position.

               

              Production Status               Customer Status

               

              New                                    New

              In Process                          Repeat

              Billed

               

              If you don't want to edit "New" in two places, then you might combine these into a single table and have a third field called "Type":

               

              Status                                Type

               

              New                                   Customer

                                                        Production

              In Process                         Production

              Repeat                              Customer

              Billed                                 Production

               

              Does that make more sense?

              • 4. Re: Value List Best Practices
                beverly

                It might be worth a note, that if you are using the table to store value lists, the field with list-like (return delimited) values will be considered as separate in the list.

                 

                So Mike's

                NEW with

                Customer¶Production in the Type field would produce one "copy" of the unique value in a drop-down (or checkbox or radio) even though the value is repeated in other status, too. That is if the type field is used for the values.

                 

                The advantage is for conditional (or cascading) value-lists to limit what appears in the second selection based on the first. Type field is used again for the second list, but based on a relationship to the status value(s).

                 

                beverly

                • 5. Re: Value List Best Practices
                  Mike_Mitchell

                  Yes, true. In this case, "Type" is intended to be used as a filtering relational key, but the point is valid nonetheless.

                  • 6. Re: Value List Best Practices
                    clayhendrix

                    Thank you. I understand your reply now. I misunderstood your original reply. You are correct, I didn't have a different question, I just failed to understand your reply, but I do now.

                     

                    Thank you so much!