Struggling with Show If formula for a Menu View

[Paul_M_Crawford (Paul)]

I am trying to control when a specific menu view (Generate Report) is visible based on the data in one table (ExpensesTable) of my app. The criteria is if data for the current user exists in the table with a “Status” of “Open” I would like the view to be visible. If no “Open” Records are in the table for the specific user I want to hide the view. I have tried the following Lookup statement in the “Show If” of the “Generate Report” view which uses a Virtual column(Employee Email Address) in the table that connects the email address of each record to the current user.

Show If Statement:
LOOKUP(useremail(),“ExpensesTable”,“Employee Email Address”,“Status”)=“Open”

Using the above, I have records that meet the criteria but the view remains hidden. I have tried the above statement within an IF statement as well with the same result.

Clearly I am missing something or trying to accomplish this using an incorrect approach. Thanks in advance for any assistance.

Paul

[Suvrutt_Gurjar]

Could you please try with the following expression?

IN(“Open”, SELECT(Expenses Table[Status], [Employee Email Address]=USEREMAIL()))

[Paul_M_Crawford (Paul)]

That worked. I need to become more familiar with the “IN” expression.

Thanks much for your help

[Suvrutt_Gurjar]

Hi @Paul_M_Crawford ,

You are welcome.

Please refer the below help article for the IN() expression.

If I may add, I believe the LOOKUP() expression could be erroneous in this case because LOOKUP() is equivalent of ANY(SELECT(…)) . It thus will select the first record out of the list of records compiled by the SELECT() function. So if for some reason the first record has status other than “Open”, the LOOKUP() will return FALSE.

On the other hand IN() will check for the presence of search word in the entire list and will return TRUE, if it finds the search word anywhere even once in the list.

IN()

LOOKUP()

[Paul_M_Crawford (Paul)]

Thanks for this @Suvrutt_Gurjar

Your explanation regarding the LOOKUP expression clears this up for me as that is likely what was happening.

Thanks again. Have a great day.