Hello Everyone,
In this blog i gonna show you how to create a simple view in Dynamics AX that consists of ItemID, Name, Sales ID, Customer Group and Customer Name using the following steps:
1. Right-click on the Views node and select New View. A new view will be created. You can open its properties by right-clicking and selecting Properties.
2. Change the name of the view to InventSalesCust and give it a label that describes the contents of the view.
3. The views can actually use queries that have already been created as a base for the data selection. This is done in the Properties of the view by choosing the query from the Query property. However, in our example, we will create the view from scratch.
4. Under the Metadata node, right-click on the Data Sources node and select New Data Source
5. Select the newly created data source and enter InventTable in the table property. The name of the data source will automatically change to InventTable_1, which is normal.
6. Under the InventTable data source, find the Data Sources node, right-click on it, and select New Data Source. This time, we want to use the SalesLine table so change the table property to SalesLine. Also change the relations property to Yes in order to get the link between the two tables active in the view.
7. Do the same to add the CustTable as a child data source to the SalesLine.
8. The next step is to define the fields that should be made available when this query is executed. Simply drag fields you need to use in the view from the InventTable_1,SalesLine_1, and CustTable_1 data source and drop them onto the Fields node.
9. You should now have a view that looks like the following screenshot:
hi
ReplyDeletemy sql query :
Select custtable.accountnum, ADDRESS.*
From CUSTTABLE
Inner Join DIRPARTYTABLE on CUSTTABLE.PARTYID = DIRPARTYTABLE.PARTYID
Inner Join DIRPARTYADDRESSRELATIONSHIP on DIRPARTYTABLE.PARTYID = DIRPARTYADDRESSRELATIONSHIP.PARTYID
Inner Join DIRPARTYADDRESSRELATIONSHI1066 on DIRPARTYADDRESSRELATIONSHIP.RECID = DIRPARTYADDRESSRELATIONSHI1066.PARTYADDRESSRELATIONSHIPRECID
Inner Join [ADDRESS] on DIRPARTYADDRESSRELATIONSHI1066.REFCOMPANYID = ADDRESS.DATAAREAID
and DIRPARTYADDRESSRELATIONSHI1066.ADDRESSRECID = ADDRESS.RECID
--Where CUSTTABLE.ACCOUNTNUM = 'MST_00006' and CUSTTABLE.DATAAREAID = 'LOJ'
Where DIRPARTYADDRESSRELATIONSHIP.ISPRIMARY=1 AND ACCOUNTNUM='MST_18909'
which one first datasource in the metadata?
thanks.