Microsoft Dynamics AX 2012 R3 Development Cookbook

2.5 (2 reviews total)
By Mindaugas Pocius
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Processing Data

About this book

Microsoft Dynamics AX 2012 R3 Development Cookbook will help you manage your company's or customer's ERP information and operations efficiently. Beginning with exploring data manipulation concepts in Dynamics AX, you will build scripts to assist data migration and organize data in AX forms. You will learn how to create custom lookups using AOT forms and generate them dynamically. After this, you'll learn how to enhance your application by using advanced form controls, and integrate your system with other external systems. You will also learn how to enhance your user interface using various Dynamics AX UI elements.

This book will help you look at application development from a business process perspective, and develop enhanced ERP solutions by learning and implementing the best practices and techniques.

Publication date:
April 2015
Publisher
Packt
Pages
388
ISBN
9781785281693

 

Chapter 1. Processing Data

In this chapter, we will cover the following recipes:

  • Creating a new number sequence

  • Renaming the primary key

  • Merging two records

  • Adding a document handling note

  • Using a normal table as a temporary table

  • Copying a record

  • Building a query object

  • Using a macro in a SQL statement

  • Executing a direct SQL statement

  • Enhancing the data consistency check

  • Exporting data to an XML file

  • Importing data from an XML file

  • Creating a comma-separated value file

  • Reading a comma-separated value file

  • Using the date effectiveness feature

 

Introduction


This chapter focuses on data manipulation exercises. These exercises are very useful when doing data migration, system integration, custom reporting, and so on. Here, we will discuss how to work with query objects from the X++ code. We will also discuss how to reuse macros in X++ SQL statements and how to execute SQL statements directly in the database. This chapter will explain how to rename primary keys, how to merge and copy records, how to add document handling notes to selected records, and how to create and read XML and comma-separated value (CSV) files. The chapter ends with a recipe about the date effectiveness feature.

 

Creating a new number sequence


Number sequences in Dynamics AX are used to generate specifically formatted numbers used for various identification. These number sequences can be anything from voucher numbers or transaction identification numbers to customer or vendor account codes.

When developing custom functionality, often one of the tasks is to add a new number sequence to the system in order to support newly created tables and forms. Adding a number sequence to the system is a two-step process. First, we create the number sequence itself; second, we start using it in some particular form or from the code. Number sequences can be created either manually or automatically by the wizard.

Dynamics AX contains a list of NumberSeqApplicationModule derivative classes, which hold the number sequence's setup data for the specific module. These classes are read by the number sequence wizard, which detects already created number sequences and proposes to create the missing ones. The wizard is normally run as part of the application initialization. It can also be rerun any time later when expanding the Dynamics AX functionality used. The wizard also has to be rerun if new custom number sequences are added to the system.

In this recipe, we will do the first step, that is, add a new number sequence to the system. In a standard application, the customer group number is not driven by a number sequence, so we will enhance this by creating a new number sequence for customer groups. The second step is explained later in the Using a number sequence handler recipe in Chapter 3, Working with Data in Forms.

How to do it...

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

  1. Open the NumberSeqModuleCustomer class in the Application Object Tree (AOT) and add the following code snippet at the bottom of the loadModule() method:

    datatype.parmDatatypeId(extendedTypeNum(CustGroupId));
    datatype.parmReferenceHelp("Customer group ID");
    datatype.parmWizardIsContinuous(false);
    datatype.parmWizardIsManual(NoYes::No);
    datatype.parmWizardIsChangeDownAllowed(NoYes::Yes);
    datatype.parmWizardIsChangeUpAllowed(NoYes::Yes);
    datatype.parmWizardHighest(999);
    datatype.parmSortField(20);
    datatype.addParameterType(
        NumberSeqParameterType::DataArea, true, false);
    this.create(datatype);

    Tip

    Downloading the example code

    You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

  2. Create a new job with the following lines of code and run it:

    static void NumberSeqLoadAll(Args _args)
    {
        NumberSeqApplicationModule::loadAll();
    }
  3. Navigate to Organization administration | Common | Number sequences and open the Number sequences list page. Run the number sequence wizard by clicking on the Generate button. On the first wizard's page, click on the Next button, as shown in the following screenshot:

  4. On the next page, click on Details to view more information. Delete everything apart from the rows where Area is Accounts receivable and Reference is Customer group. Note the number sequence codes and click on the Next button, as shown here:

  5. On the last page, click on the Finish button to complete the setup, as shown in the following screenshot:

  6. The newly created number sequences can now be found in the Number sequences list page, as shown in the following screenshot:

  7. Navigate to Organization administration | Number sequences | Segment configuration and notice the new Customer group reference under the Accounts receivable area:

  8. Navigate to Accounts receivable | Setup | Accounts receivable parameters and select the Number sequences tab page. Here, you should see the new number sequence code:

  9. The last thing to be done is to create a helper method for this number sequence. Locate the CustParameters table in the AOT by navigating to Data Dictionary | Tables and create the following method:

    client server static NumberSequenceReference numRefCustGroupId()
    { 
        return NumberSeqReference::findReference( 
            extendedTypeNum(CustGroupId)); 
    }

How it works...

We start the recipe by adding a number sequence initialization code into the NumberSeqModuleCustomer class. This class holds all the definitions of the number sequence parameters that belong to the Accounts receivable module. There are many other similar classes, such as NumberSeqModuleVendor or NumberSeqModuleLedger, that holds the number sequence definitions for other modules.

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

Before we start the wizard, we initialize number sequence references. The references are those records that are normally located under the Number sequences tab pages in the parameters forms in most of the Dynamics AX modules. This is normally done as a part of the Dynamics AX initialization checklist, but in this example, we execute it manually by calling the loadAll() method of the NumberSeqApplicationModule class.

