Forum Discussion

JimHarrison's avatar
JimHarrison
Qrew Champion
2 days ago

Find Multiple Dates older than 2 Weeks and not blank.

For future me.

Leaving this here because I was unable to find any reference using the Khoros Search or Google Search.

If((ToText([Email Date]) <> "" and [Email Date] <= (Today()- Days(14)))
or (ToText([Instructions Send Date]) <> "" and [Instructions Send Date] <= (Today()- Days(14))
or (ToText([Verification Email Date]) <> "" and [Verification Email Date] <= (Today()- Days(14)))),true,false)

This formula defines a checkbox formula field.
The Checkbox formula field is used in a Pipeline to filter records meeting the criteria.

The entire purpose of the Pipeline is to clear the dates from these records once they are older than 14 days in the past.

I add this formula field to a report with the fields being evaluated and then look at the report. Only the ones that meet my criteria should be checked. I can use the report to verify it is working or make changes as needed.

Once satisfied with the results, I add the formula checkbox to the Pipeline as a single filter.

In case you're wondering, I also put in a Give Feedback asking to grant rights to use reports as a filter in a Pipeline.

formula query
  • Mez's avatar
    Mez
    Qrew Cadet
    2 days ago

    Curious to know why you're converting the Date fields to text to evaluate instead of using IsNull([ date ]) on the field? Have you found something wrong with IsNull()?

    ToText([Email Date]) <> ""

    • JimHarrison's avatar
      JimHarrison
      Qrew Champion
      18 hours ago

      Good question. The embedded Formula consultant was giving me errors and this made it stop so I could work out the parenthesis structure.

      Upon reflection and some rest I see the null check is redundant as the date calculation handles the null check. Here is the latest iteration, checking results now. Then I will apply it to a pipeline filter and we will see how much damage we can do. 

      If (([Email Date] <= (Today() - Days(14))
      or ([Instructions Date] <= (Today() - Days(14)))
      or ([Verification Date] <= (Today() - Days(14)))
      or (IsNull([Email Date]) and [Email Trigger])
      or (IsNull([Instructions Date]) and [send_trigger])
      or (IsNull([Verification Date]) and [Verification Trigger]))
      ,true,false)