cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ecflip29
Level: Powered On

Filtering a list then sorting to only show distinct categories

Hi All,

I have a list (List1) which has columns Title, Submitter, Approver Name, Approver Identity, Age, Location for each form uploaded, and a new item is added whenever a form is submitted.

 

The Titles are all unique, approvers Identity are all unique and Location is from a set list. Approvers will be able to approve forms for a number of locations.

 

I am creating a UI which when you log in as the approver you can see all the forms you have to approve, I am trying to write the code to filter the forms so you just see the ones you have to approve, then also sort them by location, creating a page to click the location taking you then to the forms themselves.

 

Hopefully that makes sense!

 

I currently have the following code which sorts the Titles and only shows them to the specific approver when logged in. I am struggling to add in the funcitonality to have the location filter in place. I previously used - Sort(Distinct(List1,'Location),Result) to get the Location results for the form. 

 

Sort(Filter(Test3, Last(FirstN(Split(Lower(User().Email), "@"), 1)).Result=Lower('Approver Identity'), TextSearchBox1_7.Text in 'Title'), 'Title')

 

Any help would be appreciated!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Filtering a list then sorting to only show distinct categories

Hi @ecflip29 ,

 

Can you please give us some example data of your list item? Like what is the value in 'Approver Identity' field? And what are the column types of all these related columns? From your formula, it seems that you have the user name in lower spelling as text column in the 'Approver Identity' field, right?

 

Also, you mentioned that you want to sort them by location, but in your formula, it seems that you are sorting with Title field. If Location column's column type is location, then you should be able to sort the gallery using either of below parameters:

Location.Altitude/Location.Latitude/Location.Longitude

 

So the formula should be:

Sort(Filter(Test3, Last(FirstN(Split(Lower(User().Email), "@"), 1)).Result=Lower('Approver Identity'), TextSearchBox1_7.Text in 'Title'), Location.Altitude)

In addition, please explain what did you mean by "creating a page to click the location taking you then to the forms themselves."? 

 

Regards,

Mona

 

 

 

 

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
ecflip29
Level: Powered On

Re: Filtering a list then sorting to only show distinct categories

Hi @v-monli-msft 

 

For the following columns this could be an exmple of the data in the list

Title, Submitter, Approver Name, Approver Identity, Location

AB,   John,        Joe Bloggs,           jbloggs,                York

BC,   Fred,        Joe Bloggs,           jbloggs,                 Leeds

FH,   John,        Alice Bloggs,        abloggs,                Leeds

XY,   John,        Alice Bloggs,        abloggs,                London

ZP,   Alfie,        Joe Blodds,           jbloggs,                 York

 

The approver identity is the persons ID, so their email address will be jbloggs@company.com. And I am using this to ensure that when they log in to the UI they only see the ones they need to approve. So in this case when Alice logs in she sees 2, Joe 3.

 

Yes currently when Joe logs in he will just see all 3 he has to approve and the title (the code I have at the moment).

What I want to happen is on the UI when Joe logs in to approve the forms, he will see a page with York and Leeds (so not London as he has none to approve from that location), which he can then click on and when he clicks Leeds it will open a new page in the UI and show 'BC' and the York page will show 'AB' and 'ZP' which he can then click to approve.

 

Hopefully that makes more sense?

 

So initially I need to filter it all by the Approver, then I want to show the distinct Locations. Then on the next page in the UI I would like to show the Titles for the logged in approver based on their previous location selection.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 277 members 4,575 guests
Please welcome our newest community members: