Release Notes

Version 1.8.0 (2024-12-03)

Filter Only/Filter Exclude

The new Filter Only and Filter Exclude actions lets you filter on, or filter out, a specific value without having to find it first in the Filter popup. Right-click a cell containing a value, and invoke the relevant action from the context menu. Or, use the indicated keyboard shortcuts. There is a quick video demo of this feature here.

Before/after example showing the Filter Only action.

These actions work with primitive values of all standard types, including null values, empty strings, foreign key values formatted as a compact join, and values in a crosstab header.

Filters set via Filter Only/Exclude work the same as filters set from the regular Filter popup. Use Clear Filter to remove the filter (Ctrl+Shift+L or ⇧⌘L on MacOS).

Filter Exclude will properly adjust any existing filter that may be present on the same field.

The keyboard shortcut for Filter Only (Shift+Alt+Enter) is similar to that of Open Details (Alt+Enter), as the two actions both act to drill down in the data.

Data Sources

When listing database tables, show SQL VIEWs with a subtle “v” indication (). Show the exact table type that is reported by the JDBC driver in the tooltip.

Except for the special indications above, SQL VIEWs behave the same as any other table.
Avoid a “rewrite failed unexpectedly” warning when e.g. an Excel file has been moved.
PostgreSQL: In the table list, include materialized views. (They were previously omitted.)
Snowflake: Bump the bundled JDBC driver version to 3.20.0.

Visual Query System

Additional terminology adjustments in the user interface.
Avoid picking likely metadata columns (e.g. CreatedAt or updated_by) as default-visible.
Avoid flashing the spinner animation for near-instantaneous operations.
Show the Sort Next actions in the main menu bar only (under Query), to keep the context menu from getting too tall. These actions were showing only in certain cases anyway.
Tighten table list spacing in the Custom Join dialog, and other cosmetic UI improvements.

Other Changes

When copy/pasting from a single cell that is formatted as a compact join, use the full formatted string (e.g. “First Name” + “Last Name”) as the value for external applications.
For bar charts and heat maps, keep the axis stable e.g. after Filter Only.
Hide some irrelevant options in the field selector's type-to-search bar.

Version 1.7.5 (2024-11-09)

Have the Open Details action (Alt+Enter) automatically open the detail view in a sidebar, without the user having to drag its tab. Adjust focus behavior when closing the sidebar.

The Open Details action invoked with a new sidebar opened automatically.

Adjustments to the Custom Join action. Prompt if no table or constraints are selected.
Improve alignment of text and icons in the field selector, and other UI cosmetics.
Some terminology adjustments, for consistency with an upcoming documentation site.
In generated SQL code, make identifiers more readable (e.g. c42_price instead of c42).
Fix automatic selection of new items in the Folders pane, e.g. after adding a data source.
Fix a bug where the formula bar or inplace cell editor did not always update after editing.
MacOS: Fix sluggish graphics on certain external monitor configurations.
Windows: Fix possible repaint issues on certain graphics cards.

Version 1.7.4 (2024-09-24)

Improve the display of errors that occur while attempting to list the tables in a data source in the Folders pane. For example, a CSV file might have been moved or deleted. The relevant error message now displays in red next to the data source icon, instead of triggering an “unexpected” error. You can hover over the text to see the full message.
Fix a cosmetic issue in the cell editor on Retina/HiDPI screens (leftover cursor pixels).
Adjust auto-formatting to avoid decimals for very large numbers.
Improve formatting of the row count indication in the toolbar.
Fix a checkbox bug when changing advanced properties in the Fields sidebar.
MacOS: Make F2 work to rename items in the Folders and Fields sidebars.
Windows: Switch to a code signing certificate provided by Microsoft.

Version 1.7.3 (2024-08-20)

Faster Aggregate Queries

For large databases, many queries involving aggregate function (SUM, AVG, MAX etc.) now run substantially faster. This applies in particular when using the Custom Group feature.

For example, a simple SUM over a PostgreSQL table with 10 million rows, grouped by a single text field, would previously appear to hang indefinitely, but now completes in about one second on a MacBook Air M3 laptop:

The improvements are achieved through simplifications in the SQL queries that Ultorg generates, for the most common kinds of aggregate queries. The actual result of each query remains unchanged. Ultorg has a large suite of test queries that run with optimizations enabled and disabled to check that query results remain the same from release to release.

