Microsoft Dynamics AX 2012 Development Cookbook

Carry out the following steps in order to complete this recipe:

We start the recipe by adding a number sequence initialization code into the NumberSeqModuleCustomer class. As we can understand from its name, it holds the initialization of all number sequences that belong to the Accounts receivable module.

The code in the loadModule() method defines the default number sequence settings to be used in the wizard, such as data type, description, highest possible number, and so on. Additional options, such as starting sequence number, number format, and others could also be added here. All mentioned options could be changed while running the wizard. The addParameterType() method is used to define number sequence scope. In the example we created a separate sequence for each Dynamics AX company.

Before we start the wizard, we need to initialize number sequence references. This is normally done as a part of the Dynamics AX initialization checklist, but in this example we have to execute it manually by calling the loadAll() method of the NumberSeqApplicationModule class.

Next, we will run the wizard. We will skip the welcome page and in the second step of the wizard, the Details button can be used to display more options. The options can also be changed later in the Number sequences form before or even after the number sequence is actually used. The last page shows an overview of what will be created. Once completed, the wizard creates new records in the Number sequences form for each company.

The newly created number sequence reference appears in the Segment configuration form. Here we can see that the Data area checkbox is checked, meaning that we will have separate number lists for each company. The number sequence setup can normally be located in the module parameter forms.

See Chapter 3, Working with Data in Forms:

Carry out the following steps in order to complete this recipe:

In this recipe, first we will select the desired customer account that is, 1103. Here we can easily modify the select statement to include more accounts for renaming, but for demonstration purposes, let's keep it simple. Note that only fields belonging to a table's primary key can be renamed in this way.

Then we call the table's renamePrimaryKey() method, which does the actual renaming. The method finds all the related records for the selected customer account and updates them with the new account. The operation might take a while depending on the volume of data, as the system has to update multiple records located in multiple tables.

Carry out the following steps in order to complete this recipe:

First, we retrieve both records from the database and prepare them for updating.

The key method in this recipe is the merge()method. It will ensure that all data from one record will be copied into the second one and all related transactions will be updated to reflect the change.

Finally, we save changes on the destination record and delete the first one.

All code has to be within the ttsBegin/ttsCommit pair as we perform several database update operations in one go.

Such a technique could be used to merge two, or even more, records of any type.

Carry out the following steps in order to complete this recipe:

All the document handling notes are stored in the DocuRef table, where the three fields RefCompanyId, RefTableId, and RefRecId are used to identify the parent record. In our recipe, we will set those fields to the vendor company ID, vendor table ID, and vendor account record ID, respectively.

Next, we will set note type, name, and description, and insert the document handling record. In this way, we will add a note to the record. The code in this recipe could also be added to a separate method for further reuse.

Carry out the following steps in order to complete this recipe:

The key method in this recipe is in the setTmp() method. It is available on all tables, and it declares the current table instance to behave as a temporary table in the current scope. So in this recipe, we will first call the setTmp() method on the vendTable table to make it temporary in the scope of this method. That means any data manipulations will be lost once the execution of this method is over and actual table content will not be affected.

Next, we will insert a couple of test records. Here, we use the doInsert() method to bypass any additional logic, which normally resides in the table's insert() method.

The last thing to do is to check for newly created records by listing the vendTable table. We can see that although the table contains many actual records, only the ones which we inserted were displayed in the Infolog. Additionally, the two we inserted do not appear in the actual table records.

Carry out the following steps in order to complete this recipe:

In this recipe, we have two variables—mainAccount1 for original record and mainAccount2 for the new one. First, we will need to find the original record by calling findByMainAccountId() on the MainAccount table.

Next, we will copy it to the new one. Here, we will use the data() table member method, which copies all data fields from one variable to another.

After that, we will set a new ledger account number, which is a part of a unique table index and must be different.

Finally, we call the insert() method on the table, if validateWrite() is successful. In this way, we have created a new ledger account record, which is exactly the same as the existing one apart from the account number.

As we saw before, the data() method copies all table fields, including system fields such as record ID, company account, created user, and so on. Most of the time, it is OK because when the new record is saved, the system fields are overwritten with the new values. However, this function may not work for copying records across companies. In this case, we can use another function called buf2Buf(). It is very similar to the table's data() method with one major difference. The buf2Buf() function copies all data fields excluding the system ones. The code in the function is as follows:

