Get worker name from position with SQL Queries

select top 1 name from DIRPARTYTABLE
inner join HcmWorker ON HcmWorker.PERSON = DIRPARTYTABLE.RECID
AND HcmWorker.RECID = 
    (select top 1 worker from HCMPOSITIONWORKERASSIGNMENT  
    INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION =  HCMPOSITION.RECID
    AND HCMPOSITION.POSITIONID = 'PositionId'
    where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM 
    AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO);

SQL queries to get customers and vendor addresses and contact details in D365 F&O/ AX X++

All Vendors

SELECT * FROM VENDTABLE WHERE VENDTABLE.DATAAREAID='CEU'

All Addresses - Vendor 

SELECT * FROM DirPartyPostalAddressView JOIN VENDTABLE ON DirPartyPostalAddressView.PARTY =VENDTABLE.PARTY

WHERE VENDTABLE.DATAAREAID=''CEU'

All Addresses with Purpose

SELECT LOGISTICSLOCATIONROLE.*,DirPartyPostalAddressView.*,VENDTABLE.* FROM DirPartyPostalAddressView JOIN VENDTABLE ON DirPartyPostalAddressView.PARTY =VENDTABLE.PARTY

JOIN DIRPARTYLOCATIONROLE ON DIRPARTYLOCATIONROLE.PARTYLOCATION =DirPartyPostalAddressView.RECID

JOIN LOGISTICSLOCATIONROLE ON DIRPARTYLOCATIONROLE.LOCATIONROLE =LOGISTICSLOCATIONROLE.RECID

WHERE VENDTABLE.DATAAREAID='CEU'

(Click Organization administration > Setup > Global address book > Address and contact information purpose.)

All Contact Details - Vendor 

select * from dirPartyContactInfoView JOIN VENDTABLE ON dirPartyContactInfoView.PARTY =VENDTABLE.PARTY

WHERE VENDTABLE.DATAAREAID='CEU'

--All Customers

--SELECT DIRPARTYTABLE.NAMEALIAS ,CUSTTABLE.* FROM CUSTTABLE JOIN DIRPARTYTABLE ON CUSTTABLE.PARTY =DIRPARTYTABLE.RECID

--WHERE CUSTTABLE.DATAAREAID='CEU'

--All Addresses - Customer 

--SELECT DirPartyPostalAddressView.*,CUSTTABLE.PARTY FROM DirPartyPostalAddressView JOIN CUSTTABLE

--ON DirPartyPostalAddressView.PARTY =CUSTTABLE.PARTY

--WHERE CUSTTABLE.DATAAREAID='CEU'

--All Addresses with Purpose - Customer

--SELECT LOGISTICSLOCATIONROLE.NAME,DirPartyPostalAddressView.PARTY,CUSTTABLE.PARTY FROM DirPartyPostalAddressView JOIN CUSTTABLE

--ON DirPartyPostalAddressView.PARTY =CUSTTABLE.PARTY

--JOIN DIRPARTYLOCATIONROLE ON DIRPARTYLOCATIONROLE.PARTYLOCATION =DirPartyPostalAddressView.RECID

--JOIN LOGISTICSLOCATIONROLE ON DIRPARTYLOCATIONROLE.LOCATIONROLE =LOGISTICSLOCATIONROLE.RECID

--WHERE CUSTTABLE.DATAAREAID='CEU'

--All Contact Details - Customer 

--select dirPartyContactInfoView.* from dirPartyContactInfoView JOIN CUSTTABLE ON dirPartyContactInfoView.PARTY =CUSTTABLE.PARTY

--WHERE CUSTTABLE.DATAAREAID='CEU'

--Bank Details Customer

 --SELECT distinct CUSTTABLE.PARTY ,CUSTTABLE.DATAAREAID ENTITY, CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME,Address.ADDRESS ,

 --CUSTTABLE.CURRENCY ,CUSTTABLE.CUSTGROUP ,

 --CUSTTABLE.PAYMTERMID,CUSTTABLE.TAXGROUP VATGROUP ,CUSTTABLE.CASHDISC ,

 --VendBankAccount.ACCOUNTID BankAccount ,VendBankAccount.NAME 'Bank Name' ,VendBankAccount.ACCOUNTNUM 'Bank account number',

 --VendBankAccount.RegistrationNum 'Routing Number',VendBankAccount.SWIFTNo,VendBankAccount.BankIBAN

 --from CUSTTABLE left outer JOIN VendBankAccount ON VendBankAccount.VENDACCOUNT = CUSTTABLE.ACCOUNTNUM --AND VendBankAccount.ACCOUNTID = CUSTTABLE.BANKACCOUNT

 --left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY

 --left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = DIRPARTYTABLE.PRIMARYADDRESSLOCATION

 -- WHERE CUSTTABLE.DATAAREAID IN ('CEU') --and CUSTTABLE.ACCOUNTNUM ='test033'

 --Order by CUSTTABLE.DATAAREAID,CUSTTABLE.ACCOUNTNUM

