Tuesday, 12 June 2018

SQL query to extract primary address from CUSTTABLE in AX2012/Dynamics 365 operations

Hi,

Below is the the query we can for the customers primary postal address.


--For all addresses
 select CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME, Address.ADDRESS,Address.COUNTRYREGIONID 
 from CUSTTABLE 
 left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY 
 left outer join LOGISTICSLOCATION ON LOGISTICSLOCATION.RECID = DIRPARTYTABLE.RECID 
 left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = LOGISTICSLOCATION.RECID 
 Order by CUSTTABLE.ACCOUNTNUM 


--For primary address
 select CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME, Address.ADDRESS,Address.COUNTRYREGIONID  
 from CUSTTABLE  
 left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY  
 left outer join LOGISTICSLOCATION ON LOGISTICSLOCATION.RECID = DIRPARTYTABLE.PRIMARYADDRESSLOCATION  
 left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = LOGISTICSLOCATION.RECID  
 Order by CUSTTABLE.ACCOUNTNUM 

No comments:

Post a Comment

Sorting data in a form datasource in Dynamics Ax 2012 / D365F&O X++

In Microsoft Dynamics AX, you can sort a form datasource when the form is loaded. On the form, override the init method. After the call...