static void buf2Buf(Common _from, Common _to)
{
DictTable dictTable = new DictTable(_from.TableId);
FieldId fieldId = dictTable.fieldNext(0);
while (fieldId && ! isSysId(fieldId))
{
_to.(fieldId) = _from.(fieldId);
fieldId = dictTable.fieldNext(fieldId);
}
}

We can clearly see that during the copying process, all the table fields are traversed, but the system fields are excluded. We can also see that this function is slower than the internal data() method, as it checks and copies each field individually.

In order to use the buf2Buf() function, the code of the MainAccountCopy job could be amended as follows:

static void MainAccountCopy(Args _args)
{
MainAccount mainAccount1;
MainAccount mainAccount2;
mainAccount1 = MainAccount::findByMainAccountId('211100');
ttsBegin;
buf2Buf(mainAccount1, mainAccount2);
mainAccount2.MainAccountId = '211101';
if (!mainAccount2.validateWrite())
{
throw Exception::Error;
}
mainAccount2.insert();
ttsCommit;
}

Carry out the following steps in order to complete this recipe:

First, we create a new query object. Next, we add a new ProjTable data source to the query object by calling its addDataSource() member method. The method returns a reference to the QueryBuildDataSource object—qbds1. Here, we call the addSortField() method to enable sorting by project name.

The following two blocks of code create two ranges. The first is to show only projects of type fixed price and the second one is to list only records, where the project number starts with 2. Those two filters are automatically added together using the SQL and operator. QueryBuildRange objects are created by calling the addRange() member method of the QueryBuildDataSource object with the field ID number as argument. The range value is set by calling value() on the QueryBuildRange object itself. It is a good practice to use the queryValue() function to process values before applying them as a range. More functions such as queryNotValue(), queryRange(), and so on can be found in the Global application class. Note that these functions are actually shortcuts to the SysQuery application class, which in turn have even more interesting helper methods that might be handy for every developer.

Adding another data source to an existing one connects both data sources using the SQL join operator. In this example, we are displaying projects that have at least one posted hour line. We start by adding the ProjEmplTrans table as another data source.

Next, we need to add relations between the tables. If relations are not defined on tables, we will have to use the addLink() method with relation field ID numbers. In this example, relations on the tables are already defined so it is enough only to enable them by calling the relations() method with true as an argument.

Calling joinMode() with JoinMode::ExistsJoin as a parameter ensures that a record from a parent data source will be displayed only if the relation exists in the attached data source.

The last thing to do is to create and run the queryRun object and show the selected data on the screen.

It is worth mentioning a couple of specific cases when working with query objects from code. One of them is how to use the or operator and the other one is how to address array fields.

Using the OR operator

As you have already noted, regardless of how many ranges are added, all of them will be added together using the SQL and operator. In most cases it is fine, but sometimes complex user requirements demand ranges to be added using SQL or. There might be a number of workarounds, such as using temporary tables or similar tools, but we can use the Dynamics AX feature that allows passing a part of raw SQL string as a range.

In this case, the range has to be formatted in a similar manner as a fully qualified SQL where clause, including field names, operators, and values. The expressions have to be formatted properly before using them in a query. Here are some of the rules:

• The expression must be enclosed within single quotes.

• Inside, the whole expression has to be enclosed in parenthesis.

• Each subexpression must be enclosed in parentheses too.

• String values have to be enclosed within double quotes.

• For enumerations use their numeric values.

• For value formatting use various Dynamics AX functions, such as queryValue(), Date2StrXpp(), or methods from the SysQuery class.

Let us replace the code from the previous example:

qbr1.value(queryValue(ProjType::FixedPrice));

with the new code:

qbr1.value(strFmt(
'((%1 = %2) || (%3 = "%4"))',
fieldStr(ProjTable,Type),
ProjType::FixedPrice+0,
fieldStr(ProjTable,ProjGroupId),
queryValue('TM1')));

Notice that by adding zero to the enumeration in the previous code, we can force the strFmt() function to use the numeric value of the enumeration.

Now, the result will also include all the projects belonging to the group TM1 regardless of their type:

Using arrays fields

Some table fields in Dynamics AX are based on extended data types, which contains more than one array element. An example in a standard application could project sorting based on a ProjSortingId extended data type. Although such fields are very much the same as normal fields, in queries they should be addressed in a slightly different manner. In order to demonstrate the usage, let us modify the example by filtering the query to list only those projects containing the value South in the field labelled Sort field 2, which is the second value in the array.