A formula containing a SUM over another SUM formula (grand total/subtotal) will now usually compile to the same SQL as a SUM directly over the innermost referenced value.

Some aggregate queries may still be slow on large datasets, in particular when sorting on the aggregate result while simultaneously showing the input rows of the aggregate. In such cases it may help to hide the input rows, or to add a filter to reduce the amount of data. Additional optimizations may be introduced in future releases.

MacOS Improvements

Fix two-finger tap to right-click, where configured (was broken on some MacBooks).
Update the app icon to conform with Apple's design guidelines.
Fix blurry tooltips for long file names in the Folders pane.
Cosmetic adjustments to button widgets and UI font weight, to match the MacOS style.

Other Updates

Linux: Bundle icon files for the Ultorg application, and provide instructions for creating a desktop shortcut on Gnome or KDE (on the Download page).
Windows: Avoid slightly sluggish popup menus.
Fix unreliable drag-and-drop of files to the Folders sidebar (e.g. to connect to a CSV file).
Improve the type-to-search feature that is available in the Folders and Fields panels, so it behaves more like Ctrl+F search in Chrome and in Ultorg's perspective editor.
Show the Update Available message only every few days, with gradually increasing delay.
Adjust keyboard focus handling when switching to Ultorg from another app.
Adjust field selector behavior for checkboxes associated with a compact join.
When bulk pasting to insert rows, avoid skipping empty rows and columns in the input.
Adjust actions and keyboard shortcuts in the SQL/JSON Output tabs.
Update various underlying software libraries.
Additional small user interface adjustments.

Data Source Specifics

Oracle: Fix an error that prevented Apply Edits from working in some cases.
Microsoft Access files: Support the extended date/time type, introduced in Access 2019.
SQLite: Implement the int function.

Version 1.7.2 (2024-06-05)

Add the Data→Replace Data Source action, which changes the data source that is referenced by the current perspective, without otherwise modifying the query. This can be used e.g. to switch between production and development environments.

Screenshot of the Replace Data Source menu item and dialog box

For perspectives that include tables from multiple data sources, the cursor position is used to determine which data source should be replaced.

This action is primarily useful when the old and new data sources are of the same type and contain tables and columns with identical names. Future versions might add more options.

Other Improvements

When applying data edits, perform DELETE operations before INSERT, in case the database has uniqueness constraints that could otherwise conflict.
When switching to Ultorg from a different window, move focus to the perspective editor.
Avoid a warning that could appear if insert row/equals were pressed in quick succession.

Version 1.7.1 (2024-05-10)

Make bulk paste work with Logical (boolean) and JSON-typed columns.
For bulk paste into text columns, avoid parsing of pasted values as dates or numbers.
Fix a StackOverflowError from certain scroll interactions in sidebars or popups.
Fix a bug from 1.7.0 where table extracts would incorrectly display as “Recovered”.
Fix an error when the formula bar drag area is accidentally right-clicked.
MacOS: Cosmetic adjustments to popup menus.

Version 1.7.0 (2024-05-02)

Google Sheets as an Editable Relational Database

Data in Google Sheets can now be edited directly from Ultorg, and you can build a complete relational database out of Google Sheets worksheets.

This means you can use all of Ultorg's functionality without an external SQL database, and for collaborative work. Some users may be accessing data from Ultorg, and others from the Google Sheets web app. You can collect data using Google Forms, too.

To make a Google Sheets spreadsheet editable from Ultorg, organize it as follows:

Each worksheet (spreadsheet tab) should contain a single table of data.
The top row in the worksheet should contain names for each column.
Each table column should contain data of one type only: text, numbers, dates, times, timestamps, or true/false. If you are designing a new table, you can provide a row of example data so that Ultorg can detect the column types.
The leftmost column should contain values that uniquely define each row. These identifiers can be numbers (1, 2, 3, ...) or text (airport codes, JIRA tickets etc.).
If the name of the first column is ID, and you use numeric identifiers, then Ultorg will assign auto-incremented values when you insert new rows. Prefixed numbers, such as DEPT-3215, can also be auto-incremented.

For examples of properly organized Google Sheets spreadsheets, see the links below. To try data editing with these in Ultorg, first make a copy to your own Google Drive.

Simple CRM Database: Tables illustrating how Ultorg could be used as a Customer Relationship Management tool, letting you track customers and interactions with each customer (emails, meetings etc.). There is also a table containing data collected via Google Forms, which can be joined to the Customers table via the Email Address field.
Course Catalog: A database of academic courses, with instructors, meetings, and reading lists. About 12,500 rows across 5 related tables.

