using DateAdd in workflow trigger conditions

I'm trying to trigger a workflow 9 days before a date that's stored in an index field called 1st Review Date but I can't get it to work.  I've set the trigger to run every morning at 7am and in the trigger criteria I've used the "Is (where clause)" and various formats of DateAdd("d", 9, Now()) but none of them work - they all throw an error that says Wrong condition! Field : 1ST_REVIEW_DATE Value : DateAdd("d", 9, Now())

I've tried using single quotes around the "d" and removing the parentheses from behind the Now.  What's the correct syntax?

 

You can only use the DateAdd function where arithmetic expressions are allowed. Of the top of my head, I'd say that is the variable assignment activity and the condition activity.

When You are using the 'is (where-clause)' operator in the rule editors, you have to offer something that the rule editor can translate to SQL.
There is a way to do some date arithmetic using 'CURRENTDATE()' here. This seems to take an integer as parameter for a number of days to add/subtract.
So, for Your situation, You could try CURRENTDATE(-9).

Disclaimer: I have only used this when I created lists in the web configuration, never in a workflow trigger, so test this carefully, please.

It would be great if someone from the DocuWare team could elaborate on this. Are there other function-like expressions besides CURRENTDATE()? Can we do more than add or subtract days?

Cheers.
 

Thanks for the reply, Fabian.  I eagerly tried just using CurrentDate(9) and CurrentDate()+9 and neither of those worked.  So then I tried DateAdd(day, 9, CurrentDate()) and DateAdd("d", 9, CurrentDate()) and DateAdd("day", 9, CurrentDate()) and none of those worked.  I was finding myself a bit demoralized so I had a coffee, took some deep breathes, and did some more research. I discovered that GetDate() may be more appropriate so my spirits rallied and I tried various incarnations of that, including DateAdd(day, 9, GetDate()) and DateAdd("day", 9, GetDate()) but none of those worked.  Further digging uncovered a forum thread from a few years ago that indicated that the IS(WHERE) in AIX jobs doesn't support T-SQL so we may be out of luck but I'd love to have some confirmation from DW Support since that thread was several years old, which means things may changed for DW 7.x

Well I can't seem to make it work so as a workaround I've setup an AutoIndex job to trigger the workflow.  Being as this is an on-premise system I'm able to use a SQL statement in the trigger of an AIX job and found that this worked: CURRENTDATE()= DateAdd(d, -9, [1ST_REVIEW_DATE ])

I have done something similar to this by using the Schedule Trigger to send out email, and 2 additional reminders, by a date field. Then you can choose different date variables that will match for the trigger.  What is confusing is the verbiage of the choices in the date...Within Past, Within Next, After Past, After Next, Before past, before next, where you would indicate a number and a value for (Days,Months).  And this worked for me.

Then to trigger additional actions in the future I put in a counter and did dateadd math to a field to update it for the next triggger.

That's an interesting workaround Craig.  So you're saying that you completely ignored the "Is(where clause)" option in the Schedule Trigger and got the workflow going using the other options but then used the DateAdd math inside the workflow itself to trigger the additional tasks.

I'd love to hear from DocuWare Support regarding what syntax we're allowed to use with the actual "Is(where clause)" workflow trigger - that'd make life a lot easier.

I found a solution: if you use a both "Within the next" AND "After the next" and set them both to the same thing then it behaves the same as DateAdd.  For example, if you set them both to 3 days then it'll trigger the workflow 3 days before whichever index field you've selected.