Data Search

Aware IM uses queries to search for information in the database. You can use queries in various parts of your application, for example:

  • To find a customer by name or customer number to see or change customer details.
  • To find a product in a product list when creating an order.
  • To prepare a report on all orders shipped in the last month.
  • To print a customer invoice with itemized order list.

Aware IM offers several ways of specifying queries.

Form-based queries

Search using a data form has been a popular option with many database users for years. Aware IM fully supports form-based queries where you can type values in fields on form to find records in the database with matching values. You can use expressions when entering you search criteria, such as 'less than', 'more than', 'between' etc. Also, you can search using fields of related records on a form. For example, you can find all orders containing a certain product by typing the product name in the line item record on the order form.

Query composer

Query composer provides an easy way to define a query using a point-and-click interface. Simply select the business object to search for (e.g. Client), an attribute (e.g. Name), an operator (e.g. CONTAINS) and type a value (e.g. Smith). You can have multiple conditions in the same query connected with AND or OR.

SQL power without SQL knowledge

Queries can also be defined using the business rule language - by far the most flexible and powerful, although still easy, way to create a query in Aware IM. You can create elaborate search requests without much database knowledge. For example, if you want to get a list of all customers who ordered products from a particular manufacturer, you could do it like this:

FIND Client WHERE EXISTS LineItem WHERE (LineItem.Order IN Client.Orders AND LineItem.Product.Manufacturer.Name = 'Sony')

Note how this compact and easy-to-read query combines the use of objects Client, Order, Line Item, Product and Manufacturer. Behind the scenes Aware IM automatically constructs a rather complex SQL query with multiple table joins and sub-queries.

With this type of queries you can:

  • use object relationships, like in the example above. You do not need to create any additional relationships, as you might have to in systems that do not support queries, just for the sake of filtering data in searches. This work is done in Aware IM by queries;
  • use aggregate operations such as COUNT, SUM, MAX, MIN, AVG with or without conditions, for example find all clients with orders over 10000 dollars:

FIND Client WHERE SUM Order.Amount WHERE (Order IN Client.Orders AND Order.Status = 'Shipped') > 10000

  • limit a number of records returned by a query, for example: find the oldest client:

FIND Client WHERE Client.Status='Active' ORDER BY Client.DateOfBirth TAKE BEST 1

  • use a rich set of Aware IM functions, for example find all clients over 50 years old:

FIND Client WHERE AGE(Client.DateOfBirth) > 50

Parameterized queries

Often you may want to let the user enter the desired search parameters. For example, it would be good if the user could enter the name of the manufacturer in the above query. In Aware IM you can easily accommodate user-specified search criteria by selecting the 'ask at run-time' value in a query condition. Before executing the query Aware IM will automatically generate and display a form for the users to enter their search criteria. You do not need to add any special attributes or create additional forms.

Searching for data of different type

Aware IM lets you search for records of different types, as long as they are members of a business object group. For example, you may have objects Outgoing Email, Incoming Email and Outgoing Letter representing different types of communication with clients, as members of group Communication. Then you could search for all client contacts, regardless of their type, made during the day:

FIND Communication WHERE Communication.ContactDate = CURRENT_DATE

All found records will be displayed in the same list, but when you click on a record Aware IM will show you the record details using a data form specific to the object. Behind the scenes Aware IM automatically constructs an SQL query that includes table joins to search across multiple tables.

Refining search

When looking at the list of records found by a search, you can refine the results by making another search to further narrow down the record list, or extend it to include more records. Similarly, you can filter and sort the list of related records on a data form. This is a built-in Aware IM functionality, you do not need to create any additional queries or forms.

Personal queries

In addition to queries you define when creating your application, you can also allow the users of your application to create their own personal queries to search for data in a specific way. This is especially useful for more sophisticated applications when people may need to make many different search requests. Aware IM keeps personal queries separately for each user and makes them available to the user when he or she logs into the application.

SQL queries

If you are an SQL expert you can specify certain queries in SQL. Since Aware IM works with SQL-compliant databases, you can construct any statements allowed in the standard SQL. In practice, though, you will probably find that the business rule language is sufficient to create almost any query you may need.

Testimonials more »