cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nigelh54
Frequent Visitor

Help with filtering a gallery

Hi,

   I am trying to get a list of directreports that are not already in a SharePoint list or that a yes/no field is no depending on another field value

 

Filter(myReports, (Not(DisplayName in colPolicy.Name) && !IsBlank(myReports.Department)) || ('Policy Compliance'.Completed=0 && Dropdown1.Selected.Result = colPolicy.'Policy Name'))

 

myReports is a collection from AD

Policy Compliance is the SharePoint list

Completed is a yes/no field

Name is a text field

What i'm trying to achieve is that the gallery only shows people that are not in the list or that if they are in the list then that the completed yes/no field is no against the particular selection of the dropdown box

 

The first part of the expression works but not the second

1 ACCEPTED SOLUTION

Accepted Solutions
v-albai-msft
Community Support
Community Support

Hi @Nigelh54 ,

From your information, I know that:

1. You want to get records from collection myReports, if the DisplayName of each records is not show in list.Name column.

2. If the DisplayName already show in list.Name column, check about these records in list that if "Completed=0 &&'Policy Name'=Dropdown1.Selected.Result", if yes, also show these records from list.

If my understand is correct, based on your requirement, you can try formula like this:

ClearCollect(FinalResult,
Filter(Filter(myReports, Not(DisplayName in 'Policy Compliance'.Name)),!IsBlank(Department)),
Filter('Policy Compliance',Completed<>true && Dropdown1.Selected.Result ='Policy Name'))

Notes:

1. after create the collection FinalResult, use this collection directly for your gallery.

2. If colPolicy is the collection from your list and you creating this collection using formula like ClearCollect(colPolicy,'Policy Compliance'), you can use colPolicy instead of your list name in above formula(replace using this collection to avoid delegation issue).

 

Below I will explain more about some issues in your formula, that's why I use formula like above.

1. For your first part, Not(DisplayName in colPolicy.Name) do get the people who are not in the list, but per my test, !IsBlank(myReports.Department) does not work as expect . You can have a test on your side, only filter use your first part, here are my test result:

v-albai-msft_0-1622172040708.png

 

 

2. Your first part is doing filter based on myReports, but your second part is doing filter based on your SP list, this two filters cannot be achieved inside one Filter function. So I combine these two filters into a same collection.

3. There is an known issue with “Yes/No” field in SP List, so I would suggest you try formula like this colPolicy.Completed<>true.

Best regards,

Allen

View solution in original post

1 REPLY 1
v-albai-msft
Community Support
Community Support

Hi @Nigelh54 ,

From your information, I know that:

1. You want to get records from collection myReports, if the DisplayName of each records is not show in list.Name column.

2. If the DisplayName already show in list.Name column, check about these records in list that if "Completed=0 &&'Policy Name'=Dropdown1.Selected.Result", if yes, also show these records from list.

If my understand is correct, based on your requirement, you can try formula like this:

ClearCollect(FinalResult,
Filter(Filter(myReports, Not(DisplayName in 'Policy Compliance'.Name)),!IsBlank(Department)),
Filter('Policy Compliance',Completed<>true && Dropdown1.Selected.Result ='Policy Name'))

Notes:

1. after create the collection FinalResult, use this collection directly for your gallery.

2. If colPolicy is the collection from your list and you creating this collection using formula like ClearCollect(colPolicy,'Policy Compliance'), you can use colPolicy instead of your list name in above formula(replace using this collection to avoid delegation issue).

 

Below I will explain more about some issues in your formula, that's why I use formula like above.

1. For your first part, Not(DisplayName in colPolicy.Name) do get the people who are not in the list, but per my test, !IsBlank(myReports.Department) does not work as expect . You can have a test on your side, only filter use your first part, here are my test result:

v-albai-msft_0-1622172040708.png

 

 

2. Your first part is doing filter based on myReports, but your second part is doing filter based on your SP list, this two filters cannot be achieved inside one Filter function. So I combine these two filters into a same collection.

3. There is an known issue with “Yes/No” field in SP List, so I would suggest you try formula like this colPolicy.Completed<>true.

Best regards,

Allen

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,175)