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

GroupBy & Collection

I'm new to PowerApps and I'm trying to do what i initaily thought would be pretty simple.

I have an Excel table that looks something like this:

 

2018-10-20 20_12_46-CRM instances - Excel.png

 

What I would like to do is to have a Gallery1 to show me only unique Company values, and when I select one of the Companies it drills through and opens Gallery2 that shows filtered Emplyee and other data for only selected company.

 

I tried to create Collection by creating OnSelect function Collect(colCompany,GroupBy(Table1,"Company","ColumnName")) but it just creates empty collection.

 

What am I doing wrong? How can I solve this?

 

Thank you

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: GroupBy & Collection

Sure, If you used two screens and two galleries, you would make the items property of the first gallery Sort(Distinct(datasource,Company),Result,Ascending).  This would eliminate duplicates from the Company column.  Inside Gallery1 you would put a single label and make it's text property Result.  You would make the OnSelect property of the  > icon: Navigate(Screen2,None,{cmpy:Result)

The Items property of Gallery2 would be: Filter(datasource,Company=cmpy).  You could then put labels in Gallery2 for all of your columns. 

A simpler way of doing something similar would be to use a single gallery and a dropdown control on the same screen. The dropdown would have the Items property Sort(Distinct(datasource,Company),Result,Ascending) and the gallery would have its Items property: Filter(datasource,Company=Dropdown1.Selected.Result).  You may want to try both suggestions and see which one  you like better.  

Capture.PNG

 

 

5 REPLIES 5
Super User
Super User

Re: GroupBy & Collection

Hi @IvanMislav,

 

Capture.PNGIt sounds like Groupby is going to be the way to go for you. A nifty way to show it is to put together a gallery and a subgallery.  In the example, I used Groupby to create the Items property for the Gallery and Subgallery.  Start by putting a flexible height Gallery on the screen.  Then Click on the small pencil in the upper left of the gallery and insert a second flexible height gallery, this is the subgallery. You want to use flexible height galleries to accommodate the subgallery if it contains a lot of items.  The subgallery will overlie the entire gallery section, so resize it.  I like to fill it with a different color to help differentiate it from the gallery.  Then set the Items property of the first gallery to GroupBy(datasource, "Company", "Restofthedata") Put a label in the first gallery above the subgallery and set the text property to Company.  Go to the subgallery and make the Items property Restofthedata.  You can then add labels from all of the rest of the columns in your data source.  If this answers your question, please mark it as solved.

IvanMislav
Level: Powered On

Re: GroupBy & Collection

Thanks @Drrickryp

 

This is cool but it won't work in my case because my subitems need to show much more fields. Is there a way this could be done as I described in my initial post, shifting from screen to another screen with filtered gallery?

Super User
Super User

Re: GroupBy & Collection

Sure, If you used two screens and two galleries, you would make the items property of the first gallery Sort(Distinct(datasource,Company),Result,Ascending).  This would eliminate duplicates from the Company column.  Inside Gallery1 you would put a single label and make it's text property Result.  You would make the OnSelect property of the  > icon: Navigate(Screen2,None,{cmpy:Result)

The Items property of Gallery2 would be: Filter(datasource,Company=cmpy).  You could then put labels in Gallery2 for all of your columns. 

A simpler way of doing something similar would be to use a single gallery and a dropdown control on the same screen. The dropdown would have the Items property Sort(Distinct(datasource,Company),Result,Ascending) and the gallery would have its Items property: Filter(datasource,Company=Dropdown1.Selected.Result).  You may want to try both suggestions and see which one  you like better.  

Capture.PNG

 

 

Highlighted
IvanMislav
Level: Powered On

Re: GroupBy & Collection

Thanks, This worked. 

Could you please explain what exactly does this part of the function does: Navigate(Screen2,None,{cmpy:Result})?

Super User
Super User

Re: GroupBy & Collection

The third (optional) element of the navigation function creates a context variable that carries over to the next screen where it can be used in formulas. It is similar to UpdateContext() or Set()