How to create mutual Related Products?

[thecowis40]

Hi everyone,

This has really stumped me. I have been thinking for a couple of weeks on how to solve this; and can't crack it.

Essentially I am trying to setup a database for a customer (FMP11) for furniture. Pretty simple. One of the things they would like to have, is a related products section on the layout for each product. This would be a portal displaying all the furniture that is similar to the current item. The user would establish this relationship by adding the ID to the portal; and creating another 'similarity' relationship between the current product, and its similar brother/sister.

This is where it gets interesting. The related product, is not multiple related records in another table; but rather related products in the same table. Pretty easy right? Just create a second occurrence of the table. That would work; but the problem is; I want these relationships to be mutual. What I want; is when I have products a b c d e f g h i, and I set a's related products as b d g h, when I go to the respective records for b d g h, and look in the related product portals, I would like to see a listed for each of them.

I hope that's clear.

I can imagine a way of doing it; which is defining a relationship to a table with recordID1 and RecordID2 and linking them to the primary table with a relationship "where ID = RecordID1 OR where ID =RecordID2" but Filemaker's flaw is that the relationship doesn't allow for the setting of OR for multi-criteria relationships, only AND.

Any advice would be appreciated. I've toyed with repetitions and calculations... but no luck.

[comment]

Filemaker's flaw is that the relationship doesn't allow for the setting of OR

Yes it does: in the join table, define a calculation field =

List ( RecordID1 ; RecordID2 )

and use it as the matchfield.

See also:

http://fmforums.com/forum/topic/38282-join-table-inverse-relationships/

[thecowis40]

Yes it does: in the join table, define a calculation field =

List ( RecordID1 ; RecordID2 )

and use it as the matchfield.

See also:

http://fmforums.com/forum/topic/38282-join-table-inverse-relationships/

Would I be right in saying that under this setup; one would not be able to create new records via a portal? How can one do this?

Thanks again for the help.

[comment]

one would not be able to create new records via a portal?

No, not via a portal based on this relationship. But there are plenty of other ways, e.g. select a product in a global field and use a script to link it to the current one.