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
Topics help categorize Community content and increase your ability to discover relevant content.
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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!
Views
Replies
Total Likes
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.
can this be leveraged in Fusion?
combination of AND and OR clauses in custom API query
Views
Replies
Total Likes