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)
Customer Activities (table name: activities)
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
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
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!
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.
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
User | Count |
---|---|
62 | |
25 | |
14 | |
11 | |
6 |