ORDS How-To 06: View and edit data

1. About this guide

This document is one of a series of How-To guides for the Online Research Database Service (ORDS). It will tell you how to view and edit data within the ORDS system.

Contents

1.1. Pre-requisites

This How-To assumes that you have already registered to use the ORDS, and have either created or been given access to at least one project containing at least one database.

If this is not the case, please see the earlier guides in this series. These can be accessed via the main ORDS documentation page.

2. Viewing data

All ORDS project members can view the data in ORDS databases. To do this, you will need to go to the database page for the database you wish to work with.

To reach the database page, log in to the ORDS via https://app.ords.ox.ac.uk, You will see a list of your ORDS projects. Click the name of the project which contains the relevant database.

You will be taken to the project page, where you will see a list of all the databases that project contains. Click the database name to go to the database page.

Towards the bottom of the database page, you will see three panes – for Main database version information, Milestone database version information, and Test database version information. The options available in each pane will vary depending on which versions you currently have. For further information on creating and working with database versions, see How to manage databases and database versions.

To view data in the main database version, click the View, edit, and query data button in the Main database version information pane. (If the database has more than one version, you will also see a View, edit, and query data button in the respective database version information pane(s).)

Database version information panes

You will be taken to the View, Edit, and Query Data page. On the left-hand side, you will see a list of the tables in the database.

View, Edit, and Query Data page

Click the name of a table to view its contents.

Editing page

Beneath the table, you will see the filtering interface - see section 3. below for more details.

If the database table currently contains no records, you will see just the table description and the Add new record button (and below these, the filtering interface). See section 4. below for details of how to add records.

Editing page - no records

2.1. Sorting records

To sort the records by the contents of a specific field, click the field name. A small arrow will appear beside it. This will initially point upwards, indicating that the records are sorted in ascending order.

Editing page - records sorted by author_name

Clicking the same field name again reverses the direction of the sorting. The arrow will also invert.

Editing page - records sorted by author_name, order reversed

2.2. Resizing fields

By default, the ORDS will display all fields in a table with the same standard width. To resize a field, hover the mouse pointer over the border between two field names in the table header row. The mouse pointer will change into a double arrow symbol.

Editing page, showing a column which is too narrow to view all its content

Click and drag the border until the field is of the desired width.

Editing page, with column width increased to show full contents

2.3. Navigating large tables

If the table contains a large number of records, these will be divided into pages. A hundred records are displayed on each page.

Towards the top of the page, you will see a summary of which records you are currently viewing, the total number of records in the table, and the number of pages.

Editing page showing large dataset

You can move between pages using the Previous and Next buttons. You can also skip to a specific record by typing the record number into the Go To Record: box and then pressing Enter.

2.4. Viewing data in linked fields

If your database contains multiple tables with relationships between them, you can choose which data is displayed in linked fields on the editing page.

The database table shown below is a list of articles from a bibliographic database. Each record includes information about the article’s author and the journal in which the article was published. This information takes the form of links to other tables in the database – tblauthors for the author information, and tbljournals for the journal information.

Editing page showing linked fields - cropped version

The default option is for the primary key fields of the linked tables to be displayed. In this case, these simply contain an ID number, which is not very informative.

To change this, click the drop-down list at the top of the column, and select the field from the linked table that you would like to view.

Editing page, showing linked field drop-down list - cropped version

The table will now update to display the data from the selected field.

Editing page, with linked field showing contents of selected field

Repeat these steps for all columns containing links to other tables.

Editing page, with both linked fields showing contents of selected fields

2.5. Comments on tables and fields

Comments can be added to ORDS tables and fields using the schema designer - see sections 3.3. and 3.4.2. of How to Build and Structure Databases for more details. Comments can be used to add reminders for yourself or explanations and instructions for other users of the database. A comment on a table might, for example, explain where the data came from, or describe how the data has been manipulated or standardized. Comments on fields can be used to record units of measurement, to note the form in which data should be entered (that names should be written as Surname, Forename, for example), to indicate that the field is mandatory, and so forth.

Comments can only be added and edited via the schema designer, but they can be viewed on the data editing page.

