Complex Relationships

[Charles Henebry]

I'm using FM 7 and I want to set up a relationship that picks out members of a class OR of just a subclass, depending on what the user requests.

For simplicity, I'll set the problem up in terms of color. Let's say I have a table with six records in which one of the data fields is color:

Data table - color subclass

record 1 - true red

record 2 - true blue

record 3 - teal

record 4 - true blue

record 5 - mint green

record 6 - tawny

At present, I have a simple 1-1 relationship linking this table to the main table. When the user chooses a color from a pop-up menu, the relevant records are displayed in a portal. 



But teal and true blue are both subclasses of the class blue. Here's the trick: I'd like to list both subclasses and classes in my popup menu, and set up the relationship between tables so that the user can use a single portal to view just the teal records (#3) OR all the blue records (#s 2, 3, 4).



I have the feeling that the answer lies in how I encode the class information. The simplest would be to create a second data field which gives the class by means of a calculation. The result would be:

Data table - color subclass, color class

record 1 - true red, red

record 2 - true blue, blue

record 3 - teal, blue

record 4 - true blue, blue

record 5 - mint green, green

record 6 - tawny, orange

But when I do this, I find I can't create a working relationship in the relationships dialog box. I want to do it as follows:

       View Table                  Data Table

       color to view       =     color subclass

OR     color to view       =     color class

But when I add a second criterion in the relationships dialog box, it automatically inserts AND between the two criteria, and there doesn't seem to be any way to modify the AND into an OR.

So plainly this is the wrong way to set up the data tables. I'm sure there must be some solution, and I'm hoping one of you more experienced filemaker users has an idea.

[comment]

One way to do this is to define a calculation field in the Data table (result is text) =

Class & ¶ & Subclass

Define a value list using values from the calculation field and use it for your popup menu. Make your relationship match the selector field in the Main table to the calculation field in Data.

[Charles Henebry]

One way to do this is to define a calculation field in the Data table (result is text) =

Class & ¶ & Subclass

Define a value list using values from the calculation field and use it for your popup menu. Make your relationship match the selector field in the Main table to the calculation field in Data.

This works, but only if I use a text field with a calculated autolookup rather than a true calculation field—as a calculation field can't be indexed and so can't be used in defining a value list. That's a slight pain, as one has to periodically "update" the lookup but at least I have a working solution now. Thanks.

[comment]

a calculation field can't be indexed

Of course it can. If, for some reason, you have made the calculation unstored, change it back. I do not understand the lookup part: the calculation field needs to be in your Data table - the same table where the Class and Subclass fields are.