Microsoft Dynamics AX (Archived)

Created and modified date time fields

(0) Share

Report

Posted on by AdnDalhi

336

Hi All,

I have a quick technical question about 2 standard fields in AX ( Created date time & Modified date Time ),

I'm working now on a SQL Query, and i'm using " LOGISTICSELECTRONICADDRESS " table, but i didn't find these 2 fields,

PS: I'm obliged to use SQL, and i need to know the date of creation and modification of the customer contact, im wondering if there is any alternative to do that, or other fields to trieve Creation / Modification date.

Here is my SQL Query:

SELECT

CUSTTABLE.ACCOUNTNUM                               AS [Customer account],
LOGISTICSELECTRONICADDRESS.DESCRIPTION             AS [Description],
LOGISTICSELECTRONICADDRESS.TYPE                    AS [Type],
LOGISTICSELECTRONICADDRESS.LOCATOR                 AS [Contact Number/Address],
LOGISTICSELECTRONICADDRESS.ISPRIMARY               AS [Primary],
CUSTTABLE.DATAAREAID                               AS [Data Area Id]

FROM CUSTTABLE, DIRPARTYLOCATION, LOGISTICSELECTRONICADDRESS

WHERE CUSTTABLE.PARTY = DIRPARTYLOCATION.PARTY
AND LOGISTICSELECTRONICADDRESS.LOCATION = DIRPARTYLOCATION.LOCATION

ORDER BY CUSTTABLE.ACCOUNTNUM, CUSTTABLE.CREATEDDATETIME

*This post is locked for comments

I have the same question (0)

All responses (14)

Answers (1)

Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

If you don't see those fields, you need to enable CreatedDateTime and ModifiedDateTime properties for the table in AX.

Was this reply helpful? Yes No
AdnDalhi 336 on at

Like (0)

Report

Yes, if i activate these properties, i will be able to see the "CreatedDateTime" & "ModifiedDateTime" fields in SQL ?

I have a production environement, is that sure step ?

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

You need to deploy such changes to prod in a controlled manner. Perhaps you already have a process for that, and some technical experts who perform the actual deployment.

You should not do any ad hoc changes directly in prod AOT.

If you are in doubt, please search the web for white paper about Deploying customizations across AX2012 environments.

Was this reply helpful? Yes No
Suggested answer

Satish Panwar 14,671 Moderator on at

Like (1)

Report

It is sure thing (if table will allow you to add, then it will become visible @ SQL) and that's why you want to do this in non-production environment first (not sure if you intend to do this in prod directly).

Was this reply helpful? Yes No
AdnDalhi 336 on at

Like (0)

Report

I've just changed the 2 table properties in AX Test Environement, but i cannot see the fields yet @SQL

Was this reply helpful? Yes No
Suggested answer

Satish Panwar 14,671 Moderator on at

Like (0)

Report

did you complete sync?

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

You need to synchronize the database. Please talk to developers in your company, you need to follow the dev process that is defined for your project / company. Otherwise your changes might be wiped out with next code deployment.

Was this reply helpful? Yes No
Suggested answer

Mohammed Khatib 430 on at

Like (1)

Report

Hi Adax,

It is much better to join your query to LogisticsLocation table which already has these fields.

Note: Every Electronic address will have one Location record (1:1) relation

Was this reply helpful? Yes No
AdnDalhi 336 on at

Like (0)

Report

Thanks Mohammed for you reply,

But my records is in LogisticsEletronicAddress table,

Do you mean, i need to add a join between LogisticsElctronicAddress table and LogisticsLocation?

If yes, in this case i will get the CreatedDateTime & ModifiedDateTime for the records in LogisticsLocation table and not EletronicAddress,

Can you provide an example please by modifiyin my Query

.

Thanks in advance.

Was this reply helpful? Yes No
Verified answer

Mohammed Khatib 430 on at

Like (1)

Report

SELECT

CUSTTABLE.ACCOUNTNUM AS [Customer account],

LOGISTICSELECTRONICADDRESS.DESCRIPTION AS [Description],

LOGISTICSELECTRONICADDRESS.TYPE AS [Type],

LOGISTICSELECTRONICADDRESS.LOCATOR AS [Contact Number/Address],

LOGISTICSELECTRONICADDRESS.ISPRIMARY AS [Primary],

CUSTTABLE.DATAAREAID AS [Data Area Id],

LogisticsLocation.CREATEDDATETIME AS [Created Date Time],

LogisticsLocation.MODIFIEDDATETIME AS [Modified Date Time]

FROM CUSTTABLE

inner join DIRPARTYLOCATION

on DIRPARTYLOCATION.PARTY = CustTable.PARTY

inner join LOGISTICSLOCATION

on LogisticsLocation.RECID = DIRPARTYLOCATION.LOCATION

and LogisticsLocation.ISPOSTALADDRESS = 0

inner join LOGISTICSELECTRONICADDRESS

on LOGISTICSELECTRONICADDRESS.LOCATION = LogisticsLocation.RECID

ORDER BY CUSTTABLE.ACCOUNTNUM, CUSTTABLE.CREATEDDATETIME

Was this reply helpful? Yes No