Shrink this Giant Query for GET Email

[Jay_Chacko (JC)]

I have managed to get the query category working with the help of the Forum. However, my requirement involves a large number of categories that the BOT needs to skip. Currently, I have a complex query that technically achieves this meaning no error, but I doubt the accuracy, I am seeing a lot of error as far as its still resulting in things I am stating it not to, any way it is also difficult to manage and easy to make mistakes that can result in a bad search or a faulty API request. Here is an example of my query:

"contains(subject, 'Itemized Bills Request') or contains(subject, '2nd Itemized Bills Request') or contains(subject, '2nd Request Itemized Bills Request') and contains(from/emailAddress/address, '@ceris.com') and not categories/any(a:a eq 'BOT Dispatched') and not categories/any(a:a eq 'BOT Extracted') and not categories/any(a:a eq 'BOT Correspondence Created') and not categories/any(a:a eq 'BOT Print Itemized Bill') and not categories/any(a:a eq 'BOT Corvel Uploaded') and not categories/any(a:a eq 'BOT Updated Correspondence') and not categories/any(a:a eq 'In Process to Log') and not categories/any(a:a eq 'In Process to Log/ Hold/ MN') and not categories/any(a:a eq 'In Process to Log/Hold CLM') and not categories/any(a:a eq 'In Process to Log/Hold LF') and not categories/any(a:a eq 'In Process to Log/Hold/CF') and not categories/any(a:a eq 'In Process to Log/Hold/FL') and not categories/any(a:a eq 'In Process to Log/Hold/LM') and not categories/any(a:a eq 'In Process to Log/Hold/NYM') and not categories/any(a:a eq 'In Process to Log/JG') and not categories/any(a:a eq 'In Process to Log/MN') and not categories/any(a:a eq 'In Process to Log/PM') and not categories/any(a:a eq 'In Process to Log/TEAM') and not categories/any(a:a eq 'Completed/Logged') and not categories/any(a:a eq 'Completed/Logged/ LF') and not categories/any(a:a eq 'Completed/Logged/CF') and not categories/any(a:a eq 'Completed/Logged/CLM') and not categories/any(a:a eq 'Completed/Logged/JG') and not categories/any(a:a eq 'Completed/Logged/LM') and not categories/any(a:a eq 'Completed/Logged/MN') and not categories/any(a:a eq 'Completed/Logged/NYM') and not categories/any(a:a eq 'Completed/Logged/TEAM') and not categories/any(a:a eq 'Do Not Process') and not categories/any(a:a eq 'Do Not Process/ Returned to Auditor') and not categories/any(a:a eq 'Do Not Process/Hold/Returned') and not categories/any(a:a eq 'Duplicate')"

Here is it again for visual purpose
“contains(subject, ‘Itemized Bills Request’) or
contains(subject, ‘2nd Itemized Bills Request’) or
contains(subject, ‘2nd Request Itemized Bills Request’) and
contains(from/emailAddress/address, ‘@ceris.com’) and not
categories/any(a:a eq ‘BOT Dispatched’) and not
categories/any(a:a eq ‘BOT Extracted’) and not
categories/any(a:a eq ‘BOT Correspondence Created’) and not
categories/any(a:a eq ‘BOT Print Itemized Bill’) and not
categories/any(a:a eq ‘BOT Corvel Uploaded’) and not
categories/any(a:a eq ‘BOT Updated Correspondence’) and not
categories/any(a:a eq ‘In Process to Log’) and not
categories/any(a:a eq ‘In Process to Log/ Hold/ MN’) and not
categories/any(a:a eq ‘In Process to Log/Hold CLM’) and not
categories/any(a:a eq ‘In Process to Log/Hold LF’) and not
categories/any(a:a eq ‘In Process to Log/Hold/CF’) and not
categories/any(a:a eq ‘In Process to Log/Hold/FL’) and not
categories/any(a:a eq ‘In Process to Log/Hold/LM’) and not
categories/any(a:a eq ‘In Process to Log/Hold/NYM’) and not
categories/any(a:a eq ‘In Process to Log/JG’) and not
categories/any(a:a eq ‘In Process to Log/MN’) and not
categories/any(a:a eq ‘In Process to Log/PM’) and not
categories/any(a:a eq ‘In Process to Log/TEAM’) and not
categories/any(a:a eq ‘Completed/Logged’) and not
categories/any(a:a eq ‘Completed/Logged/ LF’) and not
categories/any(a:a eq ‘Completed/Logged/CF’) and not
categories/any(a:a eq ‘Completed/Logged/CLM’) and not
categories/any(a:a eq ‘Completed/Logged/JG’) and not
categories/any(a:a eq ‘Completed/Logged/LM’) and not
categories/any(a:a eq ‘Completed/Logged/MN’) and not
categories/any(a:a eq ‘Completed/Logged/NYM’) and not
categories/any(a:a eq ‘Completed/Logged/TEAM’) and not
categories/any(a:a eq ‘Do Not Process’) and not
categories/any(a:a eq ‘Do Not Process/ Returned to Auditor’) and not
categories/any(a:a eq ‘Do Not Process/Hold/Returned’) and not
categories/any(a:a eq ‘Duplicate’)”