Next, we execute the wizard that will create the number sequence codes for us. We 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 codes 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, which means that we will have separate number lists for each company.

See also

  • The Using a number sequence handler recipe in Chapter 3, Working with Data in Forms

 

Renaming the primary key


Most of you who are familiar with the Dynamics AX application have probably used the standard Rename function. This function allows you to rename the primary key of almost any record. With this function, you can fix records that were saved or created by mistake. The Rename function ensures data consistency, that is, all the related records are renamed as well. The function can be accessed from the Record information form (shown in the following screenshot), which can be opened by selecting Record info in the record's right-click context menu:

When it comes to mass renaming, this function might be very time-consuming as you need to run it on every record. An alternative way of doing this is to create a job that automatically runs through all the required records and calls this function automatically.

This recipe will explain how a record's primary key can be renamed through the code. As an example, we will create a job that renames a vendor account.

How to do it...

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

  1. Navigate to Accounts payable | Common | Vendors | All vendors and find the account that you want to rename, as shown here:

  2. Click on Transactions in the action pane to check the existing transactions, as shown in the following screenshot:

  3. Open the AOT, create a new job named VendAccountRename, and enter the following code snippet. Use the previously selected account:

    static void VendAccountRename(Args _args)
    {
        VendTable vendTable;
    
        ttsBegin;
    
        select firstOnly vendTable
            where vendTable.AccountNum == 'US-101';
    
        if (vendTable)
        {
            vendTable.AccountNum = 'US-101_';
            vendTable.renamePrimaryKey();
        }
    
        ttsCommit;
    }
  4. Run the job and check whether the renaming was successful by navigating to Accounts payable | Common | Vendors | All vendors again and finding the new account. The new account should have retained all its transactions and other related records, as shown in the following screenshot:

  5. Click on Transactions in the action pane in order to see whether the existing transactions are still in place, as shown here:

How it works...

In this recipe, we first select the desired vendor record and set its account number to the new value. Note that only the fields belonging to the 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 vendor account and updates them with the new value. The operation might take a while, depending on the volume of data, as the system has to update multiple records located in multiple tables.

 

Merging two records


For various reasons, the data in a system—such as customers, ledger accounts, configuration settings, and similar data—may become obsolete. This can be because of changes in the business or it can simply be a user input error. For example, two sales people can create two records for the same customer, start entering sales orders, and post invoices. One of the ways to solve this problem is to merge both the records into a single record.

In this recipe, we will explore how to merge one record into another, including all the related transactions. For this demonstration, we will merge two ledger reason codes into a single one.

How to do it...

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

  1. Navigate to General ledger | Setup | Ledger reasons in order to find the reasons that you want to merge. Pick any two ledger reasons. In this example, we will use ADJ and OTHER, as shown in the following screenshot:

  2. Open the AOT and create a new job named LedgerReasonMerge with the following code snippet (replace the reason codes with your own values):

    static void LedgerReasonMerge(Args _args)
    {
        ReasonTable reasonTableDelete;
        ReasonTable reasonTable;
    
        ttsBegin;
    
        select firstOnly forUpdate reasonTableDelete
            where reasonTableDelete.Reason == 'ADJ';
    
        select firstOnly forUpdate reasonTable
            where reasonTable.Reason == 'OTHER';
    
        reasonTableDelete.merge(reasonTable);
        reasonTable.doUpdate();
        reasonTableDelete.doDelete();
    
        ttsCommit;
    }
  3. Run the job to merge the records.

  4. Open the Ledger reasons form again; you will notice that both the reasons were merged into one and all the related transactions have also been updated to reflect the change:

How it works...

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

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

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

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

Such a technique can be used to merge two or more records of any type.

 

Adding a document handling note


Document handling in Dynamics AX is a feature that allows you to add notes, links, documents, images, files, and other related information to almost any record in the system. For example, we can track all the correspondence sent out to our customers by attaching the documents to their records in the system. Document handling on most of the forms can be accessed either from the action pane by clicking on the Attachments button, selecting Document handling from the Command menu under File, or selecting the Document handling icon from the status bar.

Document handling has a number of configuration parameters that you can find by navigating to Organization administration | Setup | Document management. Please refer to Dynamics AX documentation to find out more.

Dynamics AX also allows you to add document handling notes from the code. This can be useful when you need to automate the document handling process. In this recipe, we will demonstrate this by adding a note to a vendor account.

Getting ready

Before you start, ensure that document handling is enabled on the user interface. Open Document management parameters by navigating to Organization administration | Setup | Document management and make sure that Use Active document tables is not marked, as shown in the following screenshot:

Then, open the Document types form from the same location and locate or create a new document type with its Group set to Note, as shown in the following screenshot. In our demonstration, we will use a document type called Note:

How to do it...

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

  1. Navigate to Accounts payable | Common | Vendors | All vendors and locate any vendor account to be updated, as shown here:

  2. Open the AOT, create a new job named VendAccountDocu, and enter the following code snippet. Use the previously selected vendor account and document type:

    static void VendAccountDocu(Args _args)
    {
        VendTable vendTable;
        DocuType  docuType;
        DocuRef   docuRef;
    
        vendTable = VendTable::find('US-108');
        docuType  = DocuType::find('Note');
    
        if (!docuType ||
            docuType.TypeGroup != DocuTypeGroup::Note)
        {
            throw error("Invalid document type");
        }
    
    
        docuRef.RefCompanyId = vendTable.dataAreaId;
        docuRef.RefTableId   = vendTable.TableId;
        docuRef.RefRecId     = vendTable.RecId;
        docuRef.TypeId       = docuType.TypeId;
        docuRef.Name         = 'Automatic note';
        docuRef.Notes        = 'Added from X++';
        docuRef.insert();
    
        info("Document note has been added successfully");
    }
  3. Run the job to create the note.

  4. Go back to the vendor list and click on the Attachments button in the form's action pane or expand the File menu and navigate to Command | Document handling to view the note added by our code, as shown in the following screenshot:

