Virtual column with search on database

[Cannone_Cannone]

Hi Team !!! hello to all, I have a problem that I can not solve.
in a Table view, I have two columns with different values, “SCOPE” and “AM CLASS” column.
in a sheet that I use as a “DB” I have a scheme of where to compare values.
in practice I should create a virtual column that should return me a value based on the schema of the “DB” sheet.
Example1 if in a row of the table view, in the “SCOPE” column I have an “S” value and in the “AM CLASS” column I have a “N” value in the virtual column, it must return the value “23”.
Example2 if in a row of the table view, in the “SCOPE” column I have an “E” value and in the “AM CLASS” column I have a “W” value in the virtual column, it must return the value “17”. DB ". I hope I have explained.

Thanks!!

[WillowMobileSys]

In your image of the sheet “db”, the column names don’t match your description. I am not certain then what you are calling the result column. I’ll just call it Result.

You can assign the Virtual Column in your Table View:

SELECT(Lookup[Result], AND([Scope] = [_THISROW].[Scope], 
                           [Class] = [_THISROW].[AM Class])

“Lookup” is the name I assumed your table is called with the Result column.

I hope this helps!

[Cannone_Cannone]

WillowMobileSystems:

SELECT(Lookup[Result], AND([Scope] = [_THISROW].[Scope], 
                           [Class] = [_THISROW].[AM Class])

Thanks for you help!!
i tried with a formula , but doesen’t work

SELECT(riconsegna[Result], AND([Scope] = [_THISROW].[Scope],
[AM Class] = [_THISROW].[AM Class])

“Riconsegna” it is the table in inside the “db”.
in practice I would like to say:
that if the values scope + Class AM are equal to the values Scope + Class AM present in the “DB” sheet / “redelivery” table, return me the value First

[WillowMobileSys]

Cannone_Cannone:

SELECT(riconsegna[Result], AND([Scope] = [_THISROW].[Scope],
[AM Class] = [_THISROW].[AM Class])

I made certain assumptions that may not work in your case of data schema. “Result” is a column name I made up it needs to be replaced with the column name you wish to be returned.

Also, I made an assumption that you have a separate table you are using for your Table view in the app AND that table also has the columns [Scope] and [AM Class].

Lastly, you mentioned a Virtual Column in your Table view. The expression you end up, replacing with your table and column names, needs to be inserted into the “App Formula” in your Virtual Column.

NOTE: You mentioned in your original post the name “AM Class” but in your latest image you have a column named “Class AM”. Make sure the expression has the correct column names in your tables.

[Cannone_Cannone]

Cannone_Cannone:

SELECT(riconsegna[Result], AND([Scope] = [_THISROW].[Scope],
[AM Class] = [_THISROW].[AM Class])

Thanks a lot, the formula works, but it returns me all the values in the table, I would like it to take only the row value

[mateo]

Maybe wrapping the whole SELECT( ) into ANY( ) will work. With SELECT( ) formula you get a list o values according to this article.

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[WillowMobileSys]

Cannone_Cannone:

but it returns me all the values in the table,

Something is not right. Based on the table you have shown, the rows are unique by Scope and AM Class. The expression should return only a single row.

It hasn’t been completely clear if you are dealing with 2 tables. I have assumed you are. One for the Table view and one for the Lookup. Is this correct?

I am noticing that my expression was missing a closing “)” but you must have figured it out or else there would have been an error.

mateo:

Maybe wrapping the whole SELECT( ) into ANY( ) will work.

Yes, to get just the value (not the list of a single value), you would want to use ANY() or INDEX()