To be editable tables must have an index and they cannot be read-only. If a table has a light gray background in all of the columns it is not editable, usually because it does not have an index.
Manifold automatically creates an index when bringing tables into a project from most data sources so it is unusual to encounter a table without an index. Adding an index is easy: See the Adding an Index to a Table topic for the step by step procedure. See the Notes at the end of this topic for other atypical situations when we might be surprised to see that a table is not editable.
Please review the Getting Started, User Interface Basics, Tables and Table Windows and Big Data topics before proceeding with this topic.
We can edit tables using dialogs and menu commands to do things like change the schema of a table, use SQL queries to alter the structure of tables and the data within them or we can edit tables interactively using mouse and keyboard within a table window. This topic discusses interactive editing within a table window. Interactive editing of tables usually only makes sense for relatively small tables as it is neither convenient nor realistic to edit tables by hand when they contain millions of records.
Table windows allow us to look into a table. They provide interactive browsing capability by fetching data from the table to display. When we interactively edit data shown in a table window, such as by double-clicking into a cell and changing the value, we are working with the table window as an intermediary to the table. The table window allows us to make changes in the window's data, one record at a time, before we decide to commit those changes to the table.
Tables use background colors to call out
the status of different fields and records. These visual cues
also help editing.
Gray background indicates fields that are read-only. When an entire table is read-only, because it does not have an index or because it comes from a read-only data source, all of the fields in the table will have gray background color.
Red indicates selected records.
Blue color indicates records that are being previewed by a dialog such as the Select dialog.
Blended color indicates selected records that are also being previewed.
Blue-gray cell background color within a single record that has been edited indicates temporary edits that have been made but not yet committed.
Any changes we make to a record within a table window are temporary and will be shown with blue-gray background color until we command the table window, with a Ctrl-Enter or a click on the row handle, to commit those changes by dispatching them to the table. Cells shown with blue-gray background color have changes that can be abandoned. Cells with normal, white background that have been edited have had their changes already committed to the table.
Double-click any cell in the record to open it for editing. A triangle pointer symbol will appear in the record's row handle to indicate that record is being edited.
Edit the cell as desired. Press Esc to cancel editing or press Enter to finish editing the cell. The cell background will change to blue-gray to indicate it has been edited.
Edit any other cells in the record as desired, pressing Enter to finish editing each cell.
To commit the edits for the record, click the triangle pointer symbol in the record's row handle or press Ctrl-Enter.
To abandon the edits, click on the row handle of any other record, double-click into the cell of any other record or press Ctrl-Backspace. We can also right-click onto the record we have temporarily edited and choose Undo Changes.
Only one record at a time may be edited interactively. Edits made to a record are temporary until they are committed. Seeing a triangle pointer symbol in a record's row handle or seeing blue-gray color in a record's cells tells us that edits have been made in that record which have not yet been committed. We can commit the edits by pressing Ctrl-Enter or by clicking the triangle pointer symbol in the record's row handle.
If we want to abandon
the edits we can right-click onto
the record and choose Undo Changes,
we can press Ctrl-Backspace, we
can click on a different
record's row handle or we can begin editing a different record by double-clicking into a cell,
in which case all uncommitted prior changes will be discarded and the
previously edited record will revert to the original, unedited content.
As noted above, multiple fields in the same record can be edited before all of the edits are committed at the same time. This makes it possible to edit records where more than one field must be edited at the same time in order to satisfy constraints. For example, a table of records representing travel information may require that the departure date for a hotel reservation be later in time than the arrival date for that reservation.
See the Example: Editing Records in a Table topic for a step by step example of editing records.
NULLs
To manually set the value of a cell to NULL, edit the cell to enter any value and press Enter. Right-click onto the cell and choose Set to NULL.
If a table supports adding records the table window will display an editable, blank, template record at the end of the table, marked with an asterisk * character in the row handle. To add a new record we edit the blank record, double-clicking into one of the record's cells to begin editing.
The asterisk * character in the row handle changes to a triangle icon in the row handle when we make edits to the blank, template record. We commit changes to the edited template record by clicking the triangle icon or by pressing Ctrl-Enter.
Committing the changes will add a new record to the table, a new value for mfd_id will automatically appear in that field for the new record if that field is used, and a new, blank, template record marked with an asterisk * in the row handle will appear at the end of the table.
See the Example: Adding Records to a Table topic for a step by step example of adding records.
We can also insert records by pasting records that have been copied or cut from another table window to the Windows clipboard. Pasting tables between tables assumes the same field names. Values for read-only field names or computed fields are ignored when pasting.
Pasting with Ctrl-V will not paste records that have the same mfd_id value as existing records. Pasting with Shift-Ctrl-V will ignore mfd_id values in the pasted records and will automatically create new mfd_id values, thus avoiding any collisions in mfd_id values with existing records.
We can also create a new table in the project pane by pasting records copied or cut from a table window.
If a table supports adding new fields we can do so right from a table window with no need to open the table's schema via Edit - Schema. Tables that allow adding fields will be displayed with a blank, asterisk * field at the right of the table. Clicking on the asterisk * column handle will launch the New Field dialog to allow us to add a field. We can also launch the New Field dialog from the context menu launched by right-clicking the column head of an existing field.
See the Example: Add a Computed Field to a Table topic for a step by step example of adding a new field.
By default the New Field dialog lets us specify the name and the data type of the new field. We should, of course, set the field type to something sensible for the field we intend to add. For example, if want to add a field with names of people and we accidentally choose a type of tile instead of the nearby nvarchar when we try to populate the field with text the result will be NULLs.
Checking the Set field values box opens up an expression builder, somewhat similar to the query builder in the Command Window, that allows us to create an expression which will be automatically computed to set the value of that field. Clicking Add Field will add the field to the table and then will populate that field for each record with values computed according to the expression.
When using Set field values to create a computed field we see a live preview of the expression in the * field in the table window. As we edit the expression Manifold will automatically compute it for the records shown in the table window and will display the resulting values. The table window stays fully operational: the display can be scrolled, columns can be dragged left and right to be repositioned, we can edit existing records or add new records and so on. When we press the Add Field button the computed values are committed to the field.
Manifold uses a background thread to compute the preview. If computations for the preview are intensive, the previewed values for records that are queued for computation will be shown with gray background, switching to normal background color when the values are computed.
By default, checking the Set field values to add field values based on an expression results in static values being added to that field for each record. If we thereafter change one of the other field values that was used in the expression the value that was originally set will not change. If we want automatic updates based on the expression we use the Add Computed Field button to add a computed field to the table. Computed fields will on the fly calculate the specified expression to dynamically compute the value in the computed field.
Table windows also allow us to edit the values for existing fields by choosing the Transform command in the context menu raised by right-clicking on the column head of an existing field.
Manifold allows us to edit as many cells as we want in the same record and to then abandon those edits. That provides a level of "undo" not available in all database systems.
However, once an edit to a record has been committed, the edit is permanent and cannot be undone. Like almost all database systems there is no "undo" after edits to records have been committed in Manifold.
If we want to browse data sets without any risk of making permanent edits we can do so by checking the Open as read-only option box in the New Data Source dialog to add them as a read-only data source.
How to tell why a table is not editable? - Gray background in all of the fields of a table indicates all fields are read-only. There are three reasons why all of the fields may be read-only:
No index in the table - The Info pane or the Schema dialog will show if a table has an index. It is easy to add an index to a table.
Read-only data source - A data source that has been created read-only will have a small black lock symbol on the yellow cylinder icon for the data source.
All fields are computed fields - Computed fields are read-only. It would be rare to encounter a table where all fields are computed fields. We can tell if a field is a computed field by hovering the mouse above the column head to see what type it is. If it is a computed field the expression used will appear in the tooltip. We can also launch the Edit-Schema dialog, click on the field in the Schema dialog and see what Expression is reported for the field.
Data sources might be read-only because they were created with the Open as read-only option box checked in the New Data Source dialog. However, some tables brought into Manifold from an external data source may be read-only because of access permissions or other security controls on the database in use, as a result of permissions or policies in the operating system or limitations of the connection to a remote database.
How to edit more than one record at a time? - To edit more than one record at a time we can use queries.
Examples - Do not miss! Browse through the many examples for step by step tutorials.
Example: Closing without Saving - An example that shows how File - Close without saving the project can affect local tables and components differently from those saved already into a data source, such as an .mdb file database.
Example: Editing Records in a Table
Example: Adding Records to a Table
Example: Add a Computed Field to a Table