• UsingTheXTreeWidget

Using The XTreeWidget: How to display data that sort nicely, localize well, and remember where you were

The XTreeWidget is used throughout the xTuple ERP application to display data in tabular form. In xTuple ERP releases 2.3.2 and earlier, developers who wanted anything more than a simple list of records had to write C++ code to manually insert each row into the XTreeWidget. The queries had to convert all of the data to character strings for localization purposes, which often meant that numeric values had to be selected twice or even calculated twice - once for display purposes and again for performing calculations. Because the displayed data were actually character strings, sorting the data by clicking on column headers often behaved strangely and, to get dates to behave reasonably, the formats used for dates were different when displayed in XTreeWidgets than in date entry fields.

Starting with release 3.0, much of this complexity can be moved to the query, often resulting in less code that is easier to write and easier to maintain. This page documents how to use the XTreeWidget to best advantage, writing as little C++ or QtScript code as possible.

Write The Basic Query

The primary usage of XTreeWidgets is to show the results of database queries. So start by writing a query that returns the data you want to see. There are a few rules the queries have to follow to be usable by the XTreeWidget:

1. The first column must be an integer value, preferably the id column of the primary table being displayed.
2. All result columns must be named.
3. Avoid formatting the result columns as much as possible.

4. Avoid using AS to create column names that end with role, except as described below.

What does this mean in practice? Here are two examples, one that follows the rules and one that doesn't:

-- A Good Query:
SELECT item.*, i.uom_name AS inv_uom_name, p.uom_name AS price_uom_name
FROM item LEFT OUTER JOIN uom i ON (item_inv_uom_id=i.uom_id)
          LEFT OUTER JOIN uom p ON (item_price_uom_id=p.uom_id)
LIMIT 100;

-- A Bad Query:
SELECT item_number,
       itemCapUOM(item_id),
       formatPrice(item_listprice) AS listp,
       itemCapInvRat(item_id) AS capacityrole
FROM item
LIMIT 100;

Note the following about the good query:

• The first column is an integer because the * expands to all columns of the item table, of which the first is item_id, the internal id of the item table.

• The query retrieves the entire width of the item table. This will continue to work no matter how much the item table changes or how many columns we choose to display or which columns we choose to display. If someone comes along later and wants to add a column to the display that the original author didn't think was important, s/he can do so easily.

• There are two result columns containing names of Units of Measure and they are given unambiguous names. All of the other columns come directly from the item table and are also unambiguous.

The bad query breaks all of the rules:

• The first column is a text field, not an integer.
• The capacity unit of measure isn't named in the query.
• The list price is converted from a numeric value to a text value, which may cause problems if the code later needs to manipulate the list price as a number.

• The capacity-to-inventory UOM ratio is given a name but one that end with role, which may or may not cause problems.

Creating XTreeWidgets And Their Columns

A query on its own isn't enough; we need a place to display the results of that query and the right widget for that is the XTreeWidget.

To use an XTreeWidget on a window, you must either drop the widget onto the graphical representation of the window in Qt Designer or add it to the window using C++ or QtScript. When you do this and nothing else, the widget appears as an empty rectangle when you open the window.

The next step is to add columns to the widget. If you're writing in C++, then this is usually done in the window's constructor. Using the good query from above we could do something like this in C++:

// C++ example
...
_xtree->addColumn(tr("Item"),        _itemColumn, Qt::AlignLeft,   true, "item_number");
_xtree->addColumn(tr("Description"),          -1, Qt::AlignLeft,   true, "item_descrip1");
_xtree->addColumn(tr("List Price"), _moneyColumn, Qt::AlignRight,  true, "item_listprice");
_xtree->addColumn(tr("Price UOM"),           100, Qt::AlignCenter, false,"price_uom_name");
...
// Script version
...
_xtree.addColumn("Item",       100, 1, true, "item_number");
_xtree.addColumn("Description", -1, 1, true, "item_descrip1");
_xtree.addColumn("List Price",  60, 2, true, "item_listprice");
_xtree.addColumn("Price UOM",  100, 4, false,"price_uom_name");
...

