Dynamics AX – Form lookups and how they work

There are few different ways to achieve a lookup field on a form.

1.     Create an Extended data type – EDT123

2.     Create a table Table123

3.     Add EDT123 to the table and the Description EDT field

4.     Go back to the EDT and create a relation between table123.EDT123 and the EDT123

5.     Automatically when the EDT field is added to another table lets say CustTable a lookup displaying the values from table123 will be displayed in the drop down.

6.     Now let’s say you want to add the description field to the lookup as well. Add the description field to the AutoLookup field group on the table. Now the lookup will display EDT123 and the description field from table123.

That is a simple way to create a lookup. Another way to create a lookup is to write a dynamic lookup on the table in which the data is coming from. This method will be a static method and will require the passing of arguments – typically the formstringcontrol that is to be the point of lookup. I prefer this method and use it often.

This a very simple example of a static lookup from a table

static void lookupTruckLoadIdEndingInv(FormStringControl  _ctrl)
{
    SysTableLookup          sysTableLookup  = SysTableLookup::newParameters(tablenum(WfsRMTruckLoadStatus), _ctrl);
    Query                   query           = new Query();
    QueryBuildRange         qbr;
    ;

    query.addDataSource(tablenum(WfsRMTruckLoadStatus));

    sysTableLookup.addLookupfield(fieldnum(WfsRMTruckLoadStatus, truckLoadId ));

    query.dataSourceTable(tablenum(WfsRMTruckLoadStatus)).addRange(fieldnum(WfsRMTruckLoadStatus,retTransferred)).value(SysQuery::value(NoYes::No));

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

Joins can be peformed as well to pull back the desired data so you are not limited to querying on one table to return the proper data back to your lookup

static void lookupSettledTruckLoadIdCashier(FormStringControl  _ctrl)
{
    SysTableLookup          sysTableLookup  = SysTableLookup::newParameters(tablenum(WfsRMTruckLoadHeader), _ctrl);
    Query                   query           = new Query();
    QueryBuildRange         qbr;
    queryBuildDataSource    qbdsTruckLoadStatus,qbdsTruckLoadHeader,qbdsTenderSlipHeader;
    ;

    qbdsTruckLoadHeader = query.addDataSource(tablenum(WfsRMTruckLoadHeader));

    qbdsTruckLoadStatus         = qbdsTruckLoadHeader.addDataSource(tablenum(WfsRMTruckLoadStatus));
    qbdsTruckLoadStatus.relations(true);

    qbdsTruckLoadStatus.addRange(fieldnum(WfsRMTruckLoadStatus,settled)).value(enum2str(NoYes::No));
    qbdsTruckLoadStatus.addRange(fieldnum(WfsRMTruckLoadStatus,HHTruckLoadIdEnded)).value(enum2str(NoYes::Yes));
    qbdsTruckLoadStatus.addRange(fieldnum(WfsRMTruckLoadStatus,RetTransferred)).value(enum2str(NoYes::Yes));

    qbdsTenderSlipHeader         = qbdsTruckLoadStatus.addDataSource(tablenum(wfsRMTenderSlipHeader));
    //qbdsTenderSlipHeader.relations(true);
    qbdsTenderSlipHeader.addLink(fieldnum(WfsRMTruckLoadStatus, truckLoadId),fieldnum(wfsRMTenderSlipHeader, truckLoadId));
    qbdsTenderSlipHeader.joinMode(joinMode::NoExistsJoin);

    sysTableLookup.addLookupfield(fieldnum(WfsRMTruckLoadHeader, truckLoadId));
    sysTableLookup.addLookupfield(fieldnum(WfsRMTruckLoadHeader, routeId));
    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}

Another way is to create an actual form and call it in a method on the table. You will
still need to pass in the formstringcontrol object but in this case you will be
calling an actual existing form that you have created. Now why do this? Well
one reason may be that you want to be able to filter on a specific field in the
lookup that maybe you could not on a typical lookup or maybe you need to add a
field to the lookup that would otherwise not be possible like you can add a
display method to a lookup but maybe you want to see the field referenced in
the display method and have the ability to sort on the field or filter on the
field. For example, like DirPartyTable.Name. Sure, you can access it using a
display method but maybe you want to see it in your lookup and be able to filter on it.
So you will create a form and call it like a lookup so you can have all the the filtering of a standard form

public client static void WfsRMlookupEmplIdCashier(Object _ctrl)
{
    Args        args;
    FormRun     formRun;
    ;

    args = new Args();
    args.name(formstr(WfsRMEmplIdLookupCashier));
    args.caller(_ctrl);
    formRun = classfactory.formRunClass(args);
    formRun.init();
    _ctrl.performFormLookup(formRun);
}

The standard lookups present in AX like the item number lookup and the customer lookup are very interesting in that you only see one field on the relation but no fields in the autolookup. In these cases the lookup fields are coming from the standard indexes on the table. Take note of what you see in the itemId lookup when unchanged and then reference the indexes coincidence not really. this can apply to any new lookup you create as well.
You can also override the lookup on the datasource - field of a form or an actual field string edit control on a form and perform a lookup
Form design object

public void lookup()
{

    SysTableLookup        sysTableLookup;
    Query                 query=new Query();
    QueryBuildDataSource  qbds;

    ;
    sysTableLookup=SysTableLookup::newParameters(tablenum(Dimensions),this);

    sysTableLookup.addLookupfield(fieldnum(Dimensions,Num));
    sysTableLookup.addLookupfield(fieldnum(Dimensions,Description));

    qbds = query.addDataSource(tablenum(Dimensions));

    qbds.addRange(fieldnum(Dimensions, DimensionCode)).value(queryValue(COSAllowedDimensions::getAllowedDimensionValue(sysDim)));

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

//Lookup from a field on the form data source

public void lookup(FormControl _formControl, str _filterStr)
{
    Args    args;
    FormRun formRun;
    SysTableLookup      sysTableLookup = sysTableLookup::newParameters(tablenum(InventTable),_formControl);
    Query               query = new Query();
    QueryBuildDataSource     queryBuildDataSource;
    QueryBuildRange     queryBuildRange;
    ;
    sysTableLookup.addLookupfield(fieldnum(InventTable,ItemID));
    sysTableLookup.addLookupfield(fieldnum(InventTable,ItemName));
    sysTableLookup.addLookupfield(fieldnum(InventTable,ItemGroupID));
    sysTableLookup.addLookupfield(fieldnum(InventTable,NameAlias));
    sysTableLookup.addLookupfield(fieldnum(InventTable,ItemType));
    sysTableLookup.addLookupfield(fieldnum(InventTable,DimGroupID));

    queryBuildDataSource = query.addDataSource(tablenum(Inventtable));
    queryBuildRange = queryBuildDataSource.addRange(fieldnum(InventTable,ItemGroupID));
    //FGL, FGR, Returns
    queryBuildRange.value('xxx');

    queryBuildRange = queryBuildDataSource.addRange(fieldnum(InventTable,ItemGroupID));
    queryBuildRange.value('yyy');

    queryBuildRange = queryBuildDataSource.addRange(fieldnum(InventTable,ItemGroupID));
    queryBuildRange.value('Returns');

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

}

-Harry

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

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

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

Multiple Tables In Query

Axapta query on multiple tables

Hey friends try this code to add multiple tables in query.

QueryRun selectReportQuery()

{

    Query                   query           = new Query();

    QueryRun                localQueryRun;

    QueryBuildDataSource    qbds1;

    QueryBuildDataSource    qbds2;

    QueryBuildDataSource    qbds3;

    ;

    qbds1 = query.addDataSource(tableNum(salesTable));

    qbds1.addRange(fieldNum(salesTable, affVendorAdjust)).value('1');

    qbds2 = qbds1.addDataSource(tableNum(custInvoiceJour));

    qbds2.fetchMode(JoinMode::InnerJoin);

    qbds2.relations(false);

    qbds2.addLink(fieldNum(salesTable, salesId),fieldNum(custInvoiceJour, salesId));

    qbds2.addSortField(fieldNum(CustInvoiceJour, OrderAccount), SortOrder::Ascending);

    qbds2.addSortField(fieldNum(CustInvoiceJour, InvoiceId), SortOrder::Ascending);

    qbds2.addRange(fieldNum(CustInvoiceJour, InvoiceDate)).value(queryRange(invBeginDate, invEndDate));

    qbds3 = qbds1.addDataSource(tableNum(salesLine));

    qbds3.relations(true);

    qbds3.joinMode(JoinMode::InnerJoin);

    qbds3.fetchMode(queryFetchMode::One2Many);

qbds3.addRange(fieldNum(salesLine, mbsLineVendor)).value(queryValue(vendAccount));

Here is some more code related to query in axapta

1. Sample union query from AX 2009

2. Building a query object

3. How to create a Query(dynamically)

4. Join Two Tables at Run Time

-Harry

How to get QueryRange from Query object X++

Hi Folks,

This post is about how to get QueryRange from a query object in x++ code. This is very useful when you have to pass a query on another object and perform a certain operation based on this query and its ranges.

To understand this code sample better, let's take a scenario. On FormA I have to build a query (OR use AOT query) on run time based on certain ranges. When the user clicks on a button system will pass this query to FormB and on FormB we have to use this query and its ranges to perform other sets of operations.

Now let's see the code, which may need to write on Form B to take a query as a parameter and fetch the ranges and its values in code.



I hope this will help.

-Cheers!!!
Harry
PS: My VM is down for some reason and I couldn't prove the code and have to write it manually so you might get some syntax issues in the above code.

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

Extended Query Range in Dynamics AX

Hi Friends, 

Many developers often stuck while they try to apply range to the Dynamics AX query to filter records based on some conditions. We will try to explore the query ranges in this article and will try to play with some examples to learn how we can apply query ranges using x++ to the Dynamics AX queries.
Let’s discussed different cases...

Query q;
QueryBuildDataSource qbd;
QueryBuildRange qbr;
q = new Query();
qbd = q.addDataSource(TableNum(CustTable));
qbr = qbd.addRange(FieldNum(CustTable, AccountNum));
qbr.value('4005, 4006');

The above x++ code will generate following SQL statement.

"SELECT * FROM CustTable WHERE ((AccountNum = N'4005' OR AccountNum = N'4006'))"

qbr.value(strFmt('((AccountNum == "%1")

(AccountNum == "%2"))',

QueryValue('4005'),

QueryValue('4006')));

The above x++ code will generate following SQL statement.

"SELECT * FROM CustTable WHERE ((((AccountNum == "4005") || (AccountNum == "4006"))))"

Let's say we want to apply "OR" range on a DIFFERENT fields
You can use the following x++ code to apply the OR range to the different fields

qbr = qbd.addRange(FieldNum(CustTable, DataAreaId));

qbr.value(strFmt('((%1 == "4000")

(%2 == "The Bulb"))',

fieldStr(CustTable, AccountNum),

fieldStr(CustTable, Name)));

The above code will generate following sql statement

"SELECT * FROM CustTable WHERE ((((AccountNum == "4000") || (Name == "The Axapta"))))"

Note: We have used DataAreaId field above to apply the range however, the actual range is on AccountName and AccountNum field. This means when you use range value expressions you can use any field to obtain range object and use it to insert your range in the query. Using DataAreaId field for this purpose is the best practice.

-Harry

Dynamics AX – Passing parameters between object – What is args??

Hi Friends,

ARGS is your friend in the world of AX (me also :) ). It allows you to pass records, the calling locations (form, report, query etc) , ENUMS and the list goes on!  
Simple declaration of args

