Latest Modified Date from a View

(0) Share

Report

Posted on by Community Member

Hi,

I am just a rookie in the field of AX, so this question might be just plain stupid or wrong in terms of "technical word usage".

Problem Statement: How I can get the Latest Modified Date, if there are any updates on a View. I suppose there are multiple tables in back end which have modified date but which one to pick for a particular record?

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Community Member on at

Like (0)

Report

RE: Latest Modified Date from a View

you need to track the records by tables only. As André Arnaud de Calavon explained it earlier View is just a SQL statement on one or more tables.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
RE: Latest Modified Date from a View

Situation is represented in below snippet.

SELECT A.Columns, B.Columns, C.Columns, D.Columns, FROM TableA A JOIN TableB B ON A.ID=B.ID JOIN TableC C ON B.ID2=C.ID2 JOIN TableD D ON C.ID3=D.ID3

So as you see a record is coming after joining 4 tables (Based on business requirement or needs). This code act as a view.

Underlying tables are AX Base tables or AX views. When any user update anything on AX Front End; modified date is captured in respective table.

So, now in this case I need the Latest Modified Date for each record retrieved by above joins.

PS: Currently I have used "CASE Statement" to tackle this issue.

Was this reply helpful? Yes No
Martin Dráb 235,928 Most Valuable Professional on at

Like (1)

Report

RE: Latest Modified Date from a View

The fact that you're doing a migration doesn't explain why you want ModifiedDateTime on views.

What's the purpose of ModifiedDateTime in your views? Maybe you should use change tracking or something... And why do you have 1500 custom views in the first place? Explaining the business scenario would allow us to suggest your some options.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Latest Modified Date from a View

Business requirement is to migrate from legacy to AX. I am in middle of this process right now. The views which were used by legacy system are now to be used for AX(after correct metadata mapping). Problem was when I use say ModifiedDateTime from one table (say multiple tables joined in the view) I am not sure whether chosen ModifiedDateTime is latest or not.

Currently I have used a Case statement to handle this situation but to do that in all the views is cumbersome and not intelligent.

I was hoping if I could find another way to deal with it and thus it leads me to this forum.

Was this reply helpful? Yes No
André Arnaud de Cal... 297,389 Super User 2025 Season 2 on at

Like (0)

Report

RE: Latest Modified Date from a View

You had asked a question from which I can imagine, sometimes you want to have a latest modified date, but are you now telling you want to change all views? What is the business requirement here to change 1500 views?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Latest Modified Date from a View

Exactly, So now which Modified Date Time column should I choose in view definition when there are, say 5 tables used in Join, each having its own Modified Date Time column. I want the latest. Currently I am using a CASE statement but I can't do this for all the views as there are more than 1500 Views in question.

Was this reply helpful? Yes No
André Arnaud de Cal... 297,389 Super User 2025 Season 2 on at

Like (0)

Report

RE: Latest Modified Date from a View

A view is nothing else than a SQL statement on one or more tables. So there is no tracking for updates on this. Only tracking on real tables are possible.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Latest Modified Date from a View

I understand your reply. But how actually AX keeps track of updates on a view. There must be at least one table object in the back end which is keeping track of it. Am I right?

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 297,389 Super User 2025 Season 2 on at

Like (0)

Report

RE: Latest Modified Date from a View

Hi Himanshu,

It would be possible to create a computed column on a view which would find the maximum date out of multiple data sources.

Was this reply helpful? Yes No