cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering with datepicker from a SP list gallery (need a solution)

Guys, please.

 

I searched a lot here, but found no solution for this.

I need to filter a gallery with a DatePicker, my entries are stored in a Sharepoint List.

When I used the filter, some of the records was not showing properly.

 

Now i have a month of records complete (August), but in the future I will have another months, and scroll al the way over the gallery will be painful.

 

There's must be a way to filter a Date in SP list.

Please Help me...

1 ACCEPTED SOLUTION

Accepted Solutions
Rick72
Level 10

Re: Filtering with datepicker from a SP list gallery (need a solution)

Hi @Anonymous,

 

Date columns can't be delegated in SharePoint. Click here for more info. I agree with @BrianKnight that using SQL Server is the way to go if you really need this functionality (which could conflict with the requirement to use SharePoint ofcourse).

 

 

Robot Happy Rick

View solution in original post

9 REPLIES 9
Pat_Wickramage
Level: Powered On

Re: Filtering with datepicker from a SP list gallery (need a solution)

Hi,

Are you trying to use the date picker to filter a range or a single value?

Anonymous
Not applicable

Re: Filtering with datepicker from a SP list gallery (need a solution)

Hello, thanks for the reply.

 

I need to Filter a gallery with many records, asking your question is a range.

 

 

 

 

 

 

Community Support Team
Community Support Team

Re: Filtering with datepicker from a SP list gallery (need a solution)

Hi @Anonymous,

 

Could you please share a bit more about your issue?

Could you please show more details about your SP list?

Do you get a Delegation issue with the formula within your app?

Further, is there a Date type column within your SP list (Filter your Gallery items based on this column)?

 

I have created a SP list on my side, the data structure as below:3.JPG

 

I have made a test on my side, please take a try with the following workaround:4.JPG

 

I assume that you want to filter Gallery items whose Completed Date is greater than 08/01/2018. Please take a try with the following formula:

 

Set the Items property of the Gallery control to following formula:

Filter('20180830_case3',Completed_x0020_Date>=DatePicker1.SelectedDate)

Note: The '20180830_case3' represents the SP list data source within my app. The above formula would generate a Delegation issue.

 

If you have faced a Delegation issue with your formula within your app, it means that you could only process your data locally rather than delegate the data process to your SP list data source.

 

More details about the Delegation in PowerApps, please check the following article:

Delegation

 

In addition, if you have faced a Delegation issue within your app, you could only process 500 records (Non-delegation limit) locally at most in default.  Currently, you could also consider take a try to change the Non-delegation limit within your app, the maximum value of Non-delegation limit (Data row limit for non-delegable queies) is 2000.

 

More details about changing the Non-delegable limit, please check the following article:

Change Non-delegable limit

 

The GIF screenshot as below:Test1.gif

 

 

If you want to avoid the Delegation issue with the formula within your app, please check my response within the following thread:

https://powerusers.microsoft.com/t5/Creating-Apps/How-to-show-your-own-data-in-App/m-p/150446#M4133

 

 

 

 

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.
Anonymous
Not applicable

Re: Filtering with datepicker from a SP list gallery (need a solution)

Hello @v-xida-msft, thanks so much for your attention, I will try to explain my scenario and the issue.

 

I have a form, around 35-40 columns, and now I have around 200 records just for August( this will keep growing, until I reach the limit of 2000, I will have to erase the old ones prob).

 

The first screen of my App, shows some numbers, graphs and in the bottom a gallery with some informations that my users fill on the form.

Note that, in the top of the gallery, I put some fields that they can filter whatever they want.

When I was using the excel database, I used to have a Date_Picker to filter date, it was working perfectly.

But now when I moved to a sharepoint list because some data issues with excel database, the Date_Picker was delegating and not showing all the records.

 

First Screen:

Gallery_1.PNG

 

My Form:

Form_1.PNG

 

 

All the infos are going to a sharepoint list, note that I have highlighted the Data Field I used to use to filter.

 

Base_1.PNG

 

See the problem below:

I filtered with a date (Column name "Data do Evento"), and the records was not showing properly.

 

Pendentes Com Filtro não aparecendo todos.PNG

As you can Count, the number of entries showed when filtered the date >= 01 August, and "Status" = "Pendente" filtered, is 4.

 

But look at the database in Sharepoint list (print below), I have 6 "Status" = "Pendente".

Strange but the records showed on the gallery were on August day 30, the other ones were 26 and 28(missing).

 

 

Pendentes base SP somando 6.PNG

I have a logic on filter, that is this:

 

If(And(Filtro_Corredor.Selected.Value = "CORREDOR";
           Filtro_Motivo.Selected.Value = "MOTIVO" ;
           Filtro_Status.Selected.Value = "Status");
                   SortByColumns(
                           Filter(
                               'Acionamentos 2018';
                                   StartsWith(
                                          Ativo;
                                             Filtro_Eqp.Text
                                     );
                                  StartsWith(
                                          Prefixo_x0020_Trem;
                                             Filtro_Trem.Text
                                    );
                                   Data_x0020_do_x0020_Evento >= Filtro_Data
                );
               "ID";
        Descending
         );

 

I hope I clarified for you. Thanks!

Anonymous
Not applicable

Re: Filtering with datepicker from a SP list gallery (need a solution)

up....

Anonymous
Not applicable

Re: Filtering with datepicker from a SP list gallery (need a solution)

Can you help me please on this???

Some info are not showing when the delegation warning appears, but I have only 200 records now...

 

BrianKnight
Level 8

Re: Filtering with datepicker from a SP list gallery (need a solution)

Hi, the delegation warnings look to be the >= if I had to guess. While = is delegatable, >< are not. (https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list). I could be misreading your syntax since it has been a long day :). 

 

Your best bet might be to convert to an Azure DB, which will make all of those delegatable and much faster also. 

 

Sorry for the bad news :(. 

 

Brian

Rick72
Level 10

Re: Filtering with datepicker from a SP list gallery (need a solution)

Hi @Anonymous,

 

Date columns can't be delegated in SharePoint. Click here for more info. I agree with @BrianKnight that using SQL Server is the way to go if you really need this functionality (which could conflict with the requirement to use SharePoint ofcourse).

 

 

Robot Happy Rick

View solution in original post

TopShelf-MSFT
Level 10

Re: Filtering with datepicker from a SP list gallery (need a solution)

Thanks for your help with this thread @BrianKnight & @Rick72!

 

@TopShelf-MSFT

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,503)