Args  args = new Args();

Now lets try passing args an record.

select firstonly custTable where custTable.AccountNum == ‘XXXX’
if(custTable)
{
args.record(custTable);
}

Now lets view a snippet of code that passes in a record and runs a report using the record passed in. 
I- Create an instance of the report run class.
Create a new Args instance to hold all of this information. 
Pass the name of the report.
Instantiate the report run object and call the init and and run methods of the report.

II- Next override the init method of the report and put a condition that checks to see if a record was passed to the report from the args object. If so do not allow the user to be interactive with the report and sent the report straight to the screen.

III- Set a report variable eHeader to the record that was passed to the report. If there is no calling record to the report meaning the report is being launched from a menu or elsewhere besides a place with a calling record then allow interaction of the report query for the users to select the range criteria they want to use.

IV- Then override the fetch method and keep the super in place to allow the standard query to run however before the super use a condition to determine if a record has been passed into the report. If so then set a query of a key field to the a field from the record passed in.

V- You can use args to do the same with forms as well
You can pass in objects such as maps to run reports I have done this as well and I find it very helpful and useful

void printPickList(wfsEMPickListHeader  wfsEMPickListHeader)
{
    Args                    args = new args();
    ReportRun               reportRun;
    ;

    args.name(reportStr(wfsEMExportPickList));
    args.caller(this);
    args.record(wfsEMPickListHeader);

    reportRun = classfactory.reportRunClass(args);
    reportRun.init();

    reportRun.run();
}

