Saturday, December 11, 2010

Data Upgrade from AX 3.0/AX 4.0 to AX 2009

Hi,

In this post, I would like to explain you my experiences on the data upgrade from AX 3.0 and AX 4.0 to AX 2009. Please post your comments if you have any doubts on this........


Data Upgrade from AX 3.0 to AX 2009
  1. Perform the Consistency Check for all the required modules in AX 3.0 application and proceed to upgrade only if there are no errors during the consistency check process else it will create problems during the process of running the upgrade checklist.
  2. Back up your existing database and application files.
  3. Delete the tables, AXOLDTONEWRECIDS and Sysdiagrams (System Table), as the same does not get transferred to the AX2009 database in the upgrade tool and later issues are thrown up.
  4. Import two .xpo files from the installation media to assist with data upgrade. -UpgradeColumnList.xpo, for 32-bit to 64-bit RecId field conversion. -LeftJustified.xpo, for removing any trailing spaces from fields. Note: To help improve performance, you can apply the LeftJustified.xpo on the database that you create in step 4 after you've used the Microsoft Dynamics AX DB Upgrade Preparation tool but before you start the Microsoft Dynamics AX 2009 AOS. See step 8.
  5. (Optional) To help improve performance, remove all user data and logs of Microsoft Dynamics AX 3.0. For example, clean up the SysDatabaseLog table.
  6. Create an empty database for Microsoft Dynamics AX 2009 in SQL Server 2005.
  7. (Optional) To help improve performance, set initial data and log file sizes so that they don't increase while you perform the data upgrade process.
  8. (Optional) To help improve performance, set the recovery model to Simple for the Microsoft Dynamics AX 2009 Database.
  9. Run AXDBUpgrade.exe (The Microsoft Dynamics AX DB Upgrade Preparation tool). Note: To help improve performance, you can run this tool in Multithreaded mode. For example, to run this tool in 10 threads, enter AxDbUpgrade.exe P/10 at a command prompt.
  10. (Optional) Apply the LeftJustify file imported in step 2 to the Microsoft Dynamics AX 2009 database created in step 4.
  11. Back up your Microsoft Dynamics AX database. Your database is ready to be upgraded.
  12. Run the Microsoft Dynamics AX 2009 Setup file from the installation media. During installation, select the database that you created in step 4.
  13. Copy your upgraded customized file into the correct application directory.
  14. Start the AOS.
  15. Start the Microsoft Dynamics AX 2009 client. The Upgrade checklist is displayed automatically.
  16. Complete the steps in the Upgrade checklist to finish upgrading.
  17. Remember to take the backup of database and application files each and every stage of the upgrade checklist so that if there is any error, we can go back and start the process from that stage. this would save our time.

Data upgrade from AX 4.0 to AX 2009

  1. Perform the Consistency Check for all the required modules in AX 3.0 application and proceed to upgrade only if there are no errors during the consistency check process else it will create problems during the process of running upgrade checklist.
  2. Back up your existing database and application files.
  3. (Optional) To help improve performance, remove all user data and logs of Microsoft Dynamics AX 4.0. For example, clean up the SysDatabaseLog table.
  4. (Optional) To help improve performance, set initial data and log file sizes so that they don't increase while you perform the data upgrade process.
  5. (Optional) To help improve performance, set the recovery model to Simple for the Microsoft Dynamics AX 2009 Database.
  6. Back up your Microsoft Dynamics AX database. Your database is ready to be upgraded.
  7. Run the Microsoft Dynamics AX 2009 Setup file from the installation media. During installation, select your existing Microsoft Dynamics AX database.
  8. Start the Microsoft Dynamics AX 2009 client. The Upgrade checklist is displayed automatically.
  9. Complete the steps in the Upgrade checklist to finish upgrading.

Friday, October 22, 2010

Rename a Primary Key in AX through code

In this post, I am going to discuss about how to rename a primary key in AX. The renaming can be done for any record using the CCPrimaryKey Class. Let us suppose, if Customer Id 1000 has to be renamed as Cust_1000 then it will be renamed in all the tables(SalesTable, CustTable, SalesQuotationTable etc.,) wherever this cutomer Id has been used. The generic way to rename a primary key for any table is as follows:

Send a record to this method as a parameter.

void renamePrimaryKey(Common _common)
{
Common common;
FieldId fieldId;
DictTable dictTable;
DictField dictField;
;
common = _common;
dictTable = new SysDictTable(common.TableId);
dictField = new SysDictField(dictTable.id(), dictTable.primaryKeyField());

if (isConfigurationkeyEnabled(configurationkeynum(SIG)))
{
SIGBaseDocument::checkAndCacheRename(common,dictField.id(),newValue);
}

startLengthyOperation();
fieldId = dictField.id();
try
{
ttsbegin;

// CC Start
CCPrimaryKey::renamePrimaryKey(common, dialogField.value(), fieldId);
// CC End

common.(fieldId) = dialogField.value();

common.renamePrimaryKey();
if (common.TableId == tablenum(UserInfo))
{
common.update();
}
ttscommit;
}
catch (Exception::Error)
{
ttsabort;
}

}

Tuesday, July 27, 2010

Create Delete Actions through code in AX

Hi Folks,

It has been a long time, since I have posted any blog because of my busy project schedule.............
In this post, I am going to discuss on how to create delete actions through code in AX.

Let us create a delete action in InventTable for a testTable.

SysDictTable table;
Treenode treeNode, datreeNode;
table = new sysdicttable(tablename2id("InventTable"));
treeNode = table.treeNode();
treeNode = treeNode.AOTfindChild("DeleteActions");
DAtreeNode = treeNode.AOTadd('UNKNOWN');
DAtreeNode.AOTsetProperty("Table", "TestTable");
DAtreeNode.AOTsetProperty("DeleteAction","Cascade");
DAtreeNode.AOTsave();
In the above code snippet, we get into the AOT node by node and go till the delete actions of the table and then create the delete actions for that table.

After executing this code, just open the AOT and click on save icon. Now, just close the AX client and re-open it again. Now, you can find the delete actions created through code.

Friday, May 28, 2010

Get all Error Messages of Infolog in the desired language into a string and return it to external application

If you want to get all the error messages in the infolog in a desired language then just put this small code snippet and run it in a job.

infolog.language(LanguageId);

then whole UI of AX Client will be changed to the desired language and we will get the error messages into the infolog in that language only.

Now, when you are communicating an external application with AX and the language id of the login user of AX is different from the desired language, if you want to catch the error messages in any of your methods then the code snippet is as follows:

In the try catch block, put the following code into catch and thus you can get all error messages into a string and return it to an external application in the desired language.

Str infoLogInfo, infologErrorMsg, errorMsg;
Container holdError;
LanguageId languageId, defaultLanguageId;

//Desired Language Id
languageId = 'de';
//Default user Language Id
DefaultLanguageId = languagetable::defaultLanguage();
//Input the desired language id to infolog
infolog.language(LanguageId);
//collect the error messages from infolog in the desired language into a container
HoldError = infolog.infologData();
for(i=2;i<=conlen(HoldError);i++)
{
[InfoLogInfo,InfologErrorMsg]= conpeek(HoldError,i);
ErrorMsg = ErrorMsg +'||'+ InfologErrorMsg;
}
//Change back to the original language
infolog.language(DefaultLanguageId);
//return the error messages in the desired language
return ErrorMsg;

Saturday, May 15, 2010

Insert / Delete / Update records into an external SQL database from AX using x++

Inserting/Deleting/Updating records into an external SQL database from AX 2009 using x++ can be done using ADO Classes in AX. This can be told from the following code snippet.

In the below code snippet CCADOConnection is used to establish a connection with the external database and CCADOCommand is used to execute the command on the external database.

If we want to establish a connection using windows authentication then the connection string must be as follows:
conn.open(@"Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=VS-08-22\SQLEXPRESS;Initial Catalog=TestDatabase;")

If we want to establish a connection using SQL Server Authentication then the connection string must be as follows:
conn.open(@"Provider=SQLOLEDB;Data Source=VS-08-22\SQLEXPRESS;Initial Catalog=TestDatabase;User Id=sa;Password=sa;")


The complete program is here below:


CCADOConnection conn;
CCADOCommand cmd;
CustTable custTable;
;

conn = new CCADOConnection();
cmd = new CCADOCommand();


//To insert data into External database from Axapta Database
//Begin
// Establish a connection with SQL Server
conn.open(@"Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=VS-08-22\SQLEXPRESS;Initial Catalog=TestDatabase;");
cmd.activeConnection(conn);

while select CustTable
{
//Execute the command
cmd.commandText(strfmt("insert into Customer(CustId,CustName,CustGroup,currency) values ('%1','%2',%3,'%4')",CustTable.AccountNum,CustTable.Name,CustTable.CustGroup,CustTable.Currency));
cmd.execute();
}
//End

//To Delete data from External table using X++
//Begin
conn.open(@"Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=VS-08-22\SQLEXPRESS;Initial Catalog=TestDatabase;");
cmd.activeConnection(conn);
cmd.commandText("delete from Customer where CustId='2'");
cmd.execute();
//End

//To Update External table data from X++
//Begin
conn.open(@"Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=VS-08-22\SQLEXPRESS;Initial Catalog=TestDatabase;");
cmd.activeConnection(conn);
cmd.commandText("update Customer set CustName ='Update' where CustId='3'");
cmd.execute();
//End

Thursday, April 29, 2010

Paging in a Query(Query run)/Paging while retrieving the records from a query

Today, I have a found a new function in QueryRun class and thought of sharing it here in this post. Its use can be found while integrating AX with some external applications.

I am sure that everyone has used queryrun class in x++ code. I have found out a special function in QueryRun Class with which we can do paging while retrieving the records.i.e., retrieve a range of rows from a query i.e., For example, if we get some 14000 records from a query but we want to retrieve the records from 11501 to 12000 only then this could be a useful tip. you can follow the below code snippet to perform paging for a query.

Query query = new Query();
QueryBuildDataSource qbds;
QueryRun qr;
tableid tableid;


tableid = tablename2id('InventTable');
//Add a datasource to a query
qbds = query.addDataSource(tableid);
//Order by item id
qbds.addOrderByField(fieldname2id(tableid,'ItemId'));
//Instantiate a Queryrun class
qr = new QueryRun(Query);
qr.literals(true);
// Enable position paging for the queryrun object
qr.enablePositionPaging(true);
//Add a range by providing the parameters as starting record number and number of records
qr.addPageRange(11501,500);
xmlDoc = XMLDocument::newBlank();
// Create first line containing version info
rootNode = xmlDoc.documentElement();
xmlElement = xmlDoc.createElement('OUTPUT');
rootNode = xmlDoc.appendChild(xmlElement);
while (qr.next())
{
common = qr.getNo(1);
xmlElement = xmlDoc.createElement('ITEMID');
NodeItemId = rootNode .appendChild(xmlElement);
xmlText = xmlDoc.createTextNode(common.(Fieldname2id(tabid, 'ItemId')));
NodeItemId .appendChild(xmlText);
}
Now, thus we can get the list of required 500 itemids into an xml and thus we can pass it to some external application.

Although it has huge advantage, it has a disadvantage too, i.e., this cannot be used on a temporary table.

An important point to remember is that an ordering must be done for the query before paging is done. For Ex: qbds.addOrderByField(fieldname2id(tableid,'ItemId')) or qbds.addOrderByField(fieldname2id(tableid,'ItemId'), sortorder::descending) or any other ordering method. Therefore, Paging will not work if the query is not in a sorted order.



Tuesday, April 27, 2010

Convert pdf or any type of file to base 64 string in AX using x++

Here is a code snippet to convert any file into a base64 string in x++ using some dot net inbuilt classes in AX.

To convert it, initially clrinterop permission must be granted to access the dot net inbuilt classes then load the file into file info, initialize the byte array with the length of file, stream and read the file and then convert the file to a base 64 string.

System.Byte[] pdfDocBuffer;
System.IO.FileInfo fi_pdfDoc;
System.IO.FileStream fs;
str Content;
// Grant clrinterop permission.
new InteropPermission(InteropKind::ClrInterop).assert();
//Load the file
fi_pdfDoc = new System.IO.FileInfo(@'C:/SalesOrder.pdf');
//Initiallize the byte array by setting the length of the file
pdfDocBuffer= new System.Byte[int642int(fi_pdfDoc.get_Length())]();
// Stream the file
fs= new System.IO.FileStream(fi_pdfDoc.get_FullName(), System.IO.FileMode::Open, System.IO.FileAccess::Read);
fs.Read(pdfDocBuffer, 0, pdfDocBuffer.get_Length());
// Convert the file into a base64 string
Content = System.Convert::ToBase64String(pdfDocBuffer, 0, pdfDocBuffer.get_Length());
//Revert the access
CodeAccessPermission::revertAssert();

Thats it done..................Now njoy converting any file into a base64 string from AX.

Friday, April 23, 2010

Store and retrieve an image in a table for a record in AX

The simplest way to store an image for a record into AX tables is by using the CompanyImage Menu Item. I will explain you with an example below:

  1. Generally, most of the customers would request for a functionality to store an image for an item. For this, there is no need of high customization.
  2. Initially, Goto the Design in InventTable Form and add a Display Menu Item called CompanyLogo to its button Group.
  3. Change the label of the Menu item button in the form.
  4. Now, open the InventTable form and click on the menu item and load an image associated with a record.
  5. This image will be stored in the CompanyImage table as a BLOB in a container with the refRecId, RefTableId and RefCompanyId of the associated record(Here Record of InventTable).
  6. Retrieving it and saving to file system again is pretty easy with the help of binData Class. Here is the supporting code snippet....
bindata bin = new bindata();
str content;
container image;
InventTable inventTable;
;
inventTable = inventTable::find('100003');
image = companyimage::find(inventTable.dataAreaId, inventTable.TableId, inventTable.RecId).Image;
bin.setData(image);
// Create the base64 encoded string
content=bin.base64Encode();
info(content);
// Save it to the file system as a tif format
AifUtil::saveBase64ToFile(@"C:\test.tif", content);

Thats it done, Now you can see the image in your file system....................This can be done to store and retrieve the images for any record from any table.

Wait For more posts on encoding files in AX.

Wednesday, April 21, 2010

calculate total sales order or sales quotation amount / discounts / tax etc., through code( X++ ) in AX

I have seen that few developers have a problem in getting the total Sales order amount or sales quotation amount with Tax /discounts etc., in the preferred currency before invoicing because these are not stored into any specific tables.

The below form shows, where we can see the sales totals, Accounts Receivable->Sales Order-> Inquiries Menu->Totals

Now, this can be achieved through code using SalesTotals class. Check out the below code snippet.


SalesTotals salesTotals;
SalesTable salesTable;
container displayFields;
str totalTax, amountWithoutTax, amountInclTax;

salesTable = salesTable::find('SO-1112345');
salesTotals = SalesTotals::construct(salesTable, salesUpdate::All);
salesTotals.calc();
displayFields = salesTotals.displayFieldsCurrency(salesTotals.currencyCode());

amountWithoutTax = conpeek(displayFields, TradeTotals::posBalance());
amountInclTax = conpeek(displayFields, TradeTotals::posTotalAmount());
totalTax = conpeek(displayFields,TradeTotals::posTaxTotal());

In the above way, we can get all the values available in the totals form in the Sales Order Form by changing the TradeTotals values.

We can get the values in the desired currency by providing a valid currency as a parameter to the displayFieldsCurrency method.

In the same way we can apply it to get the Sales Quotation totals also but instead of salestotals we need to use SalesQuotationTotals class.

Tuesday, April 20, 2010

Print Sales Invoice Report in PDF / HTML / RTF / ASCII etc., format through code

I have observed that lot of developers face difficulty in printing the Sales Invoice report into file system through code. Here is the code snippet for it.

SalesFormLetter salesFormLetter;
PrintJobSettings printJobSettings;
CustInvoiceJour custInvoiceJour;
SalesId salesId;
#File
salesid = "SO-100010";
salesFormLetter = SalesFormLetter::construct(DocumentStatus::Invoice,false);
printJobSettings = new PrintJobSettings();
printJobSettings.setTarget(PrintMedium::File);
printJobSettings.format(PrintFormat);
printJobSettings.fileName( '//10.0.57.22/AOSPrintShare//' + salesId+#pdf );
printJobSettings.warnIfFileExists(false); SalesFormLetter.updatePrinterSettingsFormLetter(printJobSettings.packPrintJobSettings());
select custInvoiceJour where custInvoiceJour.SalesId == salesId;
custInvoiceJour.printJournal(salesFormLetter);

In the above Code, I have tried to print a Sales Invoice Report in pdf format. In the same way by changing the extension you can print the same report in all valid formats like html, rtf, ascii etc.,

Sunday, April 18, 2010

send e-mail through code with attachments in AX

1. To send an email through AX, Initially we need to configure the e-mail parameters in the administration module.

2. If you want to configure an email template. That can also be done from the basic module.

LanguageId LanguageId;
FilePath FilePath;
SysMailer mailer;
SysEmailAddress ToAddress;
SysEmailId EmailId;
new InteropPermission(InteropKind::ComInterop).assert();
FilePath = @'//10.0.23.25/AOSPrintShare/TestSalesOrder.pdf;
mailer = New SysMailer();
mailer.fromAddress(SysEmailTable::find(EmailId).SenderAddr, SysEmailTable::find(EmailId).SenderName);
mailer.subject(SysEmailMessageTable::find(EmailId, LanguageId).Subject);
mailer.tos().appendAddress(Toaddress);
mailer.htmlBody(SysEmailMessageTable::find(EmailId, LanguageId).Mail);
mailer.SMTPRelayServer(SysEmailParameters::find().SMTPRelayServerName,
SysEmailParameters::find().SMTPPortNumber,
SysEmailParameters::find().SMTPUserName,SysEmailParameters::password(),
SysEmailParameters::find().NTLM);
mailer.attachments().add(FilePath);
mailer.sendMail();
CodeAccessPermission::revertAssert();

In the above code, Initially I have given COM object access permission and then i have given the UNC path of the pdf file that has to be attached and assigned it to the file path variable then I have attached the required parameters to the sysmailer class and then performed sending e-mail functionality and then reverted back the code access.

Wednesday, April 14, 2010

How to use COM Wrapper dll in AX

Here, I am telling you about how to register a COM wrapper dll and then extract & use it in AX.

(i) Install the COM Wrapper DLL Files, into the bin directory of the AOS.

(ii) Register it with the following Command,%WINDIR%\Microsoft.NET\Framework\v2.0.50727\regasm.exe Test.Wrapper.DLL /tlb:Test.Wrapper.tlb

(iii) Now, After Successful Registration, Follow the below path and click on COM Class Wrapper Wizard.i.e., Tools->Development tools->Wizards->COM Class Wrapper Wizard

(iv) On the first Page of the Wizard, Click Next Button

(v) Select the installed Wrapper and then click Next Button.

(vi) Then give some string as an element mask and then click finish.

Now, you can see that all the classes created in the wrapper dll will be present in the AOT.

Just directly use those class/classes in your code.

Things to remember while you call this class :
As the Wrapper DLL has been registered in the AOS, So the methods in the extracted classes have to be called from the methods of classes whose run on property is server.








Monday, April 12, 2010

Common problem in Printing AX Reports as a file using Batch job scheduling through code in the Server File System

I have come across many people having a problem in printing the AX reports into the file system of server using batch job scheduling............

The main problem is in configuration.........

The solution is pretty simple and is as follows:

As AX batch job scheduling always runs on the server, the below configuration has to be followed:

(i) Enable AOS printing on the AOS server configuration (see below). Do not restart the AOS yet.


(ii) Due to a bug in the Configuration Utility, we need to manually update the corresponding value in the registry.

In this path-> HKEY-LOCAL MACHINE->SOFTWARE->MICROSOFT->DYNAMICS-5.0->Configuration->Original search for the Name

useserverprinters and set the value to “1”. After that restart the AOS.

(iii) Provide an UNC Path on the AOS computer that will used for saving the PDF Files (temp. only).

Thats it done.............

Now you can print the AX reports as a pdf, rtf,txt or any other valid format through code(x++) from batch job scheduling.

Saturday, April 10, 2010

My First Blog

Hi Everyone,
My name is Giridhar Raj Singh Chowhan.
I have been working on Microsoft Dynamics AX for over 2.5 years as a Technical Consultant.........
For a long time, I have been thinking of blogging about my experiences in AX...........
Atlast today I have started blogging.............
It is always very exciting when you start something new..........
My main motto of starting this blog is to share my knowledge & experience on AX and write about the most common problems and solutions that I have faced in my implementations............
So, that the developers can save their time and also get some good amount of knowledge................
You can encourage me by leaving your comments for my posts, Your feedback will always be appreciated and encouraged......................
you can always reach me at giridharax@gmail.com.......................

Thanks and Regards
Giridhar Raj.