This creates four columns in the XTreeWidget itself. Each column has certain properties controlled by the arguments to addColumn():

1. The title of the column. The tr() wrapper invokes Qt's translation mechanisms.

2. The width of the column in pixels. Positive numeric values are used literally; for example the 100 on the "Price UOM" line means that the columns should be made 100 pixels wide. This value is ignored if this window has previously been opened and column width memory has been turned on (the default). The values _itemColumn and _moneyColumn are symbolic values defined in the source file widgets/xtreewidget.h; these are used throughout the application to provide consistency in how XTreeWidgets are presented. The value -1 is a special case and means that this column should take up whatever space is left after all of the other columns have been sized.

3. The alignment of the data in this column. These are symbolic values defined by Qt.

4. The default visibility of this column: If true then the column is visible by default and if false then the column is not visible by default. Note that these defaults only apply if the user has never opened this window before or if column memory has been turned off; otherwise the column memory feature of xTuple ERP will take over and set the visibility.

5. The query column to use when populating this column in the XTreeWidget.

If you are writing in QtScript then the code looks similar but not identical. The differences are in the column widths and how they are aligned. The symbolic values used in the C++ code are defined using C++ mechanisms and aren't available to QtScript users. In addition there is no context to provide run-time translation.

Populating XTreeWidgets

Equipped with a query and an XTreeWidget with columns, we can now populate the XTreeWidget with the results of that query:

// C++:
XSqlQuery myq;
myq.exec("SELECT item.*, i.uom_name AS inv_uom_name, p.uom_name AS price_uom_name "
         "FROM item LEFT OUTER JOIN uom i ON (item_inv_uom_id=i.uom_id) "
         "          LEFT OUTER JOIN uom p ON (item_price_uom_id=p.uom_id) "
         "LIMIT 100;");
_xtree->populate(myq);

//QtScript:
var myq = toolbox.executeQuery(
         "SELECT item.*, i.uom_name AS inv_uom_name, p.uom_name AS price_uom_name "
       + "FROM item LEFT OUTER JOIN uom i ON (item_inv_uom_id=i.uom_id) "
       + "          LEFT OUTER JOIN uom p ON (item_price_uom_id=p.uom_id) "
       + "LIMIT 100;");
 mywindow.findChild("_xtree").populate(myq);

If it turns out that we need to add another column to the display, all we have to do is add another addColumn() call. The XTreeWidget takes care of most of the localization issues for us, showing dates in the current locale's format and choosing the decimal and group separators for numeric values appropriate for that locale. One thing that does not happen automatically is formatting numbers to the proper number of decimal places.

Numeric Formatting

Numeric values can be formatted according to rules set through system locales. To do so add special columns to the query that name the "numeric role" of this value -- whether this is a weight, a cost, a percentage, etc. The value of the result column must be one of a small set of special values (see Full Disclosure for a full list) and the name of the result column must follow the following convention: nameOfBaseResultColumn_xtnumericrole. For example:

'salesprice' AS item_listprice_xtnumericrole,
'uomratio'   AS item_altcapinvrat_xtnumericrole,
...

The advantages this provides over the old scheme of calling formatting functions are:

• Better sensitivity to locale issues - the XTreeWidget uses the same algorithm as individual text fields to localize the data.
• Reduced need to recalculate values - the XTreeWidget can format the results of calculations or stored procedure calls to display them, rather than the old scheme which required calculating the same numbers twice, once for internal use and again for display formatting.

Adding Color

You can also add color to the display. Again you must follow a naming convention for the name of the result column: nameOfBaseResultColumn_qtforegroundrole. There are three ways to name the color (see Full Disclosure for details):

1. Use a localizable color name. This will make your display match the colors used in the rest of xTuple ERP.
2. Use a standardized color name.
3. Use an RGB value.

To color a specific value you have to be careful to write the query in such a way that the special color is only used for that value. Make sure you write your query so that the color gets the NULL value wherever you want the default color. For example you could highlight the item number for all items that do not have a pricing unit of measure using xTuple ERP locale value for emphasis:

...
CASE WHEN (price_uom_name IS NULL) THEN 'emphasis'
     END AS item_number_qtforegroundrole,
