I’m using the “Get Email” activity to search an email folder, refining my query with parameters to target specific emails. I’m interested in emails containing certain keywords found in both the subject and body. Specifically, I want to exclude emails with “FW” or “RE:” in the subject, as these indicate forwarded or replied messages. Users might alter the subject and remove these indicators. Additionally, I’m searching the body and excluding common phrases like “From:” and “To:” that appear in threaded or replied emails.
After processing each email, I assign different categories. To ignore emails already categorized by my process, I’ve added these categories to the original query list so that the initial “Get Email” activity also ignores them.
Here are two queries that are currently functional without API errors, but they’re not providing the desired results as described above:
** First Query:**
((contains(subject, 'Invoice inquiry') or contains(subject, 'Payment inquiry') or contains(subject, 'Outstanding invoice') or contains(subject, 'Past Due invoice') or contains(subject, 'Payment status') or contains(subject, 'Statement')) and
(contains(body/content, 'Invoice inquiry') or contains(body/content, 'Payment inquiry') or contains(body/content, 'Outstanding invoice') or contains(body/content, 'Past Due invoice') or contains(body/content, 'Payment status') or contains(body/content, 'Statement')) and
not (startsWith(subject, 'FW') or startsWith(subject, 'RE:')) and
not (contains(body/content, 'From:') or contains(body/content, 'Sent:') or contains(body/content, 'To:') or contains(body/content, 'Cc:') or contains(body/content, 'Subject:')) and
not (categories/any(a:a eq 'BOT PROCESSED- STATEMENT') or categories/any(a:a eq 'BOT EXCEPTION') or categories/any(a:a eq 'BOT- NEEDS REVIEW')))
Second Query:
((contains(subject, 'Invoice inquiry') or contains(body/content, 'Invoice inquiry') or contains(subject, 'Payment inquiry') or contains(body/content, 'Payment inquiry') or contains(subject, 'Outstanding invoice') or contains(body/content, 'Outstanding invoice') or contains(subject, 'Past Due invoice') or contains(body/content, 'Past Due invoice') or contains(subject, 'Payment status') or contains(body/content, 'Payment status') or contains(subject, 'Statement') or contains(body/content, 'Statement')) and
not (startsWith(subject, 'FW') or startsWith(subject, 'RE:') or contains(body/content, 'From:') or contains(body/content, 'Sent:') or contains(body/content, 'To:') or contains(body/content, 'Cc:') or contains(body/content, 'Subject:')) and
not (categories/any(a:a eq 'BOT PROCESSED- STATEMENT') or categories/any(a:a eq 'BOT EXCEPTION') or categories/any(a:a eq 'BOT- NEEDS REVIEW')))
Both queries should include all the criteria I mentioned earlier.
In the first query, I’ve noticed that it returns 21 emails out of 50, which seems to match my query requirements. However, there’s an issue with the categories not being respected; even after setting the ‘BOT PROCESSED- STATEMENT’ category to these emails and rerunning the query, I still get the same 21 emails. This indicates that the categories I’m telling it to ignore are not being applied correctly.
On the other hand, the second query, although more organized with grouped conditions like contains subject, contains body, starts with subject, starts with body, etc., is only returning 6 emails. Given that I know there are more emails that should match the criteria, I find this result to be inaccurate, especially after clearing all categories before running it.
I prefer the first query initially because it returns more results, but it needs fixing regarding the category issue. Additionally, I need to understand what’s wrong with the second query that’s causing it to return only 6 emails.
Could you please advise on how to adjust the first query to respect the categories I want to ignore and identify any issues with the second query that may be limiting its results?