I also have to do this in search instead of getting a bunch of emails and then looping the email list to remove, its part of my requirement, also doing that way , we will just search the same email again and again due to bot runs every 10 minutes or so.

My hope is to shrink the category matching to like 5 types instead of 30+

something like categories/any(a:a contains ‘Duplicate’) which doesn’t work
Only combination of categories query format that works for me is this
"categories/any(a:a eq ‘Duplicate’)" which is why I did this long format.

Below are nonworking examples of my attempts, bad filter per UiPath error log - you can see what I am trying to do

"contains(from/emailAddress/address, '@elementblue.com') and not startsWith(categories/any, 'BOT')"
"contains(from/emailAddress/address, '@elementblue.com') and not contains(categories/any(a:a eq, 'In Process'))"

Also seems like after the “and not” is not working completely , if my query is simply

"contains(subject, 'Itemized Bills Request') or contains(subject, '2nd Itemized Bills Request') or contains(subject, '2nd Request Itemized Bills Request') and contains(from/emailAddress/address, '@elementblue.com') and not categories/any(a:a eq 'BOT Dispatched')"

It is still bringing in emails that have category “BOT Dispatched”, why is that?
just this works , but not when I chain it?

not categories/any(a:a eq 'BOT Dispatched')"

[argin.lerit]

Hello @Jay_Chacko - This is a great question and the first thing that I noticed is the usage of parentheses in the query. There may be conditions in there that need to be evaluated together and need to be enclosed in parentheses. Use a text editor like Notepad++ that shows you the parentheses pairing to make it easier for you to see where to put parentheses

Without the recommended parentheses, all these individual conditions are evaluated on their own, that’s why you get unwanted results. Let me know if this works out for you or if you need more help.

[Jay_Chacko (JC)]

thanks i will fix that, what about shrinking it , any idea ? I would love to use a contain function for categories but not sure how to use that for categories

categories/any(a:a eq ‘BOT Dispatched’)"

if I change the above format in anyway I get API error , the eq can be replaced with ne, that’s about it

[argin.lerit]

@Jay_Chacko

Below are nonworking examples of my attempts, bad filter per UiPath error log - you can see what I am trying to do

"contains(from/emailAddress/address, '@elementblue.com') and not startsWith(categories/any, 'BOT')"
"contains(from/emailAddress/address, '@elementblue.com') and not contains(categories/any(a:a eq, 'In Process'))"

Also seems like after the “and not” is not working completely , if my query is simply

Perhaps the / characters in the parameter are messing with the query. I would try the following and maybe try with {} if [] doesn’t workout.

"contains([from/emailAddress/address], '@elementblue.com') and not startsWith([categories/any], 'BOT')"
"contains([from/emailAddress/address], '@elementblue.com') and not contains([categories/any](a:a eq, 'In Process'))"

[Anil_G (Anil Gorthi)]

@Jay_Chacko

I guess this is the syntax for any with contains

categories/any(a:contains(a,'BOT'))

Can you please try this

Cheers

[Jay_Chacko (JC)]

@Anil_G

categories/any(a:contains(a,'BOT'))

I got a Microsoft Office 365 Scope: Code: ErrorInvalidUrlQueryFilter
Message: The query filter contains one or more invalid nodes.

[Jay_Chacko (JC)]

