Gallery: Filter this or that or that... + Sort by 1 of 4 options + if specific user display All! Oh my!

[KimberlyM]

I am on my last step to finishing my app and I am finding it a doozy.

What I want is to:

1. Filter by AssignedTo OR LeadMember1 OR LeadMember2 OR LeadMember3 OR LeadMember4
//These choices are all dropdown selections made on my edit screen

2. Sort by a dropdown selection based on 4 different columns in my Lead_Data SharePoint List. 

//These column choices are Date (Date to be default sort), AssignedTo, Status and LeadMembers (1 through 4)

3. Allow my admin users (defined in SharePoint List "Admin_LeadData"), to see ALL entries in the gallery.

I have been successful in filtering AssignedTo, "Filter(Lead_Data, AssignedTo.Value = varUser)", but all the other help I can find relates to filter by this AND this instead of the OR that I need in this situation.

[Jeff_Thorpe · Accepted solution]

Here is how you would do the "Or" with the with the varUser:

Filter(Lead_Data, AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser)

[KimberlyM]

@Jeff_Thorpe Thank you! This has me well on my way! I googled what the || means so I now know it means 'OR', instead of just assuming it mean 'OR'. Thanks!

Can you lead me a little more... I am loving learning PowerApps. 

In this I am thinking if you are logged in as one of the admin users and your user full name matches the sharepoint list full name then do not filter anything. Else filter by the user logged in... etc. 

#1
If(Admin_LeadData.FullName = varUser, !Filter(), Filter(Lead_Data, AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser))

Or my thought process was also if your user does not match someone listed in the admin sharepoint list then filter, else do nothing.

#2
If(Admin_LeadData.FullName = !varUser, Filter(Lead_Data, AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser))

I believe #2 is more accurate as I have only an error for the '=' that states that it is an invalid argument but adding '.Value' to the end of 'Admin_LeadData.FullName' or 'varUser' makes the statement angrier.

Can you help me understand where or why my thinking is incorrect. Thank you!

[Jeff_Thorpe · Accepted solution]

If you want all the items to show if the person is an Admin and just filter if the use isn't an Admin then try this formula:

If(Admin_LeadData.FullName = varUser, Lead_Data, 

Filter(Lead_Data, AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser))

[KimberlyM]

@Jeff_Thorpe, makes complete sense, don't know why I didn't think of it as it reads like speaking English. However, I still don't understand why in this part of the function

If(Admin_LeadData.FullName = varUser, Lead_Data, 

the '=' is an 'invalid argument type'. My Admin_LeadData, FullName, is a 'Single line of text' and my varUser is defined onStart of the App as 'Set(varUser, User().FullName);'. I learned previously that when this happens usually '.Value' fixes this problem but in this case it makes it much angrier. Why?

[v-yutliu-msft · Accepted solution]

Hi @KimberlyM ,

Is Admin_LeadData a list name and FullName a field name?

If so, the reason why you met this problem is that Admin_LeadData.FullName means a table of one column, while varUser is a text type. You could not compare them directly.

So I suggest you try this:

If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)) ,   //justify whether has a record that fullname is the same as varUser
    Lead_Data, ......)

Best regards,

[Jeff_Thorpe]

@KimberlyM ,

The Lead_Data after the Admin = current user logic is returning the data from the data source unfiltered. You only use the Filter() if you need to filter the data source by some conditions.