Initial Values: SELECT, ANY, or LOOKUP?

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 :slight_smile:


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

2 Likes

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!

@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

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.

2 Likes
2 Likes