Hacking the xTuple Desktop Client With Extensions

Gil Moskowitz edited this page Nov 2, 2018 · 15 revisions

What is an Extension, Really?

The xTuple ERP desktop client is a C++ application that communicates with a PostgreSQL database (see the Architectural Overview). This application can be customized in several ways. Minor customizations include changing the format of reports that the application runs and adding custom commands to run external applications.

Sometimes you need more extensive and invasive changes. You might need to add any of the following:

  • new reports that are not part of the xTuple suite
  • new windows to perform tasks that are not part of the xTuple core
  • modifications to existing windows, perhaps hiding certain buttons or adding new fields
  • new business logic
  • new tables in the database

This kind of change should be encapsulated in an xTuple Extension. For example, if you need to record and report on work details for your business that xTuple does not track, you might want to add a new table, add a new window for data entry, add another window to summarize the collected data, create a report for printing, and add both of these new windows to the application menus. All of these resources bundled together constitute an extension.

Most of the tools you need to create a new extension are included in the xTuple ERP desktop client. Options for creating individual components abound:

Type Embedded Tool Stand-alone tool
Report embedded OpenRPT openrpt report writer for designing, printing, importing, and exporting report definitions; rptrender to print; importrpt or importrptgui to load report definitions into the database; exportrpt to extract report definitions from the database
MetaSQL embedded MetaSQL editor metasql for editing, importing, and exporting; importmqlgui for loading MetaSQL statements into the database
screens embedded Qt Designer standalone Qt Designer (you'll have to install Qt yourself)
scripts embedded script editor any text editor
custom commands embedded command editor [ none ]
database objects [ none ] pgadmin or text editor with psql?

You will also need a text editor to create some of the support files for your extension and the xTuple Updater to install your extension.

This document is pretty long, so here's a table of contents:

Developing Extensions in the Filesystem

You expect your extension to be useful for a long time to come. Otherwise you wouldn't spend the time creating it. Therefore, prepare for the future by starting out right - put your files in the filesystem and under version control (git or svn or cvs or ...). Build your extension one piece at a time and load it into the database with the Updater (see Packaging). One positive result of starting this way is you don't pollute the public schema with your extension, so it's easy to turn off or discard your changes if things don't work out.

The following directory structure works well:

  • your-package-name is the parent directory that holds all of your work for this one extension
    • packages holds Updater packages to install or update your package from one version to the next
    • resources
      • client
        • dict is for translation files
        • metasql is for the MetaSQL statements to drive your reports, display windows, etc.
        • reports contains the report .xml files
        • scripts is for the JavaScript files that control application behavior
        • uiforms holds your user interface .ui files for windows you create
      • database
        • functions for stored procedures
        • indexes
        • tables
        • triggers
        • views contains any views that you might want, such as api_ views for importing data
    • doc is for any documentation you choose to write

Extensions Live in the Database

After they are installed, the pieces of your extension live in the database. Obviously the stored procedures, tables, indexes, views, etc. are database objects. What may not be so obvious is that your reports, scripts, .uis, and MetaSQL statements are stored in the database, too.

Each package has the following parts once it's installed:

  • a row in the pkghead table
  • a database schema to hold its components
  • special tables in your schema that are linked back to tables in the public schema:
    • pkgmetasql is linked to public.metasql
    • pkgreports is linked to public.reports
    • pkgscripts
    • pkguiforms
    • pkgcmd
    • pkgcmdarg
    • pkgimage
    • pkgpriv

We'll talk below about making sure the parts of your extension get into the right schema.

Modifying Existing Core Windows

It is easy to alter the behavior and appearance of the core desktop client with a few lines of JavaScript. The hard part is figuring out what those few lines should say. There are also limits to what you can do, mostly because much of the application was written without scripting in mind. Let's start with tweaking an existing application window.

There are a few basic things you have to know:

  • What is the name of the window's C++ class? The name of your script must be exactly the same as the C++ class of the window it modifies.
  • What are the names of the window's widgets?
  • What are the entry points for you to inject your behavioral changes?

Read Finding What to Change. That gives a strategy to get the window's class name.

Open the classname.ui in Qt Designer to find the widget names and classes. When you click on a widget, the Property Editor window shows the name of the widget and its class. Use the xTuple ERP Programmer Reference to learn more about each widget can do. Anything listed as a Property, Public Slot, Signal, or Q_INVOKABLE public member function can be accessed from a script. Most enumerations can be used in scripts, too. Clicking on the List of all members link shows the full list of members, those written by xTuple and those inherited from Qt widgets.

Similiarly for the window itself, the xTuple ERP Programmer Reference description of the classname gives a list of C++ properties, signals, slots, and Q_INVOKABLE methods for the overall window.

These properties, signals, slots, and Q_INVOKABLE methods are your entry points. These are all ways your script can find out what the window is doing and modify its behaviors. Read the Qt documentation on signals and slots for details.

Scripts automatically get the following global objects:

  • mainwindow is the application's main window - an instance of the GUIClient class. Read the GUIClient reference page for the available signals, slots, properties, and invokable methods.
  • mywindow is the XMainWindow or XDialog of the screen the script is being run against. You'll have to read the reference docs for this particular window to find out which it is.
  • toolbox is an instance of the ScriptToolbox utility class.

The toolbox.executeQuery() and toolbox.executeDbQuery() methods return an instance of XSqlQuery. You can use this object to find out if the query ran successfully and to walk through the query results. Some but not all of the C++ features of XSqlQuery are available to scripts; see XSqlQueryProto in the xTuple Programmer Reference for details.

A Simple Example

Let's do something simple — limit the start date on the Summarized General Ledger Transactions window so it the query doesn't run forever or show too much data. For this example to make sense, you just need to know that you can connect a signal from one widget to one or more slots of different widgets, and that a JavaScript function can act as a slot.

  • First run the application and open that window - Accounting > General Ledger> Reports > Summarized Transactions
  • The debugging output tells us that the class name of this window is dspSummarizedGLTransactions
  • Open dspSummarizedGLTransactions.ui in Qt Designer
  • Click on the dates in the view of the window
  • Note this object is named _dates and has the class DateCluster
  • The DateCluster reference lists endDate and startDate as properties, an updated() signal, and functions setStartDate() and setEndDate() that are Q_INVOKABLE

The documentation is not explicit but we can reasonably assume that the updated() signal is emitted when either the start or end date of the DateCluster change. So the plan is to write a JavaScript slot that changes the start date if the user enters one that is too old. We need to be careful and avoid creating an endless loop in the signal/slot connections. Here is the JavaScript code:

var _dates   = mywindow.findChild("_dates"),   // mywindow is defined for you
    _now     = Date.now(),
    _minDate = new Date(_now - 1000 * 60 * 60 * 24 * 366)     // ~ a year ago

// every JavaScript function can act as a slot
// by convention we name everything that acts like a slot sSomething
function sFixDate() {
  if (_dates.startDate < _minDate) {
    _dates.startDate = _minDate;          // _dates will emit updated() again

// not sFixDates() => connect the function, not its return value

We can cheat for demonstration purposes and stuff this in the public schema:

  • System > Design > Scripts
  • click NEW
  • type the name of the window class into the Name field exactly - dspSummarizedGLTransactions
  • make sure the Enabled box is checked
  • type, paste, or IMPORT SCRIPT this script into the editor window
  • click SAVE

Note that the SAVE button gave you the choice of saving to the database, a file, or both. This helps you keep your scripts in sync between the filesystem and database.

Now open a new instance of the Summarized General Ledger Transactions window. This will load the window and the new script. Windows that are already open do not automatically load script changes. Check the Database Log window for errors loading the script. Close the G/L summary window, fix the script errors, and reopen the G/L summary.

Enter a date in the Start Date field, such as -5 (5 days ago) and see that it works properly. Now try different dates and watch what happens. Anything newer than a year ago should work fine and anything older than that should be changed to this date 1 year ago, +/- a day.

You should now delete this script or at least disable it (EDIT, uncheck Enabled, and SAVE).

Adding a Column

Adding a column to an existing display is also pretty easy. The major limitation is that the window has to use a MetaSQL statement stored in the database. You could work around this limitation but that gets really messy.

Here is another example, adding a fiscal quarter column to the Summarized General Ledger Transactions:

  • Read qt-client/displays/dspSummarizedGLTransactions.cpp and note that the query used has the group summarizedGLTransactions and name detail
  • find the MetaSQL statement (System > Design > MetaSQL Statements)
  • open the statement in the MetaSQL Editor
  • change the line near the top containing f_notes, to f_notes, extract(quarter from gltrans_date) as fisquarter,
  • create a one-line script called dspSummarizedGLTransactions:
mywindow.findChild("_list").addColumn(qsTr("Quarter"), 100, 1, true, "fisquarter");

Now when you view the summarized G/L, you should see the quarter in the right-most column of the display. These values may print as 1.00, 2.00, etc. See Using the XTreeWidget for ideas on how to fix that.

More complex changes

You will have to read the C++ code if you want to make major changes. A lot of people try to change how existing windows save data, perhaps adding new error checking or saving data not handled by the core. Here is the general strategy for this kind of problem:

  • Look at the signal/slot connections in the C++ code, particularly for connections to slots named sSave or save
  • Disconnect these connections in your extension script
  • Write new JavaScript functions that have three phases:
    • add your error checking
    • call mywindow.sSave() or mywindow.save()
    • add your database manipulation

Beware that other scripts on this window may try to do the same thing. If this happens you might be unable to accomplish your goal. Contact xTuple for help.

Developing New Windows

The process for developing new windows is similar to that of scripting an existing window but you have more work to do. Now you really have to read some C++ to see how the app does its work. You might also be able to use other extensions as a model.

UI Layout

UI design is an art. Using Qt Designer is a craft - it takes a lot of practice to get good but it is not hard to get started. Basically read the Qt Designer docs to learn how to use the tool and look at a bunch of .ui files in qt-client/guiclient to see how we do it.

Pay particular attention to picking the right xTuple widgets to use and choosing good QLayout classes to bundle your widgets together. The Qt widgets are great but the xTuple widgets have been written to suit the needs of our application. Use good widget names, too, because your script will need to refer to them. Bad widget names make for hard-to-read code.

Script Creation

Make sure that the script and UI screen have exactly the same name, just like with scripting existing windows. This is how the application matches them to each other.

Use the script editor to create your JavaScript or to import files from the filesystem. The typical script has four sections:

  • variable declaration and initialization
  • adding columns to XTreeWidgets
  • definitions of functions that act as slots
  • the set of signal/slot connections

Adding to the Menus

If you create new windows then you'll probably want to add them to the application menus. This is done by adding an initMenu script to your package. All scripts named initMenu are loaded when the desktop client application starts. Keep in mind that you will have to cycle through quitting and starting the app while testing this part of your extension.

Here is the entire initMenu from the fixCountry package:

function sFixCountries()
  try {
  var newdlg = toolbox.openWindow("fixCountries", 0, Qt.Window, Qt.NonModal);
  } catch(e) { print("sFixCountries exception: " + e); }

var _crmutilsmenu = mainwindow.findChild("menu.crm.utilities");
var fixCountriesAction = _crmutilsmenu.addAction(
                  qsTr("Fix Countries before setting Strict Countries option"));
fixCountriesAction.enabled = privileges.value("MaintainAddresses")   &&
                             privileges.value("MaintainCreditMemos") &&
                             privileges.value("MaintainSalesOrders") &&


This script has 5 parts:

  • sFixCountries() is a slot that opens a scripted window called fixCountries
  • get the Qt object that is the CRM > Utilities submenu
  • add a menu item to that submenu
  • enable that menu item if the current user should be allowed to use it
  • connect the triggered signal to the sFixCountries() slot

That last part may sound a bit odd. "Triggered" in this case means "however the user selected the menu item, do it". This includes opening the menu and clicking on that item with the mouse, using a hot-key, walking the menus with arrow keys, or poking at it with a finger on a touchscreen.

Translation and Localization

See Hacking the Core for information on making your extension translatable and localizable.

The primary difference between making the core translatable and making an extension translatable is the name of the function that exposes a string to translation:

var ans = QMessageBox.question(mywindow, qsTr("Are you sure?"),
                               qsTr("Are you sure you wanted to click the %1 button?")
                               QMessageBox.Yes | QMessageBox.No);

Note the use of qsTr instead of tr or QObject::tr.

Script Debugging

There are several ways to debug your scripts. As mentioned elsewhere, you can follow debugging output in the Database Log window. Use mainwindow.sReportError() to log your own debugging output. One common problem is that script functions will throw JavaScript exceptions. To find these, you can use try-catch blocks:

function sSave() {
  try {
    var params = { id: _widget.id },
        q = executeQuery("INSERT ... ;", params); // error: missing 'toolbox.'
  } catch (e) {
    mainwindow.sReportError("exception in sSave: " + e); // write to debug output
    QMessageBox.critical(mywindow, qsTr("sSave Exception"), e.message);

When sSave() gets called, this will write exception in sSave: Reference Error: executeQuery is not defined to the debug output and will also open an error dialog with the same message. You can even wrap your entire script in one large try-catch to find top-level errors.


The easiest way to add a report to your extension is to copy one that already exists and alter it. The best way to ensure your new report is part of your extension and not in the public schema is to save it as a file on disk, add it to your package.xml extension description (see Packaging), and reload it. Make sure you change the name of the report (Document > Properties) and save the file as name.xml.

Read Reports and the OpenRPT Product Guide to learn more about report creation.

Database Objects

You probably need to add new tables, triggers, and stored procedures to store and manage the data for your extension. As we said above, your extension will probably live and grow over time and you will probably find that your data needs change. If you share or sell your extension then sometimes your extension will need to be installed and sometimes it'll need to be upgraded. Therefore, try to write as much of your database object creation code as possible to be idempotent so a single Updater package can be used to install or upgrade your extension:

-- don't do this:
create table ic.icflav (
  icflav_id          serial primary key,
  icflav_name        text,
  icflav_description text,
  icflav_calories    integer

-- do this instead:
select xt.create_table('icflav', 'ic');

select xt.add_column('icflav','icflav_id',        'serial', 'primary key', 'ic'),
       xt.add_column('icflav','icflav_name',        'text', '',            'ic'),
       xt.add_column('icflav','icflav_description', 'text', '',            'ic'),
       xt.add_column('icflav','icflav_calories', 'integer', '',            'ic');

comment on table ic.icflav is 'Ice cream flavors';

These xt functions check if the table or column or ... exist and creates them if they don't. We currently (October 2018) have these functions:

  • add_column
  • add_constraint
  • add_index
  • add_primary_key
  • create_table

Many Postgres DDL (Data Definition Language) statements have drop if exists and either create if not exists or create or replace clauses you can use to make your database definitions idempotent. Check the Postgres reference docs for complete information. Common uses in xTuple core and extension code include:

  • create or replace function
  • drop trigger if exists followed by create trigger

Be careful when you drop database objects. It's tempting to use the drop ... cascade variant. The problem with doing so is that you may inadvertently drop something without knowing it. For example, if you drop type mytype cascade then any functions with parameters of mytype or tables with columns of mytype will be dropped. This probably is not what you want. It's much safer to drop without the cascade. Then you can make your script explicitly handle anything that depends on mytype. This seems like a lot of work but it solves two problems:

  • It helps you find all of the dependencies in your extension and fix them to handle the change
  • It saves other extensions which depend on your extension from silent failures after upgrading your extension


The xTuple Updater is used to package extensions to the desktop core. Read more about it on the Updater wiki.

Learning More

xTuple offers Developer training classes to give hands-on experience developing extensions.

Also see:

You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.