...

Alternatively you can color the entire row simply by not giving a prefix to qtforegroundrole:

...
CASE WHEN (price_uom_name IS NULL) THEN 'emphasis'
     END AS qtforegroundrole,
...

Adding Indentation

Time to get really fancy...

Let's say you want to create a display that shows a list of orders and the line item details of those orders but you want those details nested underneath the orders so they can be expanded or hidden. With careful crafting of the database query the XTreeWidget can handle this. The trick is to write the query in such a way that the right data appear in the right columns and can be sorted so that all of the line items appear directly after their parent order.

Again the XTreeWidget uses a specially named column in the query result to handle indentation. Since indentation is always performed for the whole line, there is never a prefix to tie the role to a particular column. The keyword for indenting is xtindentrole.

Any line which sets xtindentrole to 0 will appear as a top-level item in the XTreeWidget. Anything with an xtindentrole greater than 0 will be indented under the last item returned by the query with a smaller xtindentrole. This means that level 2 items will be indented under the prior level 1 item and level 1 items will be indented under the prior level 0 item. It also means that if there is a level 0 row followed immediately by a level 3 row, the level 3 row will be indented directly under the level 0 row.

As an example, here's the query that populates the indented Vouchering Edit List in xTuple ERP 3.0:

SELECT orderid, seq,
       CASE WHEN seq = 0 THEN vouchernumber
            ELSE ''
       END AS vouchernumber, ponumber, itemnumber,
       vendnumber, description, itemtype, iteminvuom, f_qty, cost,
       'curr' AS cost_xtnumericrole,
       seq AS cost_xttotalrole,
       CASE WHEN seq = 3 THEN 1
            ELSE seq END AS xtindentrole,
       CASE WHEN findAPAccount(vendid) < 0 THEN 'error'
       END AS qtforegroundrole
FROM (SELECT orderid,
      CASE WHEN length(ponumber) > 0 THEN 0 ELSE 1 END AS seq,
      vouchernumber, ponumber,
      CASE WHEN (itemid = 1) THEN invoicenumber
           ELSE itemnumber END AS itemnumber,
      CASE WHEN (itemid = 1) THEN itemnumber
           ELSE ''         END AS vendnumber,
       vendid, description,
       itemtype, iteminvuom, f_qty, cost
FROM voucheringEditList
UNION -- pull out the credits
SELECT DISTINCT orderid, 2 AS seq, vouchernumber, '' AS ponumber,
       :credit AS itemnumber, '' AS vendnumber, vendid,
       account AS description,
       '' AS itemtype, '' AS iteminvuom, NULL as f_qty, cost
FROM voucheringEditList
WHERE itemid = 2
UNION -- calculate the debits
SELECT orderid, 3 AS seq, vouchernumber, '' AS ponumber,
       :debit AS itemnumber, '' AS vendnumber, vendid,
       CASE WHEN findAPAccount(vendid) < 0 THEN :notassigned
            ELSE formatGLAccountLong(findAPAccount(vendid))
       END AS description,
       '' AS itemtype, '' AS iteminvuom, NULL as f_qty,
       SUM(cost) AS cost
FROM voucheringEditList
WHERE itemid = 2
GROUP BY orderid, vouchernumber, vendid
ORDER BY vouchernumber, ponumber desc, seq) AS sub;

There's a lot of code here but it isn't too hard to understand if you break it down:

• The query is two levels deep -- the outer query adds formatting to the results of the inner query, details like how many decimal places to use for the cost column and what colors to use.

• The inner query is a UNION of several different subsets of the data in voucheringEditList.

• voucheringEditList itself is a database view. The query here uses the view for historical reasons. It might make sense to rewrite this query to use the underlying tables directly.

• Each part of the UNION sets a seq column, which is used in two ways:

  1. To help sort the data in the inner query. The data are always presented as a voucher and the purchase order to which it applies, followed by credits applied to that voucher, and ending with the total debits incurred by that voucher.
  2. To help indent the data in the outer query. The data are displayed with the Voucher number at level 0, individual purchase orders which that voucher covers indented below the voucher, individual credits below the purchase orders, and the total incurred debit back directly tied to the voucher.