Below SQL Query to extract a quick customer contact list from Dynamics AX 2012.

REF

REF

SELECT 

  VENDTABLE.ACCOUNTNUM AS CUSTID,

  DIRPARTYTABLE.NAME AS CUSTNAME,

  CASE LOGISTICSELECTRONICADDRESS.TYPE WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' END AS CONTACTTYPE,

  LOGISTICSELECTRONICADDRESS.DESCRIPTION AS CONTACTNAME,

  LOGISTICSELECTRONICADDRESS.LOCATOR AS CONTACTDETAILS

FROM DIRPARTYTABLE AS DIRPARTYTABLE

INNER JOIN VENDTABLE ON DIRPARTYTABLE.RECID = VENDTABLE.PARTY

INNER JOIN DIRPARTYLOCATION ON DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY

INNER JOIN LOGISTICSELECTRONICADDRESS ON DIRPARTYLOCATION.LOCATION = LOGISTICSELECTRONICADDRESS.LOCATION

WHERE VENDTABLE.DATAAREAID='CEU'

ORDER BY DIRPARTYTABLE.NAME

Vendor Bank Details SQL Query in AX

Vendor Bank Address in X++ Code and save it in CSV in AX

Fetch Customers Primary Address or Vendor Primary Address having Transactions (SQL SERVER Query) in AX D365

Fetch Product Master 

SELECT B.DISPLAYPRODUCTNUMBER,DESCRIPTION,NAME,B.SEARCHNAME FROM ECORESPRODUCTTRANSLATION A JOIN ECORESPRODUCT B ON A.PRODUCT =B.RECID WHERE B.DISPLAYPRODUCTNUMBER in ('A0001','A0002')

--Storage Dimensions

select ECORESSTORAGEDIMENSIONGROUPITEM.ITEMID ,ECORESSTORAGEDIMENSIONGROUP.NAME from INVENTTABLE JOIN ECORESSTORAGEDIMENSIONGROUPITEM ON

 INVENTTABLE.ITEMID =ECORESSTORAGEDIMENSIONGROUPITEM.ITEMID AND

 INVENTTABLE.DATAAREAID =ECORESSTORAGEDIMENSIONGROUPITEM.ITEMDATAAREAID

 JOIN ECORESSTORAGEDIMENSIONGROUP ON

 ECORESSTORAGEDIMENSIONGROUPITEM.STORAGEDIMENSIONGROUP =ECORESSTORAGEDIMENSIONGROUP.RECID

 WHERE INVENTTABLE.DATAAREAID='CEU'

--Tracking Dimensions

select ECORESTRACKINGDIMENSIONGROUPITEM.ITEMID ,ECORESTRACKINGDIMENSIONGROUP.NAME from INVENTTABLE JOIN ECORESTRACKINGDIMENSIONGROUPITEM ON

 INVENTTABLE.ITEMID =ECORESTRACKINGDIMENSIONGROUPITEM.ITEMID AND

 INVENTTABLE.DATAAREAID =ECORESTRACKINGDIMENSIONGROUPITEM.ITEMDATAAREAID

 JOIN ECORESTRACKINGDIMENSIONGROUP ON

 ECORESTRACKINGDIMENSIONGROUPITEM.TRACKINGDIMENSIONGROUP =ECORESTRACKINGDIMENSIONGROUP.RECID

 WHERE INVENTTABLE.DATAAREAID='CEU'

 --Item Model Group Units

 select * from INVENTTABLEMODULE where DATAAREAID='CEU'

 --Released Products

 Select * from INVENTTABLE where DATAAREAID='CEU'

 --Item Group

 select * from INVENTITEMGROUPITEM where ItemDATAAREAID='CEU'

Vend Open Trans

SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount 

FROM VENDTRANS

where VENDTRANS.closed = 0

AND ((VendTrans.TransType = 36)

OR (VendTrans.TransType = 3) --Purch

OR (VendTrans.TransType = 14)) --Vend

AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))

--AND VENDTRANS.ACCOUNTNUM = '1001' AND VENDTRANS.DATAAREAID = 'USMF'

SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount ,*

FROM VENDTRANS where

--where VENDTRANS.closed = 0

--AND ((VendTrans.TransType = 36) OR (VendTrans.TransType = 3)

-- OR (VendTrans.TransType = 14)) AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))

 VENDTRANS.DATAAREAID = 'pui'

SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount 

FROM VENDTRANS

where VENDTRANS.closed = 0 and

VENDTRANS.DATAAREAID = 'pui'

AND ((VendTrans.TransType = 36) --RTax25_BadDebtDebitAmortisation

OR (VendTrans.TransType = 3) --Purch

OR (VendTrans.TransType = 14)) --Vend

AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))

--AND VENDTRANS.ACCOUNTNUM = '1001' AND VENDTRANS.DATAAREAID = 'USMF'

Code for insert, update and delete using Chain of Commands in D365 Finance & Operations

1:  [ExtensionOf(tableStr(Table1))]  
2:  final class Table1_Extension  
3:  {  
4:  	public void insert()  
5:  	{  
6:  		this.fieldName = “to insert”; //update field before insert method is executed  
7:  		next insert();  
8:  		this.fieldName = “to insert”; //update field after insert method is executed  
9:  	}  
10:  	public void delete()  
11:  	{  
12:  		//logic before delete method is executed  
13:  		next delete();  
14:  		//logic before after method is executed  
15:  	}  
16:  	public void update(anytype _parameters)  
17:  	{  
18:  		//logic before update method is executed  
19:  		next update(_parameters);  
20:  		//logic after update method is executed  
21:  	}  
22:  }  

Add action to Info/Error message in D365FnO

Starting in version 10.0.10 Platform update 34, you can use the Message::AddAction() method to embed an action within a message. This will help user to navigate through error on the form to update the data.

Example:

class TestJob_MenuAction
{
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        MenuItemMessageAction actionData = new MenuItemMessageAction();
        actionData.MenuItemName(menuItemDisplayStr(Inventsite));
        str jsonData = FormJsonSerializer::serializeClass(actionData);
        Message::AddAction(MessageSeverity::Error, "Site information required.", 'Site Master.', 
                           MessageActionType::DisplayMenuItem, jsonData);
    }
}

Output:

On click of Site Master, InventSite form will be opened.

Find Company details in Dynamics AX

Company Address:

CompanyInfo companyInfo = CompanyInfo::find();
info(strFmt("%1",companyInfo.postalAddress().Address));

Currency details:

CompanyInfo::standardCurrency() 

	OR

Ledger::accountingCurrency(CompanyInfo::current());

Error:"The Visual Studio component cache is out of date. Please restart Visual Studio."

Solution :

1. Open cmd by pressing Windows + R.
2. Run %temp%.
3. Select and delete SSMS folder from Temp folder.

D365 finance & operations : SSRS reports deployment using windows powershell

To deploy reports using windows powershell, run Windows PowerShell as admin and use following commands :

To deploy all reports:

K:\AosService\PackagesLocalDirectory\Plugins\AxReportVmRoleStartupTask\DeployAllReportsToSSRS.ps1 -PackageInstallLocation “K:\AosService\PackagesLocalDirectory”

To deploy a specific report:

K:\AosService\PackagesLocalDirectory\Plugins\AxReportVmRoleStartupTask\DeployAllReportsToSSRS.ps1 -Module _SpecifyModuleName_ -ReportName _SpecifyReportName_ -PackageInstallLocation "K:\AosService\PackagesLocalDirectory"

Table event handler in D365

Dataeventhandlers in table :

1. Create new class with final keyword, suffixed _Extension and add following method.
2. Decorate with ExtensionOf() attribute.

Example :

[ExtensionOf(tableStr(TableName)]

final class TableNameEventHandler

{

}

OnInserted eventhandler

[DataEventHandler(tableStr(TableName), DataEventType::Inserted)]

public static void TableName_onInserted(Common sender, DataEventArgs e)

{

    ValidateEventArgs   event       = e as ValidateEventArgs;

    TableName          localbuffer    =sender as TableName;       //get current record from sender

    //code here

}

Pre and Post eventhandlers in table :

Create any normal class and add following method.

Initvalue pre-eventhandler

// pre-event handler for initvalue

[PreHandlerFor(tableStr(TableName), tableMethodStr(TableName, initValue))]

public static void TableName_Pre_initValue(XppPrePostArgs args)

{

    TableName localbuffer = args.getThis() as TableName;    //get current record from args

    //code here

}

Initvalue post-eventhandler

//post-eventhandler for initvalue

[PostHandlerFor(tableStr(TableName), tableMethodStr(TableName, initValue))]

public static void TableName_Post_initValue(XppPrePostArgs args)

{

    TableName localbuffer = args.getThis() as TableName;      //get current record from args

    //code here

}

Error message in AX/D365 : Remove prefix in error message

In AX, prefix mechanism is used for precise error messaging about the transactions that an application performs. If required for your customization you do not wish to show the prefix text in error message we can achieve that using following :

This code will show error with prefix text :

throw error("Customer not found");

Output will be : 

someprefixedtext + Customer not found

This code will suppress prefix text :

throw infolog.add(Exception::Error, "Customer not found");

Output will be : 

Customer not found

All this happens in classmethod : Global\error.
infolog is global variable.

Refer https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev-ref/xpp-exceptions to create custom error messages.

Find TableId using table browser in D365FO

Type url : https://<D365FOurl>/?mi=SysTableBrowser&cmp=USMF&prt=initial&limitednav=true&TableName=SysTableIdView

Find TableId using SQL for D365FO

Query : select * from SYSTABLEIDVIEW where id = 'table_id'