Action Behavior show_if formula help

[CPansewicz]

Hi,

I am looking for assistance building an action that is only available to app users that have a specific user type in my Users table.

I am able to do this by who is logged in and using the app:
OR (
USEREMAIL() = "useremail-1@xxx.com",
USEREMAIL() = "useremail-2@xxx.com"
)

It would be a better situation to use data from my User table instead:
USER[user_type] = "Reviewer"

I can't seem to get this to work correctly though. I think one of the problems is that the Table I am attaching the action to does not have a user_id column in the table.  Thank you in advance.

[QREW_Cam · Accepted solution]

Try this:

ANY(SELECT(User[user_type],[user_id]=USEREMAIL()))   =   "Reviewer"

 ANY(SELECT(User[user_type],[user_id]=USEREMAIL())):
Returns the value in the User Type column of the record of the user who is currently logged in to the AppSheet instance this expression is running on.

Its taking that value and if it's equal to "Reviewer" then its true and the action will show, if the ANY(SELECT()) returns a User Type other than "Reviewer" it will return FALSE which will hide the action.

I personally use ANY(SELECT())s over Lookups almost every single time, I suggest using the one which syntaxes you feel more comfortable using.

[QREW_Cam · Accepted solution]

For sure Good luck with the rest of your App!

One last thing, considering that specific scenario, it's possible that you might require the capability to grant access or display certain columns to multiple user types in the future. This can be achieved like this:

IN( ANY(SELECT(User[user_type],[user_id]=USEREMAIL()))   ,   LIST( "Reviewer" , "Admin" ) )

In this instance, if the current user's user_type is either "Reviewer" or "Admin," they would be able to view the specified action.

During my consulting calls nearly every client utilizing the [role]="...." approach eventually  requires this expression as well.

[jaichith]

you can use lookup () function. 

lookup(useremail(), user table, search column, return column)

return column values can contain like value true or false. this will control the action button.  

see this post : https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/DYNAMIC-USER-CONTROLS-EDIT-NO-ACCESS-ETC/td-p/5...

[CPansewicz]

Hi, Thank you for getting back to me. How would I apply the following for if the [user_type] = "Reviewer"?

LOOKUP(USEREMAIL(),  "Users",  "user_id", "user_type")

Thank you!

[CPansewicz]

Would I do something liks:

IF(
[user_type]="Reviewer", ), 
LOOKUP(USEREMAIL(),  "Users",  "user_id", "user_type")
)

Thank you!

[jaichith]

If () statement syntax is wrong. 

For solving your issue. I want to see your dataset , columns , table. Kindly share the screen shot.

[CPansewicz]

Hi, Attached is a screenshot of my Users Table, and then a screenshot of my Requests Table. Let me know if you need to see anything else.

[CPansewicz]

Would I use instead of Lookup?

SELECT(
User[user_id], 
[user_id].[user_type] = "Reviewer", 
TRUE
)

[jaichith]

Yes, u can but.... Select () is brute function

[CPansewicz]

Ok. Thank you! What would a better way to write this?

[QREW_Cam · Accepted solution]

Try this:

ANY(SELECT(User[user_type],[user_id]=USEREMAIL()))   =   "Reviewer"

 ANY(SELECT(User[user_type],[user_id]=USEREMAIL())):
Returns the value in the User Type column of the record of the user who is currently logged in to the AppSheet instance this expression is running on.

Its taking that value and if it's equal to "Reviewer" then its true and the action will show, if the ANY(SELECT()) returns a User Type other than "Reviewer" it will return FALSE which will hide the action.

I personally use ANY(SELECT())s over Lookups almost every single time, I suggest using the one which syntaxes you feel more comfortable using.

[CPansewicz]

Thank you so much!

[QREW_Cam · Accepted solution]

For sure Good luck with the rest of your App!

One last thing, considering that specific scenario, it's possible that you might require the capability to grant access or display certain columns to multiple user types in the future. This can be achieved like this:

IN( ANY(SELECT(User[user_type],[user_id]=USEREMAIL()))   ,   LIST( "Reviewer" , "Admin" ) )

In this instance, if the current user's user_type is either "Reviewer" or "Admin," they would be able to view the specified action.

During my consulting calls nearly every client utilizing the [role]="...." approach eventually  requires this expression as well.

[CPansewicz]

Thank you again. That is even more useful. I was accomplishing that by using OR.

[QREW_Cam]

Definitely the naturally intuitive way of achieving it, but just requires it to repeat the same query for each potential value. A query that simple running twice makes no perceivable difference unless you're working on an app that just so happens to have millions of users. haha

Have a good day! 🙂 

[CPansewicz]

Thank you again! How would I apply that to this type of formula as a row filter condition for a slice?

OR(
CONTAINS([request_status], "Pending"),
CONTAINS([request_status], "Reviewed")
)

[QREW_Cam · Accepted solution]

Either of these should work:

IN( [request_status] , LIST( "Pending" , "Reviewed" )  )

OR(
[request_status] = "Pending"),
[request_status] = "Reviewed")
)

[CPansewicz]

Again, Thank you!