Valid time state table in ax 2012

A valid time state table property is provide you maintenance of data for which changes must be tracked at different points in time. For example where benefits become effective and expire at certain times for employee.

On a table in the AOT, you can set the “ValidTimeStateFieldType” property to “Date” or “UtcDataTime” make it a valid time state table. When you set property system automatically will add the “ValidFrom” and” ValidTo” columns which track a date range in each row. The system guarantees that the values in these date or date-time fields remain valid by automatically preventing overlap among date ranges.

Note:

1. The "ValidTimeStateFieldType" property cannot be set for any table that inherits from another table.
2. Each valid time state table depend on an alternate key index.
3. The “ValidFrom” and “ValidTo” columns can both be of the date data type, or can both be of the utcDateTime data type.
4. In X++ select statement we can use “validTimeState” keyword to filter rows by date or date range. And in Query class we can use “validTimeStateAsOfDateTimeRange ()” methods that provide filtering by date range.

Now we take simple example for understanding this functionality. First we create a new table called “EmployeeTable” and create two fields “EmplD” and Benefits like figure 1.

Figure 1. Create Table.

After, we’ll set table property’s “ValidTimeStateFieldType – Date”. Then system will automatically create two new date fields called "ValidFrom" and "ValidTo" like figure 2. Next, we'll add an index on the table, containing our EmpId identifier and the two date fields.

Figure 2. Create index.

On “EmpIdx” index set property “AllowDuplicates – No” and “AlternateKey-Yes”. After this we'll set “ValidTimeStateKey – Yes” that will enable you to set “ValidTimeStateMode- NoGap/Gap” (default to NoGap) and we’ll set “NoGap”. The NoGap value tells the system that we do not allow gaps in the date ranges. For example, for first record Jan 1 to Feb 1 and a second record of March 1 to April 1,since there would be a gap between Feb 1 and March 1.

Figure 3. Set Index property.

Now open the table and add new record. Notice how the record defaults to ValidFrom with today's date, and ValidTo set to "never". Give it a benefits  of “Bonus”  and EmpId of “0031” and save.

Figure 4. Add Record.

Now, if you create another new record, it will again default in the same date values. If we enter the EmpId "0031" again and try to save the record (CTRL+S), we’ll get the error "Cannot create a record in EmployeeTable (EmployeeTable). Insert not supported with the values specified for 'Effective' and 'Expiration'. New record overlaps with multiple existing records”. 

Figure 5. Error.

It doesn't allow this overlap of the same dates. So, change the ValidFrom field another date, and when we save the record we’ll get option like “Effective dates for one or more records will be adjusted by this change. Do you wish to continue?” 

Figure 6. Add another record.

If you select yes, you will see your previously created record will be updated so that its “ValidTo” date will be changed from never to a date that connects to your new record “ValidFrom” to one day before.

Figure 7. Change date according to previously created record.

Microsoft Dynamics AX 2012 Reference Tables and Table Groups

This spreadsheet includes the name, table group, table type, whether the table is a system table, whether the table is visible in the Application Object Tree (AOT), and whether the table is shared.
Download 
Microsoft Dynamics AX 2012 Reference: Tables and Table Groups

Multi-tab Page lookup and Group Specific Lookup in Dynamics Ax

A lookup form is a small form that you use to select a value for a control. To open a lookup form, click the lookup button that appears with the control. To select a value, double-click the record that you want to appear in the control. The lookup form often shows several fields that help you find and select the correct value.

Note: The data fields that appear in a lookup form are read-only. You cannot use the lookup form to update the records that appear in the list.

In this section I’ll create different types of lookups in Ax.

1. Form as a lookup with multi-tab Page lookup
2. Group Specific Lookup in dynamics Ax

Group Specific Lookup in dynamics Ax

Step 1: Create a field with Name “ParentLevel” in VendTable string type. Create a form “VendGroupLookup” and drag- drop “VendTable” and “VendGroup” table in data source and in the VendTable data source set property  “JoinSource - VendtGroup”. Create Designs like figure 1.

Figure 1: Create VendGroupLookup form.

Step2 – create a method on “VendTable”

 public static void lookupVendorGroup(FormControl _callingControl, VendGroupId    _vendGroup)    

