Filter gallery using GroupBy

(0) Share

Report

Posted on by Iantaylor2050

603

I want to show the below list in a gallery in Powerapps, group by EventUniqueID.

For EventUniqueID=1, there are 3 records currently. In Powerapps gallery, it should show only the record which is upcoming in few days (i.e. 29 Jan 2023). It should not show 10 Jan 2023 record, since its already passed. On 30 Jan 2023, it should show the next record for EventUniqueID=1, i.e. 02 Feb 2023.

Any idea how to achieve this?

Categories:

Building Power Apps

All responses (5)

Answers (1)

Sort by

LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Iantaylor2050,

A possible way to achieve this is by using the following structure in the Items property of your gallery:
With( { uniqueID: Distinct( LISTNAME, EventUniqueID ) }, ForAll( uniqueID As CurrentID, First( Sort( Filter( LISTNAME, EventUniqueID = CurrentID.Result && 'Start Date and Time' > Now() ), 'Start Date and Time', Ascending ) ) ) )
This will retrieve the next event for each unique event ID. Please adjust the names within the code where necessary (e.g. LISTNAME).

If this solves your question, would you be so kind as to accept it as a solution.
Thanks!
Iantaylor2050 603 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @LaurensM

I want to sum up the number of openings available column, groupby EventUniqueID field and display in a gallery textbox for each events.

For EventUniqueID=11, number of openings should be displayed as 15 (7+8)

Also, if the date has already passed for an event, number of openings should be excluded for that ID. i.e. For EventUniqueID=1, since the 1st event, 10 Jan 2023 has already passed, number of openings should be displayed only as 17 (8+9). Any idea how to achieve this? Have included screenshot below
LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Iantaylor2050,

The Label within your gallery would have the following code in the Text property:
Sum( Filter( LISTNAME, EventUniqueID = ThisItem.EventUniqueID && 'Start Date and Time' > Now() ), 'No of Openings' )
Adjust the Listname and column name where necessary.

As a note: you could also use an AddColumns() in the gallery's Items property to create a new column with that value and reference the newly created column instead.

If this solves your question, would you be so kind as to accept it as a solution.
Thanks!
Iantaylor2050 603 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @LaurensM

I am using the above code to display the upcoming events in ascending order. Gallery Items property is set to below piece of code

With( { uniqueID: Distinct( LISTNAME, EventUniqueID ) }, ForAll( uniqueID As CurrentID, First( Sort( Filter( LISTNAME, EventUniqueID = CurrentID.Result && 'Start Date and Time' > Now() ), 'Start Date and Time', Ascending ) ) ) )

My SharePoint list contains below items, however powerapp gallery shows wrong ordering. The items with Start date and time as 02 Feb 2023 is shown first, when I have items dated Jan 30 and Jan 31 ordered 3rd and 4th. May I know why this is happening?
Verified answer

LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for reaching out @Iantaylor2050.

See my response on your new topic.

Since we started a new topic, would it be possible to close this one by accepting a solution.
Thanks!