Thursday 24 December 2015

Get database size using x++

How to get size of Database being used by AX ,below job can help you :


static void GetDBSize(Args _args)
{
LoginProperty loginProp;
ODBCConnection conn;
Resultset resultSet, resultSetCount;
Statement statement1, statement2;
str test;
real s;
SysSQLSystemInfo   systemInfo =  SysSQLSystemInfo::construct();
;


test = strfmt( “SELECT size FROM sys.master_files where name = ‘%1′”,systemInfo.getloginDatabase()  );

 loginProp = new LoginProperty();
loginProp.setServer(systemInfo.getLoginServer());
loginProp.setDatabase(systemInfo.getloginDatabase());
conn = new ODBCConnection(loginProp);
statement1 = conn.createStatement();
resultSet = statement1.executeQuery(test);


while (resultSet.next())
{
s = str2int(resultSet.getString(1));
s = s*8 /1024;
info(strfmt(“%1  MB”,s));
}

 }

Get database size using x++

How to get size of Database being used by AX ,below job can help you :


static void GetDBSize(Args _args)
{
LoginProperty loginProp;
ODBCConnection conn;
Resultset resultSet, resultSetCount;
Statement statement1, statement2;
str test;
real s;
SysSQLSystemInfo   systemInfo =  SysSQLSystemInfo::construct();
;


test = strfmt( “SELECT size FROM sys.master_files where name = ‘%1′”,systemInfo.getloginDatabase()  );

 loginProp = new LoginProperty();
loginProp.setServer(systemInfo.getLoginServer());
loginProp.setDatabase(systemInfo.getloginDatabase());
conn = new ODBCConnection(loginProp);
statement1 = conn.createStatement();
resultSet = statement1.executeQuery(test);


while (resultSet.next())
{
s = str2int(resultSet.getString(1));
s = s*8 /1024;
info(strfmt(“%1  MB”,s));
}

 }

Logos for new Module added in AX

Create a new module in Dynamics AX and assign image or logo to this module.
Creating Module: Go to the AOT > Menus and create new menu.  For example TestMenu.

1.    Add menu item. For example CustTable menu item.

2.    Then create new menu reference in the MainMenu menu:

o    On the MainMenu menu clicks the right mouse button and select New > Menu reference. TheSelect: Menus windows opens.

o    Drag and drop the TestMenu menu from Select:Menus window to the MainMenu.

