question

FilipLisiecki-1163 avatar image
0 Votes"
FilipLisiecki-1163 asked AnkitBafna-9596 commented

Azure Data Factory Lookup filter query for Sharepoint Online List

I would like to filter the Sharepoint Online List based on the Modified date, to get only the last modified records. I am using the ADF Lookup component.

I was trying different queries, however non of them work and I am getting all elements in the list (or an error).

Modified ge '@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-ddTHH:MM:ssZ')}'
Modified ge '@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')}'
Modified ge DateTime'@{addDays(utcNow(),-1)}'
$filter=Modified ge '@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-ddTHH:MM:ssZ')}'

ADF code:

 "typeProperties": {
                 "source": {
                     "type": "SharePointOnlineListSource",
                     "query": {
                         "value": "Modified ge '@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')}'",
                         "type": "Expression"
                     },
                     "httpRequestTimeout": "00:05:00"
                 },
                 "dataset": {
                     "referenceName": "sp_dataset",
                     "type": "DatasetReference",
                     "parameters": {
                         "list_name": "sp_list"
                     }
                 },
                 "firstRowOnly": false
             }

How it's possible to filter the Sharepoint List from the ADF?


azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @FilipLisiecki-1163 ,

Welcome to Q&A Forum!

According to your description, your problem is not much related to SharePoint Online. Therefore, we will remove the "office-sharepoint-online" tag.

If you have any concerns, please feel free to reply.

Thanks

0 Votes 0 ·

1 Answer

SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered AnkitBafna-9596 commented

Hi @FilipLisiecki-1163,

Thanks for using Microsoft Q&A !!

You need to use $filter in the query to filter the records out of the list based on your condition. Also, you need to pass filter as a text, which you can achieve by using @concat in the dynamic expression. So, you need to create filter like below -
@concat('$filter=Modified ge datetime''',formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd'),'''')

I have tried this in my environment and it worked as expected and fetched only records satisfying the filter condition. Please find below the gif for your reference.

126845-getsharepointlistfiltered.gif
Please let me know if you have any questions.

Thanks
Saurabh


Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, it worked :)

0 Votes 0 ·

@filiplisiecki-1163 Great. Thanks for the confirmation.

Thanks
Saurabh

0 Votes 0 ·

Hi @SaurabhSharma-msft ,

I am also facing the similar issue. When I apply filter query (as shown in your example) to a SharePoint Online list which has more than 5000 records then it gives internal server error but the same query works for other lists with less than 5000 records. Can you please help?

Query: @concat('$filter=Modified ge datetime''',formatDateTime(addDays(utcNow(),-1),'2021-01-01'),'''')

Error:
Error code
23205
Details
Requesting response from path myListName and query $filter=Modified ge datetime'2021-01-01' failed with status code InternalServerError and message <?xml version="1.0" encoding="utf-8" standalone="yes"?> <error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">; <code></code> <message xml:lang="en-US">An error occurred while processing this request.</message> </error>. Activity ID: c75c2a61-3174-4208-a534-74de1453128a

0 Votes 0 ·