SBX - Search With Button

SBX - Forum Post Title

Computed Column Dates

Microsoft Dynamics AX Forum

lukbel asked a question on 12 Oct 2018 5:53 AM

Question Status

Verified
Hi all
Running AX 2012 R3 CU9.

Having a bit of a problem with a computed column in a view. I'm trying to limit the data to purely data after X days ago (in this case 90 days). The obvious way I thought to do it, it doesn't seem to filter anything at all:
return strFmt("SELECT COUNT(%1) FROM %2 WHERE %3 = %4 AND %5 > %6 AND DATAAREAID = 'ABC'",
        fieldStr(ABC_Usage_RawData, Qty),
        tableStr(ABC_Usage_RawData),
        fieldStr(ABC_Usage_RawData, ItemId),
        SysComputedColumn::returnField(tableStr(ABC_Usage_FinalFigures), tableStr(InventTable), fieldStr(InventTable, ItemId)),
        fieldStr(ABC_Usage_RawData, DatePhysical),
        systemDateGet()-365
        );
In this example, Usage_FinalFigures is my view, and Usage_RawData is a view which has all the transactions I need. But it won't filter by date at all, it just gives the same answer regardless.
Am I doing something wrong?
Thanks very much in advance for your help
Cheers
Luke
Reply
Sohaib Cheema responded on 12 Oct 2018 6:41 AM
My Badges
Verified Answer

Hi Luke,

systemDateGet seems like AX(x++) function and SQL might not identify that.

Can you please try to replace that with SQL function GETDATE()

GETDATE()-365

Reply
Martin Dráb responded on 12 Oct 2018 6:41 AM
Suggested Answer

The code is conceptually flawed. Your code will execute when the view is synchronized to database, therefore you will get a hard-coded date that won't change until you synchronize the database again. You seem to want a filter based on the date when users are looking at the view, which your code can't achieve.

Don't do it in view at all. Simply use a query, where you can set the query range with systemDateGet() in code, or you can use a static value >(day(-365)).

When you're developing views, a very useful approach is looking at the generated T-SQL code. You would easily see that you didn't generate what you intended.

Reply
lukbel responded on 16 Oct 2018 3:51 AM

Worked perfectly :)

Cheers Sohaib - knew there'd be a simpler way of doing it

Reply
Sohaib Cheema responded on 12 Oct 2018 6:41 AM
My Badges
Verified Answer

Hi Luke,

systemDateGet seems like AX(x++) function and SQL might not identify that.

Can you please try to replace that with SQL function GETDATE()

GETDATE()-365

Reply
Martin Dráb responded on 12 Oct 2018 6:41 AM
Suggested Answer

The code is conceptually flawed. Your code will execute when the view is synchronized to database, therefore you will get a hard-coded date that won't change until you synchronize the database again. You seem to want a filter based on the date when users are looking at the view, which your code can't achieve.

Don't do it in view at all. Simply use a query, where you can set the query range with systemDateGet() in code, or you can use a static value >(day(-365)).

When you're developing views, a very useful approach is looking at the generated T-SQL code. You would easily see that you didn't generate what you intended.

Reply

SBX - Two Col Forum

SBX - Migrated JS