Filter data doesn't work in collection

[ratanapouy]

hi,

i create a new collection from SP list by collecting all items base on CheckPoint Value

Refresh icon:

//Create collectin by Checkpoint
ClearCollect(Database, Filter(tbl_CheckPoint_Data, CheckPoint.Value = CheckPointMaster_1.Selected.checkpoint));

//Filter by date range
ClearCollect(DatabaseItem, Filter(Database, DateValue(Text(DateValue,"[$-en-US]yyyymmdd")) >= DateValue(Label5.Text)));

the problem is, if i choose Check Point "WH05", all data is coming from SP List into collection "Database" then fillter collection "Database" into Collection "DatabaseItem"; but i am wondering, when i choose Check Point "WH08" then click Refresh.

Data retrieve from SP List into Collection "Database"; but nothing in Collection "DatabaseItem". i go to check there are records in SP list for Check Point "WH08" around 58 records base on date selection 09/02/2019 [dateformat: "mm/dd/yyyy"].

Please help.

Thanks,

Ratana

[yashag2255]

Hi @ratanapouy 

The formula looks fine to me. Can you please confirm if the date returned by Label5.Text is what you are expecting in the formula? Please share more details, so that we can debug this together.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[ratanapouy]

Label5.Text is date value which get datevalue from datepicker. i just wanted to try a new way to filter date range. Eventually, result is nothing on what i decribed above.

[yashag2255]

Hey @ratanapouy 

I don't think this should make a difference in the way you are trying to fetch the value. Both DateValue() function and date picker are returning the same value. Can you share more details on the type of DateValue column?

Can you try to directly refer to the DatePicker using the expression:

ClearCollect(DatabaseItem, Filter(Database, DateValue(Text(DateValue,"[$-en-US]yyyymmdd")) >= DatePicker1.SelectedDate))

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[ratanapouy]

hi,

DateValue column set format "yyyyddmm" which is calculated from other date comlumn "Date_Time_CheckIn". I wanted to try many ways to filter data in collection. Still doens't work. i get stuck and looking in records to find out the root cause.

[yashag2255]

Hi @ratanapouy 

Just so we are on same page, can you please confirm the type of DateValue column, is it of type text or Date?

I think we may need to modify the formula as per the column type, so please share more details so that we may be able to help you better.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[ratanapouy]

In SP list, it is single text which calculated as follow:

=TEXT(Date_Time_CheckIn,"yyyymmdd")

[yashag2255]

Hey @ratanapouy 

In that case, can you try to modify your formula as:

ClearCollect(DatabaseItem, Filter(Database, DateValue(DateValue) >= DatePicker1.SelectedDate))

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[ratanapouy]

is not succesfully gotten data. Do i have issue with data delegation?

Export SP list into Excel, i found that Check Point "WH08" has over 2k records, others have below 1k records.

[yashag2255]

Hi @ratanapouy 

Yes that is the reason behind this. In the Database Collection, only first 2000 records will be there and the rest of the records will not be filtered.

Can you try out the below formula and check if this works:

ClearCollect(DatabaseItem, Filter(tbl_CheckPoint_Data, CheckPoint.Value = CheckPointMaster_1.Selected.checkpoint && DateValue(DateValue) >= DatePicker1.SelectedDate));

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!