How it works...

All the document handling notes are stored in the DocuRef table, where three fields, RefCompanyId, RefTableId, and RefRecId, are used to identify the parent record. In this recipe, we set these fields to the vendor company ID, vendor table ID, and vendor account record ID, respectively. Then, we set the type, name, and description and insert the document handling record. Notice that we have validated the document type before using it. In this way, we added a note to the record.

 

Using a normal table as a temporary table


Temporary tables in Dynamics AX are used in numerous places. In forms and reports, they are used as data sources when it is too complicated to query normal tables. In code, they can be used for storing intermediate results while running complex operations.

Temporary tables can be either created from scratch or existing regular tables could be reused as temporary. The goal of this recipe is to demonstrate the latter approach. As an example, we will use the vendor table to insert and display a couple of temporary records without affecting the actual data.

How to do it...

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

  1. In the AOT, create a new class named VendTableTmp with the following code snippet:

    class VendTableTmp
    {
    }
    
    server static void main(Args _args)
    {
        VendTable   vendTable;
    
        vendTable.setTmp();
    
        vendTable.AccountNum = '1000';
        vendTable.Blocked    = CustVendorBlocked::No;
        vendTable.Party      = 1;
        vendTable.doInsert();
    
        vendTable.clear();
        vendTable.AccountNum = '1002';
        vendTable.Blocked    = CustVendorBlocked::All;
        vendTable.Party      = 2;
        vendTable.doInsert();
    
        while select vendTable
        {
            info(strFmt(
                "%1 - %2",
                vendTable.AccountNum,
                vendTable.Blocked));
        }
    }
  2. Run the class and check the results, which may be similar to this:

How it works...

The key method in this recipe is setTmp(). This method is available on all the tables, and it makes the current table instance behave as a temporary table in the current scope. Basically, it creates an InMemory temporary table that has the same schema as the original table.

In this recipe, we create a new class and place all the code in its main() method. The reason why we create a class and not a job is that the main() method can be set to run on the server tier by specifying the server modifier. This will improve the code's performance.

In the code, we first call the setTmp() method on the vendTable table to make it temporary in the scope of this method. This means that any data manipulations will be lost once the execution of this method is over and the actual table content will not be affected.

Next, we 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. We have to keep in mind that even the table becomes temporary; all the code in its insert(), update(), delete(), initValue(), and other methods is still present and we have to make sure that we don't call it unintentionally.

The last thing to do is to check for newly created records by showing them on the screen. We can see that although the table contains many actual records, only the records that we inserted were displayed in the Infolog window. Additionally, the two records we inserted do not appear in the actual table.

 

Copying a record


Copying existing data is one of the data manipulation tasks in Dynamics AX. There are numerous places in the standard Dynamics AX application where users can create new data entries just by copying existing data and then modifying it. A few examples are the Copy button on the Costing versions form located in Inventory management | Setup | Costing and the Copy project button on the All projects list page located in Project management and accounting | Common | Projects. Also, although the mentioned copying functionality might not be that straightforward, the idea is clear: the existing data is reused while creating new entries.

In this recipe, we will learn two ways to copy records in X++. We will discuss the usage of the table's data() method, the global buf2buf() function, and their differences. As an example, we will copy one of the existing main account records into a new record.

How to do it...

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

  1. Navigate to General ledger | Common | Main accounts and find the account to be copied. In this example, we will use 130100, as shown here:

  2. Open the AOT, create a new job named MainAccountCopy with the following code snippet, and run it:

    static void MainAccountCopy(Args _args)
    {
        MainAccount mainAccount1;
        MainAccount mainAccount2;
    
        mainAccount1 = MainAccount::findByMainAccountId( '130100');
    
        ttsBegin;
    
        mainAccount2.data(mainAccount1);
    
        mainAccount2.MainAccountId = '130101';
        mainAccount2.Name += ' - copy';
    
        if (!mainAccount2.validateWrite())
        {
            throw Exception::Error;
        }
    
        mainAccount2.insert();
    
        ttsCommit;
    }
  3. Navigate to General ledger | Common | Main accounts again and notice that there are two identical records now, as shown in the following screenshot:

How it works...

In this recipe, we have two variables: mainAccount1 for the original record and mainAccount2 for the new record. First, we find the original record by calling findMainAccountId() in the MainAccount table.

Next, we copy the original record into the new one. Here, we use the table's data() method, which copies all the data fields from one variable into another.

After that, we set a new main account number, which is a part of the table's unique index.

Finally, we call insert() on the table if validateWrite() is successful. In this way, we create a new main account record that is exactly the same as the existing one apart from the account number.

There's more...

As we saw before, the data() method copies all the table fields, including the system fields such as the record ID or company account. 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(). This function is a global function and is located in the Global class, which you can find by navigating to AOT | Classes. The buf2Buf() function is very similar to the table's data() method with one major difference. The buf2Buf() function copies all the data fields excluding the system fields. The code in the function is as follows:

static void buf2Buf(
    Common _from,
    Common _to,
    TableScope _scope = TableScope::CurrentTableOnly)
{
    DictTable   dictTable = new DictTable(_from.TableId);
    FieldId     fieldId   = dictTable.fieldNext(0, _scope);

    while (fieldId && ! isSysId(fieldId))
    {
        _to.(fieldId)   = _from.(fieldId);
        fieldId         = dictTable.fieldNext(fieldId, _scope);
    }
}