2.5.1. Viewing comments on tables

Comments on tables are displayed immediately below the table itself.

Editing page with comment on table highlighted

2.5.2. Viewing comments on fields

If a comment has been added to a field, a blue question mark symbol will appear beside the field name.

Editing page with field comment icons highlighted

To view the comment, hover the mouse pointer over the question mark symbol.

Editing page detail showing comment on field

3. Filtering data

The filtering function allows you to see a specific subset of records in a table. This can be particularly helpful when working with large data collections.

3.1. Applying a single filter

The filtering interface appears below the data table on the editing page. The default version consists of two drop-down lists and a free text box.

Default filtering interface

The first drop-down list allows you to select the field you wish to filter on. We'll select the title field for this table.

Filtering interface showing field drop-down

The second allows you to select the operator. The precise list of operators varies according to the data type of the field being filtered, but will be taken from the following:

is For exact matches.
is not For excluding specific entries.
is NULL For locating records where the specified field has the null value (that is, is empty).
like

For wildcard searches. Two wildcards can be used:

_ (underscore) can be used to replace a single character - for example, wom_n will find 'woman', 'women', etc.

% (percentage sign) replaces any number of characters - for example, Jo% will find 'Joe', 'John', 'Josephine', 'Joshua', etc.

contains For partial matches - for example, contains and will find 'hand', 'band', 'clandestine', 'mice and rats', etc.
greater than For numeric fields - finds all values larger than the specified number.
less than For numeric fields - finds all values smaller than the specified number.
before For date and time fields - finds all values earlier than a specified date or time.
after For date and time fields - finds all values later than a specified date or time.

We'll use the contains operator.

Filtering interface showing operator drop-down list

The filter criteria go in the free text box. We'll enter Church, to find all those records whose title field contains this string of letters. (Note that the filter tool is case sensitive: Church won't produce the same results as church or CHURCH.)

Filtering interface with all three fields completed

To see the filtered results, click Apply filter.

Filtered results

To remove the filter and see the full list of results again, click Clear filter.

Cleared filter - full list of results

3.1.1. Adding filter criteria for fields with special data types

The filtering interface will normally contain a free text box in which you can type your filter criteria. However, depending on the data type of the field on which you are filtering, this will sometimes change.

For numeric fields (Integer and Decimal, for example), you can either type a number into the box, or use the number selection tool which appears when you click in the box. The selection tool consists of two small arrows: click the up arrow to increase the value in the box, and the down arrow to decrease it.

Filter interface, showing number selection tool

For Date fields, you can either type a date into the box, or use the pop-up calendar which appears.

Filtering interface, showing date selection tool

(You may also sometimes see this calendar tool when filtering on Time fields; we are aware of this issue and are working to fix it.)

For Boolean fields, the free text box will be replaced by a drop-down list, allowing you to select whether the value of the Boolean field should be True (checked) or False (unchecked).

Filtering interface, showing Boolean drop-down list

3.2. Applying multiple combined filters

It is possible to apply multiple filters to the same table simultaneously. To add a line to the filtering interface, click the blue plus symbol button at the right-hand side.

Filtering interface with two lines

Note that an additional drop-down list has appeared at the left-hand side of the filtering interface. This allows you to specify whether the search criteria should be linked by an AND or an OR.

Let's create a combined filter to find all the articles whose title contains Church, and whose publication year is before 1990. (As the year field is an Integer rather than a Date field, we use the operator less than rather than before.)

Filtering interface with two lines, completed with filter criteria

Applying this filter gives us just two results.

Filtered results for a combined filter

Additional lines can be added to the filtering interface by clicking the blue plus symbols. The new line will appear immediately underneath the line whose plus symbol was clicked.

When the filtering interface contains more than two lines, the lines will be indented to indicate how the AND and OR operators apply.

This filter, for example, would find records for articles whose publication year is after 1990 but before 2000, and whose title field also contains either Church or church, and where the volume number is 43.

Filtering interface, showing a combined filter consisting of several lines

This filter contains the same number of lines containing the same filter criteria, and the same operators (three ANDs and one OR), but arranged differently.

