9 Replies Latest reply on Apr 29, 2015 11:07 AM by philmodjunk

    ValueList Table vs CustomValue List preference or hard rule

    RockSure

      Title

      ValueList Table vs CustomValue List preference or hard rule

      Post

      Any hard and fast rule to when to use a CustomValue List over a ValueList based on a Table.

      Its apparent that if the list is dynamic or large that one would use a table over a custom list.
      but I am wondering more about a 5 or 10 item list that for the most part would be static

       

       

        • 1. Re: ValueList Table vs CustomValue List preference or hard rule
          philmodjunk

          It's all in the number of values and whether the user is likely to ever need modifying the values in the list as well as how you work with your data model. Small lists that never need updating by the user are good candidates for a custom value list, but sometimes you find that you need a table with one record per value list value for specific reporting or analysis purposes anyway and then, since you already have a table that can serve as the value source for your value list, there's no point in maintaining a list of custom values in addition to those in the table.

          And yes, you can enable the edit option for custom values, but this makes it very easy to accidentally introduce errors into your value list where a developer designed method for an equivalent table based list can often minimize some of the possible errors.

          And it's actually pretty easy to take the data from a custom value list and export/import it into a table should the need arise in the future to set up a table based value list in place of the custom values list.

          • 2. Re: ValueList Table vs CustomValue List preference or hard rule
            RockSure

            I understood the above but still a little confused of where to set this up or if I need to set this up in both.

            In the case of a value list such as City where this has a table of city names and is used in client table, shipping table etc. 
            Will/should, what is best practice,  the related City names update if a change is made in the Parent table. 
            What I am trying to avoid is having to look up based on serial numbers rather then the actual city name and I may be getting this confused from my old dusty access query days.  

            VL_City:PK<-------Client::FK_City
            VL_City:PK<-------Shipping::FK_City 

            is this best set this via
            1. Fields/options/checkbox/lookup with its related options settings
            2. Value list (looks to do the same as set in fields but without the options)
            3. Or both. 

             

            Sorry just a little confused. 

             

            • 3. Re: ValueList Table vs CustomValue List preference or hard rule
              philmodjunk

              Any value list that has to enter an ID while the user selects the value by name must be set up as a "use values from field" value list. Such a value list cannot be defined as a custom values value list.

              Field 1 would be your PK field and field 2 would be the name of your city--if you can guarantee that the city names will be unique--which can easily not be the case.

              For a detailed description of how to set up this value list, see "Adventures in FileMaking #1 - conditional value lists". Before the file shows you how to set up a conditional value list--not what you describe here, there's a screen that walks you through setting up this type of ID number based "use values from field" value list.

              • 4. Re: ValueList Table vs CustomValue List preference or hard rule
                RockSure

                Yes understood. and maybe I should have started a new post.
                This is assuming not using custom value list at all. 
                The ? is that using a Pk/FK relation the child table stores the PK value in the FK field and not the descriptor from the ValueList so when you preform a find or more complex you export the client table including city and it shows 268 as a value rather then New York. 
                Would the following be correct?

                So thinking this through you would have to export the related Descriptor in the parent table "city vlauelist" table rather then the fk CityID field of the child table?
                But in regards to a find that would be the fkCityID with a valuelist assigned to it?  

                 

                 

                • 5. Re: ValueList Table vs CustomValue List preference or hard rule
                  RockSure

                  Yes understood. and maybe I should have started a new post.
                  This is assuming not using custom value list at all. 
                  The ? is that using a Pk/FK relation the child table stores the PK value in the FK field and not the descriptor from the ValueList so when you preform a find or more complex you export the client table including city and it shows 268 as a value rather then New York. 
                  Would the following be correct?

                  So thinking this through you would have to export the related Descriptor in the parent table "city vlauelist" table rather then the fk CityID field of the child table?
                  But in regards to a find that would be the fkCityID with a valuelist assigned to it?  

                   

                   

                  • 6. Re: ValueList Table vs CustomValue List preference or hard rule
                    RockSure

                    Maybe I should have started a new post  
                    Whats the difference between ValueList and Look-up and when is one better then the other. 
                     

                    • 7. Re: ValueList Table vs CustomValue List preference or hard rule
                      philmodjunk

                      They have different purposes and so asking which is better is a matter of comparing apples to oranges.

                      The fact that you are entering a pk value into an fk field holds the answer to your question. When you do that, you link the current record on your layout to a specific record in the table from which you entered the PK. If you have used Manage | database | relationship to ink the FK and PK fields, you now have access to any field in that related table, including the very descriptor field that served as "field 2" in your value list.

                      If you want to find records in the FK table, you can either use the very same value list and use the descriptor to enter the PK value in order to perform your find or you can specify the descriptor text, but in the descriptor field of the related table instead of the FK field of your layout's table.

                      Example:

                      You want to find all records in your employee leave table with "Smith" as the last name when you have this relationship:

                      Employees::__pkEmployeeID = EmployeeLeave::_fkEmployeeID

                      Go to the employee leave table
                      Enter find mode
                      Specify "Smith" in the Employees::EmployeeLastName field
                      Perform your find

                      The above "outline" can describe either a manual or a scripted find.

                      Lookups copy data from a parent table into a field in a related child table. This is useful mostly to capture a "snap shot" of a value in the parent table that could change in the future. Looking up current prices from a products table to put into an invoice is a very common use of a look up. The prices might change tomorrow and if they do, you don't want that change to alter the values shown on past invoices, so you use a look up to copy over current pricing. In my employee example, you would not copy over the employee name because a change in marital status might change the employee's name. If that happens, you only change the name in the single affected employee record, you wouldn't want to have to track down and update a bunch of employee leave records so that they show the new name as well. Thus, you leave this data as a field in the Employee table.

                      • 8. Re: ValueList Table vs CustomValue List preference or hard rule
                        RockSure

                        I think I am starting to understand.

                        Clear
                        A price should be a snapshot and done via the fields/lookup/options (apples)
                        A name should be done via the vlauelist as the name may change  (oranges)

                        Fuzzy 1:

                        For a field like state or city whose names should never change (unless a state abdicates, its a funny) it would seem at first you could do either and would or should lean towards the Fields/Lookup (mixed fruit)

                        But thinking it through I would think ValueList since you may want to do conditional vlauelist for city by state?
                        Did I get this right?

                        Fuzzy 2:
                        Both Lookup and ValueLists need relationships.

                        Lookup relationship may be any field to any field and disregards the Many to Many concerns
                        but would not be a PK to FK which would defeat the purpose?

                        Value List could also be any field to any field?
                        But when it comes to values that need dynamic update like Employee name should be based on the PK---->FK?

                         


                         

                        • 9. Re: ValueList Table vs CustomValue List preference or hard rule
                          philmodjunk

                          You are on the right track, but keep in mind that sometimes the "change" needed is not that an entity changed its name but that a user error entered the data incorrectly. And you might keep in mind that only a few years ago, Zimbabwe was named "Rhodesia" and Zaire was named Congo and did I see that it's now called Congo again?.

                          Rule one and the reason relational databases such as Filemaker exist is that you should avoid storing the same data in more than one place wherever possible. Looked Up data can generate such duplication if used where it should not be used. You have to ask and answer this question: "What happens to the database if I change the value of this field in the parent record?"

                          The typical uses of dynamic links and looked up data are based on relationships--often exactly the same relationship.