Using LOOKUP() with an Enum value to match a value in a different table and return a column value

[jcfigueroav]

Using a LOOKUP() in a table/column/formula in order to search, match and return value from different column in a different table. I tried:
LOOKUP([model1_smpart], “dataMOLD”, “model1_smpart”, “acro_smpart”)
LOOKUP(TEXT([model1_smpart]), “dataMOLD”, “model1_smpart”, “acro_smpart”)
=LOOKUP(TEXT([model1_smpart]), “dataMOLD”, “model1_smpart”, “acro_smpart”)
where:
model1_smpart is a Enum type column in actual Table (Table A)
dataMOLD is the Table (Table B) where I search
model1_smpart is the column in the Table (Table B) I search
acro_smpart is the value to return from a different column in the Table (Table B) I search
Table A and Table B have columns with same names (like model1_smpart)

Every time that I test, it returns values only from the first row in Table B.

What am I doing wrong? Am I missing anything?

I found a post:
https://community.appsheet.com/t/im-using-the-lookup-expression-to-find-the-v/4376/3
where it is supposed to be fixed with TEXT() but in my case it does not.

Please help. Thank you.

[JuneCorpuz]

jcfigueroav:

Every time that I test, it returns values only from the first row in Table B.

This is correct. Try making it more specific and use ANY() formula instead of LOOKUP()

[Steve]

jcfigueroav:

Am I missing anything?

Yep!

Read this:

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[jcfigueroav]

Sorry for the late response. You were right, I was missing a little piece of code. Thank you for pointing it out.

[Wally_Young]

Can you please post what your final working code is? I have a similar question/issue with how to accomplish this and not having any luck. Thanks!

[Marc_Dillon]

What is your expression, and what is the error?
Did you thoroughly read the LOOKUP() help article posted above?

[Wally_Young]

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:

Projects:
Project ID (key)
Project Name (text)
Related Products (List) - App Formula: SELECT(Products[Product Name], IN([Project Name], Products[Projects]))

Products:

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.

Thanks again!

[Marc_Dillon]

Wally_Young:

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

This should probably be:

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

Please thoroughly review the SELECT article below:

help.appsheet.com

SELECT()

Gather column values from row in table or slice

---

Wally_Young:

LOOKUP([_THISROW].[Project Name], “Products”, EXTRACT([Project Name], Products[Projects]), “Product Name”)

The 3rd parameter of LOOKUP() expects the name of a column. EXTRACT() returns a List type. You are also using EXTRACT() very incorrectly. Please thoroughly review its article as well:

help.appsheet.com

EXTRACT()

Extract values from text.

[Wally_Young]

Marc_Dillon:

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

Yes I have corrected the select statement to use [_THISROW].[Project Name], still produces the same results. I also did go back and find my error on the LOOKUP() function for the 3rd parameter. I don’t see a way to use LOOKUP() properly, since if I use the column name (“Projects”), this produces an error “Cannot compare List with Name in ([Projects] = [_THISROW-1].[Project ID].[Project Name])”

[Marc_Dillon]

Wally_Young:

Yes I have corrected the select statement to use [_THISROW].[Project Name], still produces the same results.

There were 2 changes present, did you do both?

Wally_Young:

I don’t see a way to use LOOKUP() properly,

You are correct. In such a case where you need to compare against a list you cannot use LOOKUP(), and need to fall back on to SELECT().

[Wally_Young]

Yes I corrected both in the SELECT() statement, now reads:

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

So appears I’m back to square one, and needing to setup a 3rd table as an intermediary to connect Products to Projects. Then my question would be how to properly allow an EnumList to select multiple projects for a product, and then store these to accomplish my original objective? Do I store the EnumList selected items in the 3rd table, along with the Product ID and Project ID keys as Refs back to Products and Projects tables?

[Marc_Dillon]

Sorry, I was merely correcting obvious expression errors that I saw. I’ve now read your post more thoroughly. Please follow the setup explained in this thread:

Many-to-Many Relationships Tips & Tricks 💡

AppSheet be default supports One-to-many relationships: you have one record (say an Order) that has many related records (order details). But there is another relationship connection type that allows you to connect many records to many records you have a table of records (say Products) where each record can be related to many records in another table (say orders). this means when you look at a product, you can see a bunch of related orders - and when you look at an order, you will see a bu…

[jcfigueroav]

LOOKUP([_THISROW].[model1_smpart], “dataMOLD”, “model1_smpart”, “acro_smpart”)

This is the final code. I’m sorry for the late response but I did not realize you asked for it until now.