Filtering interface, showing a combined filter consisting of several lines - alternative version

Consequently, this would produce rather different results - it would still find records for articles whose publication year is after 1990 but before 2000, but the results would now include all of those whose title contained Church, plus those whose title contained church and which had the volume number 43.

In the table shown above, the first of these two filters finds no results at all, whereas the second finds one.

Results of applying combined filter with several lines

The title of this record contains Church, but the volume number is 31, not 43 - so it would have been excluded by the first of the two filters.

To remove a line from the filtering interface, click the blue minus symbol at its right-hand end.

Filtering interface with minus sign highlighted

3.3. Filtering linked fields

It is possible to filter records using the content of linked fields, but this requires a slightly different approach.

The field selection drop-down list contains entries for linked fields - so for this table, which has two linked fields, we can see that both of them - author and journal - appear in the list.

Filtering interface showing field selection drop-down list

However, if we select one of these, the options we get in the operator drop-down list might not be quite what we'd expect.

Filtering interface showing the operator drop-down list

The operators here - is, is not, is NULL, greater than, and less than - are the ones the ORDS offers for a numeric field. This is because the ORDS is filtering on the primary key field of the linked-to table - which in this case, is an Integer field.

To filter on the content of other fields from the linked-to table, we need to first select the table, and then the desired field. At the top of the field selection drop-down list, you will see a list of the other tables that are linked to from this one - in this case, tbljournals and tblauthors. To distinguish table names from ordinary field names, each table name is followed by an ellipsis.

Filtering interface, showing field selection drop-down list, with tables highlighted

If we select one of the tables, an extra drop-down list is added to the interface.

Filtering interface, showing extra drop-down list for selecting fields from linked-to table

This contains a list of all fields from the linked-to table, allowing us to select the one we'd like to filter on. We'll select the author_name field.

Filtering interface with extra drop-down list, with field selected

We can now select an operator, and add filter criteria to the final box - just as we would when applying any other filter.

Filtering interface, showing extra drop-down list, completed with filter criteria

We can now apply the filter, and see the results. This filter shows us all records in tblarticles whose author_name field contains French. (Note: after applying a filter, you may sometimes find you need to reselect the fields from linked-to tables that you wish to view - see section 2.4. above for details.)

Editing page - results of applying a filter on a linked field

Filters on linked fields can be included in combined filters, as described in section 3.2. above.

Note: if a table contains two or more linked fields which link to the same table, you may find filtering on linked fields does not work as expected. We are aware of this issue, and are working to fix it as soon as possible.

4. Adding new records

If you are the project owner, a project administrator, or a contributor, you can add new records to database tables.

Open the table to which you wish to add a record. Click the Add new record button, which is located towards the bottom of the window.

This will take you to the Add New Record form.

4.1. The Add New Record form

On the right-hand side of the form, you will see a list of the fields in the table.

Below the field name you will see a small capitalized label indicating the field’s data type. This may restrict the sort of data which the field can contain. If you need to change the field type, you can do this via the schema design page – see the earlier guide in this series, How to build and structure databases, for more details, and for a full list of data types.

Add New Record form

There are four main types of fields: auto increment fields, standard fields, fields with special data types, and linked fields.

4.1.1. Auto increment fields

These are completed automatically by the ORDS system. Auto increment fields will be coloured pale grey, and will contain the words [auto increment]. Auto increment fields cannot be edited.

Add New Record form - auto increment field

Auto increment fields always have the data type Integer. The most common type of auto increment field is an ID or index field that is being used as the primary key for that table. The ORDS will automatically add the next available number, ensuring that the primary key value for each record is unique.

4.1.2. Standard fields

Standard fields appear as free text boxes. This table contains three standard fields: Name, Age, and Height_in_m.

Add New Record form - standard fields

All data entered needs to conform to the field's data type. If it doesn't, the ORDS will prevent the record from being saved, and instead will display an error message.

Note: fields which have the data type Text in the schema designer will erroneously display as having the data type Varchar in the Add New Record form. This is because the software underlying the ORDS does not distinguish between a Text field and a Varchar field with no size limit - there is no difference in terms of the sort of data that may be entered in these types of field. We are aware of this issue, and hope to be able to improve the labelling in the Add New Record form.