First, let us declare a new QueryBuildRange object in the variable declaration section:

QueryBuildRange qbr3;

Next, we add the following code, right after the qbr2.value(...) code:

qbr3 = qbds1.addRange(
fieldId2Ext(fieldnum(ProjTable,SortingId),2));
qbr3.value(queryValue('South'));

Notice that we use the global fieldid2ext() function, which converts the field ID and the array number into a valid number to be used by the addRange() method. This function can also be used anywhere, where addressing the dimension fields is required.

Now, we can run this job, as the project list based on previous criteria will be reduced even more to match projects having only a specific Sort field 2:

See Chapter 3, Working with Data in Forms:

See Chapter 4, Building Lookups:

Carry out the following steps in order to complete this recipe:

First, we define a macro that holds the where clause. Normally, the purpose of defining SQL in a macro is to reuse it a number of times in various places. We use %1 as an argument. More arguments could be added here.

Next, we create a job with the select statement. Here, we use the previously created macro in a where clause and pass vendTable as an argument.

The query works like any other query, but the advantage is that the code in the macro can be reused elsewhere.

Note that although using a macro in a SQL statement can reduce the amount of code, too much code in it might decrease the SQL statement's readability for other developers. So keep it balanced.

Carry out the following steps in order to complete this recipe:

We start the code by creating DictTable and DictField objects for handling the vendor table and its fields used later in the query. DirPartyTable table is used to get additional vendor information.

A new SqlSystem object also has to be created. It will be used to convert Dynamics AX types to SQL types.

Next, we set up an SQL statement with a number of placeholders for table or field names and field values to be inserted later.

The main query creation happens next when the query placeholders are replaced with the right values. Here we use the previously created DictTable and DictField type objects by calling their name() methods with the DbBackend::Sql enumeration as an argument. This ensures that we pass the name exactly how it is used in the database—some of the SQL field names are not necessary the same as field names within the application.

We also use the sqlLiteral() method of the previously created sqlSystem object to properly format SQL values to make sure they do not have any unsafe characters.

Once the SQL statement is ready, we initialize a direct connection to the database and run the statement. The results are returned into the resultSet object, and we get them by using the while statement and calling the next() method until the end of the resultSet object.

Note that we create an sqlPermission object of type SqlStatementExecutePermission here and call its assert() method before executing the statement. This is required in order to comply with Dynamics AX trustworthy computing requirements.

Another thing to mention is that when building direct SQL queries, special attention has to be paid to license, configuration, and security keys. Some tables or fields might be disabled in the application and may contain no data in the database.

The code in this recipe can be also used to connect to the external ODBC databases. We only need to replace the UserConnection class with the OdbcConnection class and use text names instead of the DictTable and DictField objects.

The standard Dynamics AX application provides an alternate way of building direct SQL statements by using a set of SQLBuilder classes. By using those classes, we can create SQL statements as objects as opposed to text. Next, we will demonstrate how to use the SQLBuilder classes. We will create the same SQL statement as before.

First in AOT, we create another class named VendTableSqlBuilder with the following code:

class VendTableSqlBuilder
{
}
server static void main(Args _args)
{
UserConnection userConnection;
Statement statement;
str sqlStatement;
SqlStatementExecutePermission sqlPermission;
ResultSet resultSet;
SQLBuilderSelectExpression selectExpr;
SQLBuilderTableEntry vendTable;
SQLBuilderTableEntry dirPartyTable;
SQLBuilderFieldEntry accountNum;
SQLBuilderFieldEntry dataAreaId;
SQLBuilderFieldEntry blocked;
SQLBuilderFieldEntry name;
selectExpr = SQLBuilderSelectExpression::construct();
selectExpr.parmUseJoin(true);
vendTable = selectExpr.addTableId(
tablenum(VendTable));
dirPartyTable = vendTable.addJoinTableId(
tablenum(DirPartyTable));
accountNum = vendTable.addFieldId(
fieldnum(VendTable,AccountNum));
name = dirPartyTable.addFieldId(
fieldnum(DirPartyTable,Name));
dataAreaId = vendTable.addFieldId(
fieldnum(VendTable,DataAreaId));
blocked = vendTable.addFieldId(
fieldnum(VendTable,Blocked));
vendTable.addRange(dataAreaId, curext());
vendTable.addRange(blocked, CustVendorBlocked::No);
selectExpr.addSelectFieldEntry(
SQLBuilderSelectFieldEntry::newExpression(
accountNum,
'AccountNum'));
selectExpr.addSelectFieldEntry(
SQLBuilderSelectFieldEntry::newExpression(
name,
'Name'));
sqlStatement = selectExpr.getExpression(null);
userConnection = new UserConnection();
statement = userConnection.createStatement();
sqlPermission = new SqlStatementExecutePermission(
sqlStatement);
sqlPermission.assert();
resultSet = statement.executeQuery(sqlStatement);
CodeAccessPermission::revertAssert();
while (resultSet.next())
{
info(strfmt(
"%1 - %2",
resultSet.getString(1),
resultSet.getString(2)));
}
}

