xTuple logo

xTuple Connect Product Guide

  
Collapse AllExpand All
7.3.2. Query Sets in xTuple Connect
Prev 7.3. Ad Hoc Database Queries Next

7.3.2. Query Sets in xTuple Connect

Query Sets, which are collections of MetaSQL statements, can be created and edited in the Export Data screen (System > Utilities > Export Data) and the EDI Form screen as described in Section 3, “Sending CSV Files”. See the xTuple ERP Reference Guide for full documentation of the Export Data screen.

xTuple Connect adds a SCHEDULE button to the Export Data screen. This button lets you send the Query Set to the xTuple Connect Batch Manager to be run. All Query Sets are run using the xTupleQuerySetEmail System EDI Profile.

The xTupleQuerySetEmail is similar to the xTupleMetaSQLEmail profile. The primary difference is that the default output format for Query Sets is XML. An XML file containing the results of multiple queries is easier to understand than a CSV file with the same data. This is because the CSV file may not contain header lines and, even if it did, these are easily lost because they are mixed in with the data. XML files, on the other hand, have individual tags for each data field and for each row, so the file describes itself—as long as the names of the query set, queries, and result columns are chosen well.

Here is an example of submitting a Query Set to the xTuple Connect Batch Manager:

  1. System > Utilities > Export Data

  2. Select a Destination Format

  3. Create a Query Set if one does not already exist for your needs

  4. Select the Query Set

  5. Set values for the MetaSQL parameters if necessary

  6. Click SCHEDULE

  7. The Review EDI Before Sending screen should appear

  8. Click ACCEPT

Once submitted, it can be rescheduled to run on a regular basis:

  1. System > xTuple Connect Console

  2. Select the appropriate line from the list of jobs. Look for a line with "EmailXML" in the Action column and "Document: QSET name-of-your-queryset" in the Detail column

  3. Click RESCHEDULE

  4. Set the Recurring properties

  5. Click SAVE

When crafting the queries for recurring execution, keep in mind that the values of the MetaSQL parameters are fixed. They do not change from one run of the query to another. Therefore, write your recurring queries to use as few MetaSQL parameters as possible and to use relative information instead of fixed. Here is an example of a problem and 3 solutions:

The problem

Automatically explode Work Orders on a regular basis

Bad solution: Explode a particular Work Order number passed in by the user
SELECT wo_number, explodeWo(wo_id, true) AS explodedStatus
FROM wo
WHERE wo_number = <? value('wo_number') ?>;

This is a bad solution because the user has to pass a particular Work Order. If this job is set to recur, the same Work Order will be exploded repeatedly. This will work the first time but fail every other.

Bad solution: Explode everything due by July 25, 2010
SELECT wo_number, explodeWo(wo_id, true) AS explodedStatus
FROM wo
WHERE wo_duedate <= '2010-07-25';

This is a little better, because at least this will explode newly created orders for the first few times it runs. However, it will return one row for every Work Order that is due before July 25, whether it needs to be processed or not, and it will stop doing anything useful at all after that date.

Good solution: Explode everything due in the next 7 days
SELECT wo_number, explodeWo(wo_id, true) AS explodedStatus
FROM wo
WHERE wo_status='O'
  AND wo_duedate <= CURRENT_DATE + CAST('7 days' AS INTERVAL);

This is a much better solution: it restricts the Work Orders to be checked to just those ready to be exploded and it has a moving seven day window.

Prev Up Next
7.3.1. Submitting MetaSQL Statements to the xTuple Connect Batch Manager Home 8. Importing Data with xTuple Connect