Wednesday, November 19, 2014

Insert_recordset, Update_recordset, and delete_from single transaction command.

In AX, you can manipulate a set of data by sending only one command to the database. This way of manipulating data improves performance a lot when trying to manipulate large sets of records. The commands for manipulations are insert_recordsetupdate_recordset, and delete_from. With these commands, we can manipulate many records within one database transaction, which is a lot more efficient than using the insert, update, or delete methods.
Lets discuss about these commands one by one.

  • Insert_recordset
    A very efficient way of inserting a chunk of data is to use the insert_recordset operator, as compared to using the insert() method. The insert_recordset operator can be used in two different ways; to either copy data from one or more tables to another, or simply to add a chunk of data into a table in one database operation.
    The first example will show how to insert a chunk of data into a table in one database operation. To do this, we simply use two different table variables for the same table and set one of them to act as a temporary table. This means that its content is not stored in the database, but simply held in memory on the tier where the variable was instantiated.
    static void Insert_RecordsetInsert(Args _args)
    {
    CarTable carTable;
    CarTable carTableTmp;

    /* Set the carTableTmp variable to be a temporary table.
    This means that its contents are only store in memory
    not in the database.
    */
    carTableTmp.setTmp();
    // Insert 3 records into the temporary table.
    carTableTmp.CarId = “200″;
    carTableTmp.CarBrand = “MG”;
    carTableTmp.insert();
    carTableTmp.CarId = “300″;
    carTableTmp.CarBrand = “SAAB”;
    carTableTmp.insert();
    carTableTmp.CarId = “400″;
    carTableTmp.CarBrand = “Ferrari”;
    carTableTmp.insert();
    /* Copy the contents from the fields carId and carBrand
    in the temporary table to the corresponding fields in
    the table variable called carTable and insert the chunk
    in one database operation.
    */
    Insert_Recordset carTable (carId, carBrand)
    select carId, carBrand from carTableTmp;
    }
    The other, and perhaps more common way of using the insert_recordset operator, is to copy values from one or more tables into new records in another table. A very simple example on how to do this can be to create a record in the InventColor table for all records in the InventTable.
    static void Insert_RecordsetCopy(Args _args)
    {
    InventColor inventColor;
    InventTable inventTable;
    This material is copyright and is licensed for the sole use by ALESSANDRO CAROLLO on 18th December
    Chapter 6
    [ 169 ]
    InventColorId defaultColor = “B”;
    Name defaultColorName = “Blue”;
    ;
    insert_recordset inventColor (ItemId, InventColorId, Name)
    select itemId, defaultColor, defaultColorName
    from inventTable;
    }
    The field list inside the parentheses points to fields in the InventColor table.
    The fields in the selected or joined tables are used to fill values into the fields in
    the field list.

  • Update_recordset
    The update_recordset operator can be used to update a chunk of records in a table in one database operation. As with the insert_recordset operator the update_recordset is very efficient because it only needs to call an update in the database once.
    The syntax for the update_recordset operator can be seen in the next example:
    static void Update_RecordsetExmple(Args _args)
    {
    CarTable carTable;
    ;
    info(“BEFORE UPDATE”);
    while select carTable
    where carTable.ModelYear == 2007
    {
    info(strfmt(“CarId %1 has run %2 miles”,
    carTable.CarId, carTable.Mileage));
    }
    update_recordset carTable setting Mileage = carTable.Mileage + 1000
    where carTable.ModelYear == 2007;
    info(“AFTER UPDATE”);
    while select carTable
    where carTable.ModelYear == 2007
    {
    info(strfmt(“CarId %1 has now run %2 miles”,
    carTable.CarId, carTable.Mileage));
    }
    }

    When this Job is executed it will print the following messages to the Infolog: 
    Notice that no error was thrown even though the Job didn’t use selectforupdate, ttsbegin, and ttscommit statements in this example. The selectforupdate is implicit when using the update_recordset, and the ttsbegin and ttscommit are not necessary when all the updates are done in one database operation. However, if you were to write several update_recordset statements in a row, or do other checks that should make the update fail, you could use ttsbegin and ttscommit and force a ttsabort if the checks fail.

  • Delete_from
    As with the insert_recordset and update_recordset operators, there is also an option for deleting a chunk of records. This operator is called delete_from and is used as the next example shows:

    static void Delete_FromExample(Args _args)
    {
    CarTable carTable;

    delete_from carTable
    where carTable.Mileage == 0;
    }

    Thanks for reading the post, any comments or questions are welcomed. Keep visiting the blog.

