Text Mode Filter adding OR condition for lastNoteEntryDate=Null

[Mrobi]

Hi,

I need some basic help with syntax…

I have a report which shows active tasks that are 30 days past the Planned Completion Date (i.e. Late Tasks) and do not have a recent update note (showing the PM is aware and on top of it). 

 numberOfChildren=1

 numberOfChildren_Mod=lt

 plannedCompletionDate=$$TODAY-30d

 plannedCompletionDate_Mod=lte

 lastNote:entryDate=$$TODAY-30 d

 lastNote:entryDate_Mod=lt

 status=INP        NEW

status_Mod=in

The problem is this text filter does not show me late tasks where the Last Note Entry Date is "Null".   Basically, it's hiding my late tasks if a Last Note was ever entered.
 

I need  to add an OR element to my Last Note Entry Date… here's the basic logic I need -

      … AND (lastNote:EntryDate <= $$TODAY-15 OR  lastNote:EntryDate IS NULL))

[skyehansen]

maybe I'm missing something but I don't see why you don't just do it in the standard mode. There's nothing in your filter that needs to go to text mode. See screenshot below:

[Mrobi]

Thanks, but, the 2nd filter group under the OR will then allow Last Updates within 30 Days to show on the report.  In other words, it makes the first "Last Note Entry Date < $$TODAY-30 Days" a moot statement. 

[skyehansen]

My apologies! I think I get it. That makes sense.

I realized once I really looked at the report and what you needed, that an "easy" filter isn't possible for "last note entry date is null" -- an entry date of null is basically saying "a note was never created" -- so we've been trying to create a filter for something that isn't there. For cases like this, you would need to create an exists filter (specifically a "notexists" filter). I can "guess" one for you, but don't really have the time to test it this morning. Exists/NotExists filters look like this:

EXISTS:a:$$EXISTSMOD=NOTEXISTS

EXISTS:a:$$OBJCODE=NOTE

EXISTS:a:taskID=FIELD:ID

You would set up a second filter with the number of children, planned completion date, and status filters, then switch over to text mode and include the code above, adding an "OR:1:" to the front of each line. (i.e. OR:1:EXISTS:a:_____)

Hopefully that gets you to where you want, or at the very least advances your understanding about what you were trying to do not being possible with filtering on null.