AX 2012 Development Training

1. AX 2012 Development Training Presented by Vishy Grandhi

2. Training Outline • Lesson 1: Retrieving Data • Lesson 2: Data manipulation • Lesson 3: Queries

3. Retrieving Data : Overview • Table buffers • Select statement • Field List • While Select • Ranges • Sorting • Group By • Joins

4. Retrieving Data : Table buffers • A table buffer stores complete records in a variable • The table name is specified in the declaration • Eg: CustTablecustTable; //declares a customer record table buffer • A table buffer only allows access to one row of data at a time

5. Retrieving Data : Select statement syntax • select <Find Options> • <TableBuffer or {FieldListfrom [TableBuffer]}> • <Aggregate (field identifier)> • <Sorting Options field identifier[Direction]> • <Index clause (index)> • where <selection criteria> • <Join Clausejoin>

6. Retrieving Data : Select statement options

7. Retrieving Data : Joins • Use joins to link tables so they can return values together • Inner Join is default

8. Retrieving Data : Cross-company • In AX there are multiple companies in one database and we can use cross-company joins to view data from all companies • Eg. • containerconCompanies = [ ’usmf’,‘frrt‘, ‘dat’ ];custTablecustTable;while select crossCompany : conCompaniescustTable{    print custTable.accountNum;} pause;

9. Manipulate Data : Overview • Insert • Insert_Recordset • Update • Update_Recordset • Delete • Delete_from • tts

10. Manipulate Data : Examples • custTablecustTable;custTable.accountNum = "1234";custTable.Currency = "USD";custTable.insert(); • VendTablevendTable;HcmWorkerhcmWorker;Insert_RecordSetVendTable (accountnum, Party)selectPersonnelNumber, person from HcmWorker; • SalesTablesalesTable;ttsbegin;while select forupdatesalesTable    where salesTable.CustAccount =="2001"{salesTable.SalesName ="New Enterprises";salesTable.update();}ttscommit;

11. Manipulate Data : Examples • SalesTablesalesTable;update_recordsetsalesTablesettingsalesName ="New Enterprises"wheresalesTable.custAccount =="2001"; • CustTablecustTable;ttsbegin;SelectforUpdatecustTable    where custTable.accountnum =="2032";custTable.delete();ttscommit; • CustTablecustTable;delete_fromcustTable    where custTable.Currency == "ABC";

12. Manipulate Data : Examples staticvoidMPJoins(Args _args) { SalesTable a; SalesLine b; PurchTablepurchTable; PurchLinepurchLine; ; whileselectSalesIdfrom a joinLineNum, LineAmountfrom b orderby a.SalesId, b.LineNum where a.SalesId == b.SalesId { printa.SalesId, ", ", b.LineNum, ", ", b.LineAmount; } pause; }

13. Queries : Overview • Executing a Query • Build a Query • Query objects • Query • QueryRun • QueryBuildDataSource • QueryBuildRange • Examples

14. Queries : Overview • Query object lets you create a new query or access an existing query. Also allows you to add new data sources. • QueryRun object lets you execute the query • QueryBuildDataSource allows you to access any data sources that have been setup on the Query. You can also specify sort on this object • QueryBuildRange allows you retrieve/create new filters on the data source

15. Summary of Training • In this session we looked at working with data in AX • We looked at how to read, write and delete data • We also looked at how to build Queries