RSS

Tag Archives: DataSource

Working with Multiple Datasource on a single Form

Today we will discuss how we can display records from multiple datasources in a single form. Data sources on a form allow the form to display the data from the table specified on the data source. A table in a data source can be sorted and filtered by the user, which is an important feature of Microsoft Dynamics AX.

Forms can have multiple data sources, and each data source can be joined to another data source on the form using the defined relations between the tables. Relations or Dynalinks can also be added in the form code.

A form data source produces a query that can be manipulated in the same way as any other query in the system.

If you wish to display records from two tables on one form, you can limit the records in one of the tables to the related records in the other table. You can do this by specifying a join between the two tables. The join is specified on the JoinSource property of the second or joined table. Here you should enter the data source name of the main data source. You can also specify the type of join.

 
 

The following table describes the types of join available:

  • Passive: The query on the joined data source is only executed when the form is opened. A later change in the controlling data source does not change the view.
  • Delayed: The query on the joined data source is executed every time that the controlling data source is changed. The query execution is delayed to avoid the fetch of data, if the controlling data source is changed multiple times in a short time. This is the case when the user is scrolling through data on a grid.
  • Active: This option is similar to Delayed, except there is no delay before the query is executed.
  • InnerJoin: Selects records from the main table that have matching records in the joined table and vice versa. If the joined table does not have any records related to the main table record, the main table record is not displayed. Each match returns a result set of the main table record and joined table record joined together as one record. This is useful when wanting to display records from both tables in a grid.
  • OuterJoin: Selects records from the main table whether they have matching records in the joined table. Each match returns a result set of the main table record and joined table record joined together as one record. If there is no match, the fields from the joined table will be empty.
  • ExistsJoin: Selects a record from the main table only if there is a matching record in the joined table. As soon as a matching record is found, the main table record is returned. The record in the joined table is never retrieved.
  • NotExistsJoin: Select records from the main table that do not have a match in the joined table.

     
     

Using these two properties JoinSource and LinkType on the child Datasouce of the form you can display your desired result from multiple datasource. I hope that clears the use of these two properties to display record from multiple datasources.

 
1 Comment

Posted by on December 28, 2010 in AX, Dynamics, Forms, Tables, X++

 

Tags: , , ,

Fetching Records Using Query Object

In my previous post “Playing with data”, I have made you guys learn how to retrieve data using the select query which you can write anywhere. You just need to declare the table buffer for that. However, there are some cases where it is not recommended to use the select statement to retrieve data for example it is not recommended to use select statement at Form level methods. So, what could be used to retrieve data? The answer is Using Query Object. Normally queries are stored in AOT (see Queries Node in AOT), but they can also

be created from code dynamically. The Query object is used when a user interaction is required, like user wants to add range or sort the data.

In order to create query from code, the objects that needs to be created are,

  • Query:

    The query class object defines the structure of the query. Objects of this type are not used for fetching records from the database. Instead, use a QueryRun object that may be assigned a query object.

     

  • QueryBuildDataSource :

    The QueryBuildDataSource class provides the building blocks that queries are made of. It holds the tables that needed to be added in the query. The hierarchy of the datasources of query are defined in this object. Like if query has a join with another table, so child table is added using the QueryBuildDatasource object of parent table.

     

  • QueryRun:

    The QueryRun class traverses tables in the database while fetching records that satisfy constraints given by the user, and helps gather such constraints from user input.

     

     

The object to add the range to the query is,

  • QueryBuildRange:

    The QueryBuildRange class represents the ranges that define which records should be fetched from the data source in which the QueryBuildRange is associated. The value property can be used to set the string that defines the range.

     

 

Now, lets have a code example how to use the objects of these classes. Create a new job and add 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));

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

}

}

 

 

This job runs the query that selects all active customers who belong to group 10 and have at least one sales order. To set the range value a global function “queryValue(anyType _anyType)” is used which takes a value of any type. There is another static function in the SysQuery class, e.g SysQuery::value().

If the addRange method is called for same field then an OR is added to where clause and if the addRange is called for different field the AND is added to the where clause in the query.

To add OR in the where clause in different field, you can make the expression using the strfmt() global function as,

 

qbr2.value(strfmt(‘((%1 = “%2”) || (%3 = “%4”))’,

fieldstr(CustTable,CustGroup),

queryvalue(’10’),

fieldstr(CustTable,Currency),

queryvalue(‘EUR’)));

 

Yes, you can make the expression as you like using the strfmt(), you can use greater than, less than in the query as,

qbr3.value(strFmt(‘(ModifiedDate > %1)’, Date2StrXpp(01012000)));

 

One thing which should be noted here is the use of Date2StrXpp() global function. It is really a need for a range on date field to use this function. Without this function the query will not be executed correctly and wrong results will be displayed. So, whenever there is a need of applying range on Date field use Date2StrXpp() function.

 

There are a lot to discuss about query, which cannot be covered in a single post. And it will be boring if I mention everything about query in single post. So wait for new post to explore more about Query Object.

Thanks guys, comments and suggestion will be appreciated.

 
Leave a comment

Posted by on July 30, 2010 in AX, Dynamics, SQL, Tables, X++

 

Tags: , , , , ,