Data Modeling

The use of an ID column in each table—a so-called primary key—ensures that data can be edited safely by multiple users at once, even as rows are inserted, deleted, and reordered. Tables without a primary key column can still be used in Ultorg, but are not editable. In Ultorg, a table's primary key column is indicated by a key icon () in the Fields sidebar:

[Screenshot showing the Fields sidebar with the primary key icon highlighted.]

In a relational database, it is common to have the IDs of one table be referenced by columns in other tables. For instance, a Customer Interactions table might have a column called Customer ID, which references the ID column of the Customers table. This is a powerful concept that allows you to model all kind of business relationships in a standardized way. You can use Google Sheets to design your tables, and then tell Ultorg about relationships between them using the Custom Join action:

[The Custom Join action invoked on the ID column of the Customers table, to join with Customer ID in the Customer Interactions table.]

With joins configured between tables, you can tailor Ultorg perspectives for your various business tasks, and use the same perspectives for data entry. For example, you can log a new customer interaction by placing the cursor on a cell under Customer Interactions, next to the desired parent row in Customers, and invoking Insert Record (Ctrl+Shift+Plus):

[A nested table layout showing three joined tables, with a record pending insertion in one of them.]

Fields that are joined against another table's primary key column will provide a drop-down menu when edited (as shown in the demo animation at the top of this post).

Connecting and Refreshing

To connect to a Google Sheets workbook in Ultorg, click Add Data Source→Connect to Google Sheets and follow the instructions. Ultorg perspectives can combine data from any number of Google Sheets workbooks, and other kinds of data sources (e.g. local Excel or CSV files).

If you change the structure of a database table, such as data types, column names, or the format of the primary key column, click Data→Refresh Table List/Metadata to have Ultorg detect the changes. To refresh data only, use Refresh Data (/Ctrl+R).

[The Refresh Table List/Metadata] action in the data source context menu.

More Details

Ultorg's Google Sheets integration is designed to permit concurrent edits by multiple users, provided that column definitions and primary key values remain stable. Blank or reordered rows may occur in some cases; this is normal.
The Apply Edits action will perform sanity checks on affected sheets before proceeding to make changes. This includes checking for the continued presence of relevant rows and columns, and preventing duplicate values in the primary key column.
Auto-incremented IDs may skip numbers that were previously assigned to now-deleted rows. This is by design. Very large gaps are avoided.
Editing of dates, times, and timestamps should work regardless of spreadsheet locale.
As before, the actual execution of database queries happens on your local machine, using Ultorg's bundled PostgreSQL engine. Interactions stay fast even on slow Internet connections. You can make queries and stage edits even while temporarily offline.

Other Improvements

Data Editing

Make the Fill Down action work in partially loaded groups and on primary key fields, when the cursor is on an inserted record.
Make checkbox fields (boolean/logical fields) easier to use when inserting new rows, by setting them to an explicitly unchecked (false) state by default.
Show the progress indicator while waiting for extracts to refresh after a data edit.

Miscellaneous

Add Open in Google Sheets and Open in Excel actions in the context menu that appears when you right-click a relevant data source.
Have Refresh Table List pick up table names changes from the data source.
For users on MacOS 14.4.0, suggest updating to MacOS 14.4.1 or later.
Improve various error messages and informational messages.
PostgreSQL: Make the SINGLE formula function work on UUID values.

Version 1.6.2 (2024-04-18)

Limited release for a subset of customers. All features below were later rolled into Ultorg 1.7.0.

Microsoft SQL Server

