Initial Values: SELECT, ANY, or LOOKUP?

Ms_Margo
Participant II

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

1 5 1,603
5 REPLIES 5

LeventK
Participant V

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
Participant II

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.

Steve
Participant V

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

Top Labels in this Space