This is all. The new module have been added. Reopen Dynamics AX application.
The new modules appears without image (to move module on the top position clicks the Microsoft Dynamics AX >View > Navigate Pane Options…

 Select the TestMenu menu in the AOT, click right mouse button and selectProperties. There are two properties NoramlImage and NormalResource.

In the NormalImage property the file path to the logo could be specified. But if we create product for sell then we can not specify the exact file path because each Customer will have own path.

In the NormalResource property the image id could be specified. It is suitable for our requirements. Let’s add image in Dynamics AX:

1.    Go to the AOT > Resources. Click right mouse button and select Create form file.

2.    Specify the file path and click Open.

The new image resource will be created:
But this resource doesn’t have the resource id or image id property. Even more in the NormalResourceproperty only the resource id of standard image can be specified. Standard image could be review here AOTForms > SysImageResources.

Trick: we will use the NormalImage property and Logo_image_png resource.
Create the following static method:



 static client void addImageToModule()
 {
TreeNode treeNodeMenu;
;
treeNodeMenu = SysDictMenu::newMenuName(menustr(TestMenu)).parmTreeNode();
if (treeNodeMenu)
{
treeNodeMenu.AOTsetProperty(identifierstr(NormalImage), SysResource::getImagePath(resourcestr(Logo_image_png)));
treeNodeMenu.AOTsave();
}
}



Call this method in the \Classes\Application\startupPost method:


// No SYS code must exist in this method
// If you need the startup command, look in the class SysStartupCmd



 void startupPost()
{
;
Class1::addImageToModule();
}






Reopen application.

Logos for new Module added in AX

Create a new module in Dynamics AX and assign image or logo to this module.
Creating Module: Go to the AOT > Menus and create new menu.  For example TestMenu.

1.    Add menu item. For example CustTable menu item.

2.    Then create new menu reference in the MainMenu menu:

o    On the MainMenu menu clicks the right mouse button and select New > Menu reference. TheSelect: Menus windows opens.

o    Drag and drop the TestMenu menu from Select:Menus window to the MainMenu.

This is all. The new module have been added. Reopen Dynamics AX application.
The new modules appears without image (to move module on the top position clicks the Microsoft Dynamics AX >View > Navigate Pane Options…

 Select the TestMenu menu in the AOT, click right mouse button and selectProperties. There are two properties NoramlImage and NormalResource.

In the NormalImage property the file path to the logo could be specified. But if we create product for sell then we can not specify the exact file path because each Customer will have own path.

In the NormalResource property the image id could be specified. It is suitable for our requirements. Let’s add image in Dynamics AX:

1.    Go to the AOT > Resources. Click right mouse button and select Create form file.

2.    Specify the file path and click Open.

The new image resource will be created:
But this resource doesn’t have the resource id or image id property. Even more in the NormalResourceproperty only the resource id of standard image can be specified. Standard image could be review here AOTForms > SysImageResources.

Trick: we will use the NormalImage property and Logo_image_png resource.
Create the following static method:



 static client void addImageToModule()
 {
TreeNode treeNodeMenu;
;
treeNodeMenu = SysDictMenu::newMenuName(menustr(TestMenu)).parmTreeNode();
if (treeNodeMenu)
{
treeNodeMenu.AOTsetProperty(identifierstr(NormalImage), SysResource::getImagePath(resourcestr(Logo_image_png)));
treeNodeMenu.AOTsave();
}
}



Call this method in the \Classes\Application\startupPost method:


// No SYS code must exist in this method
// If you need the startup command, look in the class SysStartupCmd



 void startupPost()
{
;
Class1::addImageToModule();
}






Reopen application.

Click method for marked records

/* Take a Button in butongroup,change the property “Multiselect = yes”
and write the follwing code clicked method of the control. */

void clicked()
{
   UB_Student_Dtl  _Student_Dtl;
   ;

   breakpoint;

   super();

   if(Stdtl_ds.anyMarked())
   {
   _Student_Dtl = Stdtl_ds.getFirst(1,false);
    while (_Student_Dtl)
    {
       info(_Student_Dtl.ID);
    _Student_Dtl = Stdtl_ds.getNext() ;
    }

   }

}

Click method for marked records

/* Take a Button in butongroup,change the property “Multiselect = yes”
and write the follwing code clicked method of the control. */

void clicked()
{
   UB_Student_Dtl  _Student_Dtl;
   ;

   breakpoint;

   super();

   if(Stdtl_ds.anyMarked())
   {
   _Student_Dtl = Stdtl_ds.getFirst(1,false);
    while (_Student_Dtl)
    {
       info(_Student_Dtl.ID);
    _Student_Dtl = Stdtl_ds.getNext() ;
    }

   }

}

ShutDown AX using code

public static void shutdownAxapta()
{

 SysGlobalCache cache = appl.globalCache();
info info;
;

 cache.set(classstr(info), identifierstr(Autologoff), true);
info=new info();

 info.shutDown(true);
}

ShutDown AX using code

public static void shutdownAxapta()
{

 SysGlobalCache cache = appl.globalCache();
info info;
;

 cache.set(classstr(info), identifierstr(Autologoff), true);
info=new info();

 info.shutDown(true);
}

Opening AX Form through code with ranges

//Write following code in your job
//————————————
void ABC_OpenFormCode()

{
   CustTable       _CustTable;
   str             menuItemStr;
   MenuFunction    menuFunction;
   Args            args = new Args();

   ;
   Select _CustTable where _CustTable.AccountNum == ’1111′;
   args.record(_CustTable);

   menuItemStr = menuitemdisplaystr(ABC_TestForm);
   menuFunction = new MenuFunction(menuItemStr, MenuItemType::Display);
   menuFunction.run(args);

}
//Write following code in your form-datasource-init() method (Here the example for CustTable)
//———————————————————-

public void init()
{
   CustTable  _CustTable;
   ;
   super();

   _CustTable = element.args().record();

   this.query().dataSourceTable(tablenum(CustTable)).addRange(fieldnum(CustTable,AccountNum)).value(_CustTable.AccountNum);

}

Opening AX Form through code with ranges

//Write following code in your job
//————————————
void ABC_OpenFormCode()

{
   CustTable       _CustTable;
   str             menuItemStr;
   MenuFunction    menuFunction;
   Args            args = new Args();

   ;
   Select _CustTable where _CustTable.AccountNum == ’1111′;
   args.record(_CustTable);

   menuItemStr = menuitemdisplaystr(ABC_TestForm);
   menuFunction = new MenuFunction(menuItemStr, MenuItemType::Display);
   menuFunction.run(args);

}
//Write following code in your form-datasource-init() method (Here the example for CustTable)
//———————————————————-

public void init()
{
   CustTable  _CustTable;
   ;
   super();

   _CustTable = element.args().record();

   this.query().dataSourceTable(tablenum(CustTable)).addRange(fieldnum(CustTable,AccountNum)).value(_CustTable.AccountNum);

}

Sending Mail through X++

Here i building a html table string of all customers belonging to a customer group and sending it through smtp server. The mail client interprets the html of the message and display the customers in a table of records.

You can build the html table by merging with the records of your table and send the mail.

   CustTable custTable;
   System.Text.StringBuilder htmlTable;
   SysMailer mailer;

   ;
  htmlTable = new System.Text.StringBuilder();
  htmlTable.Append(@”<caption> Blue Company Customers”);
  htmlTable.Append(@”  </caption> </br>”);
  htmlTable.Append(“<TABLE BORDER=1>”);

  while select custTable where custtable.CustGroup == ‘AP’
  {
     htmlTable.Append(“<TR ALIGN=’CENTER’>”);
     htmlTable.Append(“<TD>”);
     htmlTable.Append(custTable.AccountNum);
     htmlTable.Append(“</TD>”);
     htmlTable.Append(“<TD>”);
     htmlTable.Append(custTable.NameAlias);
     htmlTable.Append(“</TD>”);
     htmlTable.Append(“</TR>”);
  }

  htmlTable.Append(“</TABLE>”);
   mailer = new SysMailer();

   //building mail details

   mailer.htmlBody(htmlTable.ToString());
   mailer.subject(“Automated Mail – Customers”);
   mailer.fromAddress(“Axapta@bluecompany.com”);
   mailer.tos().appendAddress(“sayeed@bluecompany.com”);

   // Setting SMTP details

   mailer.SMTPRelayServer(“192.168.4.67″,87,”AXMail”,”1234″,false);

   // Sending Mail

   mailer.sendMail();

Sending Mail through X++

Here i building a html table string of all customers belonging to a customer group and sending it through smtp server. The mail client interprets the html of the message and display the customers in a table of records.

You can build the html table by merging with the records of your table and send the mail.

   CustTable custTable;
   System.Text.StringBuilder htmlTable;
   SysMailer mailer;

   ;
  htmlTable = new System.Text.StringBuilder();
  htmlTable.Append(@”<caption> Blue Company Customers”);
  htmlTable.Append(@”  </caption> </br>”);
  htmlTable.Append(“<TABLE BORDER=1>”);

  while select custTable where custtable.CustGroup == ‘AP’
  {
     htmlTable.Append(“<TR ALIGN=’CENTER’>”);
     htmlTable.Append(“<TD>”);
     htmlTable.Append(custTable.AccountNum);
     htmlTable.Append(“</TD>”);
     htmlTable.Append(“<TD>”);
     htmlTable.Append(custTable.NameAlias);
     htmlTable.Append(“</TD>”);
     htmlTable.Append(“</TR>”);
  }

  htmlTable.Append(“</TABLE>”);
   mailer = new SysMailer();

   //building mail details

   mailer.htmlBody(htmlTable.ToString());
   mailer.subject(“Automated Mail – Customers”);
   mailer.fromAddress(“Axapta@bluecompany.com”);
   mailer.tos().appendAddress(“sayeed@bluecompany.com”);

   // Setting SMTP details

   mailer.SMTPRelayServer(“192.168.4.67″,87,”AXMail”,”1234″,false);

   // Sending Mail

   mailer.sendMail();

ValidateField Method

public boolean validateField(fieldId _fieldIdToCheck)
{

   date d = systemdateget();
   boolean ret;

   ;
   ret = super(_fieldIdToCheck);

   switch(_fieldIdToCheck)
   {
        case fieldnum (UB_Student_Dtl,ID):
          if((strlen(this.ID)<3) || (strlen(this.ID)>3))
            // if((strlen(this.ID)<3))
            {
                 throw error(“ID should have 3 degits”);
            }
        break;

        case fieldnum (UB_Student_Dtl,FromDate):
           if(this.FromDate > d)
           {
               throw error(“Enter Valid from Date”);
           }
        break;

        case fieldnum (UB_Student_Dtl,TODate):
           if(this.ToDate< This.FromDate)
           {
              throw error(” Enter Valid ToDate”);
            }
        break;

   }
   return ret;
}

ValidateField Method

public boolean validateField(fieldId _fieldIdToCheck)
{

   date d = systemdateget();
   boolean ret;

   ;
   ret = super(_fieldIdToCheck);

   switch(_fieldIdToCheck)
   {
        case fieldnum (UB_Student_Dtl,ID):
          if((strlen(this.ID)<3) || (strlen(this.ID)>3))
            // if((strlen(this.ID)<3))
            {
                 throw error(“ID should have 3 degits”);
            }
        break;

        case fieldnum (UB_Student_Dtl,FromDate):
           if(this.FromDate > d)
           {
               throw error(“Enter Valid from Date”);
           }
        break;

        case fieldnum (UB_Student_Dtl,TODate):
           if(this.ToDate< This.FromDate)
           {
              throw error(” Enter Valid ToDate”);
            }
        break;

   }
   return ret;
}

Validation for alphabatics Id

public boolean validateWrite()
{
   //Created by: Dhanya — unibuzz
   //Date Created: 13th june 2011

   boolean ret;
   ;

   ret = super();

   if( (strlen(this.ItemGroupId)  < 3 || strlen(this.ItemGroupId)  > 3))
   {
        throw error(“Family must be exactly 3 characters”);
   }

   if(strnfind(this.ItemGroupId,“ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz”, 1, 3))

   {
       throw error(“Family should contain only alphabets. Numbers or special charaters not allowed”);
   }
   return ret;
}

Validation for alphabatics Id

public boolean validateWrite()
{
   //Created by: Dhanya — unibuzz
   //Date Created: 13th june 2011

   boolean ret;
   ;

   ret = super();

   if( (strlen(this.ItemGroupId)  < 3 || strlen(this.ItemGroupId)  > 3))
   {
        throw error(“Family must be exactly 3 characters”);
   }

   if(strnfind(this.ItemGroupId,“ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz”, 1, 3))

   {
       throw error(“Family should contain only alphabets. Numbers or special charaters not allowed”);
   }
   return ret;
}

Change Digit in Indian text

public static TempStr numeralsToTxt_IN(real _num)
{
    return Global::numeralsToTxt_EN(_num);  // for dollars
    return Global::numeralsToTxt_IN(_num);  // for Indian currency, need to add below code
}


In the Global class you can add below code



static TempStr numeralsToTxt_IN(real _num)
{
int numOfPennies = (decround(frac(_num), 2) * 100) mod 100;
real test = _num - frac(_num);

int numOfTenths;
str 20 ones[19], tenths[9], hundreds, thousands, lakhs, crores;

int64 temp;
str 200 returntxt;
str 200 pennytxt;
int penny;

real modOperator(real a1, real a2)
{
int tmpi;
real tmp1, tmp2;
tmp1 = a1 / a2;
tmpi = real2int(tmp1);
tmp2 = tmpi;
return (tmp1 - tmp2)*a2;
}

real checkPower(real _test, int64 _power)
{
int64 numOfPower;

if (_test >= _power)
{
numOfPower = _test div _power;
if (numOfPower >= 100)
{
temp = numOfPower div 100;
returntxt = returntxt + ' ' + ones[temp] + ' ' + hundreds;
numOfPower = numOfPower mod 100;
}
if (numOfPower >= 20)
{
temp = numOfPower div 10;
returntxt = returntxt + ' ' + tenths[temp];
numOfPower = numOfPower mod 10;
}
if (numOfPower >= 1)
{
returntxt = returntxt + ' ' + ones[numOfPower];
numOfPower = numOfPower mod 10;
}
switch(_power)
{
case 10000000 :
{
returntxt = returntxt + ' ' + Crores;
_test = modOperator(_test, 10000000);
break;
}
case 100000 :
{
returntxt = returntxt + ' ' + lakhs;
_test = modOperator(_test, 100000);
break;
}
case 1000 :
{
returntxt = returntxt + ' ' + thousands;
_test = modOperator(_test, 1000);
break;
}
case 100 :
{
returntxt = returntxt + ' ' + hundreds;
_test = modOperator(_test, 100);
break;
}
}
}
return _test;
}

#Define.text_1('One')
#Define.text_2('Two')
#Define.text_3('Three')
#Define.text_4('Four')
#Define.text_5('Five')
#Define.text_6('Six')
#Define.text_7('Seven')
#Define.text_8('Eight')
#Define.text_9('Nine')
#Define.text_10('Ten')
#Define.text_11('Eleven')
#Define.text_12('Twelve')
#Define.text_13('Thirteen')
#Define.text_14('Fourteen')
#Define.text_15('Fifteen')
#Define.text_16('Sixteen')
#Define.text_17('Seventeen')
#Define.text_18('Eighteen')
#Define.text_19('Nineteen')
#Define.text_20('Twenty')
#Define.text_30('Thirty')
#Define.text_40('Forty')
#Define.text_50('Fifty')
#Define.text_60('Sixty')
#Define.text_70('Seventy')
#Define.text_80('Eighty')
#Define.text_90('Ninety')
#Define.text_100('Hundred')
#Define.text_1000('Thousand')
#Define.text_100000('Lakh')
#Define.text_10000000('Crore')
#Define.text_and('Rupees and')
#Define.text_paise('Paise Only')
#Define.text_ruppe('Rupees Only')

ones[1] = #text_1;
ones[2] = #text_2;
ones[3] = #text_3;
ones[4] = #text_4;
ones[5] = #text_5;
ones[6] = #text_6;
ones[7] = #text_7;
ones[8] = #text_8;
ones[9] = #text_9;
ones[10] = #text_10;
ones[11] = #text_11;
ones[12] = #text_12;
ones[13] = #text_13;
ones[14] = #text_14;
ones[15] = #text_15;
ones[16] = #text_16;
ones[17] = #text_17;
ones[18] = #text_18;
ones[19] = #text_19;

tenths[1] = 'Not used';
tenths[2] = #text_20;
tenths[3] = #text_30;
tenths[4] = #text_40;
tenths[5] = #text_50;
tenths[6] = #text_60;
tenths[7] = #text_70;
tenths[8] = #text_80;
tenths[9] = #text_90;

hundreds = #text_100;
thousands = #text_1000;
lakhs = #text_100000;
crores = #text_10000000;



test = checkPower(test, 10000000);
test = checkPower(test, 100000);
test = checkPower(test, 1000);
test = checkPower(test, 100);

if (test >= 20)
{
numOfTenths = test div 10;
returntxt = returntxt + ' ' + tenths[numofTenths];
numOfTenths = numOfTenths mod 10;
test = test mod 10;
}
if (test >= 1)
{
numOfTenths = real2int(test);
returntxt = returntxt + ' ' + ones[numOfTenths];
}

if (numOfPennies)
{
if (numOfPennies >= 20)
{
penny = numOfPennies div 10;
pennytxt = tenths[penny];
numOfPennies = numOfPennies mod 10;
}
if (numOfPennies >= 1)
{
pennytxt = pennytxt + ' ' + ones[numOfPennies];
}
returntxt = returntxt + ' ' + #text_and + ' ' + pennytxt + ' ' +#text_paise;
}
else
{
returntxt = returntxt + ' ' + #text_ruppe;
}

return returntxt;
}

Change Digit in Indian text

public static TempStr numeralsToTxt_IN(real _num)
{
    return Global::numeralsToTxt_EN(_num);  // for dollars
    return Global::numeralsToTxt_IN(_num);  // for Indian currency, need to add below code
}


In the Global class you can add below code



static TempStr numeralsToTxt_IN(real _num)
{
int numOfPennies = (decround(frac(_num), 2) * 100) mod 100;
real test = _num - frac(_num);

int numOfTenths;
str 20 ones[19], tenths[9], hundreds, thousands, lakhs, crores;

int64 temp;
str 200 returntxt;
str 200 pennytxt;
int penny;

real modOperator(real a1, real a2)
{
int tmpi;
real tmp1, tmp2;
tmp1 = a1 / a2;
tmpi = real2int(tmp1);
tmp2 = tmpi;
return (tmp1 - tmp2)*a2;
}

real checkPower(real _test, int64 _power)
{
int64 numOfPower;

if (_test >= _power)
{
numOfPower = _test div _power;
if (numOfPower >= 100)
{
temp = numOfPower div 100;
returntxt = returntxt + ' ' + ones[temp] + ' ' + hundreds;
numOfPower = numOfPower mod 100;
}
if (numOfPower >= 20)
{
temp = numOfPower div 10;
returntxt = returntxt + ' ' + tenths[temp];
numOfPower = numOfPower mod 10;
}
if (numOfPower >= 1)
{
returntxt = returntxt + ' ' + ones[numOfPower];
numOfPower = numOfPower mod 10;
}
switch(_power)
{
case 10000000 :
{
returntxt = returntxt + ' ' + Crores;
_test = modOperator(_test, 10000000);
break;
}
case 100000 :
{
returntxt = returntxt + ' ' + lakhs;
_test = modOperator(_test, 100000);
break;
}
case 1000 :
{
returntxt = returntxt + ' ' + thousands;
_test = modOperator(_test, 1000);
break;
}
case 100 :
{
returntxt = returntxt + ' ' + hundreds;
_test = modOperator(_test, 100);
break;
}
}
}
return _test;
}

#Define.text_1('One')
#Define.text_2('Two')
#Define.text_3('Three')
#Define.text_4('Four')
#Define.text_5('Five')
#Define.text_6('Six')
#Define.text_7('Seven')
#Define.text_8('Eight')
#Define.text_9('Nine')
#Define.text_10('Ten')
#Define.text_11('Eleven')
#Define.text_12('Twelve')
#Define.text_13('Thirteen')
#Define.text_14('Fourteen')
#Define.text_15('Fifteen')
#Define.text_16('Sixteen')
#Define.text_17('Seventeen')
#Define.text_18('Eighteen')
#Define.text_19('Nineteen')
#Define.text_20('Twenty')
#Define.text_30('Thirty')
#Define.text_40('Forty')
#Define.text_50('Fifty')
#Define.text_60('Sixty')
#Define.text_70('Seventy')
#Define.text_80('Eighty')
#Define.text_90('Ninety')
#Define.text_100('Hundred')
#Define.text_1000('Thousand')
#Define.text_100000('Lakh')
#Define.text_10000000('Crore')
#Define.text_and('Rupees and')
#Define.text_paise('Paise Only')
#Define.text_ruppe('Rupees Only')

ones[1] = #text_1;
ones[2] = #text_2;
ones[3] = #text_3;
ones[4] = #text_4;
ones[5] = #text_5;
ones[6] = #text_6;
ones[7] = #text_7;
ones[8] = #text_8;
ones[9] = #text_9;
ones[10] = #text_10;
ones[11] = #text_11;
ones[12] = #text_12;
ones[13] = #text_13;
ones[14] = #text_14;
ones[15] = #text_15;
ones[16] = #text_16;
ones[17] = #text_17;
ones[18] = #text_18;
ones[19] = #text_19;

tenths[1] = 'Not used';
tenths[2] = #text_20;
tenths[3] = #text_30;
tenths[4] = #text_40;
tenths[5] = #text_50;
tenths[6] = #text_60;
tenths[7] = #text_70;
tenths[8] = #text_80;
tenths[9] = #text_90;

hundreds = #text_100;
thousands = #text_1000;
lakhs = #text_100000;
crores = #text_10000000;



test = checkPower(test, 10000000);
test = checkPower(test, 100000);
test = checkPower(test, 1000);
test = checkPower(test, 100);

if (test >= 20)
{
numOfTenths = test div 10;
returntxt = returntxt + ' ' + tenths[numofTenths];
numOfTenths = numOfTenths mod 10;
test = test mod 10;
}
if (test >= 1)
{
numOfTenths = real2int(test);
returntxt = returntxt + ' ' + ones[numOfTenths];
}

if (numOfPennies)
{
if (numOfPennies >= 20)
{
penny = numOfPennies div 10;
pennytxt = tenths[penny];
numOfPennies = numOfPennies mod 10;
}
if (numOfPennies >= 1)
{
pennytxt = pennytxt + ' ' + ones[numOfPennies];
}
returntxt = returntxt + ' ' + #text_and + ' ' + pennytxt + ' ' +#text_paise;
}
else
{
returntxt = returntxt + ' ' + #text_ruppe;
}

return returntxt;
}

Lookup Method in AX

public void lookup(FormControl _formControl, str _filterStr)
{
   Query                   query = new Query();
   SysTableLookup          sysTableLookup;
   QueryBuildDataSource    queryBuildDataSource;
   QueryBuildRange     qbr;
   ;

   sysTableLookup = SysTableLookup::newParameters(tablenum(UB_Map),_formControl);
   sysTableLookup.addLookupfield(fieldnum(UB_Map,Cities), true);
 //  sysTableLookup.addLookupfield(fieldnum(SalesLine, ItemName));
  // sysTableLookup.addLookupfield(fieldnum(SalesLine, ItemType));
  
 queryBuildDataSource = query.addDataSource(tablenum(UB_Map));
  qbr =  queryBuildDataSource.addRange(fieldnum(UB_Map, States));
   qbr.value(queryValue(UB_Task_Table1.State));

 //  queryBuildDataSource.addRange(fieldnum(AddressCityTable_BR, CountryId)).value(queryValue(zipCode.CountryRegionId));

   sysTableLookup.parmQuery(query);
   sysTableLookup.performFormLookup();
}

Lookup Method in AX

public void lookup(FormControl _formControl, str _filterStr)
{
   Query                   query = new Query();
   SysTableLookup          sysTableLookup;
   QueryBuildDataSource    queryBuildDataSource;
   QueryBuildRange     qbr;
   ;

   sysTableLookup = SysTableLookup::newParameters(tablenum(UB_Map),_formControl);
   sysTableLookup.addLookupfield(fieldnum(UB_Map,Cities), true);
 //  sysTableLookup.addLookupfield(fieldnum(SalesLine, ItemName));
  // sysTableLookup.addLookupfield(fieldnum(SalesLine, ItemType));
  
 queryBuildDataSource = query.addDataSource(tablenum(UB_Map));
  qbr =  queryBuildDataSource.addRange(fieldnum(UB_Map, States));
   qbr.value(queryValue(UB_Task_Table1.State));

 //  queryBuildDataSource.addRange(fieldnum(AddressCityTable_BR, CountryId)).value(queryValue(zipCode.CountryRegionId));

   sysTableLookup.parmQuery(query);
   sysTableLookup.performFormLookup();
}

Use Of modified method


//modifiedField() in Table Level


public void modifiedField(fieldId _fieldId)
{
   super(_fieldId);
     if(fieldnum(UB_std_marks,Mark4)==_fieldId)
   {
    this.Average = ((this.Mark1+this.Mark2+this.Mark3+this.Mark4)/4);
    if(this.Average<50)
    {
   this.Grade = “B(“+int2Str(this.Average)+”%)”;
    }
    else
    {
    this.Grade=”A(“+int2Str(this.Average)+”%)”;
    }
   }
}




OR



//Modified() in control Level
//—————————-
public boolean modified()
{
   boolean ret;
   ret = super();
   if(Stdtl_ToDate.dateValue())
   {
       Stdtl_Salary.allowEdit(True);
   }
   else
   {
       Stdtl_Salary.allowEdit(False);
   }
return ret;
}

Use Of modified method


//modifiedField() in Table Level


public void modifiedField(fieldId _fieldId)
{
   super(_fieldId);
     if(fieldnum(UB_std_marks,Mark4)==_fieldId)
   {
    this.Average = ((this.Mark1+this.Mark2+this.Mark3+this.Mark4)/4);
    if(this.Average<50)
    {
   this.Grade = “B(“+int2Str(this.Average)+”%)”;
    }
    else
    {
    this.Grade=”A(“+int2Str(this.Average)+”%)”;
    }
   }
}




OR



//Modified() in control Level
//—————————-
public boolean modified()
{
   boolean ret;
   ret = super();
   if(Stdtl_ToDate.dateValue())
   {
       Stdtl_Salary.allowEdit(True);
   }
   else
   {
       Stdtl_Salary.allowEdit(False);
   }
return ret;
}

DrillDown SSRS report in ax 2012

Drill drown reports in Dynamics Ax 2012 R3 are very vasy. For this purpose, I have to create a new AOT/Static Query AOT. This query is a join between CustTable and SalesTable. I created an Inner Join so only those Customer were selected who have associated Sales Orders. Consider following steps to create a new report:

Create a new Query with Name “DyWorldCustSales”

Drop or add Data Source with CustTable

6-21-2014 9-02-30 PM


Right click on Fields and add following fields from Customer table.


CustomerFileds


Expand Data Source inside Custtable, Add or Create DataSource with SalesTable and fields


SalesOrder


Right Click on  SalesTables Data Source and set its join properties as follows:


DataSetProperties


Expand Relationship node of SalesTable_1


Add following relationship:


Relations




Set it properties as follows:


RelationshipDetail


Now, create a new report In existing or New DataModel Report in Visual Studio project:


NewReport Project




Add New report with DyCustSalesOrderList


Add Dataset with Name DSCustomer and Point to Query which we created in above steps:


PointToQuery






QueryDataSet




Now drag and drop the dataset on designer to create a designer
And Update, rename it to  DyCustSalesOrderList


Set DataTable’s Propeties visible to set false


DataTable




Add a List and set its name to CustList


Create two groups, one for CustomerGroup and second for AccountNum:


Groups


Now drop the fields from dataset which you want to display on report




Fields




Right click on CustList and set Data Navigation Style to DrillDown from properties window






DrillDownProperties






Now right click add report to aot and then deploy to Report Server
When you run the report,  from meu Item you will find following




CustomerGroupClick
Click on Customer group this will open:
reportCustomerLevel




Click on Customer account it will drill down report on Detail level:




Details

DrillDown SSRS report in ax 2012

Drill drown reports in Dynamics Ax 2012 R3 are very vasy. For this purpose, I have to create a new AOT/Static Query AOT. This query is a join between CustTable and SalesTable. I created an Inner Join so only those Customer were selected who have associated Sales Orders. Consider following steps to create a new report:

Create a new Query with Name “DyWorldCustSales”

Drop or add Data Source with CustTable

6-21-2014 9-02-30 PM


Right click on Fields and add following fields from Customer table.


CustomerFileds


Expand Data Source inside Custtable, Add or Create DataSource with SalesTable and fields


SalesOrder


Right Click on  SalesTables Data Source and set its join properties as follows:


DataSetProperties


Expand Relationship node of SalesTable_1


Add following relationship:


Relations




Set it properties as follows:


RelationshipDetail


Now, create a new report In existing or New DataModel Report in Visual Studio project:


NewReport Project




Add New report with DyCustSalesOrderList


Add Dataset with Name DSCustomer and Point to Query which we created in above steps:


PointToQuery






QueryDataSet




Now drag and drop the dataset on designer to create a designer
And Update, rename it to  DyCustSalesOrderList


Set DataTable’s Propeties visible to set false


DataTable




Add a List and set its name to CustList


Create two groups, one for CustomerGroup and second for AccountNum:


Groups


Now drop the fields from dataset which you want to display on report




Fields




Right click on CustList and set Data Navigation Style to DrillDown from properties window






DrillDownProperties






Now right click add report to aot and then deploy to Report Server
When you run the report,  from meu Item you will find following




CustomerGroupClick
Click on Customer group this will open:
reportCustomerLevel




Click on Customer account it will drill down report on Detail level:




Details

Date Lookup in string control

Sometimes, you may need to lookup the date calendar in a string control. This was easily possible by overriding the lookup function and call the below method in the AX older versions.
public void lookup()
{
;

this.performFormLookup(SysLookup::LookupDate());
}


But in AX2012R2 you cannot use the lookupDate function like this because it is made protected and can only be used by functions in the classes which are derived from SysLookup.
Its work around is to directly call the SysDateLookup form.
The code is as below.


public void lookup() // Form control lookup
{
FormRun formRun;
Args args;
;

args = new Args(formStr(SysDateLookup));
args.caller(element);
formRun = classfactory.formRunClass(args);
formRun.init();
this.performFormLookup(formRun);
}

Date Lookup in string control

Sometimes, you may need to lookup the date calendar in a string control. This was easily possible by overriding the lookup function and call the below method in the AX older versions.
public void lookup()
{
    ;

    this.performFormLookup(SysLookup::LookupDate());
}


But in AX2012R2 you cannot use the lookupDate function like this because it is made protected and can only be used by functions in the classes which are derived from SysLookup.
Its work around is to directly call the SysDateLookup form.
The code is as below.


public void lookup() // Form control lookup
{
    FormRun formRun;
    Args    args;
    ;

    args        = new Args(formStr(SysDateLookup));
    args.caller(element);
    formRun     = classfactory.formRunClass(args);
    formRun.init();
    this.performFormLookup(formRun);
}

Fiscal Year Number Sequence

Here we are customizing existing Number sequence and convert it in to Fiscal year number sequence Many customer demands for Purchase order creation with automatic year means how to add year to a number sequence in AX2012,


e.g. to generate IDs such as 2013-xxxxx and 2014-xxxxx, that would automatically use the current year. Some people understand that number sequence scopes should allow that, but they don’t know how, from very little bit of customization we can automate purchase order number sequence with year

Customize Purchase order Number Sequence Class:

Add fiscal calendar parameter in NumberSeqModulePurchaseIOrder (LoadModule) method as shown in below screen


datatype.addParameterType(NumberSeqParameterType::FiscalCalendarPeriod, true, true);
fk1

Load NumberSeqModulePurchaseIOrder (Load Module) method Through Job

NumberSeqModulePurchaseOrder NumberSeqModulePurchaseOrder = new NumberSeqModulePurchaseOrder();
NumberSeqModulePurchaseOrder.load();


fk2




Before going to run the Job, Remove purchase order existing scope parameter record in NumberSequenceDatatype table, if we don’t remove previous Purchase order scope parameter record it will not effect in Segment configuration as shown below


CEU/Organization administration/Number Sequence/ Segment configuration




fk3


Add Fiscal Year Relation to PurchParamater Table:

CEU/Procurement and sourcing/procurement and Sourcing parameters

fk4


We have customized procurement and Sourcing parameters form for Purchase order Fiscal Number sequence, because we need to select which Fiscal year the purchase order is created every year.


Add fiscal year table relation to PurchParamter table


fk5


Add a fiscal year Field on Procurement and Sourcing parameters form


fk6

Customize Purchase Order Number Sequence Table (PurchParameter):

Add following code in Purchase order Number sequence Parameter table method (numRefPurchId), in this existing method we have change code according to fiscal year.
static client server NumberSequenceReference  numRefPurchId(TransDate _date = systemdateget())
{
//
PurchParameters PurchParameters;
NumberSeqScope scope;

select firstOnly PurchParameters; // get Selected fiscal year in
// procurement and Sourcing parameters Form

scope = NumberSeqScopeFactory::CreateDataAreaFiscalCalendarPeriodScope
(curext(),FiscalCalendars::findPeriodByPeriodCodeDate(FiscalCalendar::findByCalendarId(PurchParameters.FiscalCalendar).RecId, _date).RecId);
//

return NumberSeqReference::findReference(extendedTypeNum(PurchId),scope);
}


fk7

Fiscal Year Number Sequence Configuration

General ledger -> Setup -> Fiscal calendars.


Create a new Fiscal year or use existing Fiscal year, but In our case we have created our own Fiscal year for Ten years because we need to create Purchase order automated for Ten years


fk8




If you want to add more years follow below screen.


fk9




Click the Calendar -> Calendar year -> New fiscal year button.


Change Copy from last fiscal year from ‘true’ to ‘false’.


Change Unit from ‘Months’ to ‘Years’.


Click the Create button.


Close the Fiscal calendars form.

Number sequences creation

fk10




Click Area Page node: Organization administration -> Common -> Number sequences -> Number sequences. Click the Number sequence -> New -> Number sequence button.




fk11




Change Number sequence code from ” to ‘POFiscal’.


Change Name from ” to ‘2013’.


Change Scope from ‘Shared’ to ‘Company and Fiscal calendar period’.


Change Company from ” to ‘ceu’.


Change Calendar from ” to ‘FiscalYear’.


Change Fiscal year from ” to ‘2013’.


Change Period name from ” to ‘Period 1′.




fk12






Make it very clear that we are creating PO number sequence for the year 2013 and we have to create PO number sequence again for Year 2014, 2015, 2016.xxxxxx 2023 with same Number sequence Code i.e. ‘POFiscal’  as shown in below screen for Year 2014






fk13

Creating Purchase orders:

Before going to create Purchase order make sure that Fiscal year must be selected in Procurement and sourcing Parameter form which we have customized earlier above




fk14






This selected Fiscal year must be same which we have selected in creating Purchase order Fiscal year Number Sequence.

Fiscal Year Number Sequence

Here we are customizing existing Number sequence and convert it in to Fiscal year number sequence Many customer demands for Purchase order creation with automatic year means how to add year to a number sequence in AX2012,


e.g. to generate IDs such as 2013-xxxxx and 2014-xxxxx, that would automatically use the current year. Some people understand that number sequence scopes should allow that, but they don’t know how, from very little bit of customization we can automate purchase order number sequence with year

Customize Purchase order Number Sequence Class:

Add fiscal calendar parameter in NumberSeqModulePurchaseIOrder (LoadModule) method as shown in below screen


datatype.addParameterType(NumberSeqParameterType::FiscalCalendarPeriod, true, true);
fk1

Load NumberSeqModulePurchaseIOrder (Load Module) method Through Job

NumberSeqModulePurchaseOrder NumberSeqModulePurchaseOrder = new NumberSeqModulePurchaseOrder();
NumberSeqModulePurchaseOrder.load();


fk2




Before going to run the Job, Remove purchase order existing scope parameter record in NumberSequenceDatatype table, if we don’t remove previous Purchase order scope parameter record it will not effect in Segment configuration as shown below


CEU/Organization administration/Number Sequence/ Segment configuration




fk3


Add Fiscal Year Relation to PurchParamater Table:

CEU/Procurement and sourcing/procurement and Sourcing parameters

fk4


We have customized procurement and Sourcing parameters form for Purchase order Fiscal Number sequence, because we need to select which Fiscal year the purchase order is created every year.


Add fiscal year table relation to PurchParamter table


fk5


Add a fiscal year Field on Procurement and Sourcing parameters form


fk6

Customize Purchase Order Number Sequence Table (PurchParameter):

Add following code in Purchase order Number sequence Parameter table method (numRefPurchId), in this existing method we have change code according to fiscal year.
static client server NumberSequenceReference  numRefPurchId(TransDate _date = systemdateget())
{
   //
   PurchParameters PurchParameters;
   NumberSeqScope  scope;

select firstOnly PurchParameters; // get Selected fiscal year in
                                  // procurement and    Sourcing parameters Form 

   scope = NumberSeqScopeFactory::CreateDataAreaFiscalCalendarPeriodScope
                            (curext(),FiscalCalendars::findPeriodByPeriodCodeDate(FiscalCalendar::findByCalendarId(PurchParameters.FiscalCalendar).RecId, _date).RecId);
   //

     return NumberSeqReference::findReference(extendedTypeNum(PurchId),scope);
}


fk7

Fiscal Year Number Sequence Configuration

General ledger -> Setup -> Fiscal calendars.


Create a new Fiscal year or use existing Fiscal year, but In our case we have created our own Fiscal year for Ten years because we need to create Purchase order automated for Ten years


fk8




If you want to add more years follow below screen.


fk9




Click the Calendar -> Calendar year -> New fiscal year button.


Change Copy from last fiscal year from ‘true’ to ‘false’.


Change Unit from ‘Months’ to ‘Years’.


Click the Create button.


Close the Fiscal calendars form.

Number sequences creation

fk10




Click Area Page node: Organization administration -> Common -> Number sequences -> Number sequences. Click the Number sequence -> New -> Number sequence button.




fk11




Change Number sequence code from ” to ‘POFiscal’.


Change Name from ” to ‘2013’.


Change Scope from ‘Shared’ to ‘Company and Fiscal calendar period’.


Change Company from ” to ‘ceu’.


Change Calendar from ” to ‘FiscalYear’.


Change Fiscal year from ” to ‘2013’.


Change Period name from ” to ‘Period 1′.




fk12






Make it very clear that we are creating PO number sequence for the year 2013 and we have to create PO number sequence again for Year 2014, 2015, 2016.xxxxxx 2023 with same Number sequence Code i.e. ‘POFiscal’  as shown in below screen for Year 2014






fk13

Creating Purchase orders:

Before going to create Purchase order make sure that Fiscal year must be selected in Procurement and sourcing Parameter form which we have customized earlier above




fk14






This selected Fiscal year must be same which we have selected in creating Purchase order Fiscal year Number Sequence.