Make integrated Windows authentication work out-of-the-box (#14). This allows passwordless connections to Microsoft SQL Server via Active Directory or the local Windows user. Ultorg now bundles the necessary DLL file.
To connect to Microsoft SQL Server using integrated authentication, use a JDBC URL in the following form, with an empty username and password:
jdbc:sqlserver://localhost:1433;trustServerCertificate=true;integratedSecurity=true
(Use the actual host name of your database instead of localhost.)
Bump the SQL Server JDBC driver version to 12.6.1.
Switch the default transaction isolation level to READ COMMITTED, as higher levels are disallowed for certain table types.
Fix an error that occurred with table columns of the geography type.

PostgreSQL

Make integrated Windows authentication (SSPI) work out-of-the box. This allows passwordless connections to PostgreSQL via Active Directory, when this has been configured on the server. Ultorg now bundles the necessary DLL file and libraries.
To connect to an SSPI-enabled PostgreSQL server from Ultorg, enter your Windows username and an empty password. No special JDBC configuration is needed.
Bump the PostgreSQL JDBC driver version to 42.7.3.
Avoid an error on startup if the user adds a custom JDBC driver for PostgreSQL.

Visual Query System

Load a larger number of rows when the user first scrolls to the end of loaded results.
Adjust the filter that is proposed from Ctrl+F search on a JSON column.
Unsuppress previously canceled connection attempts when Refresh Data () is invoked.
Adjust extract selection rules for locally executed queries. When custom table extracts are present, prefer extracts with fewer columns, and filtered extracts over unfiltered extracts.
Properly display extract loading errors that affect the currently displayed perspective.
Improve logging for certain JDBC connection errors.

Other Improvements

Preview of data editing in Google Sheets. (Officially released in Ultorg 1.7.0.)
For ZIP file distributions, include the Ultorg version number in the root folder name.
Add some unadvertised alternative keyboard shortcuts.

Version 1.6.1 (2024-02-29)

Fix an UnsupportedOperation error that could appear when filter indictions grew too long.
Handle Ctrl+L to set a filter when Find is open with the cursor on a compact join value.
Smaller adjustments to functionality introduced in Ultorg 1.6.0.
Update runtime library versions.

Version 1.6.0 (2024-02-19)

Browser-Style “Find” Action (Ctrl+F)

The Find action (/Ctrl+F) lets you quickly search in the currently loaded perspective. To move the cursor forward or backward between matching cells, press Enter or Shift+Enter:

The Find action highlighting six cells with matching text, and showing a tooltip with available keyboard actions.

Matching characters are highlighted in yellow, while each cell with a match is highlighted in brown. Press Escape to close the Find bar and remove the highlights. Or press Up/Down/Tab/Shift+Tab to restore focus to the editor area while retaining highlights.

By default, all visible cells are included in the search, including column labels. To restrict the search to data values from specific columns (fields), select them first. Search highlights are still shown across all cells, but pressing Enter will move the cursor only within the selection:

The Find action invoked when a single table column has been previously selected.

In Ultorg, field selections can be done by holding down Ctrl and clicking the column label, or by pressing Ctrl+Space. (On MacOS, use the Command key instead of Ctrl.)

For cells containing numeric, date, or time values, the search term is matched against the actual text that is displayed in the cell, after formatting has been applied. For text values, the original text is searched without modification.

If the search term starts with a double quote, Ultorg will accept C-style backslash escape codes to represent special characters. For example, the search term "\n" (with quotes included) can be used to search for line breaks in text values:

A quoted string with an escape code used as a search term for the Find action, to find values containing newline characters.

When working with large datasets, there will often be data in the database that is not immediately available for searching via Ctrl+F, as Ultorg loads only enough data to fill the window by default. Rather than scrolling through thousands of rows, you can press Ctrl+L (⌘L on MacOS) to quickly set a filter based on the current search term:

Using the Ctrl+L shortcut from the Find action to set a filter on a text field.

The filter is set on the field that the cell cursor is currently located on. For text fields, a case-insensitive “contains” filter is set, with the same search condition as is used when matching cells with Ctrl+F. For numeric fields, an exact match is required. For date and timestamp fields, you can type a partial date to search for a range of dates or times, e.g. 2023-11 to find all timestamps in November of 2023:

Using the Ctrl+L shortcut from the Find action to set a range filter on a timestamp field.

Both quoted strings and date ranges are also available from the Filter toolbox. A more limited Ctrl+F search is also available in the Folders and Fields sidebars.

“Open Details” Action

The Edit→Open Link/Open Details action () provides a fast way to jump from a table row or foreign key value in one perspective to a “detail view” in a different perspective.

Place the cell cursor on a table row or foreign key value of interest, and press Alt+Enter (Option+Enter on MacOS). Ultorg will ask for a perspective to use as a detail view for the data table in question. When you click OK, Ultorg will open the detail view and apply a filter such that the originally selected record becomes visible:

Using the Open Details action to open a detail view corresponding to a selected row in the database.

The default detail view shows all fields in the data table using a single-column form layout. Joins and formulas from the original perspective may also be included. An open detail view will be reused automatically until closed.

If desired, you can customize the detail view using any of Ultorg's regular query and formatting actions. For example, you can include data from related tables via Query→Fields & Joins, or switch to a multi-column form layout using Format→Set Auto-Layout Type. Then click File→Save Perspective to save the modified perspective.

To qualify as a detail view for a given data table, a saved perspective must use said table as its data source at the root level, and must use a non-tabular auto-layout type.

It is often useful to view data side-by-side, with one table view and one detail view:

Two perspectives shown side-by-side in a split view, with instructions.

To set up a split pane like this, drag the detail view's tab to the side of the screen. You can also move a tab to a separate window, by right-clicking the tab and clicking Float.

If the currently selected cell contains a URL, the Alt+Enter shortcut will open the link in an external web browser, as in previous Ultorg versions.

Improved “Fill Down” Action

The Edit→Fill Down action (Ctrl+D) will now work when the cursor is on a deleted or inserted record. For deleted records, records below the cursor are marked for deletion as well:

The Fill Down action, when invoked while the cursor is in a record that has been marked for deletion.

For inserted records, the value under the cursor is filled down first to the end of the inserted records, and then to remaining records in the group:

The Fill Down action, when invoked on a cell in an inserted record.

The Apply Edits operation now avoids unnecessary network delays when there are many edits.

Checkbox Fields

Fields containing true/false values, also known as “boolean” or “logical” fields, now show their values as checkboxes:

True/false values in a database table being displayed and edited as checkboxes.

In Data Editing mode, you can click the checkbox to toggle it, or select it and press Space.

If the field supports a null value, separate from false, you can use the Delete key to produce it, as for other data types. Null values are indicated by the absence of a checkbox.

For Ctrl+F search purposes, a checkbox cell matches the word true or false.

Formula Bar Improvements

You can now drag the formula bar to enlarge it, like in Excel or Google Sheets:

Dragging to expand the formula bar, with instructions.

Clicking the formula bar icon will open the cell editor and auto-complete dropdown if available.

When empty, the formula bar will now show an explanatory message in grey. A cell with a null value will show the message “null value” or, for date/time fields while in Data Editing mode, the expected format for data entry:

Greyed-out indication text shown in the formula bar, for example to show the expected format of entered date values.

Non-standard data types are now indicated with their name from the external data source:

The column type indicator showing the backend type name of an otherwise unrecognized data type.

Retrieve More Rows

During interactive queries, Ultorg will normally limit the number of rows retrieved, and increase the limit only if the user scrolls to the bottom of the currently displayed result.

This behavior can now be overridden, by opening the Format popup on the root label of the displayed result (the topmost bold heading) and changing the Retrieved Row Count property:

Retrieving a large number of rows may slow down query interactions, but may be useful for long-running queries. If you have a long-running query that returns a flat table of results, you may consider using File→Create Extract to create a snapshot of the result instead.

Performance Improvements

This release includes various changes intended to improve interactive query performance.

Use fewer SQL queries to retrieve data for each Ultorg perspective.
Eliminate one case where an aggregate calculation was unnecessarily duplicated.
Provide JDBC drivers with a suggested fetch size in more cases.
Avoid triggering a new query when the user merely hides a field that is already showing on the screen, or when the same field is subsequently reintroduced. (With some exceptions.)
Avoid triggering a new query when a formula is inserted but has not yet been defined.
Eliminate a brief UI lag when loading perspective data with many rows.
Oracle: Disable the workaround for Oracle bug 6653652 on unaffected versions.
(2024-02-07: This was reported to fix slow table list retrieval.)
Oracle: Add more logging around table list retrieval.

(If you notice significant improvements or degradations in query performance from this release, we would love to hear about it.)

Filter Improvements

A summary of filter state is now shown in the Fields sidebar:

A summary of filter state being displayed next to field icons in the Fields sidebar.

When filtering on dates or timestamps, you can enter e.g. “30” in the search field as a shortcut for the date 30 days in the past:

A filter set this way will still be defined with respect to the specific constant date that is shown in the list. To create filters or formulas that change depending on the actual time that the query is executed, you can use the now or today functions, which are added in this release:

The NOW formula function, with open online documentation.

In the example above, a formula is added that calculates the number of days since each log event occurred. The user could then set a filter on the calculated field e.g. to show log events that occurred in the last 14 days.