4.1.3. Fields with special data types

4.1.3.1. Date and time fields

If the field data type is Date, Time, or Timestamp, the field will contain some pale grey text which indicates the format in which the data must be entered. Dates must be in the YYYY-MM-DD format, and times in the hh:mm:ss format.

Add New Record form - date and time fields

At the right-hand end of the field, you will see a selection tool which can be used to complete these fields. This consists of a small central square, surrounded by four arrows.

  • Click the central square to complete the field with the current date or time.
  • Click the left and right arrows to move between sub-fields (i.e. year, month, and date in Date fields; hours, minutes, and seconds in Time fields; all of these in Timestamp fields).
  • Click the up and down arrows to alter the entry in the sub-field currently selected.

You can also complete these fields using the keyboard. Use the arrow keys, Tab and Shift+Tab, or the mouse (or other selection device) to move between sub-fields.

4.1.3.2. Boolean fields

Fields with the Boolean data type will be displayed as check boxes.

Add New Record form - Boolean field

To check the box, click it.

Add New Record form - Boolean field (checked)

4.1.4. Linked fields

Linked fields display information taken from a record in another table in the same ORDS database. The name of the table linked to is displayed under the field name.

In this example, the Place_of_origin field is linked to the table called tbl_places.

Add New Record form - linked field

You will see two drop-down lists. The left-hand list allows you to select a field from the linked-to table. The contents of this field are displayed in the right-hand list, allowing you to select the appropriate record.

For example, the left-hand drop-down list is currently set to display the id field from tbl_places. This means that if we view the entries in the right-hand drop-down list, we will see the contents of this field. In this case, this merely gives a list of record ID numbers.

Add New Record form - linked field, showing right-hand drop-down list

It will be easier to find the record we want if the list displays the contents of a different field from tbl_places. Let's use the left-hand drop-down list to select the Place name field from tbl_places.

Add New Record form - linked field, showing left-hand drop-down list

The right-hand drop-down list will now display the contents of the Place name field. For longer lists of entries, the ORDS will provide a search box to make it easier to locate a specific entry.

Add New Record form - linked field, showing right-hand drop-down list with contents of Place Name field

We can complete the Place_of_origin field by clicking the appropriate entry from this list.

Add New Record form - linked field, completed

4.2. Saving the new record

Once you are happy with the contents of the form, save the new record by clicking either Save and add another or Save and return to table, as desired.

When the first record is added to a newly created table, you may sometimes find that the order of the fields is reversed in the Add New Record form. This is a known issue, and will be fixed as soon as possible. This problem should, however, only affect the first use of the Add New Record form: to display the fields in the correct order, click Save and return to table after adding the first record, then Add new record to return to the form.

At present, it is not possible to add more than a single record at a time to an ORDS database table via the user editing interface. However, if you need to do a bulk upload of data (for example, you have a large collection of additional records in a separate file, and would like to add these to an existing table), it may be possible for a member of the ORDS technical team to do this for you. Please contact the ORDS help desk by emailing ords@it.ox.ac.uk to discuss your requirements.

5. Editing data

If you are the project owner, a project administrator, or a contributor, you can also edit the contents of data tables.

To edit a table, you will first need open it for viewing. Details of how to do this are provided in section 2. above.

As with new records, the fields you may encounter come in four basic types: auto increment fields, standard fields, special fields, and linked fields. However, the content of auto increment fields cannot be edited.

5.1. Editing standard fields

To edit the contents of a standard field, click anywhere in the appropriate data cell. The cell border will be highlighted in blue, and an insertion point will appear there.

Editing page showing typo

Use the arrow keys to navigate within the cell, then make the desired changes. The cell background will turn blue, to indicate that it contains unsaved changes.

Editing page, showing corrected typo

To save, press Enter, or click Save changes. Once the data has been successfully saved, the cell background will revert to white. (If any fields have been resized, you may find you need to resize them again after saving.)

Editing page with corrected typo, after saving

