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!