Multiple products associated with multiple projects (and vice versa)

[Wally_Young]

I have a Products table that I need to select multiple projects. I also have a Projects table that contains details of projects that I also need to be able to list all of the products associated with a particular Project. I added a EnumList column to the Products table that is a base type of Text with a Valid If clause of “Projects[Project Name]”. This allows me to select multiple projects that the product is associated with. What I’m unable to do is list all the associated products when viewing a project from the Projects table. I created a virtual column in the Projects table called “Related Products” as a List type with a Ref element type referencing the Products table. The column has a App Formula of “SELECT(Products[Product Name], IN([Project Name], Products[Projects]))” in an attempt (failed attempt) to try to show all the products from the Products table that have the Project Name for the specific project record.

What am I missing? Do I need to create an intermediary table that links the tables together? Seems like a many-to-many relationship but I’m not clear how to set this up to accomplish what I’m trying to accomplish.

Any help please? Thanks!

[1minManager (Simon Robinson)]

Should it not be:

SELECT(Products[Product Name], IN([Project Name], [_ThisRow].[Projects]))

My only other suggestion is that it maybe by using IN() your comparing a list with a list. Maybe you need to use INTERSECT() or something like that

[Wally_Young]

I think it’s actually:

SELECT(Products[Product Name], IN([_ThisRow].[Project Name], Products[Projects]))

From what I’m understanding (or what I need the List column to show), I’m needing to select all products in Products table (show the Product Name) that have the Projects field (an AnyList field) that contain the same project name as the current row’s project name.

What’s occurring is that I get ALL of the Product Name fields from the Products table appearing in the List column where it finds a matching Project Name. So, I’m back to thinking that this requires an intermediary table to establish the many-many relationship between the Projects and Products. The intermediary table would need to include the Project ID key and the Product ID key field, both as refs to the Projects and Products tables. Isn’t this the way to enable viewing the common projects for a particular product, and vice versa for showing all the products for a particular project. Right??