If you wish, you can edit the contents of multiple cells before saving. However, it is good practice to save your work regularly.

A few important notes about editing data in ORDS tables:

  • If you leave the table without saving, your changes will be lost. It is therefore a good idea to get into the habit of saving immediately after making changes, and to quickly scan the table for any blue cells before exiting.
  • If the editing page is left inactive for a period of time, the ORDS may time out, and any unsaved changes will be lost. It is therefore also a good idea to ensure changes are saved before you step away from your computer, or if you switch windows to work on another task.
  • If you enter content which doesn’t match the field’s data type (for example, you try to put text or a decimal in an Integer field), the ORDS will not allow you to save the changes. Moreover, you will also lose any other unsaved changes - another reason that saving frequently is a good idea.
    • You can find out the data type of a particular field by looking at the Add New Record form (see section 4. above), or by viewing the database’s structure in the schema designer (see the earlier guide in this series, How to build and structure databases).

5.2. Editing fields with special data types

5.2.1. Date and time fields

Fields with special data types such as Date, Time, and Timestamp can be edited in much the same way as standard fields. However, the ORDS will restrict the format in which the data can be entered. Dates must be in the YYYY-MM-DD format, and times in the hh:mm:ss format.

To edit a field with one of these data types, begin by clicking the appropriate data cell. The cell border and one sub-field (i.e. the year, month, or day of a date, or the hours, minutes, or seconds of a time) will be highlighted in blue.

Editing page with date field highlighted

Navigate within the cell until the sub-field you wish to edit is highlighted, and then enter the new value. This can be done using the keyboard (use the left and right arrow keys, or Tab and Shift+Tab, to move between sub-fields), or the selection tool. The selection tool appears at the right-hand end of the field, and consists of a small central square surrounded by four arrows.

  • Click the central square to complete the field with the current date or time.
  • Click the left and right arrows to move between sub-fields.
  • Click the up and down arrows to alter the entry in the sub-field currently selected.

If the new data is typed, the whole cell background will turn blue (this will not happen if the selection tool is used).

Editing page showing edited date field

To save, press Enter or click Save changes.

5.2.2 Boolean fields

Fields with the Boolean data type will be displayed as check boxes.

Editing page showing Boolean fields as check boxes

Click a box to toggle between checked and unchecked.

To save, click Save changes. As with standard fields, it is important to ensure changes are saved before leaving the editing page.

5.3. Editing the contents of linked fields

Linked fields in ORDS tables display information taken from a record in another table in the same ORDS database. You can change the contents of a database cell to display data from a different record using the drop-down list.

For example, suppose the first record in the table below mistakenly attributes the article ‘Medieval Saints’ to Lucy Appleton, and that the author is actually P. G. Stacey.

Editing page, showing linked field

To correct this, click the downward-pointing arrow at the right-hand end of the cell to display a list of the other available records.

Editing page, showing linked field with drop-down list

Find the record you want in the list. For larger collections of records, the ORDS will provide a search box.

Click the desired option in the list to change the contents of the cell.

Editing page, showing linked field with revised content

Note that changes to linked fields will not be highlighted in blue, so it is a good idea to get into the habit of clicking Save changes immediately after making any edits.

If you wish to make a change to the content of the record that is being linked to, you will need to do this by going to the table that holds that record. For example, suppose that you wished to replace P. G. Stacey’s first initial with her full forename – Patricia. To do this, you would need to go to the table that holds the author details. You can see which table this is by looking at the top of the column – in this case, the table is called tblauthors.

Editing page - name of linked table highlighted

After ensuring that all changes have been saved, return to the View, Edit, and Query Data page by using the breadcrumb trail at the top of the screen.

Editing page with breadcrumb trail highlighted

On the View, Edit and Query Data page, click the name of the appropriate table to view it.

View, Edit, and Query Data page - tblauthors highlighted

Make the desired changes.

Editing page - showing edited data

Save your changes by pressing Enter or clicking Save changes, then use the breadcrumb trail to return to the View, Edit, and Query Data page. Reopen the original table – in this case, tblarticles. The revised version of the data will now be displayed.