public void init()
{
    super();
    if(element.args().record())
    {
        this.report().interactive(false);
        this.query().interactive(false);
        this.printJobSettings().preferredTarget(PrintMedium::Screen);
        eHeader = element.args().record();

    }
    else
    {
        this.report().interactive(true);
        this.query().interactive(true);
    }
}

public boolean fetch()
{
    boolean ret;
    if(element.args().record())
    {
        element.query().dataSourceTable(tablenum(wfsEMPickListHeader)).

       addRange(fieldnum(wfsEMPickListHeader,PickListId)).value(eHeader.PickListId);
    }
    ret = super();

    return ret;
}

void wfsRMRunManualTruckLoadIdForm()
{
    wfsWhseUser    wfsWhseUser;
    FormRun        formRun;
    Args           args = new Args();
    boolean        ret = false;
    ;
    args.record(this);
    formRun = new MenuFunction(menuitemdisplaystr(wfsManualTruckLoadSelection),

    MenuItemType::Display).create(args);
    formRun.run();
    formRun.wait();
}

-Harry

Lookup in Table

Lookup in Table

Some times we need to create a lookuo on Tables itself, so to make a lookup in table methods add the following code in your object methods;

public static void lookupStaffTable(FormControl _callingControl, CITStaffPlanId _staffplan)
{
    Query query;
    QueryBuildDataSource qbds;
    SysTableLookup lookup;
    ;

    query = new Query();
    qbds = query.addDataSource(tablenum(CITStaffPlanLinesTable));
    qbds.addRange(fieldnum(CITStaffPlanLinesTable,StaffPLanId)).value(_staffplan);
    lookup = SysTableLookup::newParameters(tablenum(CITStaffPlanLinesTable),_callingControl,false);
    lookup.parmQuery(query);
    lookup.addLookupField(fieldnum(CITStaffPlanLinesTable, Position));
    lookup.addLookupField(fieldnum(CITStaffPlanLinesTable, ResourcesperPosition));
    lookup.addLookupField(fieldnum(CITStaffPlanLinesTable, TotalRequest));
    lookup.addLookupField(fieldnum(CITStaffPlanLinesTable, RecID),true);
    lookup.performFormLookup();
}

