Reading Excel files is another side of Excel file manipulation. It is equally important as file
creation. Usage could vary from importing simple data to processing user-filled files like
timesheets, purchase orders, etc.
In this recipe, we will use the file created in the previous recipe. We will read customer data
using SysExcel classes and will show it on the screen.
How to do it…
Microsoft Office Excel format is one of the formats that have been supported by Dynamics AX
since its early versions. For example, the Document handling feature allows producing Excel
files using the data from the system. In the new version of Dynamics AX, i.e. Dynamics AX
2009, this feature is extended even more. Now almost every form has a button called Export
to Excel, which quickly allows loading form data into Excel for further analysis using powerful
Excel tools.
Microsoft Office Excel format handling is done with the help of standard Dynamics AX
application classes prefixed with SysExcel. Basically, those classes are Excel COM wrappers
plus they contain additional helper methods to ease the developer’s tasks.
To demonstrate the principle in this recipe, we will create a new Excel file and fill it with a
customer list. This technique could be used to export any business data in a similar way.
How to do it…
The possibility to color individual records is one Dynamics AX feature that can improve user experience. Normally, people like to see a reasonable number of colors in the interface they use. This makes their daily tasks not so boring. One of the common requests is to emphasize the importance of disabled records, for example, terminated employees or stopped customers by displaying their records in a different color, normally red. An opposite example could be to show processed records like posted journals or invoices sales orders, let’s say in green. In this recipe, we will color locked-in-journal accounts in the Chart of accounts form.
How to do it…
In my practice, I was asked a number of times to create various application functions that
process user-selected records in a form grid. Regardless of what function is being created, the
first step was always to determine what records were selected by the user.
In this recipe, we explore several ways of achieving this goal. We will modify the Items form
in the Inventory management module by adding a new button to it, which lists currently
selected records in the overview grid.
How to do it…
Go to the Main Table Form is a feature of Dynamics AX, which allows users to jump to the
main record just by right-clicking on the field and selecting the Go to the Main Table Form
option. It is based on table relations and is available for those controls whose data fields have
foreign key relationships with other tables.
Because of the data structure integrity, this feature works most of the time. However, when it
comes to complex table relations, it does not work correctly or does not work at all. Another
example of when this feature does not work automatically is when the form control is not bound
to a table field. In such situations, Go to the Main Table Form has to be implemented manually.
In this recipe, to demonstrate how it works, we will modify the Business relations form in the
CRM module to make sure that the Employee filter at the top of the form allows users to use
the Go to the Main Table Form feature from the context menu.
How to do it…
Dialogs are a way to present users with a simple input form. They are commonly used for
small user tasks like filling in report values, running batch jobs, presenting only the most
important fields to the user when creating a new record, etc. Dialogs are normally created
from X++ code without storing actual layout in AOT.
The application class Dialog is used to build dialogs. Other application classes like
DialogField, DialogGroup, DialogTabPage, and so on, are used to create dialog
controls. One of the common ways is to use dialogs within RunBase framework classes that
need user input.
The exporting to an XML file recipe has already explained the importance of XML in Dynamics
AX, and how XML documents could be used for exporting data from the system.
In this recipe, we will continue about XML. We will create a piece of code that reads XML files.
As a source file, we will use the previously created accounts.xml file.
How to do it…
Briefly, Extensible Markup Language (XML) defines a set of rules for encoding documents
electronically. It allows creating of all kind of structured documents. In Dynamics AX, XML
files are widely used across the application. For example, user profiles can be exported as
XML files. Business data like financial statements can also be exported as XBRL (eXtensible
Business Reporting Language) files, which are based on XML.
Query objects are used to visually build SQL statements. They are used by Dynamics AX
reports, views, forms, and other objects. Normally queries are stored in AOT, but they can also
be created from code dynamically. This is normally done when visual tools cannot handle
complex and dynamic queries. In this recipe, we will create one dynamically from code.
As an example, we will build a query that selects all active customers who belong to group 10
and have at least one sales order.
How to do it…
1. Open AOT, create a new job called CustTableSales, and enter the following code:
Copying a record
I’ve experienced that one of the tasks often used when manipulating data is record copying.
For various reasons, an existing record needs to be modified and saved as a new one. The
most obvious example could be when a user requires a function that allows him or her to
quickly duplicate records on any of the existing forms.
There are several ways of copying one record into another in X++. In this recipe, we will explain
the usage of table data() method, global buf2buf() function, and their differences. As an
example, we will copy one of the existing customer records into a new one. Normally, copying
a customer involves more data around the customer like customer contacts, bank accounts,
printing settings, and similar, but for demonstration purposes, we will assume that our goal is
only to copy the customer record itself without worrying about related data.
How to do it…
