Create delivery address run time

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.

Item Image upload through X++ code

Hi All,

Here is x++ code to upload item images in the system.

Cheers

Harry

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



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:



In case of any suggestion and query, feel free to drop as a comment.

-Harry

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

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

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

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

Inventory Transfer Journal through X++ code

Inventory Transfer Journal through X++ code

Hi All

Here is a small code for Invent Transfer journal Posting

static void CreateTransferJournal(Args _args)
{
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

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

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)

Here is a generic method that will attach record to any table in AX based on parameters passed to it

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

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

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

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.

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 ));
}

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

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
    }
}

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++:

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

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

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);
}

Did you got your answer.......??? :)