OData Filter For "Contains" On Get List Items Activity (Sharepoint)

[david.kameka]

Issue:
Was successful getting the OData filter in this activity to work for “eq” specific field and search string but when trying to test with contains function it is not working.

What I’ve tried:
“contains(fields/Status,‘New’)”
“fields/contains(Status,‘New’)”
“$filter=fields/contains(Status,‘New’)”
etc and keep getting error: Invalid Request
Using the Get List Items activity under Sharepoint Lists Activities (UiPath.MicrosoftOffice365.Activites

What I would like:
Need correct format for OData filter for the contains function so that it works in this activity.
Status is the field name and ‘New’ is the text I would like to look for. (For testing).

[Parvathy (PS Parvathy)]

Hi @david.kameka

Try this syntax:

$filter=substringof('New', Status)

Hope it helps!!

[david.kameka]

Hello Parvathy;
I tried your suggestion of:
$filter=substringof(‘New’, Status)

I also tried:
$filter=substringof(Status, ‘New’)

Getting same error.

Get List Items: Code: invalidRequest
Message: Invalid request

I know it has to do with the formatting of the OData Filter because “fields/Status eq ‘New’” works fine when tried.

Any other suggestions our welcome and appreciated.

[Anil_G (Anil Gorthi)]

@david.kameka

Please try with

contains(fieldname,'New')

Cheers

[ppr (Peter Preuss)]

had you tried also withpout $filter=

substringof(Status,'New')
substringof(fields/Status,'New')

we would not expect, but remember some dialects on derivates

substringof('New',Status)
substringof('New',fields/Status)

Another approach to simulate a contains is the indexOf(…) ne -1

[david.kameka]

Hello Anil;

I tried with:
“contains(Status,‘New’)” and received same error.

Proceeded to try with below attempts and also still error.

“contains(fields/Status,‘New’)”
“/fields/?$filter=contains(Issue, ‘Issue’)”
“.fields/?$filter=contains(Issue, ‘Issue’)”
“fields/?$filter=contains(Issue, ‘Issue’)”

[Anil_G (Anil Gorthi)]

@david.kameka

“contains(fields/Status,‘New’)”

@david.kameka

Ifeally this should be working…

Can you say what error you are getting

Invalid query?

Cheers

[david.kameka]

Hello Peter;

You got it.
Filter that worked: substringof(‘New’,fields/Status)
Results below.

Peter I’m relieved to get the answer; but, want to learn and understand this filter better. Can you provide any links or direction on how you came up with substringof instead of contains and other functions in would like to use in OData (Starts with, Ends with etc).
I was trying to learn from Microsoft site and got me no where in this case?
Link To Site: Microsoft OData Filter

Again any links or leads for me to chew on would be great.

[david.kameka]

Hi Anil;

The errors I was getting with all my other attempts was:
Invalid Request
However when I tried your most recent script: “contains(fields/Status,‘New’)”
I received a different error which was very interesting:
Get List Items: Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled.

I feel like we are getting closer to be able to use the contains function if we can resolve this latest error?

[Anil_G (Anil Gorthi)]

@david.kameka

it is because of the inverted comma…you would have copied…just try to remove and replace inverted comma

"contains(fields/Status 'New')"

cheers

[david.kameka]

Sorry Anil when I try: “contains(fields/Status,‘New’)”

I get the following error still:
Get List Items: Code: itemNotFound
Message: Item not found

I feel this should be working too; but, just can’t figure out what is missing or what needs to be removed from the statement?

I’ve got a solution from Peter; but, was really hoping to stick with the OData functions like “Contains”, “Starts With” etc etc. to get this working. Don’t want to over burden anyone though.

[Anil_G (Anil Gorthi)]

@david.kameka

Generally item
Not found comes when the column is not found…can you check the exception details from locals pnel…if you have any other info there

Cheers

[david.kameka]

Hi Anil;

Below is the full error trace but really still unable to find any leads from it.

I agree the error has to do with the column not being seen or found.
I feel the fields/ part is causing the error where it might be in the wrong position or need something else before or after it; but, I have tried placing it everywhere only to see it throw same error.

Just don’t understand how “fields/Status eq ‘New’” works and recognizes the Status Field; but, “contains(fields/Status,‘New’)” does not?

Full Error:
Microsoft.Graph.ServiceException: Code: itemNotFound
Message: Item not found
Inner error:
AdditionalData:
date: 2023-10-02T16:58:48
request-id:
client-request-id:
ClientRequestId:
at Microsoft.Graph.SimpleHttpProvider.SendAsync(HttpRequestMessage request, HttpCompletionOption completionOption, CancellationToken cancellationToken)
at Microsoft.Graph.BaseRequest.SendRequestAsync(Object serializableObject, CancellationToken cancellationToken, HttpCompletionOption completionOption)
at Microsoft.Graph.BaseRequest.SendAsync[T](Object serializableObject, CancellationToken cancellationToken, HttpCompletionOption completionOption)
at Microsoft.Graph.ListItemsCollectionRequest.GetAsync(CancellationToken cancellationToken)
at UiPath.MicrosoftOffice365.Sharepoint.Utilities.IterableSharepointListItemProvider.<>c__DisplayClass4_0.<<System-Collections-Generic-IAsyncEnumerable-GetAsyncEnumerator>b__5>d.MoveNext()
— End of stack trace from previous location —
at UiPath.MicrosoftOffice365.Sharepoint.Utilities.IterableSharepointListItemProvider.System.Collections.Generic.IAsyncEnumerable<UiPath.MicrosoftOffice365.Models.Office365SharepointListItem>.GetAsyncEnumerator(CancellationToken cancellationToken)+MoveNext()
at UiPath.MicrosoftOffice365.Sharepoint.Utilities.IterableSharepointListItemProvider.System.Collections.Generic.IAsyncEnumerable<UiPath.MicrosoftOffice365.Models.Office365SharepointListItem>.GetAsyncEnumerator(CancellationToken cancellationToken)+System.Threading.Tasks.Sources.IValueTaskSource<System.Boolean>.GetResult()
at System.Linq.AsyncEnumerable.g__Core|424_0[TSource](IAsyncEnumerable1 source, CancellationToken cancellationToken) in /_/Ix.NET/Source/System.Linq.Async/System/Linq/Operators/ToList.cs:line 36 at System.Linq.AsyncEnumerable.<ToListAsync>g__Core|424_0[TSource](IAsyncEnumerable1 source, CancellationToken cancellationToken) in /_/Ix.NET/Source/System.Linq.Async/System/Linq/Operators/ToList.cs:line 36
at UiPath.MicrosoftOffice365.Activities.SharePoint.GetListItems.ExecuteAsync(AsyncCodeActivityContext context, CancellationToken cancellationToken)
at UiPath.Shared.Activities.AsyncTaskCodeActivityImplementation.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at UiPath.Shared.Activities.AsyncTaskCodeActivity.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at UiPath.Shared.Activities.ContinuableAsyncCodeActivity.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at UiPath.MicrosoftOffice365.Activities.Office365BaseClientActivity.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at UiPath.MicrosoftOffice365.Activities.Office365ClientActivity`2.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)