投稿済み Mon, 23 Apr 2018 17:04:54 GMT 、投稿者 David Dunton Software Solutions Engineer

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.

投稿済み Mon, 23 Apr 2018 17:14:00 GMT 、投稿者 Phil Robson DocuWare Corporation Senior Director Professional Services, Americas

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

 

投稿済み Mon, 23 Apr 2018 17:26:52 GMT 、投稿者 David Dunton Software Solutions Engineer

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

投稿済み Mon, 23 Apr 2018 17:59:29 GMT 、投稿者 Phil Robson DocuWare Corporation Senior Director Professional Services, Americas

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

 

投稿済み Mon, 23 Apr 2018 18:15:41 GMT 、投稿者 David Dunton Software Solutions Engineer

Thanks Phil, i'll keep trying.

投稿済み Mon, 23 Apr 2018 18:30:00 GMT 、投稿者 Josef Zayats

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.

投稿済み Mon, 23 Apr 2018 18:34:11 GMT 、投稿者 Phil Robson DocuWare Corporation Senior Director Professional Services, Americas

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

 

 

投稿済み Tue, 24 Apr 2018 18:23:19 GMT 、投稿者 David Dunton Software Solutions Engineer

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.

 

投稿済み Tue, 24 Apr 2018 23:54:04 GMT 、投稿者 Josef Zayats

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

投稿済み Wed, 25 Apr 2018 14:23:12 GMT 、投稿者 David Dunton Software Solutions Engineer

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'

フォーラムに投稿するためにはログインが必要です。