We can clearly see that during the copying process, all the table fields are traversed, but the system fields, such as RecId or dataAreaId, are excluded. The isSysId() helper function is used for this purpose.

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

static void MainAccountCopy(Args _args)
{
    MainAccount mainAccount1;
    MainAccount mainAccount2;

    mainAccount1 = MainAccount::findByMainAccountId('130100');

    ttsBegin;

    buf2Buf(mainAccount1, mainAccount2);

    mainAccount2.MainAccountId = '130101';
    mainAccount2.Name += ' - copy';

    if (!mainAccount2.validateWrite())
    {
        throw Exception::Error;
    }

    mainAccount2.insert();

    ttsCommit;
}
 

Building a query object


Query objects in Dynamics AX are used to build SQL statements for reports, views, forms, and other AOT objects. They are normally created in the AOT using the drag and drop functionality and by defining various properties. Query objects can also be created from the code at runtime. This is normally done when AOT tools cannot handle complex and/or dynamic queries.

In this recipe, we will create a query from the code to retrieve project records from the Project management module. We will select the records where project ID starts with 00005, project type is time & material and project has at least one transaction of type hour registered.

How to do it...

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

  1. Open the AOT, create a new job named ProjTableQuery, and enter the following code snippet:

    static void ProjTableQuery(Args _args)
    {
        Query                   query;
        QueryBuildDataSource    qbds1;
        QueryBuildDataSource    qbds2;
        QueryBuildRange         qbr1;
        QueryBuildRange         qbr2;
        QueryRun                queryRun;
        ProjTable               projTable;
    
        query = new Query();
    
        qbds1 = query.addDataSource(tableNum(ProjTable));
        qbds1.addSortField(
            fieldNum(ProjTable, Name),
            SortOrder::Ascending);
    
        qbr1 = qbds1.addRange(fieldNum(ProjTable,Type));
        qbr1.value(queryValue(ProjType::TimeMaterial));
    
        qbr2 = qbds1.addRange(fieldNum(ProjTable,ProjId));
        qbr2.value(
            SysQuery::valueLikeAfter(queryValue('00005')));
    
        qbds2 = qbds1.addDataSource(tableNum(ProjEmplTrans));
        qbds2.relations(true);
        qbds2.joinMode(JoinMode::ExistsJoin);
    
        queryRun = new QueryRun(query);
    
        while (queryRun.next())
        {
            projTable = queryRun.get(tableNum(ProjTable));
            info(strFmt(
                "%1, %2, %3",
                projTable.ProjId,
                projTable.Name,
                projTable.Type));
        }
    }
  2. Run the job and you will get a screen similar to the following screenshot:

How it works...

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

Next we create two ranges. The first range filters only the projects of the ProjType::TimeMaterial type and the second one lists only the records where the project number starts with 00005. These two ranges are automatically added together using SQL's AND operator. The QueryBuildRange objects are created by calling the addRange() method of the QueryBuildDataSource object with the field ID number as the argument. The range value is set by calling value() on the QueryBuildRange object itself. We use the queryValue()function from the Global class and the valueLikeAfter() function from the SysQuery class to prepare the values before applying them as a range. More functions, such as queryNotValue() and queryRange(), can be found in the Global application class, which is located in AOT | Classes. Note that these functions are actually shortcuts to the SysQuery application class, which in turn has even more interesting helper methods that might be handy for every developer.

Adding another data source to an existing one connects both the data sources using SQL's 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 relationships between the tables. If relationships are not defined on tables, we will have to use the addLink() method. In this example, relations in the tables are already defined, so you only need to enable them by calling the relations() method with true as an argument.

Calling joinMode() with JoinMode::ExistsJoin as a parameter ensures that only the projects that have at least one transaction of type hour will be selected. In situations like this, where we do not need any data from the second data source, performance-wise it is better to use an exists join instead of the inner join. This is because the inner join fetches the data from the second data source and therefore takes longer to execute.

The last thing that needs to be done is to create and run the queryRun object and show the selected data on the screen.

There's more...

It is worth mentioning a couple of specific cases when working with query objects from the 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 SQL's AND operator. In most cases, this is fine, but sometimes complex user requirements demand ranges to be added using SQL's OR operator. 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 you to pass a part of a raw SQL string as a range.

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

  • The expression must be enclosed within single quotes and then inside the quotes—within parenthesis

  • Each subexpression must also be enclosed within parentheses

  • 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() and date2StrXpp(), or methods from the SysQuery class.

Let's replace the code snippet from the previous example with the following lines of code:

qbr2.value(SysQuery::valueLikeAfter(queryValue('00005')));
with the new code:
qbr2.value(strFmt(
    '((%1 like "%2") || (%3 = %4))',
    fieldStr(ProjTable,ProjId),
    queryvalue('00005')+'*',
    fieldStr(ProjTable,Status),
    ProjStatus::InProcess+0));

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. The strFmt() output should be similar to the following line:

((ProjId like "00005*") || (Status = 3))

Now if you run the code, besides all the projects starting with 00005, the result will also include all the active projects, as shown in the following screenshot:

See also

  • The Creating a custom filter control recipe in Chapter 3, Working with Data in Forms

  • The Using a form to build a lookup recipe in Chapter 4, Building Lookups

 

Using a macro in a SQL statement


In a standard Dynamics AX application, there are macros, such as InventDimJoin and InventDimSelect, that are reused numerous times across the application. These macros are actually full or partial X++ SQL queries that can be called with various arguments. Such approaches save development time by allowing you to reuse pieces of X++ SQL queries.

In this recipe, we will create a small macro that holds a single WHERE clause to display only the active vendor records. Then, we will create a job that uses the created macros to display a vendor list.

