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.

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.

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.

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.

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

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.

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.

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.

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.