{

    Query                   query;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    SysTableLookup          lookup;

    ;

    query = new Query();

    lookup = SysTableLookup::newParameters(tablenum(VendTable),_callingControl);

    qbds = query.addDataSource(tablenum(VendTable));

    lookup.addLookupField(fieldnum(VendTable, AccountNum),true);

    lookup.addLookupField(fieldnum(VendTable, Name));

    lookup.addLookupField(fieldnum(VendTable, VendGroup));

    qbr = qbds.addRange(fieldnum(VendTable, VendGroup));

    qbr.value(queryvalue(_vendGroup));

    lookup.parmQuery(query);

    lookup.performFormLookup();

}

Step3 – Go to VendGroupLookup form and go to VendTable data source and go to new created field “ParentLevek” and override lookup method with this code:-

public void lookup(FormControl _formControl, str _filterStr)
    {

    ;

    VendTable::lookupVendorGroup(_formControl, VendGroup.VendGroup);

    super(_formControl, _filterStr);

}

Finally, Open the “VendGroupLookup” form and when you select group “30” you will get record as lookup that has group “30” according to figure 2.

Figure 2: Result.

Form as a lookup with multi-tab Page lookup

Step 1: Create a Form for lookup called “CustVendLookup” with following designs:

\

Figure 1 Create CustVendLookup

Step 2:  Override run() and init() methods on "CustVendLookup" Form and write below code-

                 public void run()
                 {
                         FormStringControl callingControl;
                         boolean           filterLookup;
                         ;
                         callingControl = SysTableLookup::getCallerStringControl(element.args());
                         filterLookup =   SysTableLookup::filterLookupPreRun(callingControl,

                                                                                        AccountNum,CustTable_ds);
                         super();
                         SysTableLookup::filterLookupPostRun(filterLookup,callingControl.text(),

                                                                                       AccountNum,CustTable_ds);
                }

              public void init()

               {

                       super();

                      element.selectMode(AccountNum);

               }

Step 3: Create a EDT called “CustVendLookupEDT” and set property “FromHelp – CustVendLookup” form.

Step 4: Create a table called “CustVendTable”with one field called “FromAsLookup” and set property ExtendedDataType – CustVendLookupEDT”.

Step 5: Create another form called “ChangeLookup” with below designs-

Figure 2 Create second form “ChangeLookup”

Finally, Open the “ChangeLookup” form and click on lookup with FromAsLookup field. And you will see a lookup with two tab pages Customer and Vendor.

Figure 3 : Result.

Use of Joins and LinkType Property with Form data source in Dynamics Ax 2012

A join combines records from two or more tables in a database. A Join is a means for combining fields from two tables by using values common to each. We can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft SQL Server should use data from one table to select the rows in another table. 

In Dynamics Ax, We can add more than one table to a form data source. If one table includes a foreign key that is the primary key of the second table, We can set a join between the two tables. The benefits of joins are:

• If we want to use both data source as single data source.
• If we want to make parent and child relation between the tables.
• If we want to specify how form data source methods run for the tables in the form data source. Form data source methods such as active, next, and executeQuery are directed to the parent table of the join.
• If we want to improve performance when insert, delete, update and select operation implement across all the tables in the join relationship.
• If we want to synchronize navigation between tables. For example, every time that you select a new record, both data sources are updated at the same time.

Types of linkType property in Ax:

• Active-The child data source is updated immediately when a new record in the parent data source is selected. Continuous updates consume lots of resources.
• Delayed-A pause is inserted before linked child data sources are updated. This enables faster navigation in the parent data source because the records from child data sources are not updated immediately. For example, the user could be scrolling past several orders without immediately seeing each order lines.
• Passive-Linked child data sources are not updated automatically. Updates of the child data source must be programmed on the active method of the master data source.
• InnerJoin-Selects records from the main table that have matching records in the joined table and vice versa.
• OuterJoin-Selects records from the main table whether they have matching records in the joined table.
• ExistJoin- Selects a record from the main table for each matching record in the joined table.The differences between InnerJoin and ExistJoin are as follows: 1.When the join type is ExistJoin, the search ends after the first match has been found. 2.When the join type is InnerJoin, all matching records are searched for.
• NotExistJoin-Select records from the main table that do not have a match in the joined table.

Let’s take a scenario to better understanding. First we have to create two tables with following fields and indexes according to figure 1.

Figure 1. Create two tables

And populate some record in both tables like figure 2.

Figure 2. Populate some record

Now create a form and drag-drop both table in form data source and make design according to figure 3.

Figure 3. Create a form

And set property  “JoinSource - Department”  on Employee data source like below.

Figure 4. Set JoinSource property

