Jump to content

Complex Relationships


Charles Henebry
 Share

This topic is 5659 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5659 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.