In this method, we first create a new selectExpr object, which is based on the SQLBuilderSelectExpression class. It represents the object of the SQL statement.

Next, we add the VendTable table to it by calling its member method addTableId(). The method returns a reference to the vendTable object of type SQLBuilderTableEntry, which corresponds to a table node in an SQL query. We also add DirPartyTable as a joined table.

Then, we create a number of field objects of type SQLBuilderFieldEntry to be used later and two ranges to show only this company account and only active vendor accounts.

We use addSelectFieldEntry() to add two fields to be selected. Here we use the previously created field objects.

The SQL statement is generated once the getExpression() method is called, and the rest of the code is the same as in the previous example.

Running the class would give us results, which are exactly similar to the ones we got before.

Before we start, we need to create an invalid setup to make sure we can simulate data inconsistency. Open Fixed assets | Setup | Value models and create a new model, for instance, TEST:

Open Fixed assets | Setup | Fixed asset posting profiles and under the Ledger accounts group, create a new record with the newly created value model for any of the posting types:

Go back to the Value models form, and delete the previously created value model.

Now, we have a non-existing value model in the fixed asset posting settings.

Carry out the following steps in order to complete this recipe:

The consistency check in Dynamics AX validates only the predefined list of tables for each module. The system contains a number of classes derived from SysConsistencyCheck. For example, the CustConsistencyCheck class is responsible for validating the Accounts receivable module, LedgerConsistencyCheck —for General ledger, and so on.

In this recipe, we created a new class named AssetConsistencyCheck, extending the SysConsistencyCheck class for the fixed asset module. The following methods were created:

The classes we just mentioned can only be executed from the main Consistency check form. Individual checks could also be invoked as stand-alone functions. We just need to create an additional method to allow running of the class:

static void main(Args args)
{
SysConsistencyCheckJob consistencyCheckJob;
AssetConsistencyCheck assetConsistencyCheck;
consistencyCheckJob = new SysConsistencyCheckJob(
classidget(assetConsistencyCheck));
if (!consistencyCheckJob.prompt())
{
return;
}
consistencyCheckJob.run();
}

Carry out the following steps in order to complete this recipe:

We start the recipe by creating a new XmlDocument using its newBlank() method, which represents an XML structure. Then we create its root node named xml using the createElement() method, and add the node to the document by calling the document's appendChild() method.

Next, we go through the MainAccount table and do the following for each record:

Finally, we save the created XML document as a file.

In this way, we can create documents having virtually any structure.

Carry out the following steps in order to complete this recipe:

In this recipe, we first create a new XmlDocument. We create it from the file and hence we have to use its newFile() method. Then we get all the document nodes of the table as XmlNodeList. We also get its first element by calling the nextNode() method.

Next, we loop through all the list elements and do the following:

In this way, we retrieve the data from the XML file. A similar approach could be used to read any other XML file.

Carry out the following steps in order to complete this recipe:

In the variable declaration section of the main() method of the newly created CreateCommaFile class, we define a name for the output file, along with other variables. Normally, this should be replaced with a proper input variable. Here, we also define a standard #File macro, which contains a number of file-handling modes, such as #io_read, #io_write, #io_append, and so on, file types, delimiters, and other things.

Next, we create a new CSV file by calling the new() method on a standard CommaIo class. It accepts two parameters—filename and mode. For mode, we use #io_write from the #File macro to make sure a new file is created and opened for further writing. If a file with the given name already exists, then it will be overwritten. In order to make sure that a file is created successfully, we check if the file object exists and its status is valid, otherwise we show an error message.

