Searching for Data in Microsoft Dynamics AX 2009: Part 1

Exclusive offer: get 50% off this eBook here
Microsoft Dynamics AX 2009 Programming: Getting Started

Microsoft Dynamics AX 2009 Programming: Getting Started — Save 50%

Get to grips with Microsoft Dynamics AX 2009 development quickly to build reliable and robust business applications with this book and eBook

£18.99    £9.50
by Erlend Dalen | December 2009 | BPEL Microsoft SOA Web Services

This article by Erlend Dalen, explains the different options to use when you need to search and retrieve a set of data from the database. It shows you how to create a query, how to create a view, and how to write different select statements.

In this article, you will learn about the different methods of retrieving data from the database. They are as follows:

  • Queries which are reusable and often used in reports and periodic Jobs
  • Views that are created in AOT and translated to optimized select statements at runtime
  • Select statements to use in X++ to fetch data from the database to the application

Queries

Queries are typically used to ask users about ranges and sorting, and then selecting data based on the feedback from the users. A query can consist of one or multiple data sources, and can be created both as static queries in the AOT or as dynamic queries using X++. Most commonly they are used when the ranges or values are not known until runtime. Static queries are defined in the AOT, whereas dynamic queries are defined in X++ code.

Creating a static query using the AOT

Follow these steps to create a static query in the AOT:

  1. Open the AOT, expand the Queries node, right-click on Queries, and select New Query. A new query is then created in the AOT.
  2. Right-click on the query, select Properties, and change the name to CarList (or in other cases, to something that describes what kind of data the query is returning).
  3. Open a new AOT window, expand the DataDictionary node, and then expand the Tables node.
  4. Drag the CarTable and drop it onto the Data Sources node of the new query. You can also drag maps or views to the data source of a query.

You have now created the skeleton of the query. Let's now look at how to add sorting and ranges to the query.

Adding a sort order to the query

To add a sorting to the query, just drag the selected field from the Fields node under the data source and drop it under the Order By node. In our example, we'll use the Mileage field. You can then select to have it sort ascending or descending by changing the direction property on the sort field.

When the query prompt is executed in a report, the user has the ability to change the sort order.

Adding a range to the query

You can also add ranges to the data source by dragging a field from the Fields node and dropping it onto the ranges. A range can be used to narrow down the result returned by the query, or it can be used as a fixed range that the user cannot change. This is done by adding a value to the value property of the range. Values in a range can be used like this:

Range operator

Description

Example

,

Selects records where the range field matches any of the values listed

BMW, VW, Volvo

=

Selects records where the range field is a matching value

=VW

..

Selects records where the range field is between the values specified including the values used.

1000..3000

<

Selects records where the range field is less than the value specified

<2000

>

Selects records where the range field is greater than the value specified

>2000

!

Selects records where the range field is not equal to the value specified

!BMW

?

Selects records where the ? can be any character

Merc??es

*

Selects records where the range field matches the characters before or after the asterisk

Merc*

When the Status property is set to Open, the users can change the range value. If it's set to Lock, the users can see the range value before executing the query, but they are not allowed to change it. If the status property is set to Hide, the users won't even be allowed to see the range value.

In our example, we add the ModelYear field from the data source CarTable_1:

Microsoft Dynamics AX 2009 Programming: Getting Started

Joining data sources in a query

In order to select data from more than one table, you can join the data sources in your query. Data sources can be joined in a couple of different ways depending on how you would like to link them. This is done by setting the JoinMode property to the different values shown in the following table:

JoinMode

Description

InnerJoin

Will return the records where the joined data sources have matching values in the joined fields.

Ex: By using the CarTable as the primary data source and using RentalTable as the joined data source, the inner join will fetch all records from the CarTable where there is a corresponding record in the RentalTable. The corresponding records in RentalTable will also be fetched.

OuterJoin

Will return all the records from the joined table even if they don't match the joined field.

Ex: Compared to the example using the InnerJoin, this will return all records from the CarTable, but also records from the RentalTable that does not have a match in the CarTable.

ExistsJoin

This is just like the InnerJoin, except the records from the joined data source are not returned. They are only used to filter the primary data source.

Ex: In our example it will only return records in the CarTable where there is a match in the RentalTable. Records from the RentalTable will not be fetched.

NoExistsJoin

This is the opposite of ExistsJoin. It will select records from the primary data source when matching records in the joined data source does not exist.

