cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Filtering gallery by date using drop down input, not date picker

Hello,

I have a Sharepoint list where I input agenda topics for a regular meeting with the following columns:

 

Meeting Date (Date&Time Column)

Project / Subject (Lookup Column from another list with the Project name)

Responsible (People Column)

Meeting Outcomes (Text Column)

 

I am trying to build a screen for my app where I can:

1. Have a dropdown input with the available meeting dates (I don't want to use Date Picker since it doesn't allow me to refrain users from clicking on dates when there are no meetings). This dropdown would then serve as a filter for a gallery, where we would have the topics, Responsibles & outcomes for that specific meeting date.

 

2. I also want the dropdown to always default to the next available meeting (not from the first meeting on the list, which would be a past meeting).

 

I am able to get make item 1 work. But can't for the life of me figure out a formula to get item 2 done. Any suggestions?

 

Also, as a bonus, is there a way to show the date label on the dropdown at a different format other than "mm/dd/yyyy"? I would like to show it in a more "friendly" way, like "Feb-10-2020, Mar-3-2020, etc"

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Filtering gallery by date using drop down input, not date picker

@pperrot1 

 

Please try this :

1. Set Items property of Dropdown box:

 

AddColumns('SP list',"TextDate",Text('Meeting Date',"[$-en-US]mmm-d-yyy"))

 

2. Set Value property: 

 

TextDate

 

3. Set Default property:

 

First(SortByColumns(Filter(AddColumns('SP list',"TextDate",Text('Meeting Date',"[$-en-US]mmm-d-yyy")), 'Meeting Date'>Now()),"Meeting Date",Ascending)).TextDate

 

Snipaste_2020-02-10_17-14-41.png

Note: Although no error is displayed, there could be a delegation error if the list item number is larger than 500 because '>' operator is not delegable for sharepoint. Reference: https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-ope... 

Here is a workaround for avoiding delegation error.

1. create a new Gallery, set Items property to 'SP list', then the gallery will display all items from sharepoint

2. apply Gallery.AllItems statement into the code, instead of 'SP list', the delegation error will be fixed.

3. set the Gallery Visible property to false because there is no need to display this gallery.


Sik

 

Highlighted
Helper I
Helper I

Re: Filtering gallery by date using drop down input, not date picker

When you say to set Value property to "TextDate", where exactly do I do this?

There isn't a Value property to the dropdown list.

 

Nevermind.. found it...

 

@v-siky-msft 

I tried the your drop down suggestions first, and then will move to the gallery part I don't think I quite understood correctly.

 

Some issues / questions:

- Since I have more than one item by meeting date, the drop down input was showing multiple lines of the same date.. I added a Distinct function:

 

 

Distinct(AddColumns('SP List',"TextDate",Text('Meeting Date',"[$-en-US]mmm-d-yyy")),TextDate)

 

 

- I thought that worked, but then found that was simply creating a unique record for each date x agenda topic combination I had... So I need a suggestion on how to show unique date values, instead of multiple lines for the same dates.

- Also, when I added a past meeting date (Feb 4) on my source list to test if the default function was working properly, I noticed that the new record I added was showing at the bottom of the list, not prior to the Feb 11 (first meeting on the list previously). I tried adding a 'Sort Ascending' function to the formula, but noticed that the sort was working on the text values, not the actual dates... do you know how to fix that??

 

On the gallery side..

-I don't understand 2 things.. how to reference the visible gallery to the "invisible" one you suggested me to create

- I didn't understand your statement no. 2 ("apply Gallery.AllItems statement into the code, instead of 'SP list'").. where and how do I do that??

Highlighted
Helper I
Helper I

Re: Filtering gallery by date using drop down input, not date picker

@v-siky-msft 

Actually, I think I was complicating it too much. I decided to filter out past meetings on this screen and only show future meetings agenda. I will work on a second screen to show meeting outcomes from past meetings.

 

So on the first screen (Future meetings), my dropdown is listing all future meetings in the correct order with the formula below:

Distinct(SortByColumns(Filter(AddColumns('SP List',"TextDate",Text('Meeting Date',"[$-en-US]mmm-d-yyy")),'Meeting Date'>Now()),"MeetingDate",Ascending),TextDate)

 

My gallery is filtering the records based on the dropdown date selection with the formula:

Filter('SP List','Meeting Date'=FutureMeetings_dropdown.Selected.Result)

 

I am however, getting a delegation error with the equal "=" sign for large datasets (and this sharepoint list for sure can hit 500+ items). Is this the delegation error you mentioned? Can you help me to solve this? 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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