How to do it...

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

  1. Open the AOT and create a new macro named VendTableNotBlocked with the following code snippet:

    (%1.Blocked == CustVendorBlocked::No)
  2. In the AOT, create a new job called VendTableMacro with the following code snippet:

    static void VendTableMacro(Args _args)
    {
        VendTable   vendTable;
    
        while select vendTable
            where #VendTableNotBlocked(vendTable)
        {
            info(strFmt(
                "%1 - %2",
                vendTable.AccountNum,
                vendTable.name()));
        }
    }
  3. Run the job and check the results, as shown in the following screenshot:

How it works...

In 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 formatted as %2, %3, and so on can be used.

Next, we create a job with the SELECT statement. Here, we use the previously created macro in the 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.

Remember that before we start using macros in SQL queries, we should be aware of the following caveats:

  • Too much code in a macro might reduce the SQL statement's readability for other developers

  • Cross-references do not take into account the code inside the macro

  • Changes in the macro will not be reflected in the objects where the macro is used until the objects are recompiled

 

Executing a direct SQL statement


Dynamics AX allows developers to build X++ SQL statements that are flexible enough to fit into any custom business process. However, in some cases, the usage of X++ SQL is either not effective or not possible at all. One such case is when we run data upgrade tasks during an application version upgrade. A standard application contains a set of data upgrade tasks to be completed during the version upgrade. If the application is highly customized, then most likely, standard tasks have to be modified in order to reflect data dictionary customizations, or a new set of tasks have to be created to make sure data is handled correctly during the upgrade.

Normally, at this stage, SQL statements are so complex that they can only be created using database-specific SQL and executed directly in the database. Additionally, running direct SQL statements dramatically increases data upgrade performance because most of the code is executed on the database server where all the data resides. This is very important when working with large volumes of data.

Another case when we will need to use direct SQL statements is when we want to connect to an external database using the ODBC connection. In this case, X++ SQL is not supported at all.

This recipe will demonstrate how to execute SQL statements directly. We will connect to the current Dynamics AX database directly using an additional connection and retrieve a list of vendor accounts.

How to do it...

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

  1. In the AOT, create a new class named VendTableSql using the following code snippet:

    class VendTableSql
    {
    }
    
    server static void main(Args _args)
    {
        UserConnection                  userConnection;
        Statement                       statement;
        str                             sqlStatement;
        SqlSystem                       sqlSystem;
        SqlStatementExecutePermission   sqlPermission;
        ResultSet                       resultSet;
        DictTable                       tblVendTable;
        DictTable                       tblDirPartyTable;
        DictField                       fldParty;
        DictField                       fldAccountNum;
        DictField                       fldDataAreaId;
        DictField                       fldBlocked;
        DictField                       fldRecId;
        DictField                       fldName;
    
        tblVendTable     = new DictTable(tableNum(VendTable));
        tblDirPartyTable = new DictTable(tableNum(DirPartyTable));
    
        fldParty = new DictField(
            tableNum(VendTable),
            fieldNum(VendTable,Party));
    
        fldAccountNum = new DictField(
            tableNum(VendTable),
            fieldNum(VendTable,AccountNum));
    
        fldDataAreaId = new DictField(
            tableNum(VendTable),
            fieldNum(VendTable,DataAreaId));
    
        fldBlocked = new DictField(
            tableNum(VendTable),
            fieldNum(VendTable,Blocked));
    
        fldRecId = new DictField(
            tableNum(DirPartyTable),
            fieldNum(DirPartyTable,RecId));
    
        fldName = new DictField(
            tableNum(DirPartyTable),
            fieldNum(DirPartyTable,Name));
    
        sqlSystem = new SqlSystem();
    
        sqlStatement = 'SELECT %1, %2 FROM %3 ' +
            'JOIN %4 ON %3.%5 = %4.%6 ' +
            'WHERE %7 = %9 AND %8 = %10';
    
        sqlStatement = strFmt(
            sqlStatement,
            fldAccountNum.name(DbBackend::Sql),
            fldName.name(DbBackend::Sql),
            tblVendTable.name(DbBackend::Sql),
            tblDirPartyTable.name(DbBackend::Sql),
            fldParty.name(DbBackend::Sql),
            fldRecId.name(DbBackend::Sql),
            fldDataAreaId.name(DbBackend::Sql),
            fldBlocked.name(DbBackend::Sql),
            sqlSystem.sqlLiteral(curext(), true),
            sqlSystem.sqlLiteral(CustVendorBlocked::No, true));
    
        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)));
        }
    }
  2. Run the class to retrieve a list of vendors directly from the database, as shown in the following screenshot:

How it works...

We start the code by creating the DictTable and DictField objects to handle the vendor table and its fields, which are used later in the query. The DirPartyTable is used to get an additional vendor information.

A new SqlSystem object is also created. It is used to convert Dynamics AX types to SQL types.

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

The main query creation takes place next, when the query placeholders are replaced with the right values. Here, we use the previously created DictTable and DictField objects . We call their name() methods with the DbBackend::Sql enumeration as an argument. This ensures that we use the table and field names exactly as they are defined in the database. This is because due to some technical restrictions, the names in SQL database sometimes might slightly differ from their names in the Dynamics AX application.

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

The value of the sqlStatement variable that holds the prepared SQL query depending on your environment is as follows:

SELECT ACCOUNTNUM, NAME FROM VENDTABLE
    JOIN DIRPARTYTABLE ON VENDTABLE.PARTY = DIRPARTYTABLE.RECID
    WHERE DATAAREAID = 'usmf' AND BLOCKED = 0

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

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

