4 Replies Latest reply on Nov 15, 2011 9:56 AM by johnhorner

    Dedicated Table for Value Lists

    johnhorner

      Title

      Dedicated Table for Value Lists

      Post

      I have a solution that ustilizes many custom value lists.  this works fine except that i have shared this solution with a friend of mine and everytime i need to update their solution with whatever current version i am working on, i loose any changes they have made to their custom value lists and they get my default value lists.  i was going to dedicate a table with a single record and text fields to store these value lists so that their changes would be imported along with the rest of their data.  however, i noticed that when i do this it always seems to display the field contents used to generate the value lists in alphabetical order, and not the order in which they are entered into their respective fields.  this also means that i cannot create "groups" using the "-" character.  for example, if my field contains:

      main number

      cell

      fax

      -

      studio

      home

       

      i get instead:

      _________________

      cell

      fax

      home

      main number

      studio

       

      is there anyway to preserve the original order when using field contents for a value list (besides numbering them)?  please advise.  thanks!

        • 1. Re: Dedicated Table for Value Lists
          philmodjunk

          Not easily and not for long lists of values or for value lists where you need to use two fields in the value list setup.

          There is a way to specify an arbitrary order for your table of values if you can fit the use of your value list within those limitations.

          Add two fields to your table of values: Order: a number field and cSortKey, a calculation with this rather unusual calculation:

          Substitute ( 10^Order - 1 ; 9 ; " " )

          ^ is the exponent key so if order = 3, you get 10 raised to the 3rd power or 1000. Subtract one and you get 999. The subsitute function then replaces the 9's with spaces. Thus an order of 5 generates 5 spaces where an Oder of 2 generates 2 spaces.

          Set up your value list with the specify field option and choose your value field for field one and cSortKey for field two. now specify that the values be sorted by the second field.

          • 2. Re: Dedicated Table for Value Lists
            johnhorner

            wow... that is a clever work around... i like it!  mind if i ask a follow up?  this is the first time that i am trying to use a table based value list.  my initial thought was to have a single record in this table and a field for each value list that i was going to set up this way.  each field would contain the values separated by a carriage return.  i thought this would be simple to implement and also allow the user to customize the value list by simply providing them access to the fields in this table as needed to edit directly.  however, that clearly will not work with the method you outlined which, if i understand it correctly, would require a separate record for each value?  what i don't completely understand (among many things, of course!), is how you structure this for more than one value list.  for example, if i have two value lists and one of them has 10 values and the other has 2 values, then there are 8 empty records (or i should say 8 empty fields in those 8 records that are not needed for the shorter value list).  are the empty values ignored when generating the list?  do you need a separate order field and csort field for each value list related field?  it seems like it would also require a moderate amount of scripting to allow a casual user the ability to edit fields?  is this how you would implement your technique or is there a better way... i'm assuming you don't make a separate table for wach value list? 

            • 3. Re: Dedicated Table for Value Lists
              philmodjunk

              You should use separate tables for each such value list. And you definitely want separate records for every value. This way, you can use all of the tools for managing records in your database for managing the values in your value lists. Portals, sorted lists, table views, etc can be very useful methods for managing these lists of values.

              You may also want to consider setting up conditional value lists instead of your arbitrarily ordered lists. With a conditional value list, you can select a category in value list a, and then the values shown in value list b are only those values which are members of that selected category.

              Forum Tutorial: Custom Value List?

              Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

              Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

              • 4. Re: Dedicated Table for Value Lists
                johnhorner

                thanks phil... that's very helpful (especially the demo file)!