Ex: In our example it will return records from the CarTable that did not have any matching records in the RentalTable. (Cars that has never been rented)

Follow these steps to add a new data source and join it with the first one:

  1. First, we will create a duplicate of the query that we have created so far, as we would like to use the original query in the Reporting Services report. To duplicate any AOT object, right-click on the object and select Duplicate. A duplicate is then created with the prefix CopyOf.
  2. Now rename the new query to RentalCarList.
  3. Also, change the range under the CarTable_1 data source to ModelYear instead of Model. This range will be used later in this article.
  4. Drag another table, map, or view, and drop it onto the Data Sources node below the first data source. In our example, we will add the RentalTable. Therefore, open a new AOT window and browse to Data Dictionary | Tables | RentalTable. Drag the RentalTable and drop it onto the Data Sources node under the CarTable data source in the query.
  5. Open the properties of the RentalTable data source in the query and change the Relations property to Yes.
  6. If you expand the Relations node under the RentalTable data source, you should now see that the CarTable data source is linked to the RentalTable data source by the CarId. Your AOT should look like this:

Creating a dynamic query using X++

A query can also be built dynamically using X++ code. This can be the only way of creating the query, if you would like the query to work in one way in some cases and in another way in other cases. An example can be where you would like to join one table if one condition is true and another table if the condition is false. To do this, you need to understand how the query object model works.

The most commonly used classes in the query object model are:

  • Query: Contains the definition of the query. Can consist of one data source or several data sources if they are related.
  • QueryRun: Class used to execute the query and loop through the result.
  • QueryBuildDataSource: Links to one data source in the query. Can be linked to another QueryBuildDataSource object to join linked data sources.
  • QueryBuildRange: Enables the end user to limit the result by adding a value in the specified query range.
  • QueryBuildFieldList: List of all the fields in data source. OneQueryBuildFieldList object for each QueryBuildDataSource. By default the property Dynamic is set to true so that all fields are returned.
  • QueryBuildLink: Links two data sources in a join. Is set on the child data source.

The query definition is set up by creating and linking objects from the query object model together. The following example shows how this is done in order to create a similar query as we did in the previous section of this article when we created a query called RentalCarList in the AOT.

static void queryRentalCarList(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));
}

Using a query

Ok, so now we have the query definition. But that doesn't help us much unless we are able to execute the query, right?

This example uses the previous example and just adds the QueryRun object and loops through the result by using the next() method on the QueryRun object.

static void queryRunRentalCarList(Args _args)
{
Query query;
QueryBuildDataSource queryBuildDataSource1,
queryBuildDataSource2;
QueryBuildRange queryBuildRange;
QueryBuildLink queryBuildLink;
QueryRun queryRun;
CarTable carTable;
RentalTable rentalTable;
;
// 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));
// Set the range
queryBuildRange.value("2008..");
// Add the second datasource to the first data source
queryBuildDataSource2 =
queryBuildDataSource1.addDataSource(tablenum(RentalTable));
// Add the link from the child data source to the parent data
//source
queryBuildLink = queryBuildDataSource2.addLink(
fieldnum(CarTable,CarId),fieldnum(RentalTable, CarId));
// Create a new QueryRun object based on the query definition
queryRun = new QueryRun(query);
// Loop through all the records returned by the query
while (queryRun.next())
{
// Get the table data by using the get() method
carTable = queryRun.get(tablenum(CarTable));
rentalTable = queryRun.get(tablenum(RentalTable));
info (strfmt("CarId %1, RentalId %2", carTable.CarId,
rentalTable.RentalId));
}
}

The following result is obtained after running the query:

Microsoft Dynamics AX 2009 Programming: Getting Started

The exact same result will show up if we execute the query that was defined in the AOT in the previous section of this article.

The code would then look like this:

static void queryRunRentalCarListAOT(Args _args)
{
Query query;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
QueryRun queryRun;
CarTable carTable;
RentalTable rentalTable;
;
// Create a new query object based on the Query in the AOT called
//RentalCarList
query = new Query(querystr(RentalCarList));
// Find the datasource for the CarTable
queryBuildDataSource = query.dataSourceTable(tablenum(CarTable));
// Find the range that we added to the query in the AOT
queryBuildRange =
queryBuildDataSource.findRange(fieldnum(CarTable, ModelYear));
// Set the value of the range
queryBuildRange.value("2008..");
// Create a new QueryRun object based on the query definition
queryRun = new QueryRun(query);
// Loop through all the records returned by the query
while (queryRun.next())
{
// Get the table data by using the get() method
carTable = queryRun.get(tablenum(CarTable));
rentalTable = queryRun.get(tablenum(RentalTable));
info (strfmt("CarId %1, RentalId %2", carTable.CarId,
rentalTable.RentalId));
}
}

