cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ratanapouy
Helper III
Helper III

date range filter doesn't get any data from datasource

hi,

 

there is new collect to get data from datasource by using filter function. there is delegation warning message then there is no data

Delegation.png

---------

 

Refresh(tbl_CheckPoint_Data);
ClearCollect
(
tblCheckPointDataAllItem,
Filter(tbl_CheckPoint_Data, CheckPoint.Value=CheckPointMaster_1.SelectedText.checkpoint, DateValue(Text(Date_Time_CheckIn)) >= DateValue(Text(CheckInDateFrom.SelectedDate)) && DateValue(Text(Date_Time_CheckIn)) <= DateValue(Text(CheckInDateTO.SelectedDate)))
)

 

------------

 

if i remove date range, all data come in. how to fix it?

 

Thanks,
Ratana

8 REPLIES 8
lucia
Resolver II
Resolver II

it doesn't work for me. get zero result.

 

 

v-xida-msft
Community Support
Community Support

Hi @ratanapouy ,

Could you please share a bit more about the tbl_CheckPoint_Data data source? Is it a SQL Table?

How many records stored in your tbl_CheckPoint_Data data source? More than 2000?

 

Firstly, based on the formula you provided, I think you have faced a Delegation warning issue with your formula. In default, you could only process 500 records locally within your app. You could change the "Data rows limits for Non-delegable queries" option to maximum value -- 2000 in Advanced settings of App settings of your app, then you could process 2000 records at most locally in your app.

 

If the records which matches the date range filter in your tbl_CheckPoint_Data data source are more than 2000 row index, the result would not be shown up in your app. Your Filter formula could only process first 2000 records at most locally.

 

In addition, please also check if you have typed a proper date range filter in your formula. Please consider modify your formula as below:

Refresh(tbl_CheckPoint_Data);
ClearCollect(
              tblCheckPointDataAllItem,
              Filter(
tbl_CheckPoint_Data,
CheckPoint.Value = CheckPointMaster_1.SelectedText.checkpoint,
Value(Text(Date_Time_CheckIn, "yyyymmdd")) >= Value(Text(CheckInDateFrom.SelectedDate, "yyyymmdd")) && Value(Text(Date_Time_CheckIn, "yyyymmdd")) <= DateValue(Text(CheckInDateTO.SelectedDate, "yyyymmdd")) /* <-- Modify your Date range filter here */
) )

then execute above formula, check if the proper records would show up.

 

If the amount of your tbl_CheckPoint_Data data source records is more than 2000, please check and see if the alternative solution mentioned within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

Within above alternative solution, you need to bulk-load records from your tbl_CheckPoint_Data data source into multiple individual collections, then merge these collections into single one collection. After that, use the merged collection as data source in your formula instead of the tbl_CheckPoint_Data data source.

 

In addition, if the tbl_CheckPoint_Data data source is a SQL Table, I think this issue may be related to date time column. Currently, direct date filters do not work for SQL Server (check here for more details).

Please check and see if my alternative solution mentioned within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/Creating-Apps/Avoid-delegation/td-p/341885

 

Best regards,

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.

even i try to modify formula as your instruction, there is no result. i am using sharepoint and over 2k records.

Hi @ratanapouy ,

Currently, I could not reproduce your issue on my side.

 

Please consider take a try with the following workaround:

Set the OnStart property of App to following (I assume that there are 10000 records stored in your SP List😞

Concurrent(
            ClearCollect(col1, Filter('YourSPList', ID >= 1 && ID <= 2000)),
            ClearCollect(col2, Filter('YourSPList',ID >= 2001 && ID <= 4000)),
            ClearCollect(col3, Filter('YourSPList', ID >= 4001 && ID <= 6000)),
            ClearCollect(col4, Filter('YourSPList', ID >= 6001 && ID <= 8000)),
            ClearCollect(col5, Filter('YourSPList', ID >= 8001 && ID <= 10000))
);
ClearCollect(MergedCollection, col1, col2, col3, col4, col5)

Then modify your formula as below (set the OnStart property of a button to following😞

Refresh(tbl_CheckPoint_Data);
ClearCollect(
             tblCheckPointDataAllItem,
              Filter(
MergedCollection,
CheckPoint.Value=CheckPointMaster_1.SelectedText.checkpoint,
DateValue(Text(Date_Time_CheckIn)) >= DateValue(Text(CheckInDateFrom.SelectedDate)) && DateValue(Text(Date_Time_CheckIn)) <= DateValue(Text(CheckInDateTO.SelectedDate))
) )

then re-load your app (fire the OnStart property of App control), press the above button control, check if the issue is solved.

 

Please also make sure that you have already set the "Data row limits for Non-delegable queries" option to maximum value -- 2000 in Advanced settings of App settings of your app.

 

Best regards,

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.

i am wondering why the records from date 13/08/2019 to now doesn't display in gallery even those records exist in SP list.

HI @ratanapouy ,

Please make sure the date time field value retrieved from your SP List is in same date format as the result the DatePicker control returned in your app.

 

In addition, please also make sure the date time value of your SP list in your app are the same value as that in your SP List itself.

 

Please make sure the Region setting of your SP Site is set to same value as your local region (if not, the date time value retrieved in your app is not same with the date time value in your app, due to Time Zone Offset😞7.JPG

 

Best regards,

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.

already set; but there is no result. i get stuck.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (27,808)