I have an external list/content type set up that reads data from a SQL database view. (I only have access to one view, I do not have access to any tables directly.)
This database has been around for years and has over 50,000 items in it. I need to work with only the most recently modified items.
I’ve set up a Read List operation that has a Limit filter of 2000 on it (because I read that that’s a good practice, and that’s the BCS limit anyway), and a filter on the
ModifiedDate field to filter for anything more recently modified than 1/1/2019. Currently that returns about 220 items, but clearly as time goes on, that number is going to grow, and eventually reach the 2000 item limit.
Once it reaches the limit, and for sake of argument and setting up an example, let’s say the 2000th item modified since 1/1/2019 is modified on 12/31/2019. Then a new item is added, and subsequently modified on 1/1/2020. I am assuming that since that’s the 2001st item modified since 1/1/2019, it will not be returned from that Read List operation because it exceeds the limit.
Now, I know I can set up a CAML query that uses a
Method element to specify the name of the Read List operation, and I can use a
Filter element to specify the Modified Date filter I set up, and pass a different value. I have done it successfully and verified that if I pass a filter value of 4/5/2019, I only get back about 5 items.
But my question is: is that CAML query and filter value actually getting passed all the way back to the SQL database, or is that just filtering on the subset of data that SharePoint already retrieved using the Read List and default filter set up in the Content Type?
So back to the example scenario, come 1/1/2020 when the view is only showing the 2000 items modified between 1/1/2019 and 12/31/2019 and leaving out item 2001 modified 1/1/2020, if I set up a CAML query to pass a modified date of 12/15/2019 to the filter, will I be able to get that 1/1/2020 item because the query is actually going all the way back to the SQL source? Or will it only give me back the items modified between 12/15/2019 and 12/31/2019 because that’s the original data returned by the Read List operation set up in the Content Type?