7 Replies Latest reply on Sep 20, 2011 9:04 AM by Sorbsbuster

    Importing a value list



      Importing a value list


      We have traditionally allowed the user to edit a value list by sending them to a field in a Preferences Table where the value list entries are recorded.  The value list is then defined as the values in that field.  It is not as neat as the interface they are presented with when allowing the 'Edit...' button on a pop-up list, but the problem that this method overcomes is when we release an upgrade and import the user's data then the value list will reflect their changes.

      We've been using that method for some time - is there a more elegant way to import a custom value list at point of upgrade?  Or at least is there a way that allows the value list to have non-selectable separators and be in a custom sort order?


        • 1. Re: Importing a value list

          Or perhaps I could describe the problem and maybe there is another completely different way to provide the solution.

          Let's say the user needs to create a record for a full description of the tasks to be performed.  Once completed, the full task description might read:

          "Set work piece in jig.  Drill hole.  De-burr edges. Stamp with Part Number.  Spray with oil.  Store safely."

          The vast majority of the individual parts of the tasks for any complete job can be permed from a reasonably short list of separate tasks, just in different sequences, or with minor editing amendments.  So we give the user a pop-up list beside the full Task Description field and they can click each component of the task one at a time and gradually build it up.  They can freely edit the full description.  It makes the typing of the full task description just a matter of a few clicks in 90%+ of cases.  The users decide what tasks they want to add to their list, and what order they want them in, and a pop-up list with an 'Edit...' is just perfect.

          Except - so far at least - at upgrade time, where we would lose their value list customisation unless we remember to copy and paste from the old file to the new.  The lack of robustness in that method means we don't do it.

          • 2. Re: Importing a value list

            The inflexability of value lists for sorting and displaying separators haunts us all.  We have had intern Developers come in and re-create custom value lists (as well as de-jidder and beta test).  There is no way to add separators if based upon values from field without adding a dummy record with ------ and then using auto-enter (replace) to remove the darned thing if it is entered (but you could never get two in a value list).  And having a dummy record in actual data is very bad idea.  And there is no way to sort by a first field but only show the second.

            I suspect that the next version will address some of the bugs associated with value lists.  Maybe they have fixed this problem as well.  We can  hope.

            • 3. Re: Importing a value list

              Thanks for the insight: at upgrade I can extract the value list values, but it's getting them back in again that is the stumbling block.

              Another way is to create another table for that value list, with a genuine Item ID and an ItemDescription, and let the user fill in values there.  They can be allowed to create several records with an ItemDescription of '---' to act as a separator.  The value list can be set to be drawn from 2 fields, only show second field, etc, sort by the ItemID or, better, another user-specified sort value (ie: a 3rd field they enter in that Value list table.)

              We just don't use that because it seems so convoluted compared to the temptation that is FM's native value list (and there was an element of thinking we had missed some easy way to do it.)  With no suggestion of the easier method I think I'll use that idea in this current project.

              Thnaks for the help LaRetta.


              • 4. Re: Importing a value list

                Update: if you make the user experience of updating the Value List in a sperate table acceptable, then it works OK(-ish).  Here is what I did:

                - Create a Value List Table for that value list
                - It has an ItemID, Item Description, and ItemPriority (set to auto-increment in jumps of 10)
                - Added a calculation field of ItemPriority and Description, to make it look like '030 - Drill hole', for example

                - Define the Value List as derived from the Value List Table Item.  Set the first field to be ItemDescription, second field to be ItemPriorityAndDescription, show only second field.  Sort by second field.

                Other niceties were added, like if they click a separator line it doesn't seem to select anything.  If the user changes any sort order value it automatically re-sorts the list and pitches them out in 10s again to make slotting a new value into place easier.

                It has all the functions I need; just have to see how the users react.  If anyone wants more info just ask, or if you've an improvement, please say.

                • 5. Re: Importing a value list

                  I have this working now.  The user can customise a Value List however they like and it will be imported at upgrade.  The features are:

                  - it is maintained in its own table
                  - the user can click an 'Edit...' button beside the pop-up and it takes them to the Value List.  It always displays all records and correctly sorted
                  - on that Value List layout they can, by button, create a new value, add a new separator, move a value from here to there on the list, or nudge any value up or down the list

                  - the only downside I am stuck with is that the pop-up displays the sort-order value (though does not insert it).  So the pop-up list looks like:

                  010 - Get eggs
                  020 - Get flour
                  030 - ------
                  040 - Make pancakes
                  050 - Make pizza dough
                  060 - Make cake
                  070 - ------
                  080 - Put in oven
                  090 - Put under grill
                  100 - Put in microwave
                  110 - ------
                  120 - Put in fridge
                  130 - Put in freezer

                  If anyone has any interest I'll put it into its own file and post it.

                  • 6. Re: Importing a value list

                    One gimmick that works for short, single field value lists to have a specified sort order field, yet keep the sort field invisible is to set up a field that only stores spaces. Each record has a unique number of space characters in this field. This field has to be included as field 2 in the value list setup, but is invisible to the user.

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

                    Is a calculation often used to turn the number in SortField into a string of the same number of spaces.

                    • 7. Re: Importing a value list

                      That's very clever.  I realise now why you said it had to be for short lists - I let my sort order jump in 10s to let the user easily pick a sort order in between two others (although I later added the Nudge and MoveFromHereToHere buttons, so I could drop that now) which produces the same effect of a very wide box as a long list would.

                      Thanks, Phil - I'll see how many tasks users typically create and review if I should/could change.