-Harry

How to use Complex Query Ranges in Dynamics AX

Use of Complex Query Ranges in Dynamics AX

     1.  Adding a query with a datasource.

query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable));
// Add our range
queryBuildRange = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));

    2.  Simple criteria

Lets find the record where the value of ItemId field is Item1. Take note of the single quotes and parenthesis surrounding the entire expression.

queryBuildRange.value(strFmt('(ItemId == "%1")', queryValue("Item1")));
Find records where the ItemType is Service. Note the use of any2int().
queryBuildRange.value(strFmt('(ItemType == %1)', any2int(ItemType::Service)));

Find records where the ItemType is Service or the ItemId is Item1. Note the nesting of the parenthesis in this example.

queryBuildRange.value(strFmt('((ItemType == %1) || (ItemId == "%2"))', any2int(ItemType::Service), queryValue("Item1")));

Find records where the modified date is after 1st January 2000. Note the use of Date2StrXpp() to format the date correctly.

queryBuildRange.value(strFmt('(ModifiedDate > %1)', Date2StrXpp(01012000)));

    3.  Complex criteria with combined AND and OR clauses

We need to find those records where the ItemType is Service, or both the ItemType is Item and the ProjCategoryId is Spares. This is not possible to achieve using the standard QueryRange syntax.

