0 Replies Latest reply on Feb 9, 2017 12:22 PM by chris_belanger

    MagicValueList -- how to use it for two-field [key and value]

    chris_belanger

      I have really appreciated using MagicValueList (demo by Andries Heylen) to set up valuelists for data input. For anyone unfamiliar with this, here is a link:

      https://filemakerhacks.com/2012/07/25/magic-value-lists/

       

      I know a lot of members of this community are using this module.

      It has very clear instructions on how to get it to work with a checkbox set. It works swell when used for just one field (i.e. Name).

      But what I actually need to store is the ID related to that Name field.

       

      This can be done using FileMaker's Value List manager. A valueList can use two fields (i.e. ID and NAME), display only the 2nd field (NAME), but store the 1st (the ID) in the data field that it controls.  See FileMaker Help Link -> Defining value lists

       

      I need to be able to duplicate this using MagicValueList. It seems it should be possible IFF:

      1) I can discover how FileMaker internally stores its list in the instance above (ID & NAME) and

      2) I can configure (i.e. fake out) FileMaker into using the ValueList in the MagicValueList technique.

       

      I have set up two related tables, GROUPS and OPTIONS. OPTIONS has a foreign key to GROUPS.

      I wrote a custom function VL_GetGroupValueList ( GroupID ) that uses SQL to return a list from OPTIONS that has the OptionID and OptionName.

      SAMPLE:

      1010,Busn

      1011,Invoice

      1012,Sales

      1013,Ads

      1014,Production

      1015,Web

      1016,Programming

       

      The Field Name this is tied to is Systems.

      I followed the directions for CheckBox setup for the Systems field.

      1) I set a Conditional Formatting Formula of    MVL_CheckBox_1 ( VL_GetGroupValueList ( 3 ) )

       

      2) I also set the OnObjectEnter script trigger for Systems to use the EmptyScript (blank) script with the parameter:

           MVL_CheckBox_1 ( VL_GetGroupValueList ( 3 ) )

       

      Remember that VL_GetGroupValueList ( GroupID ) is a my custom function that queries OPTIONS and returns the OptionID & OptionName using a SQL query. Combined with the results of that function as shown in my SAMPLE above, this results in this:

      What I need is for the Systems field to contain only the keys 1012 1015 1013 for example above, rather than that whole shebang.

       

      Any ideas?

      If / when I get this solved, I am happy to post the solution and to explain it to the maker of the MagicValueList module for inclusion.

       

      Thanks,

      Chris Belanger

      Innovative Solutions