SELECT Expression from Operators table

Hi Guys
I have an ‘Operators’ table which holds users, managers etc. I am hoping to use that table to fill in an INITIAL VALUE in various tables.

SELECT(Operators[EmailAddress],([Email]=USEREMAIL()),true)

This is probably the first expression I have created but it returns all email addresses in that table instead of just one from the Operators table so its getting there.

I would need also to use a similar expression to pull other information so its not just as simple as putting USEREMAIL() in the INITIAL VALUE

Example:
SELECT(Operators[Role],([Email]=USEREMAIL()),true)
SELECT(Operators[Branch],([Email]=USEREMAIL()),true)

Theory being:
Select a column value from the Operators table where the email address in table Operators matches the useremail.

Can anyone help with this.

Assuming you want to fill the initial value with the name of the user who is logged in, here’s one way of doing that :

  1. Create a slice from the operators table with [Email]=useremail(), which filters out all but the logged in user. Name that slice Profile.

  2. Assuming the operators table has a field named ‘Name’, the initial value expression would be any(Profile[Name]).

If you want to use select, then the expression would be:

any(
select(
Operators[The field you desire], ([Email]=useremail())
)
)

Make sure to use ANY() to return just one value from a list, which select does. Even if your condition, in theory, returns one value, like matching Email to useremail(), Appsheet treats that as an one item list. ANY() changes that one item list to just one item.

N.B. The expression using ANY() and SELECT() is equivalent to the LOOKUP() expression if you do it, but I keep forgetting the syntax of LOOKUP() so I find the former option more handy.

2 Likes

The ANY( … Select … seems to give me what I want.
This enables me to build the Operators table and pull all kinds of INITIAL VALUE information as long as the Operators table is accurate, it’s a life saver.

So thank you Guys, I’m loving Appsheet

1 Like

You can use the slice approach too. Apparently to me its more effective, as it is short in length and I guess, would be a little less hard on the sync.