Axapta 2012 - Exact Views and GETDATE ()
I am trying to make a date range in a view work relative to today. But I won't find any function to set on the query range to make it dynamic.
Example:
Creates:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MYCUSTINVOICETABLEVIEW] AS
SELECT T1.INVOICEID AS INVOICEID,T1.DATAAREAID AS DATAAREAID,T1.PARTITION AS PARTITION,T1.RECID AS RECID
FROM CUSTINVOICETABLE T1
WHERE (INVOICEDATE<={ts '2017-07-18 00:00:00.000'})
GO
But instead of looking relatively last synchronization date
, I want to look versus today
.
SELECT ... FROM CUSTINVOICETABLE T1
WHERE (INVOICEDATE<=GETDATE())
Any ideas how I can customize query ranges for this?
Tried so far:
- .. GetDate ()
- .. Today()
- .. CurrentDate ()
- lessThanDate (0)
- "<GETDATE ()"
- <currentSessionDateTime ()
According to How to update / sync a view based on a dynamic range query? , this is not possible (for UserID ()). I want to double check if this matches one case for dates. The view is consumed by another application in the same field (not in the form of an ax).
source to share
It's ugly and I still think there might be a better way, but here is the solution I just tested. There are obviously much better ways in x ++, forms and reports, but the result below can be requested externally from AX and will still return dynamic results.
You have a structure Query> View> Query> View, with the top level view being your consumed view.
The first view contains the data you want and the computed date columns filled with the following code to get the dynamic date:
public server static str today()
{
return 'CONVERT (date, GETDATE())';
}
Then you query that view and put an extended query range on it to check your field and dynamic column GETDATE (). Then you have a view built on top of this request that your external application can consume.
In the example below, all field lists are set to dynamic yes. Obviously, you always carry the relevant data from the root request.
The example in the figure leads to the following view definitions:
CREATE VIEW "DBO".TESTVIEW AS SELECT T1.SALESID AS SALESID,
T1.RECEIPTDATEREQUESTED AS RECEIPTDATEREQUESTED,
T1.DATAAREAID AS DATAAREAID,T1.PARTITION AS PARTITION,T1.RECID AS RECID,
(CAST ((CONVERT (date, GETDATE())) AS DATETIME)) AS CURRENTDATE
FROM SALESTABLE T1
CREATE VIEW "DBO".TESTCONSUMABLEVIEW AS
SELECT T1.CURRENTDATE AS CURRENTDATE,T1.RECEIPTDATEREQUESTED AS RECEIPTDATEREQUESTED,
T1.SALESID AS SALESID,T1.DATAAREAID AS DATAAREAID,T1.PARTITION AS PARTITION,
T1.RECID AS RECID
FROM TESTVIEW T1 WHERE (RECEIPTDATEREQUESTED<CURRENTDATE)
source to share
My answer is based on @ Spencer Kershaw's answer. So credit for his work, but I want to provide a more concise answer that does not require a request and is hopefully clearer for others trying to complete the task.
To accomplish what you want, you need to do 3 things.
- Create a static method to use for a computed column
- Add the derived column to your view
- Enter the range correctly using a random field
1. Add this method to your view:
public server static str today()
{
return 'CONVERT (date, GETDATE())';
}
2. Right click on the node fields and select New>Date Computed Column
. About the properties of the set ViewMethod = today
.
3. Add a range. Select dataAreaId
for the field (this is optional). Set the property to a Value
value ("YourComparisonField" < "today")
. The syntax can be important here.
This gives:
CREATE VIEW [dbo].[AAATESTVIEW]
AS
SELECT T1.ADDRESS AS ADDRESS
,T1.MODIFIEDDATETIME AS MODIFIEDDATETIME1
,T1.RECID AS RECID1
,T1.PARTITION AS PARTITION
,T1.RECID AS RECID
,(CAST((CONVERT(DATE, GETDATE())) AS DATETIME)) AS TODAY
FROM LOGISTICSPOSTALADDRESS T1
WHERE (N'modifiedDateTime1' < N'today')
GO
source to share