IS (WHERE) Clause - Previous Month NOT last 30 Days-1524510294

Looking to "Configure a List" where date=previous calendar month. IE, if the current month is June, then display May results.

Is this possible? The "Within the past" is a number of days if i choose month, thus showing the wrong results.

I've played with the "Is (Where Clause)", without any luck.

This is for a cloud system, so SQL Statements are out of reach.

David,
I have not tested this but MSSQL supports DATEADD. So look to do something like this:

All before this month:

DATE <= DATEADD(month,-1,GETDATE())

Only last month:

(DATE >= DATEADD(month,-2,GETDATE()) AND DATE <= DATEADD(month,-1,GETDATE()))

Phil Robson
Senior Director Support Americas

Thank you for the quick reply.

I tried both of those, recieved a "Wrong condition!" error.

https://www.docuware.com/sites/default/files/forums-images/2018-04-23%2013_25_38-DocuWare.png

https://www.docuware.com/sites/default/files/forums-images/2018-04-23%2013_26_06-Configurations.png

David,
It would appear the the IS(WHERE...) clause does not support trancact SQL. I was able to execute a query as I indicated, but I used the SQL Query option, which is not supported for the Cloud.

Your clause is not quite right. Remove the word DATE at the start. Moot point though because it still won't work.

Sorry. But I don't have a solution right now. I'll give it some more thought.

Phil Robson
Senior Director Support Americas

Thanks Phil, i'll keep trying.

David,

I think your best bet to use t-sql in Docuware Cloud is AutoIndex workflow - these support SQL syntax with your FC table fields (not system fields though). So you can run an AI workflow that sets an additional field to a value that you could use in your Tasklist selection. Works for me.

David,
Good idea from Josef. If you do, replace GETDATE() with CURRENTDATE() that is what I used with my test.

Phil Robson
Senior Director Support Americas

https://www.docuware.com/forum/english-forums/docuware-help-technical-pr...

Looks like Free SQL Statements aren't available in Cloud, unless i'm missing something.

Thanks.

David,

Look at the Cloud AutoindexJob configuration - external database filter allows for SQL statement. - see the screenshot. The sql statement can reference your file cabinet tables and use any valid sql syntax and functions including DATEADD. I could not find a way to use system dields like DWSTOREDATETIME here, but this can be overcome by using custom fields that automatically aquire system fileds values.

https://www.docuware.com/sites/default/files/forums-images/2018-04-24_2055.png

Thats what I needed! Thanks to both of you.

I have one autoindex to set the value and another to remove it.

Tested and working.

SELECT
      DWDOCID
FROM BOOKINGDEALS
WHERE DATEPART(m, ContractDate) = DATEPART(m, CAST(GETDATE()AS Date)) -1
      AND DATEPART(yyyy, ContractDate) = DATEPART(yyyy, (DATEADD(m, -1, GETDATE())))

;

SELECT
      DWDOCID
FROM BOOKINGDEALS
WHERE DATEPART(m, ContractDate) = DATEPART(m, CAST(GETDATE()AS Date)) -2
      AND DATEPART(yyyy, ContractDate) = DATEPART(yyyy, (DATEADD(m, -2, GETDATE())))
      AND Status = 'PREVIOUSMONTH'