Complex value list partial-solution

[dbx1999]

Hello everyone,

I have hit a stump with a current solution I'm working on and wondered if anyone would have any ideas. The problem is that i have 2 conditional value lists but i need them merged into one.

For our example here's my database:

Groups.Groups_ID

Items.Groups_ID

Items.Options_ID

Options.Options_ID

Options.Options_Name

Options.is_global

The first value list will have a relationship that returns all Options that have the same Group_ID as Items.Group_ID which the user selects. We'll call this relationship"Group Options"

The second value list will return all Options that have the is_global flag set. We'll call this relationship "Global Options"

So right now we have 2 value lists. One that has the group options and one that has the global options. The next step is to figure out how to merge these two value lists together. The closest I've come to accomplishing this is the following:

I create a calculation field Items.Option_ID_Calc which uses the List() function on the "Group Options" and "Global Options" relationships which will give us a return delimited list of the values in those relationships. Since you must have an indexed field for a value list to work i also created a text field which I called Items.Options_ID_Lookup that looks up it's value from the calculation field Items.Option_ID_Calc.

Next I create a self join relationship on the Items table and create the value list referencing the Items.Options_ID_Lookup field. Everything up to this point works as expected, but the final step is where I'm having trouble. I need to have the Options.Options_ID as the value that gets stored into the popup menu's field, but i want to display the user friendly name Options.Name in the popup list. So I create another calculation field and lookup field that references the display name. Items.Options_Name_Calc and Items.Options_Name_Lookup. I can set the value list to use the value from Items.Options_ID_Lookup and display the values from Items.Options_Name_Lookup. The name is parsed properly but the entire value field is appended to each line item in the popup list.

For example if i have the following names and values:

1 Option One

2 Option Two

The popup menu will display:

12 Option One

12 Option Two

and when a list item is selected the value saved to the field is:

1

2

So the selected value is stored with the returns in-tact instead of being parsed per line and associated with the display value. Once again if i just use one field and not two in my value list the returned value is correct.

I'm not sure if this is a FileMaker bug or if there is something I'm doing wrong. I very well expect that i could be over complicating this issue but i haven't had much success trying to find this exact issue anywhere out there. Any help would be appreciated.

[comment]

Try something like this:

In Options, a calculation field (result is Text) =

Case ( IsEmpty ( GroupID ) ; "Global" ; GroupID )

In Groups, a calculation field (result is Text) =

GroupID & ¶ & "Global"

A relationship based on matching these two fields will show all Options that belong to the current group OR do not belong to any group (don't really see the need for a special flag field here).

[dbx1999]

Works like a charm! I was sure that i was over complicating the situation. Thank you very much for your wisdom!