queryBuildRange.value(strFmt('((%1 == %2) || ((%1 == %3) && (%4 == "%5")))',fieldStr(InventTable, ItemType),any2int(ItemType::Service),any2int(ItemType::Item),fieldStr(InventTable, ProjCategoryId),queryValue("Spares")));

-Harry

How to create a lookup on Dimension(SysDimesion) ENUM

How to create a lookup on Dimension(SysDimesion) ENUM

Note:
1. DimentionSetCombination is the table in Which dimension hierarchy stored 
2. MachineHour is a customized table in this example, you can use your own logic in the same
3. fieldId2Ext is an X++ keyword which is used to access a particular array index value.
 example:  fieldId2Ext(fieldNum(DimensionSetCombination,Dimension),4)


public void lookup()
{
    SysTableLookup       sysTableLookup =      SysTableLookup::newParameters(tablenum(DimensionSetCombination), this);
    Query                query;
    QueryBuildDataSource queryBuildDataSource;
    QueryBuildRange      queryBuildRange, queryBuildRangeDlvryWH;
    ;

    sysTableLookup.addLookupfield(fieldId2Ext(fieldNum(DimensionSetCombination,Dimension),4), true);

    query                   =   new Query();
    queryBuildDataSource    =   query.addDataSource(tablenum(DimensionSetCombination));
    queryBuildRange         =   queryBuildDataSource.addRange(fieldId2Ext(fieldnum(DimensionSetCombination,Dimension),3));

    queryBuildRange.value(MachineHours.CostElement);
    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}

-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.......??? :)

Conditional Joins in x++

Conditional Joins in x++

Here is the code to apply joins conditionally in Microsoft Dynamics AX.

query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable), "InventTable");
dsInventItemBarCode = dsInventTable.addDataSource(tableNum(InventItemBarCode), "InventItemBarCode");
dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);
// Add any two ranges
queryBuildRange1 = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
queryBuildRange2 = dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));

Find all records where either the ItemType is Service, or the ItemType is Item and a barcode exists. The join criteria is only applied in the second half of the expression, so all Service items will appear irrespective of whether they have a bar code. Again, this is not possible to achieve using the standard query ranges.

queryBuildRange2.value(strFmt('((%1.%2 == %3) || ((%1.%2 == %4) && (%1.%5 == %6)))',
query.dataSourceTable(tableNum(InventTable)).name(), // InventTable %1
fieldStr(InventTable, ItemType), // ItemType %2
any2int(ItemType::Service), // %3
any2int(ItemType::Item), // %4
fieldStr(InventTable, ItemId), // ItemId %5

fieldStr(InventItemBarCode, ItemId))); // %

-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

Document handling via X++