Views

Views in AX are objects that are used to retrieve data from the database that is stored in the memory on the layer in which the view is instantiated. The views are actually stored as database views on the SQL server. This means that there are potentially great performance benefits of using views compared to using an equivalent query. This depends of course on the complexity of the query, but in general the performance benefits of using a view compared to a query will increase along with the complexity of the query.

Views can be used throughout AX in all places where tables can be used. This includes forms, queries, reports, and X++ code.

Views in AX can never be used to write data, only to read data from the database. This differs from the SQL implementation that has write-back possibilities for views.

Microsoft Dynamics AX 2009 Programming: Getting Started Get to grips with Microsoft Dynamics AX 2009 development quickly to build reliable and robust business applications with this book and eBook
Published: December 2009
eBook Price: £18.99
Book Price: £30.99
See more
Select your format and quantity:

Creating a view

We will now create a view that consists of CarId, CarBrand, Model, Customer Name, FromDate, and ToDate using the following steps:

  1. First, we locate the Views node under the Data Dictionary in the AOT.
  2. Right-click on the Views node and select New View. A new view will be created. You can open its properties by right-clicking and selecting Properties.
  3. Change the name of the view to CarCustRental and give it a label that describes the contents of the view.
  4. The views can actually use queries that have already been created as a base for the data selection. This is done in the Properties of the view by choosing the query from the Query property. However, in our example, we will create the view from scratch.
  5. Under the Metadata node, right-click on the Data Sources node and selectNew Data Source
  6. Select the newly created data source and enter CarTable in the table property. The name of the data source will automatically change to CarTable_1, which is normal.
  7. Under the CarTable data source, find the Data Sources node, right-click on it, and select New Data Source. This time, we want to use the RentalTable so change the table property to RentalTable. Also change the relations property to Yes in order to get the link between the two tables active in the view.
  8. Do the same to add the CustTable as a child data source to the RentalTable.
  9. The next step is to define the fields that should be made available when this query is executed. Simply drag fields you need to use in the view from the CarTable_1, RentalTable_1, and CustTable_1 data source and drop them onto the Fields node.
  10. You should now have a view that looks like the following screenshot:

    Microsoft Dynamics AX 2009 Programming: Getting Started

After saving the view, you can browse the contents of the view in the same way as you can with a table by using the table browser. Just open the view and the table browser will display the view with its contents.

You can also take a look at the view in the SQL Management Studio by opening the AX database node, then the Views node, and finding the view you just created. Right-click on the view and select Design to open it in design view. It should then look something like the following screenshot:

Microsoft Dynamics AX 2009 Programming: Getting Started

[ 1 | 2 | 3 ]

>> Continue Reading Searching for Data in Microsoft Dynamics AX 2009: Part 2

If you have read this article you may be interested to view :

Microsoft Dynamics AX 2009 Programming: Getting Started Get to grips with Microsoft Dynamics AX 2009 development quickly to build reliable and robust business applications with this book and eBook
Published: December 2009
eBook Price: £18.99
Book Price: £30.99
See more
Select your format and quantity:

About the Author :


Erlend Dalen

Erlend Dalen started working as a developer with the first version of Axapta in 1998. From 2000 he spent 2 years developing eCommerce, mobile, and integration solutions in Java for a Norwegian IT consultancy company. He has worked for Columbus IT since 2002, first as a senior developer in Norway and in USA and now as the technology manager of the Norwegian branch, where his responsibilities have been to implement new technology areas, creating an eCommerce solution for Dynamics AX, and being the technology solution architect in internal and customer projects.

Books From Packt

RESTful Java Web Services
RESTful Java Web Services

Joomla! 1.5 SEO
Oracle SOA Suite Developer's Guide

SOA Patterns with BizTalk Server 2009
SOA Patterns with BizTalk Server 2009

Programming Microsoft Dynamics NAV 2009
Programming Microsoft Dynamics NAV 2009

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Microsoft Office Live Small Business: Beginner’s Guide
Microsoft Office Live Small Business: Beginner’s Guide

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

WCF Multi-tier Services Development with LINQ
WCF Multi-tier Services Development with LINQ

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software