Another thing that needs to be mentioned 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 also can be used to connect to 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.

There's more...

The standard Dynamics AX application provides an alternate way of building direct SQL statements by using a set of SQLBuilder classes. By using these 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 we did before.

First, in AOT, create another class named VendTableSqlBuilder using the following code snippet:

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 the preceding 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(). This method returns a reference to the vendTable object of the type SQLBuilderTableEntry, which corresponds to a table node in a SQL query. We also add DirPartyTable as a joined table.

Then, we create a number of field objects of the SQLBuilderFieldEntry type to be used later and two ranges to show only this company account and only the 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 will give us the results that are similar to the ones we got earlier.

 

Enhancing the data consistency check


It is highly recommended for system administrators to run the standard Dynamics AX data consistency checks from time to time, which can be found by navigating to System administration | Periodic | Database | Consistency check, to evaluate the system's data integrity. This function finds orphan data, validates parameters, and checks many other things.

In this recipe, we will see how we can enhance the standard Dynamics AX consistency check to include more tables in their data integrity validation.

Getting ready

Before we start, we need to create an invalid setup in order to make sure that we can simulate data inconsistency. Navigate to Fixed assets | Setup | Value models and create a new model, for instance, TEST, as shown in the following screenshot:

Navigate to Fixed assets | Setup | Fixed asset posting profiles and under the Ledger accounts tab page, create a new record with the newly created value model for any of the posting types, as shown here:

Go back to the Value models form and delete the previously created value model. Now, we have a nonexistent value model in the fixed asset posting settings.

How to do it...

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

  1. In the AOT, create a new class named AssetConsistencyCheck with the following code snippet:

    class AssetConsistencyCheck extends SysConsistencyCheck
    {
    }
    
    client server static ClassDescription description()
    {
        return "Fixed assets";
    }
    
    client server static HelpTxt helpText()
    {
        return "Consistency check of the fixed asset module";
    }
    
    Integer executionOrder()
    {
        return 1;
    }
    
    void run()
    {
        this.kernelCheckTable(tableNum(AssetLedgerAccounts));
    }
  2. Navigate to System administration | Periodic | Database | Consistency check, select the newly created Fixed assets option from the Module drop-down list, and click on OK to run the check, as shown here:

  3. Now, the message displayed in the Infolog window should complain about the missing value model in the fixed assets posting settings, as shown in the following screenshot:

How it works...

The consistency check in Dynamics AX validate 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 validating 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:

  • description(): This provides a name to the consistency check form.

  • helpText(): This displays some explanation about the check.

  • executionOrder(): This determines where the check is located in the list.

  • run(): This holds the code to perform the actual checking. Here, we use the kernelCheckTable() method, which validates the given table.

There's more...

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

static void main(Args _args)
{
    SysConsistencyCheckJob consistencyCheckJob;
    AssetConsistencyCheck  assetConsistencyCheck;

    consistencyCheckJob = new SysConsistencyCheckJob(
        classIdGet(assetConsistencyCheck));

    if (!consistencyCheckJob.prompt())
    {
        return;
    }

    consistencyCheckJob.run();
}
 

Exporting data to an XML file


Briefly, XML defines a set of rules for encoding documents electronically. It allows the creation of all kinds of structured documents that can be exchanged between systems. In Dynamics AX, XML files are widely used across the application.

Probably the main thing that is associated with XML in Dynamics AX is the Application Integration Framework (AIF). It is an infrastructure that allows you to expose business logic or exchange data with other external systems. The communication is done by using XML-formatted documents. By using the existing XML framework's application classes prefixed with Axd, you can export or import data into the system. It is also possible to create new Axd classes using AIF Document Service Wizard from the Tools menu to support the export and import of newly created tables.

Dynamics AX also contains a set of application classes prefixed with Xml, such as XmlDocument and XmlNode. Basically, these classes are wrappers around the System.XML namespace in the .NET Framework.

In this recipe, we will create a new simple XML document by using the Xml classes in order to show the basics of XML. We will create a file with the data from the main account table and save it as an XML file.

