How to use || (OR) and && (AND) multiple conditions to filter a Gallery

[Australia]

I am happy with my current IF formula as shown below.

If(

(!IsBlank(
LookUp(
'Administrator Name SP List',
UserName= varUserName
).UserName
) ||  !IsBlank(
LookUp(
'Auditor Name SP List',
UserName = varUserName
).UserName))

,

True_value
,
Else_value

)

______

In simple English:

If a user's name appears on the Administrator SP list or on the Auditor SP list, the gallery show every report, or else only show reports where Report Owner Name  = UserName. 

But now I want to modify the IF statement where Auditors should only see reports that they are named on, AND

where not named, only those that have 'Report Status' = "Auditor Review Required" (This status is a label on the gallery itself and its value: ThisItem.'Report Status'.Value).

Hope someone can shed some light on this and guide me out of the tunnel.

Thank you.

[zmansuri · Accepted solution]

First of all store your lookup formula in variable.

App.OnStart

Set(varAdmin,LookUp('Administrator Name SP List',UserName= varUserName).UserName);

Set(varAuditor,LookUp('Auditor Name SP List',UserName = varUserName).UserName);

If(!IsBlank(varAdmin),"Logic Where everything is Displayed",
If(!IsBlank(varAuditor),Filter(ListName,varUser=ColumnName || 'Report Status' = "Auditor Review Required"),
Filter Logic where user can see only his own record))

This will check if user is admin. Then show all records. IF he is not admin then it will check weather he is Auditor. IF he is Auditor it will show 2 kinds of record. One where Auditor name is mentioned OR 'Report Status' = "Auditor Review Required". If he is not admin or auditor it will only display records related to current logged in user.

Tweak it according to your need

[Australia]

@zmansuri 

Wow thanks for the prompt reply.  Amazing!

So to tweak it slightly for my scenario, it should be like 

If(!IsBlank(varAuditor),Filter('Report Data List',varUsername=ReportOwnerName || 'Report Status' = "Auditor Review Required")

Let me try the above shortly.

May I know going forward should I use onStart set variable rather than the lookup function used earlier?

[zmansuri]

It increases readability of code

[Australia]

Beautiful. 

I just tried the formula and it works! The status was just missing .value so I added it.

Thanks heaps @zmansuri ! You are a legend.

If(!IsBlank(varAuditor),Filter('Report Data List',varUsername=ReportOwnerName || 'Report Status'.value = "Auditor Review Required")

[zmansuri]

Glad it worked