In my previous post  Document handling In Dynamics ax, I post step by step tutorial to use base functionality of document handling. Today I Shared dirty code for document handling via X++ code.
Well, Document handling a great feature of Axapta; It really just depends on what your application requires. For instance, one application was based upon the sales order having a certain document attached to a sales order based on upon a custom enumeration.  So the button has a name like an invoice declaration for instance. 
If the button is clicked two menu options appear 
– Add document or 
- View document; 

*If the add document button is clicked
The select file dialog appears and the user selects a file and clicks OK. At this point, the file is written to the document handling table with the custom enumeration marked as “Invoice declaration”. 

*If the view document option is selected and there is a file to view, 
The standard documentation form will appear and the document will automatically be launched for viewing. 

What I decided to do is was find the lasted record with the enumeration set as the button that is being clicked. I pass RecId of this record using args to the Document handling form so when the form is launched I have access to the recId of the record I want to see. I set a class declaration variable of type Int64 to the recId of being passed in so I have access to the variable all over the form. This way I can use the variable in conditional statements to determine if I want to execute code. Below I will demonstrate how to insert a record in the document handling form and how I view a document of selection from the document handling form from the calling form.

Loading a document from the sales order to the document handling table

    Args                            args = new args();
    formrun                         FormRun;
    filenameSave                    filename;
    Args                            args1 = new args();
    wfsEMSalesOrderDocumentation    wfsEMSalesOrderDocumentation;
    docuref                         docuref;
    form                            form2;
    FilenameFilter                  _conFilter;
    filename                        file;
    recid                           docuvaluerecid;
    docuvalue                       docuvalue;
    int                             hwnd;//wfs ctodd 6/21/2012
    ;

    if(SalesTable)
    {
        _conFilter = ['All FIles','*.*'];
         hWnd = element.hWnd();
        file = WinAPI::getOpenFileName(infoLog.hWnd(),_conFilter,'','Select Document Path');
        if(file)
        {
            ttsbegin;
            docuref.TypeId = "File";
            docuref.Name   = "Commercial Invoice";
            docuref.Notes  = docuRef.Notes;
            docuRef.Restriction  = DocuRestriction::External;
            docuref.RefCompanyId = SalesTable.dataAreaId;
            docuref.WfsExportType = wfsexporttype::CommercialInv;
            docuref.RefTableId   = tablenum(SalesTable);
            docuref.RefRecId     = SalesTable.RecId;
            docuref.insert();

           docuvalue.initValue();
           docuvalue.insert();
           docuref.ValueRecId = docuvalue.RecId;
           docuref.update();
           docuvaluerecid = docuvalue.RecId;

           select forupdate docuvalue where docuvalue.recid == docuvaluerecid;
           docuvalue =  docuvalue::writeDocuValue(docuref,file);
           ttscommit;
      }

Viewing a file to the document handling table from the sales order table

     Args                            args = new args();
    formrun                         FormRun;
    filenameSave                    filename;
    Args                            args1 = new args();
    wfsEMSalesOrderDocumentation    wfsEMSalesOrderDocumentation;
    docuref                         docuref;
    form                            form2;
    FilenameFilter                  _conFilter;
    filename                        file;
    recid                           docuvaluerecid;
    docuvalue                       docuvalue;
    ;

    if(SalesTable)
    {
        args = new Args();
        args.name(formstr(docuview));
        select reverse docuRef  order by createdDateTime 

where Docuref.RefTableId == salesTable.TableId 

     && docuRef.RefRecId == salesTable.RecId 

     && docuRef.WfsExportType == wfsExportType::CommercialInv;

        args.record(docuRef);
        args.parm(int642str(docuRef.RecId));
        args.caller(this);

        FormRun = classfactory.formRunClass(args);
        FormRun.init();
        FormRun.run();
        FormRun.wait();

        salesTable_ds.reread();
        salesTable_ds.refresh();
    }

When viewing my document I want to pass in the record I want to see from the document handling form

//This is the document handling form init method

void init()
{
    #define.ctrlBuildAdd('ctrlAdd')
    DocuType                            docuType;
    FormBuildMenuButtonControl          menuCtrl;
    FormBuildFunctionButtonControl      itemButtonCtrl;

    if (! infolog.parmDocuHandlingActive())
    {
        throw(error("@SYS60737"));
    }
    if (infolog.parmDocu().isDocuViewSet())
    {
        infolog.parmDocu().setActive();
        throw Exception::Info;
    }
    optionView = new DocuOptionView();
    optionView.getLast();

    DocuType::createDefaults();
    docuTypeIdFile = DocuType::typeFile();

    menuCtrl = this.form().design().control(#ctrlBuildAdd);
    while select docuType
        index TypeIdx
    {
        itemButtonCtrl = menuCtrl.addControl(FormControlType::MenuFunctionButton,docuType.TypeId);
        itemButtonCtrl.menuItemType(MenuItemType::Action);
        itemButtonCtrl.menuItemName(menuitemactionstr(DocuActionNew));
        itemButtonCtrl.text(docuType.TypeId);
    }

//Here I set my variable exportRecId to the recId of the parameter I passed in using args
    if(element.args().caller().name() == formstr(wfsEMExportSales)) // dms
 {
 exportRecId = str2int64(element.args().parm());
 }
    super();
    link.enabled(false);
    multiPaste.enabled(false);

    formSplitter_Y = new SysFormSplitter_Y(grpSplit,grpRefGrid,this);
}

Here is the init method on the datasource DocuRef I need to create a query range

void init()
{
    Query           q;
    QueryBuildDataSource qB;
    super();

    q = new Query();
    qB = q.addDataSource(tablenum(DocuRef));
    criteriaRefCompanyId    = qB.addRange(fieldnum(DocuRef,RefCompanyId));
    criteriaRefTableId      = qB.addRange(fieldnum(DocuRef,RefTableId));
    criteriaRefRecId        = qB.addRange(fieldnum(DocuRef,RefRecId));
    criteriaCreatedBy       = qB.addRange(fieldnum(DocuRef,CreatedBy));

    I create a range for the recId of the record being passed in. This is checked with a condition

 We don't want to create the range if no value exists in exportRecId

    if(exportRecId)
 {
 recIdRange = qB.addRange(fieldnum(DocuRef,recId));
 }
    element.setRangeCreateId(optionView.parmShowAllUser());
    this.query(q);

    this.cacheAddMethod(tablemethodstr(DocuRef, isValueAttached));
}

The executeQuery of DocuRef which will perform the query execution of the form for that datasource

void executeQuery()
{
    if (element.isActualFound())
    {
        criteriaRefCompanyId.value(queryValue(actualCompanyId));
        criteriaRefTableId.value(queryValue(actualTableId));
        criteriaRefRecId.value(queryValue(actualRecId));
    }
    else
    {
        criteriaRefCompanyId.value(queryValue(naStr()));
        criteriaRefTableId.value(queryValue(naInt()));
        criteriaRefRecId.value(queryValue(naInt()));
    }

    If my variable has a value then we pass that value to the range I created in the 

 in the init method
   

 if(exportRecId)
 {
 recIdRange.value(SysQuery::value(exportRecId));
 }

    super();
}

Again I use the variable exportRecId to determine if the document should automatically

be viewed when launching from the sales order form. So basically if I have the recId

from a caller I want the document to be automatically launched

void run()
{
    element.setCaption("@SYS26830");

    filter.selection(optionView.parmShowAllUser());
    showOnlyRef.value(optionView.parmShowOnlyRef());
    showImage.value(optionView.parmShowImage());
    this.setShowRef();
    this.setShowImage();
    if(exportRecId)
 {
 showImage.value(1);
 this.setShowImage();
 }
    super();
}

-Harry