Collapse AllExpand All

12.5.1. Query Sets and the QuerySet widget

A query set is a collection of database queries that are run in sequence. They can be used for exporting data, checking the status of the database, or performing routine database maintenance tasks. When the query set is run, each individual query in the query set is run in the order you choose. The results of the individual queries, called query items, are saved together in a single file.

To create, edit, and use a query set, follow this path: System > Utilities > Export Data

Note

If you have xTuple Connect installed and enabled, you can also work with query sets when editing an EDI form as part of an EDI profile. The EDI form must have CSV, HTML, or XML as its output format. The results of the query set are then sent by email or FTP. See the xTuple Connect Product Guide for more information.

You will see a screen that contains the following:

Query Set widget

In this example there is one query set defined in the database, named ARStmt.

To modify an existing query set, select it from the list and click the EDIT button. Similarly, to delete an existing query set, select it from the list and click the DELETE button.

To create a new query set, click the NEW button. You will see the following screen:

New Query Set

You are presented with the following options:

Name

Enter a short name for this query set.

Description

Enter a simple description of what this query set does.

Queries in this set

This is a list of the query items which have already been defined as part of this query set.

Notes

Enter notes about this query set—a full description of why you created it or what it is intended to do.

Use the NEW button to add a query item to this query set. You will see the following screen:

The Query Item window showing an example of a custom MetaSQL query

You are presented with the following options:

Name

Enter a name for this query item.

Order

Select the position of this query item in the sequence. Query items with a lower order value will be run before those with a higher order.

Type of Query

Choose whether this query item should list all data from a particular database table or view, if it should use a MetaSQL statement already defined in the database for another screen or report, or if you want to create a new MetaSQL statement for use just by this query item.

Notes

Add comments about this query item here.

If you choose Custom Query as the Type of Query, you will see the MetaSQL editor. The MetaSQL language and MetaSQL editor are described in the OpenRPT Product Guide. To summarize that information, the MetaSQL language lets you build SQL queries dynamically and the MetaSQL editor lets you type and test MetaSQL statements. The query shown in the example above lists all of the header data for every sales order with one or more open line items, optionally restricted to a single customer.

Click SAVE to save this query item.

To add line item data to this query set, create another query item. Set the order to 1, select Custom Query as the Type of Query, and enter the following text in the MetaSQL editor:

SELECT *
  FROM api.salesline
 WHERE status='O'
<? if exists('cust_number') ?>
  AND order_number IN (SELECT order_number
                         FROM api.salesline
                        WHERE customer_number = <? value('cust_number') ?>)
<? endif ?>
;

If this query set gets run with no active parameters, it will show all sales orders with at least one open line item followed by all open sales line items. If you make the cust_number parameter active and set it to a valid customer number, the query set will produce all open sales orders and their open sales line items for just that one customer.