Monday, November 17, 2014

Types of Form Templates in AX 2012

CreateNewFormFromTemplate.jpg


Form Templates Some form templates are available to help create the correct form type with the appropriate controls, and to keep the design consistent across the application. The following table shows the available form templates and where they should be used.


Examples of Templates
The following table gives examples of each form template

To create a form using a template, right-click the Forms node in the AOT, select New Form From Template, and then select the template. Try to create each template and examine the controls and design that is created.


There are seven different predefined form templates in ax 2012.
  • ListPage
  • DetailsFormMaster
  • DetailsFormTransaction
  • SimpleListDetails
  • SimpleList
  • TableOfContents
  • Dialog
  • DropDialog
ListPage - A list page is a form that displays a list of data related to a particular entity or business object. A list page provides provisions for displaying data and taking actions on this data. Every module has at least a couple of list pages. List pages are further classified as primary and secondary list pages. A secondary list page will only display a subset of data from the primary list page. Example, CustTableListPage, VendTableListPage, ProjProjectsListPage. Best practice is to have ListPage as a suffix in the name of the form for all list pages.

DetailsFormMaster: This template is used for forms which display data for stand-alone entities or business objects. Example includes Customers, Vendors, and Projects etc. If you look at the forms for these, i.e. CustTable, VendTable, ProjTable, their style property will be set to DetailsFormMaster.
DetailsFormTransaction: This template is used for forms which display data for entities which have child records associated with it. In other words, if the data being displayed is header-lines type in nature, use this template. Example, Sales orders, Purchase orders etc. If you look at the style property of SalesTable, VendTable, their properties will be set to DetailsFormTransaction.
SimpleListDetails - This template is used to display primary fields in a list and detailed data in a tab page. This style is useful to view all records in a form and selecting a particular record will bring up their details. Example includes HmSkillMapping, etc.
SimpleList - This template is a very basic form which displays data in a grid. No extra or fancy detail is displayed. This style is best suited for forms where data being shown is not very detailed in nature or has limited fields. Example includes AifAction, etc.
TableOfContents - This template is the new style which should be adopted for all parameter forms in Dynamics AX 2012. Take a look at any parameters form and its style property will be set to TableOfContents. This style sets all the tabs as a hot link in the left hand side navigation pane. Clicking on the link will bring up the controls on that tab page. This style is a very neat and appealing UI design which is surely welcome. Example includes Custparameters, vendparameters, etc.
Dialog - This template is used on forms which show static data or are intended to capture some user input for further actions. These forms are mostly modal in nature and should be dismissed before any further actions can be taken.
DropDialog - This template is used for forms that are used to gather quick user inputs to perform an action. Drop dialog forms are generally attached to an action pane button. They appear to be             dropping from the menu button when clicked. Example includes HcmWorkerNewWorker,                                                               HcmPositionWorkerAssignmentDialog

Whenever going for a new form development, always ensure that you use the template to create a new form. The template almost does 40% of your design work for you. All you have to do is add the data sources and fields and add business logic.
Microsoft has taken a lot of customer feedback and invested a lot in the UI design. There are reasons why the buttons and navigations are laid out like they are. If you are deviating from the recommended best practices, not only are you introducing best practice deviations, you may be complicating the design and navigation for your users.
In case, you already have designed forms without using the pre-defined templates, you can run the form style best practices checker from add-ins. This will list out all deviations your form has and you can fix them by clicking a button.

Tuesday, November 11, 2014

Dynamics AX Caching