Finally, when we trying to set LinkType property to different types of joins we will get below results .

Figure 5.Result

Simple observation of Table Inheritance in 2012

In this section show Table Inheritance. A table can extend/inherit another table. Each table has the “SupportInheritance” property and the “Extends” property, which together control table inheritance. When we create new table in Ax it extends default “Common” table.  The Common Table extension cannot be seen in Extends Property. “Common” table is System table that is located at AOT/System Documentation/Tables/Common.

Here, most important question is when/where to Use Table Inheritance? We can use Table Inheritance:

1.When there is one too many or many to many relationships between two tables

2.When base table and derived table both carry different information about same item/object.

I’ll show you a simple Example that will help you to understand table inheritance in Ax 2012.

First, Create a table called “ClassTable” and immediately set property “SupportInheritance – Yes” before creating any fields.You have to create a field in BaseTable(ClassTable) called “InstanceRelationType” that must be int64 type and set on InstanceRelationType on ClassTable. Create another fields according to Figure 1.

Figure 1 – Create ClassTable

Note: If you have any field in table you cannot set “SupportInheritance- Yes”. When you trying to to set “Yes” you’ll get a error like Figure 2.

Figure  2 - Error

Now, Create second Table called “Student” and immediately set property “SupportInheritance – Yes”. And set property “Extends – ClassTable” before creating any fields. After, create another fields according to figure 3.

Figure 3 – Create Student Table

After, Open “Student” table and u will get all fields from “ClassTable” in “Student” table like figure 4.

Figure 4 - Result

Note: We cannot create record directly in base table because it’s only reference. When you insert a record into a derived table, the system automatically inserts the required base table row. When you delete a record from a base table, the system automatically deletes dependent rows from the derived table.

We can also inherit methods from base table in Ax 2012 like class.

Create FormConrols Dynamically or by Button

We can add lots of FormControls dynamically on form design. In this Post, I am showing you to add some formControls runtime or click by Button. So if you want to add these control at runtime you have to write all below code in “init()” on form Methods and if you want to add these control clicking by button then you have to override “click()” on created button methods. And I'm going to use second way (click by Button) in this post for every control. For this first we should create a form with designs according to figure 1.

Figure 1 – Create Form Design

 1. Create Group :-

void clicked()

{

    FormBuildDesign                  formBuildDesign = form.design();

    FormBuildGroupControl       formBuildGroupControl;

    FormGroupControl               formGroupControl ;

     ;

    formGroupControl = OldGroup.addControl(FormControlType::Group,'NewGroup');

    formGroupControl.caption("Description");

    FormBuildGroupControl = formBuildDesign.control(OldGroup.id());

 }

Figure 2 – Add GroupControl

2. Create ComboBox :-

void clicked()

{

    FormBuildDesign                       formBuildDesign = form.design();

    FormBuildComboBoxControl    formBuildComboBoxControl;

    FormComboBoxControl            formComboBoxControl;

    ;

    formComboBoxControl = OldGroup.addControl(FormControlType::ComboBox,'ComboBox');

    formComboBoxControl.label("ComboBox");

    formComboBoxControl.enumType(118); // 118 is Id for ItemType enum

    formBuildComboBoxControl = formBuildDesign.control(OldGroup.id());

    super();

}

Figure 3 – Add ComboBox

3. Create CheckBox:-

void clicked()          

{

    FormBuildDesign             formBuildDesign = form.design();

    FormBuildCheckBoxControl    formBuildCheckBoxControl;

    FormCheckBoxControl         formCheckBoxControl;

    ;

    formCheckBoxControl = OldGroup.addControl(FormControlType::CheckBox,'CheckBox');

    formCheckBoxControl.label("CheckBox");

    formBuildCheckBoxControl = formBuildDesign.control(OldGroup.id());

    super();

}

Figure 4 – Add CheckBox

4. Create TabPage:-

void clicked()

{

    FormBuildDesign            formBuildDesign = form.design();

    FormBuildTabPageControl    formBuildTabPageControl;

    FormTabPageControl         formTabPageControl;

    ;

    formTabPageControl = tab.addControl(FormControlType::TabPage,'TabPage');

                                            //tab is already create tab set auto declaration - Yes

    formTabPageControl.caption("Details");

    formTabPageControl.width(500);

    formTabPageControl.height(100);

    formBuildTabPageControl = formBuildDesign.control(tab.id());

    super();

}

Figure 5 – Add TabPage

