AnsweredAssumed Answered

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

Question asked by chris_belanger on Feb 9, 2017

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

Outcomes