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

[Anonymous]

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...

[Pat_Wickramage]

Hi,

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

[Anonymous]

Hello, thanks for the reply.

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

[v-xida-msft]

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:

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

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:

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

[Anonymous]

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:

My Form:

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

See the problem below:

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

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).

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]

up....

[Anonymous]

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]

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 · Accepted 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).

Rick

[Anonymous]

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

@Anonymous