cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Complex Filter - GroupBy, First or Distinct?

Hi All.

 

Imagine the following data structure: 

 

ProjectNameUserApproved/DeniedApproved/Denied Date
WinterMikeRemoved07/11/2018
SummerMikeApproved06/11/2018
SpringMikeApproved05/11/2018
WinterMikeApproved05/11/2018
OctoberMikeDenied04/11/2018

 

Mike has been busy and has been joing projects all over the place. However, Mikes manager has removed Mike from a project with a Flow and triggered this top entry to appear. 

 

How can I filter a Gallery to show only the items Mike has been approved for, but at the same time hide ones that he has subsiquently been removed from?

 

So using the list above, his gallery should only show the following: 

 

ProjectNameUserApproved/DeniedApproved/Denied Date
SummerMikeApproved06/11/2018
SpringMikeApproved05/11/2018

 

The "denied" is hiding, because it has no relevance to him, and the "Winter" project is hiding because he has subsiquenty been removed and the latest item in the list shows this.

 

I can't figure this out! I've been rotating between using Filter/GroupBy/First - but I just cant get it .... Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi @MikeTwister,

Could you please share a bit more about your data source used to store the projects? Is it a SP list?

I assume that your data source is a SP list, I have made a test on my side, please take a try with the following workaround:1.JPG

Set the Items proeprty of the Data Table control to following formula:

Filter(
RenameColumns(Filter('20181109_case3','Approved/Denied'.Value="Approved"),"ProjectName","ProjectName1"),
IsBlank(LookUp('20181109_case3',ProjectName=ProjectName1 && Approved_x002f_Denied.Value="Removed"))
)

Note: The Approved/Denied column is a Choice type coumn in my SP list. I assume that the Approved/Denied column is also a Choice type column in your SP list.

On your side, you should type:

Filter(
RenameColumns(Filter('YourSPList','Approved/Denied'.Value="Approved"),"ProjectName","ProjectName1"),
IsBlank(LookUp('YourSPList',ProjectName=ProjectName1 && Approved_x002f_Denied.Value="Removed"))
)

Above formula may cause a Delegation issue, in order to get rid of this issue, please also take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following formula:

ClearCollect(RecordsCollection, 'YourSPList')

Set the Items proeprty of the Data Table control to following formula:

Filter(
     RenameColumns(Filter(RecordsCollection,'Approved/Denied'.Value="Approved"),"ProjectName","ProjectName1"),
     IsBlank(LookUp(RecordsCollection,ProjectName=ProjectName1 && Approved_x002f_Denied.Value="Removed"))
)

If your SP list have more than 2000 records, in order to get rid of the Delegation issue, you could consider take a try to convert the Approved/Denied column (Choice type column) into a Single line of text type column, then the Delegation issue would be solved.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
Super User III
Super User III

 
Highlighted
Solution Sage
Solution Sage

Hello,

You can try this..

Button1.OnSelect = ClearCollect(Collection1,{Name:"Mike",Project:"Winter",Status:"Removed"},{Name:"Mike",Project:"Summer",Status:"Approved"},{Name:"Mike",Project:"String",Status:"Pending"},{Name:"Mike",Project:"Autumn",Status:"Approved"},{Name:"Mike",Project:"October",Status:"Denied"},{Name:"Mike",Project:"Winter",Status:"Approved"})

Gallery1.Items = Filter(Collection1,Status="Approved"&&!(LookUp(Filter(Collection1,!(Status="Approved")),Project=Collection1[@Project],Status)="Removed"))

Highlighted
Super User III
Super User III

Hi @MikeTwister,

I know that you explored the GroupBy function but you may want to check out the part of the documentation where they talk about using GroupBy, filtering the result and then using the Ungroup() function to re-create the table. There is a good example of this near the bottom of the page regarding Cities, Countries and population.  It seems that this is similar to what you are trying to do.  Once you get the hang of it, you should be able to deal with more complicated tables than the one in your example.

ref: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby

 

 

Highlighted
Super User III
Super User III

I really like @Mike8 's formula! It's a great illustration of disambiguation syntax.
For info, the only thing I can add to it is that if you want to support other users, here's how you would amend the formula.

 

ClearCollect(Collection1,
             {Name:"Mike",Project:"Winter",Status:"Removed"},
{Name:"Mike",Project:"Summer",Status:"Approved"},
{Name:"Mike",Project:"String",Status:"Pending"},
{Name:"Mike",Project:"Autumn",Status:"Approved"},
{Name:"Mike",Project:"October",Status:"Denied"},
{Name:"Mike",Project:"Winter",Status:"Approved"},
{Name:"Sally",Project:"Winter",Status:"Approved"} )
Filter(Collection1,
       Status="Approved" && 
       !(LookUp(Filter(Collection1,
                       !(Status="Approved")),Project=Collection1[@Project] && 
                       Name=Collection1[@Name]).Status
         ="Removed")  
)

 

Highlighted

Thank you @timl. 🙂
Yes, it can be improved. I also like what you have added.
Also I didn't test for any bugs. The formula doesn't take into consideration the date. If you approve a user for a project, remove the user and add him again to the project, the formula will not diplay the correct data. It can be used as a reference though for MikeTwister to create what he needs. 

Highlighted

Sounds good, however the projects can be nearly endless. New ones are created, and closed after time. The filter in question is basically on the "audit trail" list. This list will end up being 500+ entires long before it gets archived off. I'm assuimng you can't just create a collection of everything in the list?

 

It sounds confusing, I know - but I haven no real way of explaining it properly...

Highlighted
Community Support
Community Support

Hi @MikeTwister,

Could you please share a bit more about your data source used to store the projects? Is it a SP list?

I assume that your data source is a SP list, I have made a test on my side, please take a try with the following workaround:1.JPG

Set the Items proeprty of the Data Table control to following formula:

Filter(
RenameColumns(Filter('20181109_case3','Approved/Denied'.Value="Approved"),"ProjectName","ProjectName1"),
IsBlank(LookUp('20181109_case3',ProjectName=ProjectName1 && Approved_x002f_Denied.Value="Removed"))
)

Note: The Approved/Denied column is a Choice type coumn in my SP list. I assume that the Approved/Denied column is also a Choice type column in your SP list.

On your side, you should type:

Filter(
RenameColumns(Filter('YourSPList','Approved/Denied'.Value="Approved"),"ProjectName","ProjectName1"),
IsBlank(LookUp('YourSPList',ProjectName=ProjectName1 && Approved_x002f_Denied.Value="Removed"))
)

Above formula may cause a Delegation issue, in order to get rid of this issue, please also take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following formula:

ClearCollect(RecordsCollection, 'YourSPList')

Set the Items proeprty of the Data Table control to following formula:

Filter(
     RenameColumns(Filter(RecordsCollection,'Approved/Denied'.Value="Approved"),"ProjectName","ProjectName1"),
     IsBlank(LookUp(RecordsCollection,ProjectName=ProjectName1 && Approved_x002f_Denied.Value="Removed"))
)

If your SP list have more than 2000 records, in order to get rid of the Delegation issue, you could consider take a try to convert the Approved/Denied column (Choice type column) into a Single line of text type column, then the Delegation issue would be solved.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,482)