yes indeed seems that way, added parenthesis and seems like I am getting the result I want with long query. Thanks

Any idea about the core issue ? shrinking to use contain a function with the query for category?

[argin.lerit]

@Jay_Chacko

What does your current query look like now? Let’s see how we can make it shorter.

Thanks

[Jay_Chacko (JC)]

After the parenthesis Fix , here it is

"(contains(subject, 'Itemized Bills Request') or contains(subject, '2nd Itemized Bills Request') or contains(subject, '2nd Request Itemized Bills Request')) and (contains(from/emailAddress/address, '@ceris.com')) and not (categories/any(a:a eq 'BOT Dispatched') or categories/any(a:a eq 'BOT Extracted') or categories/any(a:a eq 'BOT Correspondence Created') or categories/any(a:a eq 'BOT Print Itemized Bill') or categories/any(a:a eq 'BOT Corvel Uploaded') or categories/any(a:a eq 'BOT Updated Correspondence') or categories/any(a:a eq 'In Process to Log') or categories/any(a:a eq 'In Process to Log/ Hold/ MN') or categories/any(a:a eq 'In Process to Log/Hold CLM') or categories/any(a:a eq 'In Process to Log/Hold LF') or categories/any(a:a eq 'In Process to Log/Hold/CF') or categories/any(a:a eq 'In Process to Log/Hold/FL') or categories/any(a:a eq 'In Process to Log/Hold/LM') or categories/any(a:a eq 'In Process to Log/Hold/NYM') or categories/any(a:a eq 'In Process to Log/JG') or categories/any(a:a eq 'In Process to Log/MN') or categories/any(a:a eq 'In Process to Log/PM') or categories/any(a:a eq 'In Process to Log/TEAM') or categories/any(a:a eq 'Completed/Logged') or categories/any(a:a eq 'Completed/Logged/ LF') or categories/any(a:a eq 'Completed/Logged/CF') or categories/any(a:a eq 'Completed/Logged/CLM') or categories/any(a:a eq 'Completed/Logged/JG') or categories/any(a:a eq 'Completed/Logged/LM') or categories/any(a:a eq 'Completed/Logged/MN') or categories/any(a:a eq 'Completed/Logged/NYM') or categories/any(a:a eq 'Completed/Logged/TEAM') or categories/any(a:a eq 'Do Not Process') or categories/any(a:a eq 'Do Not Process/ Returned to Auditor') or categories/any(a:a eq 'Do Not Process/Hold/Returned') or categories/any(a:a eq 'Duplicate'))"

[argin.lerit]

@Jay_Chacko

Please try this if it works

(contains(subject, 'Itemized Bills Request') or contains(subject, '2nd Itemized Bills Request') or contains(subject, '2nd Request Itemized Bills Request')) and (contains(from/emailAddress/address, '@ceris.com')) and not (categories/any(p:startsWith(p, 'BOT ')) or categories/any(p:startsWith(p, 'In Process to Log')) or categories/any(p:startsWith(p, 'Completed/Logged')) or categories/any(p:startsWith(p, 'Do Not Process')) or categories/any(a:a eq 'Duplicate'))

Thanks!

[Jay_Chacko (JC)]

@Jay_Chacko

I got a Microsoft Office 365 Scope: Code: ErrorInvalidUrlQueryFilter

yup same error. Whats the difference in a and p, where you getting these infor to test? , thanks

[argin.lerit]

@Jay_Chacko This is the reference I use: Use the filter query parameter to filter a collection of objects - Microsoft Graph | Microsoft Learn There’s no significant difference between a and p, they’re just variables.

Are you using Postman to test your queries? Maybe you can check what the categories object actually looks like in the returned data so we can know why this command is failing.

Thanks!

[Jay_Chacko (JC)]

Categories itself is a siring list. The main email list from get email is 365list that contain Office365Message

Here is the full list of properties and methods

message resource type - Microsoft Graph v1.0

[postwick (Paul Ostwick)]

It has to be

category/any(a:a eq (‘Some Category’))

I’ve never tried contains. Don’t know if there is a way to do contains

[postwick (Paul Ostwick)]

Since you’re trying to filter out all those categories, would there be a way to look for the categories you DO want, and maybe that list is shorter?