Date Effective tables with ValidTimeState and Dynamics AX 2012

You may have noticed the new ValidTimeStateFieldType property on tables in AX 2012. The enum values are None, Date and UtcDateTime. This is part of the new & cool Date Effective Table framework that comes with Dynamics AX 2012. The idea is to minimize the effort for managing period gaps, overlapping periods, period validation, etc. So, let's get down to how it works.

• We create new table called DEV_ValidTimeState and we add new field named ItemId
• We set the ValidTimeStateFieldType table property to UtcDateTime. At this point AX automatically creates 2 new fields: ValidFrom and ValidTo.   
   
• Now we need to create new unique index, which should include the fields ItemId, ValidFrom, and ValidTo
• We set the following index properties  
• AllowDuplicates to No.
• Alternate Key to Yes.
• ValidTimeStateKey to Yes. 
• ValidTimeStateMode to Gap. 
• Now let's insert some records.

static void createValidTimeState(Args _args)
{
    DEV_ValidTimeState table;
    ;

    delete_from table;

    table.clear();
    table.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);

    table.ValidFrom = DateTimeUtil::newDateTime(1\1\2012, 0);
    table.ValidTo = DateTimeUtil::maxValue();
    table.ItemId = '1000';

    table.insert();

    table.clear();
    table.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);

    table.ValidFrom = DateTimeUtil::newDateTime(6\6\2012, 0);
    table.ValidTo = DateTimeUtil::maxValue();
    table.ItemId = '1001';

    table.insert();
}

• Here is how to select and update records from the table. Please note the use of the validTimeStatekeyword with select statements and there is a new xRecord method calledvalidTimeStateUpdateMode.

static void updateValidTimeState(Args _args)
{
    DEV_ValidTimeState table;
    utcDateTime fromDateTime, toDateTime;
    ;

    fromDateTime = DateTimeUtil::newDateTime(3\3\2012, 0);
    toDateTime   = DateTimeUtil::maxValue();


    select validTimeState(fromDateTime) table;

    info(table.ItemId);

    select validTimeState(fromDateTime) * from table;

    info(table.ItemId);

    select validTimeState(fromDateTime) ItemId from table;

    info(table.ItemId);

    select validTimeState(fromDateTime, toDateTime) ItemId from table;

    info(table.ItemId);

    ttsBegin;

    while select forUpdate validTimeState(fromDateTime) table
    {
        table.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
        table.ItemId = '1002';
        table.update();

    }

    ttsCommit;
}

• The query framework was updated to support the new Time Effectiveness feature. Here is the code sample:

static void queryValidTimeState(Args _args)
{
    DEV_ValidTimeState      table;
    utcDateTime             fromDateTime, toDateTime;       
    Query                   q;
    QueryRun                qr;   
    QueryBuildDataSource    qbds;
    ;

    fromDateTime = DateTimeUtil::newDateTime(3\3\2012, 0);
    toDateTime   = DateTimeUtil::maxValue();

    
    q = new Query();    
    
    qbds = q.addDataSource(tableNum(DEV_ValidTimeState));
    
    q.validTimeStateAsOfDateTime(fromDateTime);
    
    qr = new QueryRun(q);
    
    while(qr.next())
    {
        table = qr.get(tableNum(DEV_ValidTimeState));
        info(table.ItemId);
    } 
} 

To sum up, in AX 2012 we have new feature that allows us to manage the time periods associated with an entity. We get all the benefits of validation and period gaps management for free (that's sweet). In order to support the new feature some changes have been introduced:

• All tables now have new property called ValidTimeStateFieldType
• Table indexes now have new properties
• ValidTimeStateMode 
• ValidTimeStateKey
•  Alternate Key should be set to Yes
• The kernel class xRecord and all tables now have the validTimeStateUpdateMode method. 
• There is new system enum ValidTimeStateUpdate with the following values:
• Correction – the ValidFrom or ValidTo values of existing rows must be modified to keep the date effective data valid after the update completes. 
•  CreateNewTimePeriod – a new record is inserted into the table to maintain the validity of the date effective data after the update completes. 
• EffectiveBased – forces the update process to switch to CreateNewTimePeriod for each row that spans the current date-time; otherwise to switch to Correction. 
• The kernel class Query now has 4 new methods:
• validTimeStateAsOfDate
• validTimeStateAsOfDateTime
• validTimeStateDateRange 
• validTimeStateDateTimeRange

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_recordset, update_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.

Types of Form Templates in AX 2012

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.