Answered

OData Filter based on Time failed partially

(0) Share

Report

Posted on by Mojos

Guys,

I tried to filter out with SharePoint list from 01 to 24 with current time HH, I have no issues with 10 onwards but have problem to filter 01 to 09

DispatchTime eq '@{outputs('Compose_-_Today_Time')}' or
DispatchTime eq @{formatDateTime(addHours(utcNow(),7),'HH')} or
DispatchTime eq '@{trim(outputs('Compose_-_Today_Time'))}' or

the result was :

"body": {
 "status": 400,
 "message": "The expression \"DispatchTime eq '09\n' or \nDispatchTime eq 09 or \nDispatchTime eq '9' or \nDispatchTime eq '9'\" is not valid. Creating query failed.\r\nclientRequestId: ad130b4f-f303-44f1-8de2-de210c7b4068\r\nserviceRequestId: 8dca3aa1-5014-6000-0032-6f7663bb0722"
 }
}

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (1)

SudeepGhatakNZ 14,394 Most Valuable Professional on at

Like (0)

Report
@Mojos ,

The error indicates that the filter expression contains newlines and possibly mismatched types. Try this

Add a Compose action to get the current time formatted as "HH"

formatDateTime(utcNow(), 'HH')

Add another Compose action to get the time 7 hours ahead, formatted as HH:

formatDateTime(addHours(utcNow(), 7), 'HH')

Use the outputs of these Compose actions in your filter query without newlines:

DispatchTime eq '@{outputs('Compose_Today_Time_Formatted')}' or DispatchTime eq '@

Was this reply helpful? Yes No
Mojos 16 on at

Like (0)

Report

Hi @SudeepGhatakNZ, I've same setup, mentioned in first post .. have it as Compose and as Function into the OData filter, and again it works for 10-24 but not for 00 - 09

Was this reply helpful? Yes No
SudeepGhatakNZ 14,394 Most Valuable Professional on at

Like (0)

Report

Can you provide the RAW input and Output of the action that's failing?

Was this reply helpful? Yes No
Mojos 16 on at

Like (0)

Report
Herewith failing raw data
input
{ "host": { "connectionReferenceName": "shared_sharepointonline", "operationId": "GetItems" }, "parameters": { "dataset": "https://xxxxxx.sharepoint.com/sites/xxxxxx", "table": "xxxxxx", "$filter": "DispatchTime eq '07\n' or \nDispatchTime eq 07 or \nDispatchTime eq '7' or \nDispatchTime eq '7'" } }
output
{ "statusCode": 400, "headers": { "Cache-Control": "max-age=0, private", "Vary": "Origin", "X-NetworkStatistics": "0,4194560,0,0,18905468,0,4194560,8", "X-SharePointHealthScore": "0", "X-MS-SPConnector": "1", "X-SP-SERVERSTATE": "ReadOnly=0", "DATASERVICEVERSION": "3.0", "SPClientServiceRequestDuration": "62", "SPRequestDuration": "63", "X-DataBoundary": "NONE", "X-1DSCollectorUrl": "https://mobile.events.data.microsoft.com/OneCollector/1.0/", "X-AriaCollectorURL": "https://browser.pipe.aria.microsoft.com/Collector/3.0/", "SPRequestGuid": "a16ec694-6adb-49cc-8ad8-ceea77953e99", "request-id": "a16ec694-6adb-49cc-8ad8-ceea77953e99", "MS-CV": "oTu5E4TAAGANOATPoifw2w.0", "Strict-Transport-Security": "max-age=31536000", "X-Frame-Options": "SAMEORIGIN", "Content-Security-Policy": "frame-ancestors 'self' teams.microsoft.com *.teams.microsoft.com *.skype.com *.teams.microsoft.us local.teams.office.com teams.cloud.microsoft *.office365.com goals.cloud.microsoft *.powerapps.com *.powerbi.com *.yammer.com engage.cloud.microsoft word.cloud.microsoft excel.cloud.microsoft powerpoint.cloud.microsoft *.officeapps.live.com *.office.com *.microsoft365.com *.stream.azure-test.net *.microsoftstream.com *.dynamics.com *.microsoft.com onedrive.live.com *.onedrive.live.com securebroker.sharepointonline.com;", "MicrosoftSharePointTeamServices": "16.0.0.25019", "X-Content-Type-Options": "nosniff", "X-MS-InvokeApp": "1; RequireReadOnly", "P3P": "CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\"", "X-AspNet-Version": "4.0.30319", "X-Powered-By": "ASP.NET", "Timing-Allow-Origin": "*", "x-ms-apihub-cached-response": "false", "x-ms-apihub-obo": "false", "Date": "Sat, 13 Jul 2024 00:00:45 GMT", "Content-Length": "306", "Content-Type": "application/json", "Expires": "Fri, 28 Jun 2024 00:00:46 GMT", "Last-Modified": "Sat, 13 Jul 2024 00:00:46 GMT" }, "body": { "status": 400, "message": "The expression \"DispatchTime eq '07\n' or \nDispatchTime eq 07 or \nDispatchTime eq '7' or \nDispatchTime eq '7'\" is not valid. Creating query failed.\r\nclientRequestId: a16ec694-6adb-49cc-8ad8-ceea77953e99\r\nserviceRequestId: 13b93ba1-c084-6000-0d38-04cfa227f0db" } }
Run history, doesn't work between 00-09

Was this reply helpful? Yes No
Verified answer

Mojos 16 on at

Like (0)

Report
copilot help me to fix this :

original query create unnecessary whitespace or newline characters (\n) within the expression.

DispatchTime eq '@{outputs('Compose_-_Today_Time')}' or DispatchTime eq @{formatDateTime(addHours(utcNow(),7),'HH')} or DispatchTime eq '@{outputs('Compose_Today_Time_+7')}' or DispatchTime eq '@{outputs('Compose_H')}'

revised query

DispatchTime eq '@{outputs('Compose_-_Today_Time')}' or DispatchTime eq '@{formatDateTime(addHours(utcNow(),7),'HH')}' or DispatchTime eq '@{outputs('Compose_Today_Time_+7')}' or DispatchTime eq '@{outputs('Compose_H')}'

Was this reply helpful? Yes No