Having trouble with combining AND and OR statements in a report. | Community
Skip to main content
S
ShawnMe
Level 3
November 17, 2021
Question

Having trouble with combining AND and OR statements in a report.

  • November 17, 2021
  • 2 replies
  • 808 views

I would like to run a report that finds the freelancers assigned to projects within particular programs, but I want to filter out all those with "••None" selected that are not owned by a particular user. The drop down field I am pulling freelancer selections from is called "Freelancer Assigned - Donor Relations". One of the selections in the field is "••None", and I only want the "••None"-selected projects that are owned by "J" to show up.

I have made it work in standard mode by selecting every value in the drop down except "••None" in a filter, but I will have to adjust the report any time I add a new freelancer. Using AND and OR statements in text mode would fix that, but I have been unable to make it work. Here is where I am:

‚

DE:Freelancer Assigned - Donor Relations_Mod=notblank

AND:1:DE:Freelancer Assigned - Donor Relations=••None

AND:1:DE:Freelancer Assigned - Donor Relations_Mod=notin

programID=XX YY

programID_Mod=in

statusEquatesWith=CUR KGG XDF PLN ONH IDA

statusEquatesWith_Mod=in

‚

OR:1:DE:Freelancer Assigned - Donor Relations=••None

OR:1:DE:Freelancer Assigned - Donor Relations_Mod=in

OR:1:ownerID=J

OR:1:ownerID_Mod=in

OR:1:status=CUR KGG XDF IDA ONH PLN

OR:1:status_Mod=in

‚

The results show everything I need except the "••None" selections. I have to use DE:Freelancer Assigned - Donor Relations_Mod=notblank because I do not want projects with that field blank to show.

Thank you,

Shawn

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

2 replies

C
ChloeRo
Level 4
November 18, 2021

Honestly, I always have the hardest time with "AND:1" filters when I have a "OR" filters in the report. It always disregards the "OR" filters that I build or it does some weird combination of the filters.. I don't know if it's user error or what, but I eventually gave up trying.

That said, I think this MIGHT work. I essentially re-defined your "OR:1" as "OR:2", and then prefixed your first set of filters with "OR:1", including the line for "AND:1", so now it reads "OR:1:AND:1".

OR:1:DE:Freelancer Assigned - Donor Relations_Mod=notblank

OR:1:AND:1:DE:Freelancer Assigned - Donor Relations=••None

OR:1:AND:1:DE:Freelancer Assigned - Donor Relations_Mod=notin

OR:1:programID=XX YY

OR:1:programID_Mod=in

OR:1:statusEquatesWith=CUR KGG XDF PLN ONH IDA

OR:1:statusEquatesWith_Mod=in

‚

OR:2:DE:Freelancer Assigned - Donor Relations=••None

OR:2:DE:Freelancer Assigned - Donor Relations_Mod=in

OR:2:ownerID=J

OR:2:ownerID_Mod=in

OR:2:status=CUR KGG XDF IDA ONH PLN

OR:2:status_Mod=in

Sometimes when you copy and paste text mode, the tabs convert to regular spaces, so when you paste the code into your report, delete the spaces between your status abbreviations and re-enter them as tabs.

S
ShawnMeAuthor
Level 3
November 22, 2021

Apologies for the delayed response, Chloe.

Unfortunately, that code resulted in only the "••None" projects being pulled. It ignored the line

OR:1:DE:Freelancer Assigned - Donor Relations_Mod=notblank

which is basically the opposite of where I was.

But that got me thinking. So I tried taking what I had and switching the filter sections so that the OR section included the code for the selections other than the "••None" projects. And that seems to be working.

DE:Freelancer Assigned - Donor Relations=••None

DE:Freelancer Assigned - Donor Relations_Mod=in

ownerID=5f1757cb00ee28645310345460d019b5

ownerID_Mod=in

statusEquatesWith=CUR KGG XDF PLN ONH IDA

statusEquatesWith_Mod=in

OR:1:DE:Freelancer Assigned - Donor Relations_Mod=notblank

OR:1:AND:1:DE:Freelancer Assigned - Donor Relations=••None

OR:1:AND:1:DE:Freelancer Assigned - Donor Relations_Mod=notin

OR:1:programID=556dccee004513704be43ad2389f476c 5660b9320021940aef7c55b78ef5dba9

OR:1:programID_Mod=in

OR:1:statusEquatesWith=CUR KGG XDF PLN ONH IDA

OR:1:statusEquatesWith_Mod=in

Thanks for your help!

RandyRoberts
Community Advisor
RandyRobertsCommunity Advisor
Community Advisor
November 19, 2021

Anytime I copy text mode I paste it into an IDE or a syntax colored text editor so I can 'fix it up" before I paste into Workfront.

I've never been happy with the way "OR" statements are handled. If I want some object I own with an A or B in the name it's way more code than it should be

owner=me

AND

name contains A

OR

owner=me

AND

name contains B

I'd like to have the first part constant so I could write:

owner=me

AND

name contains A OR B

When I have a lot of constants and a lot of OR statements I have to re-write all the contents for each OR. I have one report where I filter for:

status=CUR BHK PLN

status_Mod=in

ownerID=$$USER.ID

ownerID_Mod=in

companyID_Mod=isblank

and then I filter for 28 OR statements (missing project form info) and I have to copy all 5 lines of the constant to each OR statement equalling 160 lines.

    Rafal_Bainie
    Community Advisor
    Rafal_BainieCommunity Advisor
    Community Advisor
    July 2, 2024

    can this be leveraged in Fusion?

    combination of AND and OR clauses in custom API query

    Terms & ConditionsAccessibility statement

    Loading footer...