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

Struggling with GroupBy and selecting extra columns without grouping

I'm trying to to show images stored in a Sharepoint Document Libary in a PowerApps Gallery.

This works when the Formula is set to get all contents from the table in the result set.

 

Table 'BbCache' has 2 columns of which I'd like the result to be grouped on and 1 additional column I'd like to be in the resultset without grouping. Title, Trainingday, Venue and Link to file. I'd like to group by on trainingday + venue and get the title and link to file column as well in the result set.

 

In SQL I'd write:

"SELECT Trainingday, Venue, Linktofile FROM BbCache GROUP BY Trainingday, Venue ORDER BY Trainingday DESC"

 

How would I write this query in Power Apps?

 

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz Community Champion
Community Champion

Re: Struggling with GroupBy and selecting extra columns without grouping

Hi @RobbertJan ,

You need 

First(ThisItem.Training).'FullPath'
and
First(ThisItem.Training).Venue

As you said, Training is a Record, so to get Text (which both are), you have to tell it which one of the record set you want. You can only choose First() and Last() from outside the record, or you can do a sub-gallery with Items ThisItem.Training and it should contain all the values that are grouped by that TrainingDay.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

View solution in original post

6 REPLIES 6
BrianS Resolver I
Resolver I

Re: Struggling with GroupBy and selecting extra columns without grouping

Off the top of  my head I can't give you the exact syntax, but there is a GroupBy function - if I remember correctly you just give it the table and the field(s) you want to group. You would use this in the Datafield for the gallery and put whatever fields yo want view in the gallery.

RobbertJan
Frequent Visitor

Re: Struggling with GroupBy and selecting extra columns without grouping

I've tried this... Sort(GroupBy(BbCache; "Trainingday"; "Training"); "Trainingday"; Descending)

The result set now contains 2 columns. Training and Trainingday. Column Training contains tables on every row.

 

When I set the source of the image to ThisItem.Training.'Link to item' or to ThisItem.Training.'FullPath'
I receive an error that an image or url is expected.

 

When I set the Gallery's subtitle text to ThisItem.Training.Venue

I get an error about value Text expected.

 

On a sidenote, the sorting seems to be done alphabetically instead of numerically by date.

 

Do I need to convert or set the datatypes of the values?

BrianS Resolver I
Resolver I

Re: Struggling with GroupBy and selecting extra columns without grouping

Without knowing more about what your data is, I'm not sure I can help. If the Training date is a date field, it should sort correctly, if it is a text field it will sort alpha.

Not sure what data you are trying to show in the image. If it is an attachment you need to use the thumbnail attribute.

WarrenBelz Community Champion
Community Champion

Re: Struggling with GroupBy and selecting extra columns without grouping

Hi @RobbertJan ,

You need 

First(ThisItem.Training).'FullPath'
and
First(ThisItem.Training).Venue

As you said, Training is a Record, so to get Text (which both are), you have to tell it which one of the record set you want. You can only choose First() and Last() from outside the record, or you can do a sub-gallery with Items ThisItem.Training and it should contain all the values that are grouped by that TrainingDay.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

View solution in original post

RobbertJan
Frequent Visitor

Re: Struggling with GroupBy and selecting extra columns without grouping

Thanks, using First does the trick. I wasn't expecting multiple records in the GroupBy result.

 

The sorting problem has been resolved by using GroupByColumn instead of Sort.

Highlighted
WarrenBelz Community Champion
Community Champion

Re: Struggling with GroupBy and selecting extra columns without grouping

No problams @RobbertJan ,

The issue with all functions that produce a potential table is that it is just that (potential depending on what data you Group with it, which Power App has no idea on before it happens) so a table structure is presented, even if your result is only one record in the group.

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (8,426)