Dynamic value list strategy--relies on quickly updated table relations

[Deed]

I am using FM Pro 11 Advanced for Mac, and I am looking for help simplifying my use of value lists for a project.

I have a fairly complex set of 20 related data tables, each table with 100 fields, and approximately 20 fields in each table use value lists (400 value lists total). The field names are unique throughout the 20 tables--i.e., there are 2000 field names. The value lists contain highly variable text codes that are used in X12 Electronic Date Interchange (EDI) formats--e.g., X012, CH, 8, 837. Because the codes themselves are non-intuitive, I need to be able to see short code descriptions next to the codes when using the value lists; that constraint requires that I create the value list using "Use values from field . . .", which allows me to display a second "description" field.

One strategy is to create 400 separate files to supply the necessary value lists. Each file would have two fields: code and description. I am pretty sure this would work; it would just be a lot of files. In addition, because the codes are periodically updated by external standards-setting organizations, it would be tedious to maintain these files.

Another strategy--which might or might not be feasible but seems more elegant--is to create a master "value list" table. Besides having fields of "code" and "description", the table would have a field called "externalfieldname".

Step 1: Within each of the 20 tables, I would include a calculation field called "activefieldname". Each field in the table that needs a value list would have a script trigger "On object enter" which would execute a script to update the activefieldname.

Step 2: I would link this table to the master value list table using: "activefieldname" in the data table and "externalfieldname" in the master value list table.

Step 3: The value list for each field would be defined as "Use values from field . . ." and would "Include only related values". So I ought to see only the value list items that are relevant to the "activefieldname".

Step 4: It doesn't work. "Activefieldname" is updated appropriately, but the value list items that are displayed seem to be using the prior "activefieldname", not the just-updated "activefieldname". It seems that the "related table" functionality is not updating fast enough to supply the value list items that I need.

Should I keep trying to make this work? How? Or could someone recommend another value list management strategy?

Thanks--

[comment]

Could you please update your profile? It says version 8.5, yet you speak of script triggers.

[innodat]

To stick to the concept you've set up thus far...

You probably need to find a way to commit the record after updating the field name in order for the value list to work as expected. In theory you could do that, then go back to your field (not sure Set Field by Name [...] works, you might need GoToObject [...] ) - the problem is that this only works with drop-down lists, not pop-up menus. With pop-up menus, it doesn't "pop" up the value list. A possible work-around could be field validation in conjunction with drop-downs...