The expression in the App Formula for the List type field:
SELECT(Products[Product Name], IN([Project Name], Products[Projects]))
It’s not an error that’s appearing, it’s the results that aren’t working properly or what I expect. The results show some returned records that show no Product Names, but others that show all of the Product Names. When reviewing the records that return all Product Names from the Products table, they don’t even have matching Project Name entries that would match to the Project records they would seem to connect to from the query.
The table structures (abbreviated) for reference:
Project ID (key)
Project Name (text)
Related Products (List) - App Formula: SELECT(Products[Product Name], IN([Project Name], Products[Projects]))
Product ID (key)
Product Name (text)
Projects (EnumList) Type: Text, Valid If: Projects[Project Name]
When adding/updating a Product in the Products table, it does present me with a list of Project Name fields to select one or many from, and are stored in the Products table as text (example: Orange Project, Red Project, Blue Project).
What I’m trying to accomplish is the ability to show the list of all the projects in the Projects table in a table view, that shows the list of the associated/related products that have the same Project name selected in the Product[Projects] field. So if my products table for example has:
Product ID: 1 Product Name: Hammer Projects: Orange, Purple
Product ID: 2 Product Name: Screwdriver Projects: Red, Blue
Product ID: 3 Product Name: Wrench Projects: Orange, White
I would expect that when showing the Projects field, it should show:
Project ID: 1 Project Name: Orange Related Products: Hammer, Wrench
Project ID: 2 Project Name: Red Related Products: Screwdriver
Project ID: 3 Project Name: Purple Related Products: Hammer
What occurs instead:
Project ID: 1 Project Name: Orange Related Products: Hammer, Screwdriver, Wrench
Project ID: 2 Project Name: Red Related Products: Hammer, Screwdriver, Wrench
Project ID: 3 Project Name: Purple Related Products: Hammer, Screwdriver, Wrench
Again, it’s not that every project in the Projects folder shows all Products in the Related Products list field, it’s somewhat random that I can’t tell why it shows all for some, and none for others. But the one’s that it shows Products in the Related Products field always show the same list of all Products.
I have reviewed and also tried using the LOOKUP() function as well vs. SELECT() which produces the same results:
LOOKUP([_THISROW].[Project Name], “Products”, EXTRACT([Project Name], Products[Projects]), “Product Name”)
I’m sure this is something fundamental I’m missing here, just not clear what it is. And I also am thinking that I still need an intermediary table to connect both the Products and Projects table together to establish the many-many relationship that these have.