Cache Location
Caches are used on both the client and the server. The Microsoft Dynamics AX runtime manages the cache by removing old records when new records are added to the cache.
Client Cache
A client-side cache can be used only by the client. The client cache is used when a select is executed from the client tier. If no record is found in the client cache, the client then searches the server cache for the record. If the record isn't located in the server cache, it's retrieved from the database. The maximum number of records maintained in a client cache is 100 records per table for a given company.
Server Cache
A server-side cache can be used by any connection to the server. The server cache is used when aselect is executed on the server tier. If no record is found in the cache, it's retrieved from the database. The maximum number of records maintained in a server cache is 2,000 records per table for a given company.
Record Caching
Microsoft Dynamics AX database record caching is a performance-enhancing feature that helps avoid database access when it's not strictly necessary. Retrieving database records from memory instead of the database significantly speeds up data access. Caching can reduce the performance penalty for repetitively accessing the same database records.
 Types of Caching

Caching is transparent to the application; however, it's important to know how caching works to optimize its performance in Microsoft Dynamics AX. Following are the types of caching:
  • Single-record
  • Set-based
Single-record caching has the following characteristics:
  • Defined at design time
  • Moves records to the cache based on the table's CacheLookup property and the type ofSELECT statement that is used to retrieve the record

Set-based caching has the following characteristics:
  • Defined either at design time or in X++ code
  • Moves sets of records to the cache
  • Implemented either through the table's CacheLookup property or in code by using theRecordViewCache class
 Single-Record Caching
Record caching is enabled for a table when all the following statements are true:
  • The CacheLookup property on the table is enabled by setting it to one of the following values:
·         notInTTS
·         Found
·         FoundAndEmpty

  • The table's PrimaryIndex property is set to a unique index that exists on the table. The RecId index does not qualify as a caching index unless you set the table'sPrimaryIndex property to this index.
  • The record buffer disableCache method has not been called with a parameter of true.
The fields in the table's unique index make up the caching key. A record is placed in the cache when the following criteria are met:
  • The table is cached by setting the CacheLookup property to notInTTS, Found, or FoundAndEmpty.
  • The SELECT statement that selects the records uses an equal operator (==) on the caching key. The fields in the WHERE clause of the SELECT statement match the fields in the index referenced by the table's PrimaryIndex property.
The table's CacheLookup property defines how and when records are cached as shown in the following table.
CacheLookup Property Value
Result
None
No data is cached or retrieved from the cache for this table.
This property value should be used for tables that are heavily updated or where it's unacceptable to read outdated data.
NotInTTS
All successful caching key selects are cached.
When in a transaction (after ttsBegin), no caches made outside the transaction are used. When inside a transaction, the record is read once from database and subsequently from cache. The record is select-locked when read in a transaction, which ensures that the record cached is not updated while the transaction is active.
A typical example of the NotInTTS property is the CustTable in the Microsoft Dynamics AX standard application. It's acceptable to read outdated data from the cache outside a transaction, but when data is used for validation or creating references, it is ensured that the data is real-time.
Found
All successful caching key selects are cached. All caching key selects are returned from the cache if the record exists there. A selectforUpdate in a transaction forces reading from the database and replaces the record in the cache.
This is typically used for static (lookup) tables, such as Unit, where the record usually exists.
FoundAndEmpty
All selects on caching keys are cached, including selects that are not returning data.
All caching key selects are returned from caching if the record exists there, or the record is marked as nonexistent in the cache. A selectforUpdate in a transaction forces reading from the database and replaces the record in the cache.
An example of FoundAndEmpty record caching is in the Discount table in the Microsoft Dynamics AX standard application. By default, the Discount table has no records. By using a FoundAndEmpty cache on this table, the keys that are queried for but not found are stored in the cache. Subsequent queries for these same non-existent records can be answered from the cache without a round trip to the database.
EntireTable
Creates a set-based cache on the server. The entire table is cached as soon as at least one record is selected from the table.

