Value List based on relationship

[Carl Smith]

I have a Products table (Key - Product ID), a Product Attributes table (Keys - Product ID, Attribute ID) and an Attributes table (Keys - Attribute ID, Attribute Group ID).

My first Table Occurence from the Products table to the Attributes table goes via the Product Attribute table.

I have created a new Table Occurence using the relationship from Products Field 1 (calculation field - number) to the Attribute Group ID (number field) in the Attributes table in order to get "filtered" records (e.g. those available based on this Table Occurence)and want to attach a value list to Products Field 2 (number field).

I have defined the Value List to use values from this relationship to the new Table Occurence.

My problem is that although I can see the correct records if I use a portal, when I define the value list based on that relationship I'm getting a list that includes other existing records in the Attributes table; not the specific ones that would be based on the Field 1 relationship.

Am I missing something? Any advice?

[bcooney]

What is your goal? A portal on the Product Form that shows all the Attributes for this product and that can be filtered by Attribute Group ID?

[Carl Smith]

I want to be able to have the value list show only those records that are found using the Field 1 to Attribute Group ID relationship.

[bcooney]

Sorry, I don't understand. Perhaps if you described your fields beyond "Field1" than I might figure out your goal here.

You might want to search for "conditional value lists" as what you say suggests that's what you are looking to do. Or, "filtered portals."

[Carl Smith]

I've attached a file that hopefully gives you a better insight to my problem.

My feelings are that I've defined the value incorrectly, but can't immediately see why I'm not getting the list I wanted (see text on Products layout in attached file)

Thanks

Value_List_issue.zip

[bcooney]

Here's a new version for you to take apart. I've reorganized your Relationship Graph to use the Anchor-Buoy method (it's a must for any project).

You were looking for what is called a filtered portal. You'll see on the products form layout two portals, one filtered by Attribute Group and the other showing all attributes for a product.

Value_List_issue2.fp7.zip

[comment]

use the Anchor-Buoy method (it's a must for any project).

Is it?

http://www.fmforums.com/forum/showtopic.php?tid/191475/post/273144/#273144

[bcooney]

missed that thread, thanks, comment. Still swearing by A/B, though.

[comment]

That's all right - as long as it's clear that it's a personal preference, and that each method has its pros and cons. There's no "must" here.

[Carl Smith]

Hi bcooney. Thanks for the example file. I think I need to expand upon my problem so I've spent the last few days trying to resolve this issue and have now created a file, which I've attached.

PROBLEM:

I want to be able to view a list of valid attributes from the Products table and then, by the use of another Value List, filter the valid attributes that appear in the portal by the First Word field (in Attributes)for a portal to show only the related records. The value list should only list those attributes that exist as valid (e.g. Deleted = 0) and the list - based on my example file - should be as follows;

Blue

Red

Pink (not Yellow as this has been deleted)

I then intend to select a record from this portal to create Product Attributes based on this final list.

At the moment I can't get my head around the relationship required that gives me the result I'm after.

related_value_lists.zip

[bcooney]

I changed your relationship for the second portal. As for matching on the word, don't. When you create your value list, still include the Attribute ID and the word (just show the second value so the user only sees the word), but store the AttributeID. You'll need a related value list from Products.

related_value_lists2.zip

[comment]

If I understand you correctly, you want something like this.

related_value_lists.fp7.zip