Changes to an Ultorg perspective are normally read-only; they do not cause changes in the underlying data source. Using the Data Editing mode (), you can stage edits to the data, review the edits visually, and then write the edits back to the data source.

Ultorg allows editing across joins and one-to-many relationships. See Insert and Delete and Edit from Dropdown for more information.

Data editing is supported for connections to relational databases, to Google Sheets, and to Microsoft Excel files. You can also mix read-only data sources with supplemental data that you provide yourself in a spreadsheet, and edit the latter from inside your Ultorg perspective.

Data Editing Mode

“Has this happened to you? You open an Excel file and start typing and nothing happens, and then you select a cell and you can start typing. Where did all of that initial text go? Well, sometimes it got entered into some random cell, to be discovered later during data analysis.” — Broman and Woo, Data Organization in Spreadsheets.

Unlike a spreadsheet, Ultorg enforces a clear separation between changes to data and changes to a query or the presentation of its result. Typing to edit data will, by default, have no effect except to offer to enable the data editing mode first:

The data editing mode can be entered with the indicated keyboard shortcut (Ctrl+Shift+E), or from the icon in the toolbar (). Once data editing mode is enabled, a status bar is shown:

You can now make edits in the data, as you would in a spreadsheet. Move the cursor to a cell, and start typing. The status bar will show your changes as pending until you explicitly apply them with Apply Edits (Ctrl+Shift+S, or the button in the status bar).

To insert and delete rows, use the Insert Record () and Delete/Revert Record () actions in the toolbar (Ctrl+Shift+Plus and Ctrl+Shift+Minus). See this page for more details.

Undo and Redo

You can Undo (, Ctrl+Z/⌘Z) and Redo (, Ctrl+Y/⌘Y) pending data edits.

To see the old value under a pending edit, you can temporarily revert the edit with Ctrl+Shift+Minus, and then undo the revert with Ctrl+Z/⌘Z.

For simple edits, Undo works even after the edit has been committed to the data source with Apply Edits. In this case, Ultorg creates a new pending edit that would revert the prior change.

Undo-after-commit works for edits in existing rows, including those made with Fill Down or with the dropdown list. Insert and Delete are undoable only until Apply Edits is invoked.

Perspectives and Data Edits

In a spreadsheet, information is stored exactly where you enter it. An Ultorg perspective, by contrast, does not store data. Rather, it represents a query that pulls data from external sources, for display on your screen. The data editing system is designed accordingly.

Effects on Other Perspectives

Pending data edits are displayed in the current perspective only. Once you press Apply Edits, the changes are committed to the external data source. This may in turn affect the query results that are shown in other perspectives, in addition to the current one.

For example, we may have one perspective that shows instructor assignments for one particular university course, and another that shows all instructors ordered by total assigned teaching time. Deleting an instructor's assignments in the first perspective may affect that instructor's position in the second perspective:

After edits are committed, the Apply Edits action automatically refreshes all open perspectives that include data from the affected data tables.

If other users, or external systems, are making simultaneous changes to the same data source, you can use Refresh Data (, Ctrl+R/⌘R) to refresh the current perspective manually. Pending changes will survive the refresh.

Effects on the Current Perspective

Some data edits can, once applied, cause rows to move around or disappear. This could be the case, for instance, if the field you are editing is sorted or filtered on.

To keep data in place during editing, the effects of edits on the current perspective's query results are computed only after Apply Edits has been invoked.

For example, the following perspective shows a list of courses sorted by Capacity and filtered to include only courses with a final exam. When a course's Capacity is edited, its position in the list changes. When the course's Exam Type is changed, the course is excluded by the filter.

Note that the result of formulas are also displayed with respect to data as it exists before any currently pending edits are applied.

Changing the Current Perspective

You can make changes to a perspective that has pending edits, e.g. to show additional fields.

Some changes may cause existing pending edits to no longer be visible, e.g. if a row with pending edits is filtered out. All pending edits must be visible before edits can be applied, or before additional edits can be made.

For edits to existing table rows, the row's primary key value defines the target of the pending edit. The primary key is the column(s) that the data source has designated to contain stable row identifiers for that table (e.g. customer IDs for a “Customers” table).

The use of primary key values to identify edited rows means that edits will be applied to the correct rows even if changes to the perspective query, or external changes to the database, cause rows to move around in the visual layout.

Error Handling

There are some restrictions on editing operations, and the context in which they may be performed. Most errors are indicated with a tooltip that appears next to the cell cursor:

In the example above, the user attempted to change a value in an existing row, but the value was part of the row's primary key, which is typically kept read-only. In this case the user could delete the existing row and insert a new row with the desired value instead.

Other kinds of errors appear only once the Apply Edits action is invoked. These include data validation errors that may be emitted by the external data source. Such errors are common; you can examine them in the dialog box that opens in this case:

On relational databases, Apply Edits performs all operations in a single database transaction. In case of errors, the entire transaction is rolled back, before any partial changes can be observed by other users. You can correct the error and try again.

Input Format for Edited Values

As in a spreadsheet, individual data values can be edited either directly in their cell or in the formula bar. The format required depends on the data type of the field that is being edited.

Text Values

For Text fields, values are entered as-is. Line breaks, if needed, can be typed with Alt+Enter, like in Excel or Google Sheets. Quoted strings in the JSON style are also accepted.

If a text value contains certain undisplayable characters, or extra trailing whitespace, the cell editor will show it in the quoted format by default (e.g. "Tab\tAnd an Extra Line Break\n").

Numeric Values

For numeric fields, values are entered with regular digits and no currency symbols (e.g. 12345), possibly with decimals (e.g. 12.49). Exponential notation (e.g. 1.572e6) and percentages (e.g. 5.2% for 0.052) are also accepted.

Either comma or period is accepted as a decimal separator, depending on the regional settings of your operating system.

Numbers in the formula bar are always shown with period as the decimal separator. (The latter may change in a future Ultorg version.) Regional settings are still respected for input purposes, as well as when formatting numbers in the main perspective area.

You can enter numbers of arbitrary decimal precision, to whatever number of significant digits is supported by the underlying data table. This is in contrast with e.g. Microsoft Excel, which rounds all numbers to 15 significant digits.

Date and Time Values

For date, time, and timestamp values, the required format for data entry (ISO 8601) is shown in the formula bar in grey text:

Null Values and Empty Strings

Many table columns support null values, which indicate missing or non-applicable data. These are similar to empty cells in a spreadsheet. In Ultorg, null values are indicated by the grey text “(null value)” in the formula bar, or an empty input when the value is actually being edited.

Alternatively, a field of the Text type can contain an empty string which serves a similar purpose. In cases where a table column can contain either null values or empty strings, Ultorg will indicate the latter with two double quotes in the formula bar ("").

You can press the Delete key to set the selected cell to a null value. For text fields that do not permit null values, the value will be set to an empty string instead.

Logical (Checkbox) Values

Fields of the Logical type, also known as a “boolean” type, contain either the value true or false. In the perspective layout, such values display 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. Pressing Delete again will restore the value of false.

Compact Join Values

For cells displaying a compact join, the original referencing ID value is shown in the formula bar, rather than the display fields from the referenced table. During data editing, you can usually select the reference from a dropdown list, rather than entering an ID value directly.