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.
This is correct. Try making it more specific and use ANY() formula instead of LOOKUP()
Yep!
Read this:
Sorry for the late response. You were right, I was missing a little piece of code. Thank you for pointing it out.
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!
What is your expression, and what is the error?
Did you thoroughly read the LOOKUP() help article posted above?
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!
This should probably be:
SELECT( Products[Product Name] , IN( [_THISROW].[Project Name] , [Projects] ) )
Please thoroughly review the SELECT article below:
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:
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])โ
There were 2 changes present, did you do both?
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().
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?
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:
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.
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |