Folk,
Here is quick code to create the delivery address on runtime. You can use this on Purchase Requisition, Purchase order or a person.
So we are going to use LogisticsPostalAddressEntity to get the right address with a new one or an existing one. This code should also handle if there is any update in any existing record by updating effective date stamp.
Have a look on below code,
Give a try and share your feedback.
Cheers!!!
Harry.
Search This Blog
Showing posts with label Jobs. Show all posts
Showing posts with label Jobs. Show all posts
December 11, 2018
January 17, 2017
Item Image upload through X++ code
Hi All,
Here is x++ code to upload item images in the system.
Cheers
Harry
August 26, 2015
Date manipulation through X++ code [AX 2012]
Hi Folks,
I come though a requirement where I need to manipulate on “FromDate” and “ToDate”. When user enter these value through a dialog box, user can select any day from month for eg.
From Date: June 6, 2015
To Date: August 20, 2015
![image image](//lh3.googleusercontent.com/-P-9PF_O_v0w/Vd084Vl0FzI/AAAAAAAABCM/-VCoOWWl3ck/image_thumb%25255B3%25255D.png?imgmax=800)
And to perform some action on Financial stuff we may need to consider the whole month like
from June 1, 2015 to August 31, 2015
You can fetch this month range in your x++ code, here is a sample code, try it…
static void theAxapta_MonthRange(Args _args)
{
DialogField dialogStartDate, dialogEndDate;
Dialog dialog = new Dialog("Month range");
TransDate fromDate, todate;
dialogStartDate = dialog.addField(extendedTypeStr(TransDate));
dialogEndDate = dialog.addField(extendedTypeStr(TransDate));
dialogStartDate.label("From Date");
dialogEndDate.label("To Date");
if(dialog.run())
{
fromDate = mkDate(1, mthOfYr(dialogStartDate.value()), year(dialogStartDate.value()));
toDate = (mkDate(1, (mthOfYr(dialogEndDate.value()) + 1), year(dialogEndDate.value())) - 1);
info(strFmt("Start Month %1", fromDate));
info(strFmt("End Month %1", todate));
}
}
Output:
![image image](//lh3.googleusercontent.com/-2RtIihQc4aw/Vd086NxzKlI/AAAAAAAABCs/yqC4QWSyy0g/image_thumb%25255B5%25255D.png?imgmax=800)
In case of any suggestion and query, feel free to drop as a comment.
-Harry
I come though a requirement where I need to manipulate on “FromDate” and “ToDate”. When user enter these value through a dialog box, user can select any day from month for eg.
From Date: June 6, 2015
To Date: August 20, 2015
![image image](http://lh3.googleusercontent.com/-P-9PF_O_v0w/Vd084Vl0FzI/AAAAAAAABCM/-VCoOWWl3ck/image_thumb%25255B3%25255D.png?imgmax=800)
And to perform some action on Financial stuff we may need to consider the whole month like
from June 1, 2015 to August 31, 2015
You can fetch this month range in your x++ code, here is a sample code, try it…
static void theAxapta_MonthRange(Args _args)
{
DialogField dialogStartDate, dialogEndDate;
Dialog dialog = new Dialog("Month range");
TransDate fromDate, todate;
dialogStartDate = dialog.addField(extendedTypeStr(TransDate));
dialogEndDate = dialog.addField(extendedTypeStr(TransDate));
dialogStartDate.label("From Date");
dialogEndDate.label("To Date");
if(dialog.run())
{
fromDate = mkDate(1, mthOfYr(dialogStartDate.value()), year(dialogStartDate.value()));
toDate = (mkDate(1, (mthOfYr(dialogEndDate.value()) + 1), year(dialogEndDate.value())) - 1);
info(strFmt("Start Month %1", fromDate));
info(strFmt("End Month %1", todate));
}
}
Output:
![image image](http://lh3.googleusercontent.com/-2RtIihQc4aw/Vd086NxzKlI/AAAAAAAABCs/yqC4QWSyy0g/image_thumb%25255B5%25255D.png?imgmax=800)
In case of any suggestion and query, feel free to drop as a comment.
-Harry
April 20, 2015
How to send Email through X++ code
Hi All,
Use below code to sent emails from X++ code. Below code is written in a Job you can reuse the same in your logic.
static void dpk_TestEmail(Args _args)
{
// Set these variables.
str sender = “sender@dnaitservices.com”;
str recipient = 'rcpt@dnaitservices.com';
str cc = 'cc@dnaitservices.com';
str subject = 'TheAxapta_Subject';
str body = 'TheAxapta_Msg';
str fileName = @'D:\test.txt';
List toList;
List ccList;
ListEnumerator le;
Set permissionSet;
System.Exception e;
str mailServer;
int mailServerPort;
System.Net.Mail.SmtpClient mailClient;
System.Net.Mail.MailMessage mailMessage;
System.Net.Mail.MailAddress mailFrom;
System.Net.Mail.MailAddress mailTo;
System.Net.Mail.MailAddressCollection mailToCollection;
System.Net.Mail.MailAddressCollection mailCCCollection;
System.Net.Mail.AttachmentCollection mailAttachementCollection;
System.Net.Mail.Attachment mailAttachment;
;
try
{
toList = strSplit(recipient, ';');
ccList = strSplit(cc, ';');
permissionSet = new Set(Types::Class);
permissionSet.add(new InteropPermission(InteropKind::ClrInterop));
permissionSet.add(new FileIOPermission(filename, 'rw'));
CodeAccessPermission::assertMultiple(permissionSet);
mailServer = SysEmaiLParameters::find(false).SMTPRelayServerName;
mailServerPort = SysEmaiLParameters::find(false).SMTPPortNumber;
mailClient = new System.Net.Mail.SmtpClient(mailServer, mailServerPort);
le = toList.getEnumerator();
le.moveNext();
mailFrom = new System.Net.Mail.MailAddress(sender);
mailTo = new System.Net.Mail.MailAddress(strLTrim(strRTrim(le.current())));
mailMessage = new System.Net.Mail.MailMessage(mailFrom, mailTo);
mailToCollection = mailMessage.get_To();
while (le.moveNext())
{
mailToCollection.Add(strLTrim(strRTrim(le.current())));
}
le = ccList.getEnumerator();
mailCCCollection = mailMessage.get_CC();
while (le.moveNext())
{
mailCCCollection.Add(strLTrim(strRTrim(le.current())));
}
mailMessage.set_Priority(System.Net.Mail.MailPriority::High);
mailMessage.set_Subject(subject);
mailMessage.set_Body(body);
mailAttachementCollection = mailMessage.get_Attachments();
mailAttachment = new System.Net.Mail.Attachment(fileName);
mailAttachementCollection.Add(mailAttachment);
mailClient.Send(mailMessage);
mailMessage.Dispose();
CodeAccessPermission::revertAssert();
info("Email sent.");
}
catch (Exception::CLRError)
{
e = ClrInterop::getLastException();
while (e)
{
info(e.get_Message());
e = e.get_InnerException();
}
CodeAccessPermission::revertAssert();
}
}
-Harry
Use below code to sent emails from X++ code. Below code is written in a Job you can reuse the same in your logic.
static void dpk_TestEmail(Args _args)
{
// Set these variables.
str sender = “sender@dnaitservices.com”;
str recipient = 'rcpt@dnaitservices.com';
str cc = 'cc@dnaitservices.com';
str subject = 'TheAxapta_Subject';
str body = 'TheAxapta_Msg';
str fileName = @'D:\test.txt';
List toList;
List ccList;
ListEnumerator le;
Set permissionSet;
System.Exception e;
str mailServer;
int mailServerPort;
System.Net.Mail.SmtpClient mailClient;
System.Net.Mail.MailMessage mailMessage;
System.Net.Mail.MailAddress mailFrom;
System.Net.Mail.MailAddress mailTo;
System.Net.Mail.MailAddressCollection mailToCollection;
System.Net.Mail.MailAddressCollection mailCCCollection;
System.Net.Mail.AttachmentCollection mailAttachementCollection;
System.Net.Mail.Attachment mailAttachment;
;
try
{
toList = strSplit(recipient, ';');
ccList = strSplit(cc, ';');
permissionSet = new Set(Types::Class);
permissionSet.add(new InteropPermission(InteropKind::ClrInterop));
permissionSet.add(new FileIOPermission(filename, 'rw'));
CodeAccessPermission::assertMultiple(permissionSet);
mailServer = SysEmaiLParameters::find(false).SMTPRelayServerName;
mailServerPort = SysEmaiLParameters::find(false).SMTPPortNumber;
mailClient = new System.Net.Mail.SmtpClient(mailServer, mailServerPort);
le = toList.getEnumerator();
le.moveNext();
mailFrom = new System.Net.Mail.MailAddress(sender);
mailTo = new System.Net.Mail.MailAddress(strLTrim(strRTrim(le.current())));
mailMessage = new System.Net.Mail.MailMessage(mailFrom, mailTo);
mailToCollection = mailMessage.get_To();
while (le.moveNext())
{
mailToCollection.Add(strLTrim(strRTrim(le.current())));
}
le = ccList.getEnumerator();
mailCCCollection = mailMessage.get_CC();
while (le.moveNext())
{
mailCCCollection.Add(strLTrim(strRTrim(le.current())));
}
mailMessage.set_Priority(System.Net.Mail.MailPriority::High);
mailMessage.set_Subject(subject);
mailMessage.set_Body(body);
mailAttachementCollection = mailMessage.get_Attachments();
mailAttachment = new System.Net.Mail.Attachment(fileName);
mailAttachementCollection.Add(mailAttachment);
mailClient.Send(mailMessage);
mailMessage.Dispose();
CodeAccessPermission::revertAssert();
info("Email sent.");
}
catch (Exception::CLRError)
{
e = ClrInterop::getLastException();
while (e)
{
info(e.get_Message());
e = e.get_InnerException();
}
CodeAccessPermission::revertAssert();
}
}
-Harry
September 07, 2013
How to compare UTCDATETIME with DATE only in reports Filter
How to compare UTCDATETIME with DATE only in reports Filter
Some time we need to companre UTCDateTime with DATE datatype. Generally we faced this issue during reports dialog box.
here is a the solution to compare the UTCDateTime and DATE datatypes in axapta.
public class ReportRun extends ObjectRun
{
DialogField DialogFromDate;
DialogField DialogToDate;
DialogField DialogPaymId;
UtcDateTime FromDate;
UtcDateTime ToDate;
Name PaymId;
}
public boolean getFromDailog()
{
;
fromDate = clrSystemDateTime2UtcDateTime(dialogFromDate.value());
todate = clrSystemDateTime2UtcDateTime(dialogTodate.value() + 1);
PaymId = DialogPaymId.value();
return true;
}
-Harry
September 06, 2013
Join Two Tables at Run Time
Join Two Tables at Run Time
static void theAxapta_JoinTables(Args _args){
Query query;
QueryBuildDataSource queryBuildDataSource1,
queryBuildDataSource2;
QueryBuildRange queryBuildRange;
QueryBuildLink queryBuildLink;
;
// Create a new query object
query = new Query();
// Add the first data source to the query
queryBuildDataSource1 = query.addDataSource(tablenum(CarTable));
// Add the range to this first data source
queryBuildRange = queryBuildDataSource1.addRange(fieldnum(CarTable, ModelYear));
// Add the second datasource to the first data source
queryBuildDataSource2 = queryBuildDataSource1.addDataSource(tablen
um(RentalTable));
// Add the link from the child data source to the
//parent data
source
queryBuildLink = queryBuildDataSource2.addLink(fieldnum(CarTable,
CarId),fieldnum(RentalTable, CarId));
}
Note:
This process (query through X++ code) is very similar to create a query directly through AOT node.
AOT -> Query -> Right Click -> New Query
I would suggest first create a query through query node, than go for this code.
-Harry
September 03, 2013
X++ Code to Find and Remove The Duplicate Objects in AOT
X++ Code to Find and Remove The Duplicate Objects in AOT
Please Note following cautions before proceeding....BackUp of Application:-
Setup a new instance and Copied the var.aod files (Or other layer where your customization exist) from production instance and restored the DB with live DB.
DO NOT DO THIS DIRECT ON LIVE SERVERS
Use the following code to delete the identical copy.
static void theAxapta_FindAndDeleteIdenticalObjects(Args _args)
{
SysTreeNode comparable1, comparable2;
TreeNode curLevelTreeNode, upperLevelTreeNode;
UtilIdElements utilElements, joinUtilElements;
;
while select UtilElements
where UtilElements.utilLevel == UtilEntryLevel::var &&
(
UtilElements.recordType == UtilElementType::Form ||
Utilelements.recordType == UtilElementType::Report ||
Utilelements.recordType == UtilElementType::Table ||
Utilelements.recordType == UtilElementType::Class ||
Utilelements.recordType == UtilElementType::Enum ||
Utilelements.recordType == UtilElementType::ExtendedType
)
{
//Should use join if for a normal table, but not applicable for UtilElements
//Performance hit if use exists join
select firstonly recid from joinUtilElements
where joinUtilElements.utilLevel != UtilElements.utilLevel &&
joinUtilElements.name == UtilElements.name &&
joinUtilElements.recordType == UtilElements.recordType;
if (joinUtilElements.RecId)
{
curLevelTreeNode = SysTreeNode::findNodeInLayer(UtilElements.recordType, UtilElements.name, UtilElements.parentId, UtilElements.utilLevel);
upperLevelTreeNode = SysTreeNode::getLayeredNode(curLevelTreenode, 1);
comparable1 = SysTreeNode::newTreeNode(curLevelTreeNode);
comparable2 = SysTreeNode::newTreeNode(upperLevelTreeNode);
if (SysCompare::silentCompare(comparable1, comparable2))
{
info(strFmt("Element name: %1, Element type: %2", UtilElements.name, enum2str(UtilElements.recordType)));
//Remove the node
curLevelTreeNode.AOTdelete();
}
}
}
}
-Harry
July 18, 2013
Inventory Transfer Journal through X++ code
Inventory Transfer Journal through X++ code
Hi All
Here is a small code for Invent Transfer journal Posting
{
InventJournalTable inventJournalTable;
InventJournalTrans inventJournalTrans;
InventJournalCheckPost inventJournalCheckPost;
NumberSeq num;
boolean _throwserror=true;
boolean _showinforesult=true;
InventDim frominventDim,ToinventDim;
;
ttsbegin;
inventJournalTable.clear();
num = new NumberSeq();
num = NumberSeq::newGetNum
(InventParameters::numRefTransferId());
inventJournalTable.initFromInventJournalName(InventJournalName::find
(InventParameters::find().TransferJournalNameId));
inventJournalTable.Description = “Inventory Transfer Journal”;
inventJournalTable.SystemBlocked = true;
inventJournalTable.insert();
inventJournalTrans.clear();
inventJournalTrans.initFromInventJournalTable(inventJournalTable);
inventJournalTrans.ItemId = “xxxxxx”;
frominventDim.InventLocationId=”xx”;
frominventDim.inventSiteId =”xx”;
ToinventDim.InventLocationId = “xxxx”;
ToinventDim.InventSiteId = “xx”;
ToinventDim = InventDim::findOrCreate(ToinventDim);
frominventDim = InventDim::findOrCreate(frominventDim);
inventJournalTrans.InventDimId = frominventDim.inventDimId;
inventJournalTrans.initFromInventTable(InventTable::find(“1101″));
inventJournalTrans.Qty = 10;
inventJournalTrans.ToInventDimId = ToinventDim.inventDimId;
inventJournalTrans.TransDate = SystemDateget();
inventJournalTrans.insert();
inventJournalCheckPost =
InventJournalCheckPost::newJournalCheckPost
(JournalCheckpostType::Post,inventJournalTable);
inventJournalCheckPost.parmThrowCheckFailed(_throwserror);
inventJournalCheckPost.parmShowInfoResult(_showinforesult);
inventJournalCheckPost.run();
inventJournalTable.SystemBlocked = false;
inventJournalTable.update();
ttscommit;
}
-Harry
May 06, 2013
X++ Code to Create Purchase Order and Post the Invoice.
X++ Code to Create Purchase Order and Post the Invoice.
Following code will create the Purchase order from code and post the invoice as well by making use of "PurchFormLetter" class.static void TheaxaptaCreatePOInvoice(Args _args)
{
NumberSeq numberSeq;
Purchtable Purchtable;
PurchLine PurchLine;
PurchFormLetter purchFormLetter;
;
ttsbegin;
numberSeq = NumberSeq::newGetNumFromCode(purchParameters::numRefPurchaseOrderId().NumberSequence,true);
// Initialize Purchase order values
Purchtable.initValue();
Purchtable.PurchId = numberSeq.num();
Purchtable.OrderAccount = '3000';
Purchtable.initFromVendTable();
if (!Purchtable.validateWrite())
{
throw Exception::Error;
}
Purchtable.insert();
// Initialize Purchase Line items
PurchLine.PurchId = Purchtable.PurchId;
PurchLine.ItemId = 'B-R14';
PurchLine.createLine(true, true, true, true, true, false);
ttscommit;
purchFormLetter = purchFormLetter::construct(DocumentStatus::Invoice);
purchFormLetter.update(purchtable, // Purchase record Buffer
"Inv_"+purchTable.PurchId, // Invoice Number
systemdateget()); // Transaction date
if (PurchTable::find(purchTable.PurchId).DocumentStatus == DocumentStatus::Invoice)
{
info(strfmt("Posted invoiced journal for purchase order %1",purchTable.PurchId));
}
}
You can Change the document status to packingSlip , if you want to post packing slip by using the same code.
-Harry
April 20, 2013
X++ code for document attachment
X++ code for document attachment
In Axapta, we can attach document with Purchase order via document handling, if you need to attached a document using your x++ code, we need to use following x++ AOT objects.
- DocuRef (table)
- DocuActionArchive (class)
void attachDoc(RefTableId _refTableId, RefRecId _refRecId, selectableDataArea _refCompanyId, FileName _name)
{
DocuRef docuRef;
DocuActionArchive archive;
;
docuRef.clear();
docuRef.RefRecId = _refRecId;
docuRef.RefTableId = _refTableId;
docuRef.RefCompanyId = _refCompanyId;
docuRef.Name = _name;
docuRef.TypeId = 'File';
docuRef.insert();
archive = new DocuActionArchive();
archive.add(docuRef, _name);
}
To use this method write following code to attache the document.
this.attachDoc(tableNum(PurchTable), purchTable.RecId, purchTable.dataAreaId, filepathname);
-Harry
April 19, 2013
Creating Vendors through X++ in AX 2012- PART I
Creating Vendors through X++ in AX 2012- PART I
--Create party for the vendor--public void TheAxaptaCreateParty(VendorRequestCreate _vendorRequestCreate)
{
;
if(_vendorRequestCreate.DirPartyType == DirPartyBaseType::Person)
{
dirPerson.Name = _vendorRequestCreate.VendorName;
dirPerson.NameAlias = _vendorRequestCreate.FirstName;
dirPerson.NameSequence = dirNameSequence::find('First Last').RecId;
dirPerson.insert();
dirPersonName.FirstName = _vendorRequestCreate.FirstName;
dirPersonName.MiddleName = _vendorRequestCreate.MiddleName;
dirPersonName.LastName = _vendorRequestCreate.LastName;
dirPersonName.ValidFrom = DateTimeUtil::newDateTime(systemDateGet(),str2time ('00:00:00'),DateTimeUtil::getUserPreferredTimeZone());
dirPersonName.ValidTo = DateTimeUtil::maxValue();
dirPersonName.Person = dirPerson.RecId;
dirPersonName.insert();
dirParty = new DirParty(dirPerson);
}
else
{
dirOrganisation.Name = _vendorRequestCreate.VendorName;
dirOrganisation.NameAlias = _vendorRequestCreate.FirstName;
dirOrganisation.LanguageId = 'EN-US';
dirOrganisation.KnownAs = _vendorRequestCreate.VendorName;
dirOrganisation.PhoneticName = _vendorRequestCreate.VendorName;
dirOrganisation.insert();
dirParty = new DirParty(dirOrganisation);
}
}
-Harry
April 01, 2013
Get Current Company in AX
Get Current Company in AX
Below is the example on how to get current company in AX 2009.In AOT > Jobs, paste the following code:
static void curExtExample(Args _arg)
{
str curCompany;
;
// Sets curCompany to the extension of the current company.
curCompany= curExt();
print "Current extension is " + curCompany;
pause;
}
You can use this code in your forms and reports also to parameterized your reports/Forms.
-Harry
December 07, 2012
Change company from the current company in X++ code
Change company from the current company in X++ code
If in any point you need to change change company during the code, This example will helps you to change the company from current company in x++ code.static void main()
{
CustTable custTable;
;
//Assume that you are running in company 'DAT'.
changeCompany('DAT1') //Default company is now 'DAT1'.
{
custTable = null;
while select custTable
{
//custTable is now selected in company 'DAT1'.
}
}
//Default company is again set back to 'DAT'.
changeCompany('DAT2') //Default company is now 'DAT2'.
{
//Clear custTable to let the select work
//on the new default company.
custTable = null;
while select custTable
{
//custTable is now selected in company 'DAT2'.
}
}
//Default company is again 'DAT'.
}
-Harry.
November 16, 2012
Opening the table from x++ code
Opening the table from x++ code
This code helps you to open the any Table from X++ code. Here is an example for SalesTable, just copy and paste this into a job you will get the table.static void TableBrowser(Args _args)
{
SysTableBrowser sysTableBrowser = new SysTableBrowser();
;
//Browse the SalesTable table
sysTableBrowser.run(tablenum(SalesTable ));
}
November 10, 2012
How to update vendor addresses in Dynamics AX
How to update vendor addresses in Dynamics AX 2009
Here is trick of X++ by which we can update the address for a vendor in Dynamics ax 2009, below job is the possible answer and may helps.static void UpdateVendAddressType(Args _args)
{
VendTable vendTab; // Replace VendTable with CustTable when run this for customers.
DirPartyTable dirPartyTab;
Address addTab;
;
ttsbegin;
while select vendTab join dirPartyTab join forupdate addTab
where vendTab.PartyId == dirPartyTab.PartyId
&& addTab.AddrTableId == dirPartyTab.TableId
&& addTab.AddrRecId == dirPartyTab.RecId
{
if(addTab.Name == ‘Birincil Adres’)
{
addTab.type = AddressType::Payment;
addTab.update();
}
}
ttscommit;
}
- Harry
July 17, 2012
Building a query object
Building a query object
Query objects are used to visually build SQL statements. They are used by Dynamics AX
reports, views, forms, and other objects. Normally queries are stored in AOT, but they can also
be created from code dynamically. This is normally done when visual tools cannot handle
complex and dynamic queries. In this recipe, we will create one dynamically from code.
As an example, we will build a query that selects all active customers who belong to group 10
and have at least one sales order.
How to do it…
1. Open AOT, create a new job called CustTableSales, and enter the following code:
static void CustTableSales(Args _args)
{
Query query;
QueryBuildDataSource qbds1;
QueryBuildDataSource qbds2;
QueryBuildRange qbr1;
QueryBuildRange qbr2;
QueryRun queryRun;
CustTable custTable;
;
query = new Query();
qbds1 = query.addDataSource(tablenum(CustTable));
qbds1.addSortField(
fieldnum(CustTable, Name),
SortOrder::Ascending);
qbr1 = qbds1.addRange(fieldnum(CustTable,Blocked));
qbr1.value(queryvalue(CustVendorBlocked::No));
qbr2 = qbds1.addRange(fieldnum(CustTable,CustGroup));
qbr2.value(queryvalue(’10′));
qbds2 = qbds1.addDataSource(tablenum(SalesTable));
qbds2.relations(false);
qbds2.joinMode(JoinMode::ExistsJoin);
qbds2.addLink(
fieldnum(CustTable,AccountNum),
fieldnum(SalesTable,CustAccount));
queryRun = new QueryRun(query);
while (queryRun.next())
{
custTable = queryRun.get(tablenum(CustTable));
info(strfmt(
“%1 – %2″,
custTable.Name,
custTable.AccountNum));
}
}
2. Run the job, and the following screen should appear:
How it works…
First, we create a new query object. Next, we add a new CustTable data source to the query
by calling its addDataSource() member method. The method returns a reference to the
QueryBuildDataSource object—qbds1. Here, we call addSortField() to enable sorting by
customer name.
The following two blocks of code creates two filter ranges. The first is to show only active
customers and the second one is to list only customers belonging to a single group 10. Those
two filters are automatically added together using the SQL AND operator. QueryBuildRange
objects are created by calling the addRange() member method of the QueryBuildDataSource
object with the field ID number as argument. Range value is set by calling value() on the
QueryBuildRange object itself. It is a good practice to use queryvalue() or a similar function
to process values before applying them as a range. More functions like querynotvalue(),
queryrange(), and so on can be found in the Global application class. Note that these
functions actually process data using the SysQuery application class, which in turn has even
more interesting helper methods that might be handy for every developer.
Adding another data source to an existing one connects both data sources using the SQL
JOIN operator. It this example, we are displaying customers that have at least one sales
order. We start by adding the SalesTable table as another data source. We are going to
use custom relations between those tables, so we need to disable standard relations by
calling the relations() method with false as an argument. Calling joinMode() with
JoinMode::ExistsJoin as a parameter ensures that a record from a parent data source
will be displayed only if the relation exists in its attached data source. And finally, we create a
relation by calling addLink() and passing the field ID number of both tables.
Last thing to do is to create and run the queryRun object and show the selected data on
the screen.
There’s more…
It is worth mentioning a couple of specific cases when working with query objects from code.
One of them is how to use the OR operator and the other one is how to address array fields.
Using the OR operator
As you have already noted, regardless of how many ranges are added, all of them will be
added together using the SQL AND operator. In most cases, it is fine, but sometimes complex
user requirements demand ranges to be added using SQL OR. There might be a number of
work-arounds, like using temporary tables or similar, but I use the Dynamics AX feature that
allows passing raw SQL as a range.
In this case, the range has to be formatted like the fully qualified SQL WHERE clause including
field names, operators, and values. Each separate clause has to be in brackets. It is also very
important that filter values, especially if they are specified by the user, have to be properly
formatted before using them in a query.
Let’s replace the code from the previous example:
qbr2.value(queryValue(’10′));
with the new code:
qbr2.value(strfmt(
‘((%1 = “%2″) || (%3 = “%4″))’,
fieldstr(CustTable,CustGroup),
queryvalue(’10′),
fieldstr(CustTable,Currency),
queryvalue(‘EUR’)));
Now, the result would also include all the customers having the default currency EUR.
Using arrays fields
Some table fields in Dynamics AX are based on extended data types, which contains more
than one array element. An example in a standard application could be financial dimensions
based on the Dimension extended data type or project sorting based on ProjSortingId.
Although such fields are very much the same as normal fields, in queries, they should be
addressed slightly different. To demonstrate the usage, let’s modify the example by filtering
the query to list only customers containing a specific Purpose value. In the standard
application, Purpose is the third financial dimension, where the first is Department and the
second is Cost centre.
First, let’s declare a new QueryBuildRange object in the variable declaration section:
QueryBuildRange qbr3;
Next, we add the following code right after the qbr2.value(…) code:
qbr3 = qbds1.addRange(
fieldid2ext(fieldnum(CustTable,Dimension),3));
qbr3.value(queryvalue(‘Site1′));
Notice that we use the global fieldid2ext() function, which converts the field ID and
the array number into a valid number to be used by addRange(). This function can also be
used anywhere, where addressing the dimension fields is required. The value 3 as its second
argument here means that we are using a third dimension, that is, Purpose. In my application, I
have purposes defined as Site1, Site2, and Site3, so I simply use the first one as filter criteria.
Now, when we run this job, the customer list based on previous criteria will be reduced even
more to match customers having only a specific Purpose set.
-Harry
How to create a Query(dynamically)
How to create a Query(dynamically) and add a link in Axapta
Hi friends,
Today we are trying to join two tables dynamically and adding a link type between the two tables. Open the Aot and in jobs write the following code
static void CustTableSales1(Args _args)
{
Query query;
QueryRun queryrun;
QueryBuildDataSource qbds1;
QueryBuildDataSource qbds2;
QueryBuildRange qbr1;
QueryBuildRange qbr2;
CustTable custTable;
;
query = new query();
qbds1 = query.addDataSource(tablenum(CustTable));
qbds1.addSortField(fieldnum(custTable,AccountNum),Sortorder::Descending);
qbr1 = qbds1.addRange(fieldnum(custTable,custGroup));
qbr1.value(queryvalue('10'));
qbr2 = qbds1.addRange(fieldnum(custTable,Blocked));
qbr2.value(queryvalue(CustVendorBlocked::No));
qbds2 = qbds1.addDataSource(tablenum(SalesTable));
qbds2.relations(false);
qbds2.joinMode(joinmode::ExistsJoin);
qbds2.addLink(fieldnum(CustTable,AccountNum),fieldnum(SalesTable,CustAccount));
queryrun = new queryrun(query);
while(queryrun.next())
{
custTable = queryrun.get(tablenum(custTable));
info(strfmt("%1 - %2",custtable.AccountNum,custTable.Name)); // to check your result
}
}
Today we are trying to join two tables dynamically and adding a link type between the two tables. Open the Aot and in jobs write the following code
static void CustTableSales1(Args _args)
{
Query query;
QueryRun queryrun;
QueryBuildDataSource qbds1;
QueryBuildDataSource qbds2;
QueryBuildRange qbr1;
QueryBuildRange qbr2;
CustTable custTable;
;
query = new query();
qbds1 = query.addDataSource(tablenum(CustTable));
qbds1.addSortField(fieldnum(custTable,AccountNum),Sortorder::Descending);
qbr1 = qbds1.addRange(fieldnum(custTable,custGroup));
qbr1.value(queryvalue('10'));
qbr2 = qbds1.addRange(fieldnum(custTable,Blocked));
qbr2.value(queryvalue(CustVendorBlocked::No));
qbds2 = qbds1.addDataSource(tablenum(SalesTable));
qbds2.relations(false);
qbds2.joinMode(joinmode::ExistsJoin);
qbds2.addLink(fieldnum(CustTable,AccountNum),fieldnum(SalesTable,CustAccount));
queryrun = new queryrun(query);
while(queryrun.next())
{
custTable = queryrun.get(tablenum(custTable));
info(strfmt("%1 - %2",custtable.AccountNum,custTable.Name)); // to check your result
}
}
Sample union query from AX 2009
Sample union query from AX 2009
Hi friends;
Queries build with the Query classes now supports unions, meaning that you can combine the result from several tables into one result set. The results you want to combine from the different tables must be structured the same way for all tables.
You could for example create a query combining CustTable and VendTable. This would be particularly useful if you need to present for example a lookup form showing both customers and vendors in the same grid. In earlier version you’d have to push customer and vendor data to a temporary table before being able to present the combined data in one grid.
Here is an example on how to build and use a union query from X++:
You could for example create a query combining CustTable and VendTable. This would be particularly useful if you need to present for example a lookup form showing both customers and vendors in the same grid. In earlier version you’d have to push customer and vendor data to a temporary table before being able to present the combined data in one grid.
Here is an example on how to build and use a union query from X++:
static void union(Args _args) { Query query; QueryBuildDataSource qbdsCustTable; QueryBuildDataSource qbdsVendTable; QueryRun queryRun; CustTable custVendTable; Map mapTableBranches = new Map(types::Integer, typeId2Type(typeId(TableId))); SysDictTable dictTable; ; // The map is used to match the UnionBranchID with a table id mapTableBranches.insert(1, tableNum(CustTable)); mapTableBranches.insert(2, tableNum(VendTable)); query = new Query(); query.queryType(QueryType::Union); qbdsCustTable = query.addDataSource(tableNum(CustTable)); qbdsCustTable.unionType(UnionType::UnionAll); // Include duplicate records qbdsCustTable.fields().dynamic(false); qbdsCustTable.fields().clearFieldList(); qbdsCustTable.fields().addField(fieldNum(CustTable, AccountNum)); qbdsCustTable.fields().addField(fieldNum(CustTable, Name)); qbdsVendTable = query.addDataSource(tableNum(Vendtable)); qbdsVendTable.unionType(UnionType::UnionAll); // Include duplicate records qbdsVendTable.fields().dynamic(false); qbdsVendTable.fields().clearFieldList(); qbdsVendTable.fields().addField(fieldNum(VendTable, AccountNum)); qbdsVendTable.fields().addField(fieldNum(VendTable, Name)); queryRun = new QueryRun(query); queryRun.prompt(); while (queryRun.next()) { custVendTable = queryRun.getNo(1); dictTable = SysDictTable::newTableId(mapTableBranches.lookup(custVendTable.unionAllBranchId)); info (strFmt("%1 %2 (%3)", custVendTable.AccountNum, custVendTable.Name, dictTable.name())); } }
- Harry
May 28, 2012
Join Two Tables at Run Time
How to join Two tables at RunTime, without using Datasource
static void QueryJoin2Tables(Args _agrs)
{
AxTestTable1 AxTestTable1; // Table 1
AxTestTable2 AxTestTable2; // another table which you want to join with table 1
Query q;
QueryBuildDataSource qbdAxTestTable1, qbdAxTestTable2; // objects for both tables
QueryBuildRange qbr;
QueryRun qr;
;
q = new Query(); //to make a new query
qbdAxTeatTable2 = q.addDataSource(tablenum(AxTestTable2)); // To Add table to query
qbdAxTesttable2.addRange(fieldnum(AxTestTable2,RollNo)).value("2");
qbdAxTeatTable1 = qbdAxTestTable2.adddataSource(tablenum(AxTestTable));
qbdAxTesttable1.addlink(fieldnum(AXTestTable2,RollNo),fieldnum(AxTestTable,Rollno));
qbdAxTestTable.joinMode(joinMode::InnerJoin);
qr = new QueryRun(q); // to fetch records from query
while(qr.next())
{
AxTestTable1 = qr.get(tablenum(AxTestTable));
info(AxtestTable.RollNo);
}
}
-Harry
May 02, 2012
Enum values in Query ranges
Enum values in Query ranges
I've come accross a very geniun mistake which most of the developers usually do. Specially those who are working on a single language and do not test their code properly for other languages. This is related to the use of Enum values in Query ranges.
public void init()
{
QueryBuildRange criteriaOpen;
;
super();
criteriaOpen = this.query().dataSourceTable(tableNum(ProdTable)).addRange(fieldnum(ProdTable, ProdStatus));
criteriaOpen.value("Started");
// it does not work in non-English interface!!!
Though you will not find any compilation or run time error with this. However, the query will not read value when you run it in non english environment.
The correct way to use enum in query is
criteriaOpen.value(QueryValue(ProdStatus::StartedUp);
}
{
QueryBuildRange criteriaOpen;
;
super();
criteriaOpen = this.query().dataSourceTable(tableNum(ProdTable)).addRange(fieldnum(ProdTable, ProdStatus));
criteriaOpen.value("Started");
// it does not work in non-English interface!!!
Though you will not find any compilation or run time error with this. However, the query will not read value when you run it in non english environment.
The correct way to use enum in query is
criteriaOpen.value(QueryValue(ProdStatus::StartedUp);
}
Did you got your answer.......??? :)
Subscribe to:
Posts (Atom)