dereference a SELECT statement

[StephenSaid]

I'm trying to only show a view if the current user has a SuperAdmin role.

My DB structure: https://app.quickdatabasediagrams.com/#/d/cQgIjj

IN(
  "SuperAdmin" , 
  SELECT( 
    Users[roles][role], 
    [email] = USEREMAIL() 
  )
)

 I'm getting error Unable to find column 'email' in the above expression. 

I must be using dereferencing badly. 

[WillowMobileSys · Accepted solution]

---

@StephenSaid wrote:

I must be using dereferencing badly. 

---

Correct.  That expression is not valid syntax.  Because [Roles] is an EnumList of Ref, you would need to implement a "two-tier" expression IF you want to use the role name to validate with.  An alternative is to hard-code the Role ID of the Role name.  I'll show both below

IN(
  "SuperAdmin" , 
  SELECT( Roles[Role], IN([Role ID], ANY(SELECT(Users[Roles],[email] = USEREMAIL() )))
)

 The ANY() function extracts the EnumList column, otherwise its a List of a List and the IN() functino can't operate on that.

The alternative...

IN(
  "<<SuperAdmin Role ID here>>" , 
  ANY( SELECT( Users[roles], email] = USEREMAIL()))
)

Since the Roles data is very static,  this works just as well as searching by the Role Name. 

[StephenSaid]

@WillowMobileSys 

Thank you.

This is the expression I ended up with:

IN(
"SuperAdmin", 
SELECT( 
Roles[Role], 
IN( [roleID], ANY(SELECT(Users[Roles], [email] = USEREMAIL() ) ) )
)
)

 I prefer this since it is more readable.