Number Sequence in Dynamics Ax

Microsoft Dynamics AX has a number sequence framework to generate alphanumeric number sequences that are used to identify transaction documents such as sales orders, purchase order and others.

We can create number sequence with existing module or new module. For both condition, first we create a table “BookTable” with two fields “BookId” and “Name” and create index on table called “BookIdx” and drag-drop “BookId” field on it. 

Figure 1. Create BookTable

Create a form “BookForm” and drag-drop “BookTable” in the datasource and create design according figure 2.

Figure 2. Create BookForm

Now, we create number sequence with existing module or new module.

 1. Create number sequence with existing module

1.Create a EDT that extends num and Add a relation in EDT.

Figure 3. Create EDT 

2.Edit a class NumberseqReference_CustTable  and  modify “loadModule()” method

   numRef.dataTypeId = typeId2ExtendedTypeId(typeid(Book_edt));        

    numRef.referenceHelp="Unique key for the Complaint Report";

    numRef.wizardContinuous = true;  

    numRef.wizardManual = NoYes::No;            

    numRef.wizardAllowChangeDown = NoYes::No;              

    numRef.wizardAllowChangeUp= NoYes::No;          

    numRef.wizardHighest= 999999;    

    numRef.sortField = 19;       

    this.create(numRef);

3.Modify CustParameters Table and Add methods.

   static client server NumberSequenceReference numRefComplaintId()

    {

     return NumberSeqReference::findReference(typeId2ExtendedTypeId(typeid(Book_edt)));

   }

4.In the “BookForm” classDeclaration declare NumberSeqFormHandler.

5.On “BookForm”  under “BookTable” datasource – Override write(), Delete(), create() methods like:

public void write()

{   super();

    element.numberSeqFormHandler().formMethodDataSourceWrite();

}

public void delete()

{   element.numberSeqFormHandler().formMethodDataSourceDelete();

    super();

}

public void create(boolean _append = false)

{   element.numberSeqFormHandler().formMethodDataSourceCreatePre();

    super(_append);

    element.numberSeqFormHandler().formMethodDataSourceCreate();

}

6.create method on “BookForm” form methods.

NumberSeqFormHandler numberSeqFormHandler()

{

    if (!numberSeqFormHandler)

        {

            numberSeqFormHandler = NumberSeqFormHandler::newForm(CustParameters::numRefComplaintId().NumberSequence, element, BookTable.dataSource(), fieldnum(BookTable, BookId));

        }

    return numberSeqFormHandler;

}

7. Goto>Basic>Setup>Number sequences>create new Number sequence code and set fields according to you

8. Goto> References>find new number sequence reference and set number sequence code field.

2. Create number sequence with new  module

1.Create a EDT that extends num and Add a relation in EDT

2.Modify numseqmodule Enum and a element “Book”.

3.Create  a class NumberseqReference_Book  that extend NumberSeqReference class and  Add loadModule() method and numberSeqModule().

protected void loadModule()

{

    NumberSequenceReference numRef;

    ;

    /* Setup Book Details */

    numRef.DataTypeId = typeId2ExtendedTypeId(typeid(Book_edt));

    numRef.ConfigurationKeyId = configurationkeynum(Test);

    numRef.ReferenceHelp ="Unique key for test identification. ";

    numRef.WizardManual = NoYes::No;

    numRef.WizardAllowChangeDown = NoYes::No;

    numRef.WizardAllowChangeUp = NoYes::No;

    numRef.SortField = 1;

    this.create(numRef);

}

public static client server NumberSeqModule numberSeqModule()

{

    ;

    return NumberSeqModule::Book;

}

4.Create  “BookParameters” Table and Add methods

client server static NumberSequenceReference numRefBookId()

{

    return NumberSeqReference::findReference(typeId2ExtendedTypeId(typeid(Book_edt)));

}

Figure 4. Create BookParameters Table

5.In the “BookForm” classDeclaration declare NumberSeqFormHandler.

6.On “BookForm”  under “BookTable” datasource – Override write(), Delete(), create() methods like:

public void write()

{   super();

    element.numberSeqFormHandler().formMethodDataSourceWrite();

}

public void delete()

{   element.numberSeqFormHandler().formMethodDataSourceDelete();

    super();

}

public void create(boolean _append = false)

{   element.numberSeqFormHandler().formMethodDataSourceCreatePre();

    super(_append);

    element.numberSeqFormHandler().formMethodDataSourceCreate();

}

7.create method on “BookForm” form methods.

NumberSeqFormHandler numberSeqFormHandler()

{

    if (!numberSeqFormHandler)

        {

            numberSeqFormHandler = NumberSeqFormHandler::newForm(BookParameters::numRefBookId().NumberSequence, element, BookTable.dataSource(), fieldnum(BookTable, BookId));

        }

    return numberSeqFormHandler;

}

8. Goto>Basic>Setup>Number sequences>create new Number sequence code and set fields according to you

9. Goto> References>find new number sequence reference and set number sequence code field.

3. In Dynamics Ax 2012 it is require that Create a new job with the following code and run it to call all loadModule:

           static void NumberSeqLoadAll(Args _args)

            {

                   NumberSeqApplicationModule::loadAll();

            }

How to create Authentication form when the Dynamics Ax open.

In this section I’ll show you how to create authentication form when the dynamics ax open. This form will contain username and password stringEdit control fields. Both fields verify username and password for security. If you’ll enter wrong username and password dynamics ax environment will not open and if you close authentication form dynamics ax environment will automatically shutdown. I’m showing you implementing this functionality step by step-:

Step 1 – Create a form named “AuthenticationForm” with following designs.

Figure 1. Create AuthenticationForm.

Step 2 – Set below properties on form controls and buttons-

      1. Design
                 Width – 10
                 Height – 70
                 Caption – Authentication
      2. StringEdit:UserName

                AutoDeclaration  – Yes

                Label – Username

      3. StringEdit:Password

                AutoDeclaration  – Yes

                PasswordStyle - Yes

                Label – Password

      4. Button:Login

                Text – Log In

      5. Button:Cancel

               Text – Cancel

Step 3 – Override methods on buttons and form-

1. Override “close()” method on form methods and write code-

            public void close()

             {

                     infoLog.shutDown(true);

                     super();

              }

2. Override “clicked()” method on “Button:Login” and write below code-

        void clicked()

           {

                    str un, pass;

                    ;

                   un = UserName.text();

                   pass = Password.text();

                   if(un == "kamal" && pass == "jangir")

                    {

                             box::info("User name and Password correct");

                            element.closeOk();

                    }

                  else

                    {

                             box::info("User name and Password incorrect Try again");

                    }

                  super();

          }

3. Override “clicked()” method on “Button:Cancel” and write below code-

         void clicked()

         {

                 ;

                 box::info("Ax Closed");

                infoLog.shutDown(true);

               super();

        }

Step 3 – Now Go to Classes and find info class and create a new method-

static void openFormByCode2(Args _args)

{

    FormRun formRun;

    Args args = new Args();

    ;

    args.name(formstr(AuthenticationForm));

   

    formRun = ClassFactory.formRunClass(args);

    formRun.init();

    formRun.run();

    formRun.wait();

}

Step 4 – Finally override “startup()” method on info class(Classes/Info/startup() ) and add below code-

        Args _args;

    ;

    _args = new Args();

    info::openFormByCode2(_args);

Now open new dynamics ax environment and check but be careful don’t close current environment.

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