How to do it...

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

  1. Open the AOT and create a new class named CreateXmlFile with the following code snippet:

    class CreateXmlFile
    {
    }
    
    static void main(Args _args)
    {
        XmlDocument doc;
        XmlElement  nodeXml;
        XmlElement  nodeTable;
        XmlElement  nodeAccount;
        XmlElement  nodeName;
        MainAccount mainAccount;
        #define.filename(@'C:\Temp\accounts.xml')
    
        doc     = XmlDocument::newBlank();
    
        nodeXml = doc.createElement('xml');
    
        doc.appendChild(nodeXml);
    
        while select RecId, MainAccountId, Name from mainAccount
            order by mainAccountId
            where mainAccount.LedgerChartOfAccounts ==
                LedgerChartOfAccounts::current()
        {
            nodeTable = doc.createElement(tableStr(MainAccount));
    
            nodeTable.setAttribute(
                fieldStr(MainAccount, RecId),
                int642str(mainAccount.RecId));
    
            nodeXml.appendChild(nodeTable);
    
            nodeAccount = doc.createElement(
                fieldStr(MainAccount, MainAccountId));
    
            nodeAccount.appendChild(
                doc.createTextNode(mainAccount.MainAccountId));
    
            nodeTable.appendChild(nodeAccount);
    
            nodeName = doc.createElement(
                fieldStr(MainAccount, Name));
    
            nodeName.appendChild(
                doc.createTextNode(mainAccount.Name));
    
            nodeTable.appendChild(nodeName);
        }
    
        doc.save(#filename);
    
        info(strFmt("File %1 created.", #filename));
    }
  2. Run the class. The XML file named accounts.xml will be created in the specified folder. Open the XML file using any XML editor or viewer, such as Microsoft Internet Explorer, and review the created XML structure, as shown in the following screenshot:

How it works...

We start the recipe by creating a new XmlDocument using the 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 all the main accounts in the current chart of accounts and perform the following tasks for each record:

  • Create a new XmlElement node, which is named exactly the same as the table name, and add this node to the root node.

  • Create a node that represents the account number field and a child node representing its value. The account number node is created using createElement() and its value is created using createTextNode(). The createTextNode() method basically adds a value as text with no XML tags.

  • Add the account number node to the table node.

  • Create a node representing the account name field and a child node representing its value.

  • Add the account name node to the table node.

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

In this way, we create an XML document that contains the current chart of accounts.

 

Importing data from an XML file


In Dynamics AX, an XML file is imported in a similar way as it is exported. In this recipe, we will continue using the XML application classes. We will create a new class that reads XML files and displays their content on the screen. As the source file, we will use the previously created accounts.xml file.

How to do it...

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

  1. Open the AOT and create a new class named ReadXmlFile with the following code snippet. Use the document created in the previous recipe:

    class ReadXmlFile
    {
    }
    static void main(Args _args)
    {
        XmlDocument doc;
        XmlNodeList data;
        XmlElement  nodeTable;
        XmlElement  nodeAccount;
        XmlElement  nodeName;
        #define.filename(@'C:\Temp\accounts.xml')
    
        doc  = XmlDocument::newFile(#filename);
    
        data = doc.selectNodes('//'+tableStr(MainAccount));
    
        nodeTable = data.nextNode();
    
        while (nodeTable)
        {
            nodeAccount = nodeTable.selectSingleNode(
                fieldStr(MainAccount, MainAccountId));
    
            nodeName = nodeTable.selectSingleNode(
                fieldStr(MainAccount, Name));
    
            info(strFmt(
                "%1 - %2",
                nodeAccount.text(),
                nodeName.text()));
    
            nodeTable = data.nextNode();
        }
    }
  2. Run the class. The Infolog window will display the contents of the accounts.xml file on the screen, as shown here:

How it works...

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

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

  • Get an account number node as an XmlElement.

  • Obtain an account name node as an XmlElement.

  • Display the text of both the nodes in the Infolog window. Normally, this should be replaced with more sensible code to process the data.

  • Get the next list element.

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

 

Creating a comma-separated value file


CSV files are widely used across various systems. Although nowadays modern systems use XML formats for data exchange, CSV files are still popular because of the simplicity of their format.

Normally, the data in a file is organized, so one line corresponds to one record and each line contains a number of values, normally separated by commas. Record and value separators can be any other symbol, depending on the system requirements.

In this recipe, we will learn how to create a custom CSV file from the code. We will also export a list of main accounts—account number and name from the current chart of accounts.

How to do it...

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

  1. Open the AOT and create a new class named CreateCommaFile with the following code snippet:

    class CreateCommaFile
    {
    }
    static client void main(Args _args)
    {
        CommaTextIo         file;
        container           line;
        MainAccount         mainAccount;
        #define.filename(@'C:\Temp\accounts.csv')
        #File
    
        file = new CommaTextIo(#filename, #io_write);
    
        if (!file || file.status() != IO_Status::Ok)
        {
            throw error("File cannot be opened.");
        }
    
        file.outRecordDelimiter('\r\n');
        file.outFieldDelimiter(',');
    
        while select MainAccountId, Name from mainAccount
            order by MainAccountId
            where mainAccount.LedgerChartOfAccounts ==
                LedgerChartOfAccounts::current()
        {
            line = [
                mainAccount.MainAccountId,
                mainAccount.Name];
            file.writeExp(line);
        }
    
        info(strFmt("File %1 created.", #filename));
    }
  2. Run the class. A new file named accounts.csv should be created in the specified folder. Open this file with Notepad or any other text editor to view the results, as shown in the following screenshot:

How it works...

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. Here, we also declare the standard #File macro, which contains a number of file-handling definitions like modes, such as #io_read, #io_write, #io_append, file types, and delimiters.

Then, we create a new CSV file by calling the new() method on a standard CommaTextIo class. It accepts two parameters: filename and mode. For mode, we use #io_write from the #File macro to make sure that 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 whether 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 you to process data with various language-specific symbols.

Next, we specify the delimiters for the output file. As the name suggests, by default, a CSV file contains a number of rows separated by line breaks and a number of values in each line separated by commas. The two methods outRecordDelimiter() and outFieldDelimiter() allow you to specify those delimiters for output files. In this example, we called these two methods just for demonstration purposes as the values we specify are the default values anyway.

Finally, we loop through all the main accounts in the current chart of accounts, store all the account numbers and their names into a container, and write them to the file using the writeExp() method.

In this way, we create a new CSV file with a list of main accounts inside.

There's more...

You probably might have 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 this, 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
{
}

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.");
    }

    file.outRecordDelimiter('\r\n');
    file.outFieldDelimiter(',');

    while select MainAccountId, Name from mainAccount
        order by MainAccountId
        where mainAccount.LedgerChartOfAccounts ==
            LedgerChartOfAccounts::current()
    {
        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 that we match the requirements.

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

 

Reading a comma-separated value file


Besides data import/export, CSV files can be used for integration between systems. It is probably the most simple integration approach, when one system generates CSV files in some network folder and another system reads those files at specified intervals. Although this is not a very sophisticated real-time integration, in most cases, it does the job and does not require any additional components, such as Dynamics AX AIF or similar.

In this recipe, you will learn how to read CSV files from the code. As an example, we will process the file created in the previous recipe.

How to do it...

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

  1. In the AOT, create a new class named ReadCommaFile with the following code snippet:

    class ReadCommaFile
    {
    }
    
    static client void main(Args _args)
    {
        CommaTextIo         file;
        container           line;
        #define.filename(@'C:\Temp\accounts.csv')
        #File
    
        file = new CommaTextIo(#filename, #io_read);
    
        if (!file || file.status() != IO_Status::Ok)
        {
            throw error("File cannot be opened.");
        }
    
        file.inRecordDelimiter('\r\n');
        file.inFieldDelimiter(',');
    
        line = file.read();
    
        while (file.status() == IO_Status::Ok)
        {
            info(con2Str(line, ' - '));
            line = file.read();
        }
    }
  2. Run the ReadCommaFile class to view the file's content, as shown in the following screenshot:

How it works...

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 ensure that the existing file is opened for reading only. We also perform the same validations to make sure that the file object is correctly created; otherwise, an error message is displayed.

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 changes from IO_Status::OK to any other status, which means we reached the end of the file or something unexpected had happened. 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. The data in the file is then simply shown on the screen using the standard global info() function in conjunction with the con2Str() function, which converts a container to a string.

There's more...

File reading can also be executed in a way similar to file writing on the server tier in order to improve performance. The client modifier has to be changed to server, and the code with the FileIoPermission class has to be added to fulfill the code access security requirements.

The modified class will look similar to the following code snippet:

class ReadCommaFileServer
{
}

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.");
    }

    file.inRecordDelimiter('\r\n');
    file.inFieldDelimiter(',');

    line = file.read();

    while (file.status() == IO_Status::Ok)
    {
        info(con2Str(line, ' - '));
        line = file.read();
    }

    CodeAccessPermission::revertAssert();
}
 

Using the date effectiveness feature


Date effectiveness is a feature in Dynamics AX 2012 that allows developers to easily create date range fields. Date ranges are used to define record validity between the specified dates, for example, defining employee contract dates.

This feature significantly reduces the amount of time that developers spend on writing code and also provides a consistent approach to implementing data range fields.

This recipe will demonstrate the basics of the date effectiveness feature. We will implement the date effectiveness functionality for e-mail templates on the E-mail templates forms.

How to do it...

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

  1. In the AOT, find the SysEmailTable table and change its property, as shown in the following table:

    Property

    Value

    ValidTimeStateFieldType

    Date

  2. Note the two new fields that are automatically added to the table, as shown in the following screenshot:

  3. Add the newly created ValidFrom and ValidTo fields to the existing emailIdIdx index and change its properties, as shown in the following table:

    Property

    Value

    AlternateKey

    Yes

    ValidTimeStateKey

    Yes

    ValidTimeStateMode

    NoGap

  4. Save the table. The system should prompt you to synchronize the table. Click on Continue, as shown here:

  5. After the changes, the index should look similar to the following screenshot:

  6. Next, add the ValidFrom and ValidTo fields to the table's Identification group, as shown in the following screenshot:

  7. In the AOT, find the SysEmailTable form, refresh it using the Restore command from the right-click context menu, then locate its data source named SysEmailTable and change its properties, as follows:

    Property

    Value

    ValidTimeStateAutoQuery

    DateRange

    ValidTimeStateUpdate

    Correction

  8. In order to test the results, navigate to Organization administration | Setup | E-mail templates and notice the newly created fields: Effective and Expiration. Try creating records with the same E-mail ID and overlapping date ranges—you will notice how the system is proposing to maintain valid date ranges, as shown in the following screenshot:

How it works...

We start the recipe by setting the ValidTimeStateFieldType property of SysEmailTable to Date. 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 index's properties.

We set the AlternateKey property to Yes in order to ensure that this index is part of an alternate key.

We set the ValidTimeStateKey property to Yes in order to specify that the index is used to determine valid date ranges.

We also set the ValidTimeStateMode property to NoGap in order to ensure that e-mail templates with the same identification number can be created within continuous periods only. This property can also be set to Gap, allowing noncontinuous date ranges.

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 in the form's Overview grid. We also change a few properties of the SysEmailTable data source, as follows:

  1. Set the ValidTimeStateAutoQuery property to DateRange in order to ensure that all the records are visible. The default AsOfDate value can be used if you want to display only the records for the current period.

  2. Set the ValidTimeStateUpdate property to Correction, allowing the system to automatically adjust the dates of the associated records.

There's more...

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 this, let's modify the previously used SysEmailTable form and add the following code snippet at 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, as shown in the following screenshot:

About the Author

  • Mindaugas Pocius

    Mindaugas Pocius is currently a freelance Dynamics AX technical and functional consultant and trainer at DynamicsLab Limited (www.dynamicslab.com). The company specializes in providing development, consulting, and training services for Microsoft Dynamics AX resellers and customers.

    Mindaugas started his IT consulting career back in 2001 while he was still pursuing his master's degree in information technology at Kaunas University of Technology in Lithuania. Since then, he has become a recognized Microsoft Certified Professional for Dynamics AX in all major areas, such as development, configuration and installation, financials, projects, and trade and logistics. He is also a Certified Microsoft Trainer for Dynamics AX and has delivered numerous Dynamics AX trainings.

    Mindaugas has participated in over 20 Dynamics AX implementations. He has held a wide range of development, consulting, and lead roles while always maintaining the significant role of a business application developer.

    In December 2009, he released his first book, Microsoft Dynamics AX 2009 Development Cookbook, and then in May 2012, its second edition, Microsoft Dynamics AX 2012 Development Cookbook, both of which are published by Packt Publishing.

    Browse publications by this author

Latest Reviews

(2 reviews total)
Never received it
Concise, just what I need
Book Title
Unlock this full book FREE 10 day trial
Start Free Trial