|
xTuple ERP Reference Guide |
|
|
xTuple ERP Reference Guide |
| 12.5.1. Query Sets and the QuerySet widget | ||
|---|---|---|
 | 12.5. Export Data |  |
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:
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 button. Similarly, to delete an existing query set, select it from the list and click the button.
To create a new query set, click the button. You will see the following screen:
New Query Set
You are presented with the following options:
Enter a short name for this query set.
Enter a simple description of what this query set does.
This is a list of the query items which have already been defined as part of this query set.
Enter notes about this query set—a full description of why you created it or what it is intended to do.
Use the 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:
Enter a name for this query item.
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.
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.
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 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 |