cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Impactful Individual
Impactful Individual

Distinct Filter and Sort on Gallery from SharePoint list

Hello dear people!

 

The PowerApp i am working on is for employees to "vote" where and when they want to go eat together.

I would like to load a gallery with items from a SharePoint list but matching certain conditions.

 

The list is called "Votes" and has:

- UserId (users are in another SP list and the Id comes from there)

- PlaceId (places are in another SP list and the Id comes from there)

- Date (when they want to go eat there)

 

Right now i am loading items (Votes) like this:

SortByColumns(Filter(Votes,Text(Date,"[$-en-US]dd/mm/yyyy") = Text(VotesDatePicker.SelectedDate,"[$-en-US]dd/mm/yyyy")),"Date",Descending)

I want to load items in the gallery for each place, by date and count the amount of users that voted for that.

So lets say there are 2 Votes for the same day, by 2 different users but for the same place (PlaceId),

i don't want 2 items in the gallery but 1, showing a label with the Votes count.

 

I have this for now:

(CountIf(Votes,Date = VotesDatePicker.SelectedDate && LookUp(Places,ID = ThisItem.PlaceId).ID = Value(ThisItem.PlaceId))

This shows the correct amount of votes but is still showing 2 items in the gallery and i only want one.

I tried implementing the Distinct function but with no luck, don't know if i need another approach or if im "mis using the Distinct function" ...

 

I tried:

SortByColumns(Filter(Distinct(Votes,PlaceId),Text(Date,"[$-en-US]dd/mm/yyyy") = Text(VotesDatePicker.SelectedDate,"[$-en-US]dd/mm/yyyy")),"Date",Descending)

and

Distinct(SortByColumns(Filter(Votes,Text(Date,"[$-en-US]dd/mm/yyyy") = Text(VotesDatePicker.SelectedDate,"[$-en-US]dd/mm/yyyy")),"Date",Descending),PlaceId)

and

SortByColumns(Distinct(Filter(Votes,Text(Date,"[$-en-US]dd/mm/yyyy") = Text(VotesDatePicker.SelectedDate,"[$-en-US]dd/mm/yyyy")),PlaceId),"Date",Descending)

 

Also tried 3 above mentioned code but with ".Results" at the end but i couldn't get what i am looking for.

 

Can anyone suggest me something or point out what i am doing/thinking wrong here?

Thank you for any feedback !

Kind regards

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Hi @dimi,

 

Could you please share a screenshot of your app's configuration?

Do you want to display one Item (showing a label with Votes count) for same places within your Gallery control?

 

The Distinct function returns a one-column table that contains the results, with duplicate values removed. More details about the Distinct function, please check the following article:

Distinct function

 

I suppose that the following formula that you provided would retrieve two records (two different users for same place) within your Gallery control, is it true?

 

SortByColumns(
Filter(Votes,
Text(Date,"[$-en-US]dd/mm/yyyy") = Text(VotesDatePicker.SelectedDate,"[$-en-US]dd/mm/yyyy"
)
"Date",Descending)

 

If you only want to display one Item within your Gallery control, please take a try to modify above formula as below:

First(
SortByColumns( Filter(Votes, Text(Date,"[$-en-US]dd/mm/yyyy") = Text(VotesDatePicker.SelectedDate,"[$-en-US]dd/mm/yyyy")&&LookUp(Places,ID = ThisItem.PlaceId).ID = Value(ThisItem.PlaceId) ), "Date",Descending) )

 

or 

 

Distinct(
SortByColumns(
Filter(Votes,Text(Date,"[$-en-US]dd/mm/yyyy") = Text(VotesDatePicker.SelectedDate,"[$-en-US]dd/mm/yyyy")),
"Date",
Descending
),
PlaceId).Result

 

Then within the Gallery control, add a Label control (showing the Votes count), set the Text property to following formula:

CountIf(Votes,Date = VotesDatePicker.SelectedDate && LookUp(Places,ID = ThisItem.PlaceId).ID = Value(ThisItem.PlaceId))

 

 

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

Highlighted
Impactful Individual
Impactful Individual

 

First(...)

This!

 

 

I feel a little akward not having thought of that function Cat Embarassed BUT HUGE THANK YOU for this Smiley Very Happy

 

I am still left with small unsolved behavior:

 

Concatenate("Votes: ",Text(CountIf(Votes,Date = VotesDatePicker.SelectedDate && LookUp(Places,ID = ThisItem.PlaceId).ID = Value(ThisItem.PlaceId))))

This doesn't seem to work anymore, it showed me 2 votes (which is correct) but when closing the app and reopening it (or changing date) it is showing 0.

 

 

If i remove the First(...) function and have it as it originally was i would have 2 items in the gallery, 1 with the label showing 0 votes and the other showing 2 votes Smiley Frustrated

 

Last but not least, when clicking on a gallery item i am navigating to another screen to display this items information and i would like to load the users that voted to a listbox, it is only showing 1 users vote (my own vote) with this formula:

 

[LookUp(Users,ID= Value(ThisVote.Title)).Title].Value

Where ThisVote is the Vote item we clicked on that i pass on with the navigate function like this:

 

Navigate(VoteDetails,ScreenTransition.Fade,{ ThisVote: ThisItem })

How can i load all users that voted this into the listbox?

And what do you mean by a screenshot of the settings, are you talking about a file or settings in the web portal itself ?

 

Thank you so much for your time and help!

 

 

EDIT:

It shows me 0 votes, i remove the First() function it shows me 2 votes, put back the First() function arround it and it stays on 2, run it, change date and set it back, shows me 0 votes again and stays that way unless i repeat above steps ...

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Hi @dimi,

 

Could you please share a screenshot of your app's configuration?

Do you want to display one Item (showing a label with Votes count) for same places within your Gallery control?

 

The Distinct function returns a one-column table that contains the results, with duplicate values removed. More details about the Distinct function, please check the following article:

Distinct function

 

I suppose that the following formula that you provided would retrieve two records (two different users for same place) within your Gallery control, is it true?

 

SortByColumns(
Filter(Votes,
Text(Date,"[$-en-US]dd/mm/yyyy") = Text(VotesDatePicker.SelectedDate,"[$-en-US]dd/mm/yyyy"
)
"Date",Descending)

 

If you only want to display one Item within your Gallery control, please take a try to modify above formula as below:

First(
SortByColumns( Filter(Votes, Text(Date,"[$-en-US]dd/mm/yyyy") = Text(VotesDatePicker.SelectedDate,"[$-en-US]dd/mm/yyyy")&&LookUp(Places,ID = ThisItem.PlaceId).ID = Value(ThisItem.PlaceId) ), "Date",Descending) )

 

or 

 

Distinct(
SortByColumns(
Filter(Votes,Text(Date,"[$-en-US]dd/mm/yyyy") = Text(VotesDatePicker.SelectedDate,"[$-en-US]dd/mm/yyyy")),
"Date",
Descending
),
PlaceId).Result

 

Then within the Gallery control, add a Label control (showing the Votes count), set the Text property to following formula:

CountIf(Votes,Date = VotesDatePicker.SelectedDate && LookUp(Places,ID = ThisItem.PlaceId).ID = Value(ThisItem.PlaceId))

 

 

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

Highlighted
Impactful Individual
Impactful Individual

 

First(...)

This!

 

 

I feel a little akward not having thought of that function Cat Embarassed BUT HUGE THANK YOU for this Smiley Very Happy

 

I am still left with small unsolved behavior:

 

Concatenate("Votes: ",Text(CountIf(Votes,Date = VotesDatePicker.SelectedDate && LookUp(Places,ID = ThisItem.PlaceId).ID = Value(ThisItem.PlaceId))))

This doesn't seem to work anymore, it showed me 2 votes (which is correct) but when closing the app and reopening it (or changing date) it is showing 0.

 

 

If i remove the First(...) function and have it as it originally was i would have 2 items in the gallery, 1 with the label showing 0 votes and the other showing 2 votes Smiley Frustrated

 

Last but not least, when clicking on a gallery item i am navigating to another screen to display this items information and i would like to load the users that voted to a listbox, it is only showing 1 users vote (my own vote) with this formula:

 

[LookUp(Users,ID= Value(ThisVote.Title)).Title].Value

Where ThisVote is the Vote item we clicked on that i pass on with the navigate function like this:

 

Navigate(VoteDetails,ScreenTransition.Fade,{ ThisVote: ThisItem })

How can i load all users that voted this into the listbox?

And what do you mean by a screenshot of the settings, are you talking about a file or settings in the web portal itself ?

 

Thank you so much for your time and help!

 

 

EDIT:

It shows me 0 votes, i remove the First() function it shows me 2 votes, put back the First() function arround it and it stays on 2, run it, change date and set it back, shows me 0 votes again and stays that way unless i repeat above steps ...

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,248)