Expand my Community achievements bar.

Help with an AND statement in textmode reporting

Avatar

Level 2

I want add on to an existing report that is working nicely for me. In the text mode for filters, I want to add a statement to only show projects where production required = yes. 

 

I'm having issues with this DE syntax. I can't add AND to it or replace it with AND which is what I feel like needs to happen for this to work. 

 

Here is the current syntax:

Screenshot 2024-08-14 at 2.52.15 PM.png

 

The way the above report is constructed, it shows all projects/assignments, it IS NOT filtering out for projects that only require production.

 

 

8 Replies

Avatar

Level 2

Here is how I imagine the text mode should work, but I am incorrect. I receive an error when using the syntax below. 

 

Screenshot 2024-08-14 at 3.06.11 PM.png

Avatar

Level 10

Hi,

 

I think you need to use the DE: directly in front of your custom fields:

AND:1:task:percentComplete=100
AND:1:task:percentComplete_Mod=lt
AND:2:assignedTo:lastName_Mod=notnull
AND:3:project:name=TM2RET
AND:3:project:name_Mod=cicontains
AND:4:project:DE:PRODUCTION REQUIRED=Yes
AND:4:project:DE:PRODUCTION REQUIRED_Mod=in
...

 

Regards

Lars

Avatar

Level 2

Unfortunately, this returns as error as well.

Avatar

Community Advisor

The only use I see for "AND" syntaxing is to add options on to one filter option, as shown in this example here:

https://experienceleaguecommunities.adobe.com/t5/workfront-questions/filter-not-blank-and-not-x/m-p/...

 

i.e., the way I understand AND function is if you want to say two different things about a particular field, you would use AND to link the two things. (I want any objects where field X is not blank, and not equal to ABC)

 

So I'm wondering how you came to use "AND" in this case at all -- what is the AND driving?

 

If I had to guess, looking at your current filter statement, you have two filters acting.

 

Filter 1:

Everything where the user created the project

 

Filter 2:

User is on the project team

Task Percent Complete less than 100

Assigned to Last Name is not blank(?????)

Project Name contains TM24RET

Project Production Required = Yes

 

As a result, I can comment in 2 ways.

 

1) Probably the reason you're pulling in a lot of projects is because filter 1 is acting independently of filter 2. I can't imagine the AND adds anything to this filter at all.

2) I think the ANDs are all redundant. In fact, the whole thing could be done in standard mode, represented below (just add on the project production required field, to the screenshot)

 

skyehansen_0-1723740067514.png

 

Avatar

Level 2

Hi @skyehansen, thank you for your reply. 

 

To start from the top, I created an assignment report that shows me ALL of the projects that I am on, and the assignments associated with those projects. To successfully achieve this, I used the filters:

 

OR:1:project:enteredByID=$$USER.ID
OR:1:project:enteredByID_Mod=in
projectUsersOM:userID=$$USER.ID
projectUsersOM:userID_Mod=in

 

Screenshot 2024-08-15 at 10.25.36 AM.png

 

 

This was showing too much information, such as tasks which have been already completed, and tasks that were assigned to nobody (blank). So to solve for this, I wanted to see [projects that I entered, or projects that I am on] AND [Tasks that were less than 100% complete AND tasks that are not blank]

 

 

To successfully do this, I used the syntax below

 

AND:1:task:percentComplete=100
AND:1:task:percentComplete_Mod=lt
AND:2:assignedTo:lastName_Mod=notblank
OR:1:project:enteredByID=$$USER.ID
OR:1:project:enteredByID_Mod=in
projectUsersOM:userID=$$USER.ID
projectUsersOM:userID_Mod=in

 

This brought the report down from 2000 assignments to 80 which is accurate.

 

From here, I want to keep adding addition ANDs such as Project names contain, Production required...

 

So similar to what you posted 

 

Filter 1:

Project entered by user OR user is on project

 

Filter 2:

AND Task Percent Complete less than 100

AND Assigned to Last Name is not blank

AND Project Name contains TM24RET

AND Project Production Required = Yes

 

 

Avatar

Community Advisor

In my experience that's not how filters work. I'd honestly direct you to stay in standard mode, and go with something like this:

 

projectUsersOM:userID=$$USER.ID
projectUsersOM:userID_Mod=in
DE:project:Production Required=Yes
DE:project:Production Required_Mod=in
task:percentComplete=100
task:percentComplete_Mod=lt
assignedTo:lastName_Mod=notblank
OR:1:DE:project:Production Required=Yes
OR:1:DE:project:Production Required_Mod=in
OR:1:task:percentComplete=100
OR:1:task:percentComplete_Mod=lt
OR:1:assignedTo:lastName_Mod=notblank
OR:1:project:enteredByID=$$USER.ID
OR:1:project:enteredByID_Mod=in

 

(just copy the text blob into your report and then switch back to standard mode to see how it looks)

Avatar

Level 2

My Custom text mode imaged below works great for this one report I use.

 

Screenshot 2024-08-14 at 3.03.15 PM.png

 

I now want to add another AND statement to filter for Projects where Production Required = YES. Using the standard mode, I've determined that the language needed to identify production required tasks is below:

 

Screenshot 2024-08-14 at 3.05.04 PM.png

 

When inserting this into my report, it does not work correctly at all. I've tried to put "AND:" instead of "DE:" and I receive an error. Here is how I image the syntax should work, but I am clearly incorrect:

 

Screenshot 2024-08-14 at 3.06.11 PM.png

 

I attached an image of the textmode I am using and the result. Again, I want to ONLY see projects where Production required = Yes. So, the current result in the attached image is not achieving what I would like.

 

 

 

 

Avatar

Community Advisor

What kind of report is it? Task report? Project report?

I see you have the task object listed on line 1 and 2 and the project object listed on lines 4-11.

You only need to list the object if the object is different than the kind of report you have. Also, you need to ID custom fields with the "DE:" prefix, meaning data extension.

 

TIP: if this solved your problem, I invite you to consider marking it as a Correct Answer to help others who might also find it of use.
If you like my content, please take a moment to view and vote on my Idea Requests: https://tinyurl.com/mysocalledideas