Dynamics 365 FO - Accessing the database - Sample Code

Nghia Song - Microsoft Dynamics 365 Technical Consultant

Nghia Song

Tel - WhatsApp: +84967324794

Email: songnghia.uit@gmail.com

Exercise

Create a form with datasources is temp table

Create a form to display number of sales order for each customer group by warehouse id and sales order status

Step	Screenshots	Description
0		Start Visual Studio using Run as administrator.
1		Create new label id
2		Create EDT: NVWCountSalesOrder
3		Create table NVWCustomerSalesStatusTmp Modify Table Type: InMemory
4
5
6
7
8	public display CustName CustName() { return CustTable::find(this.CustAccount).name(); }	Create Display field Customer Name
9		Create form with data source
10		Create 2 group control
11		Add new string control and modify Auto Declaration to Yes, modify label
12		Add new button control and modify Auto Declaration to Yes, modify label
13		Create new grid and modity datasource
14		Create new string control to display customer name Modify label and data method
15	public NVWCustomerSalesOrderStatusTemp getData(CustAccount custAccount) { NVWCustomerSalesOrderStatusTemp tmpTable; SalesTable salesTable; Query query; QueryRun queryRun; QueryBuildDataSource qbds; QueryBuildRange qbr; ; tmpTable.delete(); query = new Query(); query.addDataSource(tableNum(SalesTable)); qbds = query.dataSourceTable(tableNum(SalesTable)); //Add Count Record qbds.addSelectionField(fieldNum(SalesTable,RecId),SelectionField::Count); //Add group by field qbds.addSortField(fieldNum(SalesTable,CustAccount)); qbds.addSortField(fieldNum(SalesTable,InventLocationId)); qbds.addSortField(fieldNum(SalesTable,SalesStatus)); qbds.orderMode(OrderMode::GroupBy); //Apply filter parameter if(custAccount != "") { qbr = qbds.addRange(fieldNum(SalesTable,CustAccount)); qbr.value(custAccount); } //Run query queryRun = new QueryRun(query); while(queryRun.next()) { salesTable = queryRun.get(tableNum(SalesTable)); tmpTable.clear(); tmpTable.CustAccount = salesTable.CustAccount; tmpTable.InventLocationId = salesTable.InventLocationId; tmpTable.SalesStatus = salesTable.SalesStatus; tmpTable.CountSalesOrder = int642int(salesTable.RecId); tmpTable.insert(); } return tmpTable; }	Add new method getData in code behind
16	[Control("String")] class FormStringControlCustAccount { /// <summary> /// /// </summary> public void lookup() { Query query = new Query(); QueryBuildDataSource qbds; QueryBuildRange qbr; SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(CustTable),this); sysTableLookup.addLookupfield(fieldNum(CustTable,AccountNum)); sysTableLookup.addLookupfield(fieldNum(CustTable,CustGroup)); qbds = query.addDataSource(tableNum(CustTable)); qbr = qbds.addRange(fieldNum(CustTable,CustGroup)); //qbr.value("BANSI"); sysTableLookup.parmQuery(query); sysTableLookup.performFormLookup(); } }	Add new method lookup
17	[Control("Button")] class FormButtonControlGetData { /// <summary> /// /// </summary> public void clicked() { CustAccount custAccount = FormStringControlCustAccount.valueStr(); //Get filter from parameter ; NVWCustomerSalesOrderStatusTemp.setTmpData(element.getData(custAccount)); //Call function GetData with filter parameter NVWCustomerSalesOrderStatusTemp_ds.executeQuery(); } }	Add new method for button click
18		Craete display menu item
19		Extend AccountReceivable menus Add new SubMenu and drag display menu item
20		View in modules
21		Final