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.
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.
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.
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.
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.
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.
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.