Here is an example of what this query produces:

Note that the vouchered quantity column is not formatted the same way as the cost column. This example was created while running in a non-US locale. The comma (',') decimal separator used in the cost column comes from the locale, as does the one-decimal-place precision, while the vouchered quantity is actually a string value produced by the view, which is not locale-aware (in xTuple ERP version 3.0).

Full Disclosure

Let's be honest here... the only way to get full disclosure is by reading the source code - which you can do if you like: go to http://postbooks.svn.sourceforge.net/viewvc/postbooks/xtuple/trunk/widgets/ and click on the number in the column next to xtreewidget.cpp, near the bottom of the window. Until you get around to that, however, here are some useful details about how to use the XTreeWidget.

Colors

xTuple recommends that you use as little color as necessary to make your point, and when you do need to add color that you use xTuple ERP's localizable color scheme whenever possible. This will let your changes blend well with the rest of the application and respond to the needs of users. Colors are chose from the locale palette by using one of the following names:

• error

• warning

• emphasis

• altemphasis

• expired

• future

These correspond to the similarly named fields in the locale table and on the Locale window.

If you need to use something other than one of the six named locale colors, you can use any color that Qt understands:

• You can use named colors, where the names come from the list of W3C-standard color name or, in X11 environments like most Linux and UNIX GUI environments, any valid X11 color name.

• If this is not sufficient then you can switch to RGB colors, such as

Role keywords

So, what is the full list of fancy formatting things you can do with the XTreeWidget and a carefully crafted query?

Other useful functionality

Here are some useful methods defined in the XTreeWidget class:

Here are some of the signals emitted by the XTreeWidget class:

Each row of data in the XTreeWidget is represented internally by an XTreeWidgetItem. Look at the documentation for QTreeWidget for ways to get items from the tree. For the purposes of this document, the most useful thing you can do with the XTreeWidgetItem is get the raw values stored in a particular column, something like this:

QString itemnum = _xtree->topLevelItem(4)->rawValue("item_number").toString();

There are other methods, like id() and altId(), but you don't really need those very frequently since the XTreeWidget has these methods, too, and they return values based on the user's current selection.

Some Words Of Advice

• Put all of the special XTreeWidget-control columns together at the end of the query:

SELECT item.*, i.uom_name AS inv_uom_name, p.uom_name AS price_uom_name,
       'salesprice' AS item_listprice_xtnumericrole,
       'uomratio'   AS item_altcapinvrat_xtnumericrole,
       CASE WHEN (price_uom_name IS NULL) THEN 'emphasis',
       END AS item_number_qtforegroundrole
FROM item LEFT OUTER JOIN uom i ON (item_inv_uom_id=i.uom_id)
          LEFT OUTER JOIN uom p ON (item_price_uom_id=p.uom_id)
LIMIT 100;

• If you turn down that advice, at least place the special columns right next to their base columns:

SELECT item_id,
       item_number,
       CASE WHEN (price_uom_name IS NULL) THEN 'emphasis',
       END AS item_number_qtforegroundrole,
       i.uom_name AS inv_uom_name, p.uom_name AS price_uom_name,
       item_listprice,    'salesprice' AS item_listprice_xtnumericrole,
       item_altcapinvrat, 'uomratio'   AS item_altcapinvrat_xtnumericrole
FROM item LEFT OUTER JOIN uom i ON (item_inv_uom_id=i.uom_id)
          LEFT OUTER JOIN uom p ON (item_price_uom_id=p.uom_id)
LIMIT 100;

• Don't overdo it. Put in just enough formatting to make your point and no more.

• Write your queries to SELECT maintable.* to reduce the maintenance load later and to make it easier for scripts to add columns to the XTreeWidget. You might be tempted to SELECT * even for multi-table joins, but this might cause problems with setting id values for individual rows.

• Don't clear the XTreeWidget before you populate it. If user has a particular row selected, the XTreeWidget stores its id before refilling itself and tries to reset that as the current id after it's done. By clearing the XTreeWidget first in your application code, you're not allowing this to happen.