5 Replies Latest reply on Sep 3, 2013 1:29 PM by philmodjunk

    Value Lists

    obidon

      Title

      Value Lists

      Post

           I have created a set of tables which the user populates to create a set of value lists.  These lists are technical in nature.

           Each record in the value list tables has a uuid and data field. I generally use a numeric value for uuid, which i understand increases system performance.  Data can be numeric and includes decimals or text alternatives such as Limited or Not Applicable.

           The value lists are utilised by fields in the main system.  These fields have to be viewed in a table format.  I am using a portal. this is a client requirement.

           The problems are as follows.  

             
      •           If I use a text uuid, the value list will sorted by text.  ie decimals are not sorted properley
      •      
      •           if I use a text uuid, I can not apply notation such as $ or mm2 etc
      •      
      •           If I use a number uuid, text values will not be displayed at all

           In other solutions, where a form layout can be used, I have opted for three fields

             
      •           A number choice data entry field or
      •      
      •           A text alternative field
      •      
      •           A calculation to display in the final output

           This isn't an option in my list/portal view.  Screen real eseate is at a premium

           Any thoughts?

        • 1. Re: Value Lists
          philmodjunk

               I think you need to explain what you mean by "uuid". In FileMaker 12, there's a new get function: Get ( UUID ) that returns a text string to uniquely identify the record and such should be entered into a field of type text. Is this what you are using or something else?

               UUID's or serial numbers are generally hidden from the user when possible and the format, sort order, etc of them is generally not an issue for that reason. Hence my questions as to what you mean by them.

               And in a two field value list, uuid and data field, one usually hides the first field and shows only the second or at least you sort values on the second field, so this again raises questions about exactly what you are doing.

          • 2. Re: Value Lists
            obidon

                 Hi Phil

                 I create a UUID (local key) field when a new record is created,  This is achieved by using the Get(UUID) calculation on record creation.

                 I read this post a while back http://filemakerstandards.org/display/bp/Key+values which made a lot of sense.  The logic being that if you are consistant with using number fields rather than text fields, processing will be quicker.  The solution I am working on may quickly run to millions of records.

                 My problem is rather that the second field you refer to is a mix of numbers and text.  Eg 1, 1.5, 2, 15, LIM, N/A. If this field is a number field, my text values won't display.  Conversley if I utilise a text field, my numbers are in the wrong order ie 1,1.5, 15, 2, LIM, N/A. I suspect calculation issues going forward.

                 In previous solutions, I have utilised 3 fields  to overcome this. You enter a number in one field or n/a etc in the other. Triggerscripts ensure no conflict and the third (calculation) field assess and display the appropriate answer on the report.

                 Currently I am woring on a table based solution, so the previous phillosophy won't work. Numbers in the wrong order seem like a poor compromise on my final solution

            • 3. Re: Value Lists
              philmodjunk

                   Get ( UUID ) as I understand it, generates text and numbers. Unless I am setting up a system where the data may be generated on multiple copies of the database that are then merged, I'd auto-enter a serial number instead. A number field will index differently than text which is probably where the argument is being made that the field should be number, but then the text values are ignored and I'm not sure that the values that you'll get will then correctly treated as unique values in every possible case. And an auto-entered serial number is easier to set up an manage.

                   1,1.5, 15, 2, LIM, N/A. is the correct sorted order for text as text follows different rules than numbers for sorting. If you then use this value in a calculation, it will not create an issue as numeric calculations will convert the text into the correct equivalent number value automatically. So the only issue that this creates for you is that the values are not in the correct order.

                   Do you even need to use a UUID or serial number for this value list? Using a serial number of UUID as the first field makes sense if this value is then used for linking the record to a record or records in another table. But here, you seem to be just looking up a value to use in a calculation. Thus, it may make sense (or it may not) to just enter the value and not use the UUID field at all for your value list.

                   If that can be made to work, there's a trick for specifying an arbitrary sort order for the values in this type of value list that may work for you if the number of values in your list are not too terribly long and then only if you don't need to use both fields for your value list.

              • 4. Re: Value Lists
                obidon

                     The system is particulary complex.  It is about electrical systems and will be utilised on a server with synced iPad access.

                     The value lists are in the high hundereds long.  They are catergorised and sub-catergorised.  Access is via a filtered value lists using popup menus to get the appropriate choice. The value lists will be modified by a senior manager and iPad users will resync an external preference file. I don't like serial numbers in that enviroment.

                     Is there a way of using a third field which sorts a text list by numerical sort order?

                • 5. Re: Value Lists
                  philmodjunk
                       

                            The value lists will be modified by a senior manager and iPad users will resync an external preference file. I don't like serial numbers in that environment.

                       But are the iPad users adding new records to this table of values? That would be the main reason for doing so and then I recommend that you use a text field for the UUID. And I still don't see a reason, there may be one but I don't see it in your posts, for using the value list to enter the UUID into a field. Is this field then used as a match field to other related data? (Then you should indeed use UUID or a serial number field as the first field in the value list.)

                       FileMaker value lists only permit two columns of data so that third field options is out of the question. This limitation on value lists is one of the more critical limitations of FileMaker when you compare it to competing products where you can pop up a value list of any number of columns of data and sort on a field not even specified as one of those columns (MS Access).

                       I suggest you not use a value list for this. I realize that screen  real estate on an iOS device is very precious, But you can display these items in a portal with the portal rows converted into a button such that tapping the portal row selects that value and enters it into a specified field of your current record. The portal can then be sorted exactly how you need and you can display as many different columns of data as you need in the portal.

                       You may even find it useful to use the portal with a search field where entering text in the search field filters the rows in the portal to display fewer and fewer matching values. See this demo file for examples of that method: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7 Open this file from FileMaker 12's File menu to get a copy converted to the FMP12 file format.