Editing page - showing new content from linked table

If you wish to add a new item to a linked field's drop-down list, you will need to add a new record in the linked-to table. In this case, for example, to add someone new to the list of available options for the author field, you would add a new record to the tblauthors table. See section 4. above for details of how to add a new record.

6. Deleting records

Open the table which contains the record you wish to delete. Navigate to the record.

Each record has its own Delete button, located at its far right-hand end. (If the table has a lot of columns, you may need to use the horizontal scroll bar to see these.)

Editing page, with Delete buttons highlighted

Click the Delete button for the record you wish to remove.

You will be taken to a confirmation page. Please note that deleting a record cannot be undone.

If you are certain you wish to delete the record, click Confirm. You will be returned to the table, and should see a message confirming that the record has been successfully deleted. See section 6.1. below for an important exception, however.

To return to the table without deleting the record, click Cancel.

6.1. Deleting records from linked tables

If a record is linked to by records in other tables, the ORDS will not let you delete it. Before you can delete the record, you will first need to delete or edit all the other records that link to it.

For example, suppose that you wish to delete the record for Alice Smith from the tblauthors table.

Editing page, with record for Alice Smith highlighted

If you try to do this in the ordinary way, you’ll see an error message.

Unable to delete the record - error message

We can therefore deduce that at least one record in another table links to this one.

If we are familiar with the structure of the database, we may already know which table this is. However, if we don't know, we can easily identify the table in question by looking at the database structure in the schema designer. To do this, return to the database page by clicking Database Details in the editing page breadcrumb trail, then click View and edit database structure. (See the earlier guide How to build and structure databases for more information about using the schema designer.)

Bibliography database structure

This shows us that tblauthors is linked to by the author field in tblarticles. It's therefore in tblarticles that we need to look to find the records that link to the Alice Smith record.

To view the tblarticles table, click Exit to leave the schema designer, then click View, edit, and query data, then the name of the table in the table list.

Because the author field in tblarticles is a linked field, it will by default display the contents of the primary key field in the linked-to table. We can use the drop-down list to select another field - in this case, author_name will be most useful. (We can also use the journal drop-down list to see the journal name. See section 2.4. above for more information about viewing data in linked fields.)

tblarticles, with author drop-down list highlighted

Sure enough, this shows us that there are at least a couple of records in this table where Smith, Alice is given as the author.

tblarticles, with records featuring Alice Smith highlighted

While this remains the case, the ORDS will not allow the tblauthors record for Alice Smith to be deleted – as this would then leave gaps in the records in tblarticles. (This is an ORDS safety feature, designed to prevent inadvertent loss of data.)

One way of resolving this would be to delete the records in tblarticles that reference the Alice Smith record. A slightly less drastic option is to edit the records so they no longer refer to Alice. We could, for example, change the author field to the null value – meaning that no author is listed for these records.

First of all, we need to be sure we've located all the records which reference Alice. The easiest way to do this is to use the filtering interface. (See section 3. above for more information about filtering records.)

Filtering interface, set to show records where Smith, Alice is the author

This reveals that three records in the table have Smith, Alice as the author.

Editing page, filtered to show just records referencing Smith, Alice

We can now use the drop-down list in each record to change them to the null value.

Editing page - records that were referencing Smith, Alice edited to have the null value

If we save this change and then return to the tblauthors table, the ORDS will now allow us to delete the Alice Smith record, as it’s no longer referenced by other ORDS records.

7. ORDS datasets

As described above, data in the ORDS is stored in tables, which make up databases. However, users also have the option of creating datasets. In ORDS terminology, a dataset is a set of saved query results, which can be used to display a specific subset or combination of data from one or more tables. See How to view, create, and share datasets for more details.

8. What next?

You may want to have a look at some of the other guides in this series. For a full list, visit the ORDS documentation home page.

You can also find out more about the ORDS service by visiting the ORDS home page, http://ords.ox.ac.uk/. If you have specific queries, you can contact the ORDS help desk by emailing ords@it.ox.ac.uk.

 

Service area: 

Written by IT Services. Latest revision 11 January 2016