dynamic, user customized, value lists with sort problem
I am trying to set up a method for allowing certain drop-down menus to have both default and user-added items. The user-added items will be unique for each FMP user. I also want to be able to control the sort of the items and hopefully use seperator lines where desired. I have tried a few schemes using FM value lists and even looked thru the forum, but I cant quite get it to work.
My current scheme involves using a couple tables and relationships that feed a value list, as follows.
Table 1: has a field with the "menu name" text string.
Table 2: is a join that has the menu name and a group number that provides a relational selection of items in Table 3:
Table 3: is a list of Group number, Menu Item Name, and Menu Item Sort Code
Table 1 is related to Table 2 by "menu name"
Table 2 is related to Table 3 by "menu name" and "group number"
The resulting value list has all the right items, but I cant control the sort without displaying the Sort Code in the second field. I have tried setting the sort in the relationship, which works for the Table2/3 sort, but then the individual groups are merged alphabetically (not desired).
How can I make this work?