In multilingual environments, it is better to use the CommaTextIo class. It behaves the same way as the CommaIo class does plus it supports Unicode, which allows us to process data with various language-specific symbols.

Finally, we loop though the MainAccount table, store all account numbers and their names in a container, and write them to the file using the writeExp() method.

In this way, we create a new comma-separated value file with the list of ledger accounts.

You probably already noticed that the main() method has the client modifier, which forces its code to run on the client. When dealing with large amounts of data, it is more effective to run the code on the server. In order to do that, we need to change the modifier to server. The following class generates exactly the same file as before, except that this file is created in the folder on the server's file system:

class CreateCommaFileServer
{
}
public static server void main(Args _args)
{
CommaTextIo file;
container line;
MainAccount mainAccount;
FileIoPermission perm;
#define.filename('C:\\Temp\\accounts.csv')
#File
perm = new FileIoPermission(#filename, #io_write);
perm.assert();
file = new CommaTextIo(#filename, #io_write);
if (!file || file.status() != IO_Status::Ok)
{
throw error("File cannot be opened.");
}
while select mainAccount
{
line = [
mainAccount.MainAccountId,
mainAccount.Name];
file.writeExp(line);
}
CodeAccessPermission::revertAssert();
info(strFmt("File %1 created.", #filename));
}

File manipulation on the server is protected by Dynamics AX code access security and we must use the FileIoPermission class to make sure we match the requirements.

Finally, we call CodeAccessPermission::revertAssert() to revert the previous assertion.

Carry out the following steps in order to complete this recipe:

As in the previous recipe, we first create a new file object using the CommaTextIo class. This time we use #io_read as the mode to make sure that the existing file is read only. We also perform the same validations to make sure that the file object is correctly created, otherwise we show an error message.

Finally, we read the file line by line until we reach the end of the file. Here we use the while loop until the file status becomes not IO_Status::OK, meaning we have reached the file end. Inside the loop, we call the read() method on the file object, which returns the current line as a container and moves the internal file cursor to the next line. File data is then simply output to the screen using the standard global info() function in conjunction with the con2Str() function, which converts a container to a string for displaying.

The last element of code, where the data is output, should normally be replaced by proper code that processes the incoming data.

File reading, could also be executed in a similar way as file writing on a server to improve performance. The modifier client has to be changed to server, and code with the FileIoPermission class has to be added to fulfil the code access security requirements. The modified class should look similar to the following code:

class ReadCommaFileServer
{
}
public static server void main(Args _args)
{
CommaTextIo file;
container line;
FileIoPermission perm;
#define.filename('C:\\Temp\\accounts.csv')
#File
perm = new FileIoPermission(#filename, #io_read);
perm.assert();
file = new CommaTextIo(#filename, #io_read);
if (!file || file.status() != IO_Status::Ok)
{
throw error("File cannot be opened.");
}
line = file.read();
while (file.status() == IO_Status::Ok)
{
info(con2Str(line, ' - '));
line = file.read();
}
CodeAccessPermission::revertAssert();
}

Carry out the following steps in order to complete this recipe:

We start the recipe by setting the ValidTimeStateFieldType property to Date on the SysEmailTable table. This automatically creates two new fields: ValidFrom and ValidTo that are used to define a date range.

Next, we add the created fields to the primary index where the EmailId field is used and adjust the following index's properties:

Finally, we adjust the SysEmailTable form to reflect the changes. We add the newly created ValidFrom and ValidTo fields to the SysEmailTable table's Identification group so that they automatically appear on the form's Overview grid. We also change a few properties of the SysEmailTable data source:

Set the ValidTimeStateAutoQuery property to DateRange to ensure that all records are visible. The default AsOfDate value could be used if we want to display only the records for the current period.

Set the ValidTimeStateUpdate property to Correction, allowing the user to modify the dates.

Forms with date effective records can be enhanced with an automatically-generated toolbar for filtering the records. This can be done with the help of the DateEffectivenessPaneController application class.

In order to demonstrate that, let's modify the previously used SysEmailTable form and add the following code to the bottom of the form's init() method:

DateEffectivenessPaneController::constructWithForm(
this,
SysEmailTable_ds);

Now when you open the form, it contains an automatically-generated date effectiveness filter at the top: