cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MikeTwister
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
v-xida-msft
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
Drrickryp
Super User II
Super User II

 
Mike8
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"))

Drrickryp
Super User II
Super User II

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

 

 

timl
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")  
)

 

Mike8
Solution Sage
Solution Sage

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. 

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...

v-xida-msft
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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (67,910)