12.5.1. Query Sets and the QuerySet widget

12.5.1. Query Sets and the QuerySet widget
	12.5. Export Data

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:

The QuerySet 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:

The Query Set window

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.


12.5. Export Data		12.5.2. MetaSQL Parameter Editor

xTuple ERP Reference Guide

12.5.1. Query Sets and the QuerySet widget

Note