5. Create Grid:-

void clicked()

{

    FormBuildDesign         formBuildDesign = form.design();

    FormBuildGridControl    formBuildGridControl;

    FormGridControl         formGridControl;

    ;

    formGridControl = OldGroup.addControl(FormControlType::Grid,'Grid');

    formGridControl.width(500);

    formGridControl.height(100);

    formBuildGridControl = formBuildDesign.control(OldGroup.id());

    super();

}

Figure 6 – Add GridControl

6. Create StringEdit in Grid:-

void clicked()

{

    FormBuildDesign         formBuildDesign = form.design();

    FormBuildStringControl    formBuildStringControl;

    FormStringControl         formStringControl;

    ;

    formStringControl = Grid.addControl(FormControlType::String,'StringEdit');

    formStringControl.extendedDataType(99);

    formBuildStringControl = formBuildDesign.control(TabPage1.id());

    super();

}

Figure 7 – Add StringEditControl on Grid with CustomerAccount EDT

Adding Custom filters on from

Some time we have requirement to create custom filter on form with checkbox, stringControl and ComboBox with lookup like smmContactPerson form. So we can complete this requirement with simple process. In this post I will create all filters on “CustTable” Table step by step.

Setp1-Declare two “str” variables in form Class declaration(str   custNameFilter, accountFilter). And create these controls on form according figure1

1. CheckBoxFilter(CheckBox)
        AutoDeclaration – Yes
        Lable- ShowAll
2. NameFilter (StrinEdit)
        AutoDeclaration - Yes
        Lable-Name
3.  AccountNumFilter (StrinEdit)  
        AutoDeclaration - Yes
          ExtendedDataType-CustAccount

Figure 1 - Create FormControls

Setp-2  Override modified() on CheckBoxFilter control and write code:

if(ret)

              {

                      CustTable_ds.executeQuery();

              }

Setp2 – override enter() method on NameFilter control and

super();  

this.setSelection(strlen(this.text()), strlen(this.text()));

Setp3 – override textchange() method on NameFilter  control and call modified().

             this.modified();

Setp4 – override modified() on NameFilter  control and wirte code:

 nameFilter = ‘*’+this.text()+’*’

 CustTable_ds.ExecuteQuery();

 Figure 2 - Override methods on FormControls

Setp5 – Finally override ExecuteQuery()on FormDataSoruce under CustTable and write code:

    public void executeQuery()

     {

       QueryBuildDataSource        qbds = CustTable_ds.query().dataSourceTable(tablenum(CustTable));

       QueryBuildRange      qbr1 = SysQuery::findOrCreateRange(qbds, fieldnum(CustTable, Blocked));

       QueryBuildRange      qbr2 = SysQuery::findOrCreateRange(qbds, fieldnum(CustTable, Name));

       QueryBuildRange      qbr3 = SysQuery::findOrCreateRange(qbds, fieldnum(CustTable, AccountNum));

       ;

 

       qbr2.value(custNameFilter);

       qbr3.value(accountFilter);

       if(CheckBoxFilter.value())

         {

                   qbr1.value(SysQuery::valueNot(CustVendorBlocked::No));

         }

       else

        {

                  qbr1.value(SysQuery::valueUnlimited());

        } 

      super();

    }

How to Hide Enum Element Dynamically on formControl

This post will help you to Hide Enum Element Dynamically on formControl .  This situation require when we have an enum with values Element1, Element2 and Element3. And we want to use this enum on two different places and Element2 is not require in one place. So we can hide element2 at run time using following code.

1.       Create a ComboBox on formControl and set property “EnumType –ItemType”

2.       Override enter() on crated ComboBox and write this code:

this.delete(enum2str(ItemType::BOM));

 super();

3.       Now run form and check.

Turn OFF/ON Synchronize database through code

This post will help you to turn off/on Synchronize database when making changes to AOT tables.

static void ShowSysSqlSync(Args _args)

{

    SysGlobalCache gc;

    str owner = 'SysSqlSync';

    str key   = 'ShowSysSqlSync';

    boolean showEnabled = false; //set to true to enable.

    ;

    gc = appl.globalCache();

    gc.set(owner, key, showEnabled);

    info(strfmt('Show SqlSync: %1', gc.get(owner, key)));

}

You can find more details about Synchronize database from below link.

http://blogs.msdn.com/b/daxis/archive/2009/04/26/synchronization-enhancements-in-dynamics-ax-2009.aspx