The Found and FoundAndEmpty caches cross transaction boundaries. The NotInTTS cache is newly created inside a transaction. This example, modified for the purposes of this topic, demonstrates how records are retrieved from the cache when the table's CacheLookup property is set to NotInTTS, and the PrimaryIndex property is set to a unique index on the AccountNum field.
static void NotInTTSCache(Args _args)
{
    CustTable custTable;
    ;
// The query looks for records in the cache.
// If records don't exist, the query accesses the database.
    select custTable
        where custTable.AccountNum == '4000';
    // The transaction starts.
    ttsbegin;
    // The cache is not used. The query accesses the database
    // and records are placed in the cache.
    select custTable
        where custTable.AccountNum == '4000';

    // The query uses the database because
    // the forupdate keyword is used.
    select forupdate custTable
        where custTable.AccountNum == '4000';
    // The query uses the cache and not the database.
    select custTable
        where custTable.AccountNum == '4000';
    // The query uses the cache because
    // the forupdate keyword was used previously.
    select forupdate custTable
        where custTable.AccountNum == '4000';

    // The transaction is committed.
    ttscommit;

    // The query will use the cache.
    select custTable
        where custTable.AccountNum == '4000';
}
If the table CacheLookup property was set to Found or FoundAndEmpty, the first select statement inside the transaction (after the TTSBegin statement) would retrieve the record from the cache.









Set-Based Caching
In Microsoft Dynamics AX, groups of records can be cached all at once with set-based caching. Set-based caching can be implemented in two ways:
  • At design time, by setting the table's CacheLookup property to EntireTable.
  • In code, by using the RecordViewCache class.

EntireTable Cache
*         
When you set a table's CacheLookup property to EntireTable, all the records in the table are placed in the cache after the first select. This type of caching follows the rules of single record caching in which the SELECT statement WHERE clause fields must match those of the unique index defined in the table'sPrimaryIndex property.
The EntireTable cache is located on the server and is shared by all connections to the Application Object Server (AOS). If a select is made on the client tier to a table that is EntireTable cached, it first looks in its own cache and then searches the server-side EntireTable cache. An EntireTable cache is created for each table for a given company. If you have two selects on the same table for different companies the entire table is cached twice.
Joins that include an EntireTable cached table are only performed against the cached copy when all tables participating in the join are EntireTable cached. Otherwise a database join is performed.
Important Note:
Avoid using EntireTable caches for large tables because once the cache size reaches 128 KB the cache is moved from memory to disk. A disk search is much slower than an in-memory search.
Flushing the Cache

An EntireTable cache is flushed whenever an insert, update, or delete is made to the table. At the same time, the AOS notifies other AOSs that their caches of the same table must be flushed. After the cache is flushed, a subsequent select on the table causes the entire table to be cached again. Therefore, avoid caching any table that's frequently updated. Regardless of when updates are made, EntireTable caches are flushed every 24 hours by the AOS.
RecordViewCache Cache
*         
Set-based caching is implemented in code by using the RecordViewCache class. You must first create a record buffer using the nofetch statement and then pass the record buffer to the RecordViewCacheclass when it's instantiated.
The cache is created on the server and is only accessible by the process that creates the cache object. Once the cache is instantiated, all select statements are issued against the cache, as shown in the following
static void RecordViewCache(Args _args)
{
    CustTrans       custTrans;
    RecordViewCache recordViewCache;
    ;
    // Define records to cache.
    select nofetch custTrans
       where custTrans.AccountNum == '4000';

    // Cache the records.
    recordViewCache = new RecordViewCache(custTrans);

    // Use cache.
    select firstonly custTrans
        where custTrans.AccountNum == '4000' &&
              custTrans.CurrencyCode == 'USD';
}

Due to concurrency issues, the forUpdate keyword on the instantiating X++ SELECT statement should only be used when all of the records in the result set will be updated. Otherwise it's a better strategy to use select forUpdate only for the records that are to be updated.
The RecordViewCache class is used in a select when the select is from a table that's cached, the select statement doesn't participate in a join and the select WHERE clause matches the WHERE clause with which the RecordViewCache was instantiated.
The cache created by the RecordViewCache class stores records in a linked list. Therefore Microsoft Dynamics AX searches the cache sequentially for records that match the search criteria. If the SELECTstatement contains an ORDER BY clause, a temporary index is placed on the cache and the runtime uses the index when searching records.