Matching in a join table

[sandyinlet]

I'm working with two tables, Vendors and Products. There's a join table listing matches of Vendor IDs and Product IDs. The interface shows two portals, one listing Vendors, the other listing Products. When the user clicks on a Vendor its vendor id goes into a global.

Vendor #9 is selected by the user. There are two records in the Join table showing "9" in the Vendor ID column, with a value in the adjacent Product ID column. I need those two Product Names in the Products portal to become bold. How can this be done?

With appreciation

[comment]

Something like this?

HighlightFilter.fp7.zip

[sandyinlet]

Something like this?

Not quite. Both portals need to be on the same layout. The client wants this to work in a modal way. Click on a Vendor and see all the related Products. Click on a Product and see all the related Vendors. Thus the act of clicking on a Vendor sends that ID into a global.

Your file, however, gives me a few ideas.

Much appreciated.

[comment]

I believe the principle should be very easy to adapt to your situation.

[sandyinlet]

Here's a problem FMP should be capable of solving.

My client has created four tables:

Globals

Vendors

Products

Join.VendorsProducts

With these relationships:

Globals.vendorsPortal

Globals.productsPortal

The Interface layout (Globals table) has two portals, one showing Vendors, the other Products. He wants users to click on a Vendor (putting that ID into a global) and have the related Products highlighted; and vice versa, click on a Product (putting that ID into a global) and have the related Vendors highlighted.

I have seen a partial solution to this problem, but it's one which puts the Products portal on the Vendors layout and highlights the products of the current Vendor record. However, that's not what the client wants. He wishes to use the current "two portals on one layout" approach, in effect showing the relationships in a modal way, depending on whether a Vendor or a Product was selected.

The current relationship graph shows Vendors and Products TOs with the Join table between them. An additional Products TO is linked to the Products TO such that all records are returned — ProductID "X" VendorID. It's this TO which is used as a portal on the Vendors layout. The Conditional Format for the fields on that portal is the following.

not IsEmpty ( FilterValues ( All Products::ProductID ; List ( Products::ProductID ) ) )

Can the ProductID.global be introduced into this mix and behave as the client requires? Or must this problem be approached from some other direction?

Much appreciated.

Edited June 30, 2009 by Guest

[comment]

Please do not open new threads with the same issue:

http://www.fmforums.com/forum/showtopic.php?tid/209864/

[LaRetta]

Topics merged.

[sandyinlet]

I believe the principle should be very easy to adapt to your situation.

How so? My experiments have all come up dry.

[comment]

By looking for the portal row's ProductID in a list of ProductIDs derived from the join records related to the global VendorID. I have implemented one of the two sides in the attached file.

BTW, I don't see how you can implement the other half in the same layout and still maintain a coherent user interface - but perhaps I didn't understand your description.

HighlightFilter_2.fp7.zip

[sandyinlet]

By looking for the portal row's ProductID in a list of ProductIDs derived from the join records related to the global VendorID. I have implemented one of the two sides in the attached file.

BTW, I don't see how you can implement the other half in the same layout and still maintain a coherent user interface - but perhaps I didn't understand your description.

Much appreciation for the second demo file. Finally grasped what you were driving at, and along the way, learned to loosen up and expand my concepts of what's possible with the Relationship Graph.

Using your structure I've been successful in rebuilding the master layout and suspect I'll not have any problems adjusting the scripts, conditional formatting and all the rest.

In order to support the client's desire for a single layout to perform multiple functions, I'm cloning the layout and duplicating the TOs with appropriate adjustments. When the user clicks on a Product or Vendor the layout will switch to the appropriate one. Since all the objects will be in exactly the same location, they will not perceive the switch.

Definitely learned a few things. Much thanks for the assistance.