Initial Values: SELECT, ANY, or LOOKUP?

[Ms_Margo]

Greetings All!

Not sure if I should be using SELECT, ANY, or LOOKUP for initial values when trying to pull data from one table to another based on user email….

I have two table:

Employee information (table name: team)

• Email = [email]
• Sales Region = [team_region]
• Product Area = [team_pa]

Customer Activities (table name: activities)

• Customer Name = {customer_name]
• Customer Region = [customer_region]
• Customer product = [customer_product]

When an employee enters a customer activity, i would like the customer’s region and product area to have the initial values (or maybe suggest values?) of the employees region and product area.

I am totally mixed up on which function to use, but the two i have tried are not working….

SELECT(team[team_region],[email]=USEREMAIL())
LOOKUP(USEREMAIL(),”Team”,”email”,”team_region”)

Any help would be welcome

[LeventK]

---

CUSTOMER REGION

---

LOOKUP(USEREMAIL(),"team","email","team_region")

---

CUSTOMER PRODUCT

---

LOOKUP(USEREMAIL(),"team","email","team_pa")

SELECT expression returns a LIST, not a single value
ANY() expression returns a SINGLE VALUE from the lookup table’s lookup column
LOOKUP expression is a shortcode to ANY(SELECT(…)) expression.
Please read the AppSheet Documentation well > help.appsheet.com

[Ms_Margo]

Thanks Levent!

Might anyone have a guide/suggestions on when it is best to use ANY, LOOKUP, SELECT?

Levent’s suggestion works as did the following:
ANY(SELECT(team[team_region],[email]=USEREMAIL()))

Thanks!

[LeventK]

Ms_Margo:

ANY(SELECT(team[team_region],[email]=USEREMAIL()))

@Ms_Margo

This expression is exactly identical with

LOOKUP(USEREMAIL(),“team”,“email”,“team_region”)

Bear in mind that; both ANY() and LOOKUP expressions return the very first value found as per given criteria if there are more results in the table/list.

For example; a sample table like

ColA	ColB	ColC
Fruit	Banana	5
Fruit	Apple	6
Fruit	Banana	3

LOOKUP(“Fruit”,“SampleTable”,“ColA”,“ColB”) returns Banana
LOOKUP(“Banana”,“SampleTable”,“ColB”,“ColC”) returns 5
LOOKUP(“Banana”,“SampleTable”,“ColA”,“ColC”) returns Blank value

Same applies to ANY()

ANY(SELECT(SampleTable[ColB], “Fruit” = [ColA])) returns Banana
ANY(SELECT(SampleTable[ColC], “Banana” = [ColB])) returns 5
ANY(SELECT(SampleTable[ColC], “Banana” = [ColA])) returns Blank value

Ms_Margo:

Might anyone have a guide/suggestions on when it is best to use ANY, LOOKUP, SELECT?

Provided you have multiple criteria to fetch only one definite record value only, use ANY()
Provided your search string is unique value in the table (i.e. Users table, Useremail) you can use LOOKUP()

Usecases and return values are very well explained in AppSheet Docs with some linkled expressions as well.

[Steve]

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

Core concepts & functions Expressions: The Essentials FILTER() List Expressions and Aggregates LOOKUP() MAXROW() MINROW() REF_ROWS() SELECT() What is a Key? Adding & updating rows Is this a new row?/Does this row already exist? What is the previous value of this column? Excel alternatives How do I do COUNTIF() or COUNTIFS()? How do I do SUMIF() or SUMIFS()? How do I do VLOOKUP()? Last row of the spreadsheet How do I get the last row of the spreadsheet? How do I get a column value from…

[da_common]

If the cell of the  column referenced by the lookup is empty, the lookup returns the first value of the data set.

Any solutions to this issue?

tried to force a nill value using if/else but its not working