Sort records (and items in any table)
Sorting of records is best performed in Result set View in which records are listed in a table of two or more columns:
- Each column (with the exception of the first, which holds a checkbox) is a field in the record.
- Each row holds a single record.
- Each cell in a row can hold a single value. If a field can hold more than one value (author.name (au) for instance), only the first occurrence
If a field in the current record can have more than one value, we add an occurrence of the field for each value (e.g. a book can have multiple authors so we add an occurrence of the author.name (au) field for each author). An occurrence can be a member of a group of fields, and adding an occurrence of the field adds all members of the group at once. displays in a cell (more details here).
Tip: Although we demonstrate how to sort items (rows) in Result set View, the broad technique described here is the same for sorting items in all of the many tables you will come across in Collections.
Records can be sorted on the values in one or more columns, which can be in one of three states:
- Sort Ascending (e.g. A to Z, 1 to 9)
- Sort Descending (e.g. Z to A, 9 to 1)
- Unsorted
This is the default order for records returned by a search. Records are listed by their unique reference number (priref
A record's unique identifier (aka Record number). The Record number field is found on the Management details (or Notes and description) panel in Collections version 5.0 onwards. Alternatively, add the priref column to Result set View. The field tag for the priref field is %0.) in ascending order (e.g. 1 to 9).Tip: It is useful to include priref in Result set View. See Settings for details about adding a field (column) to this View.
Notes
- Although non-indexed fields can be added as columns in Result set View, it is not possible to sort records by values in non-indexed fields. To help identify which fields are indexed and which are non-indexed, they are clearly differentiated in the list of fields in Result set settings1.
- It is not possible to sort on long text fields such as Notes and Description even if they are indexed. These fields typically have a
Free textindex, which cannot be sorted. An exception is often the (documentation) Title field. - If you need to sort records by values in a field that is not included in the Result set View, it is possible to add (and remove) columns by selecting Settings in the Result set View toolbar.
- All records listed on every page in Result set View are sorted simultaneously, not just the records listed on the current page. In this example, all 103 records across every page will be sorted together:
Sort records by the values in one column
In this example, records are sorted by the values in creator:
- Click the column header once:
The records are sorted in ascending order by the values in the column. An arrow in the header indicates the direction of the sort:
- Click the column header and the records are sorted in descending order by the values in the column:
- Click again to return the records to their unsorted state.
Alternatively, (and depending on your version of Collections) you can use the sort options available from the three dot ellipsis in a column header. The current sort state is highlighted:
Sort records by the values in more than one column
Having sorted records on the values in one column, we can then sort those records on a second (third, fourth, etc.) column.
For example, we have sorted records by the values in creator and as a result all records for items by each creator are listed together:
We can now add a second sort so that records are then also sorted by title:
Note that the header not only includes an arrow indicating the direction of the sort (ascending in this case) but a number indicating the sort order (records are sorted by column 1 and then by column 2, and so on).
You can sort records by as many columns as you require.
Unexpected sort results
Some fields can hold more than one value. A book, for example, can have more than one author and in the record for the book each author's name will be recorded as a separate occurrence If a field in the current record can have more than one value, we add an occurrence of the field for each value (e.g. a book can have multiple authors so we add an occurrence of the author.name (au) field for each author). An occurrence can be a member of a group of fields, and adding an occurrence of the field adds all members of the group at once.:
As explained here however, only the first occurrence If a field in the current record can have more than one value, we add an occurrence of the field for each value (e.g. a book can have multiple authors so we add an occurrence of the author.name (au) field for each author). An occurrence can be a member of a group of fields, and adding an occurrence of the field adds all members of the group at once. in a field will display in a cell in Result set View.
When sorting records on the values in a column, records are sorted by every occurrence in the field not only the value we see.
This may affect:
Sort order
If you sort a field in which a record has more than one occurrence of a value, your records may not appear to sort as you might expect. Here we see records sorted in ascending order by values in the creator column. The record with priref A record's unique identifier (aka Record number). The Record number field is found on the Management details (or Notes and description) panel in Collections version 5.0 onwards. Alternatively, add the priref column to Result set View. The field tag for the priref field is %0. 63 appears to be out of place with Macleod listed before Bakst:
If we look at this record in Record details View, we see that there are two occurrences of creator, MacLeod and Bach:
Only the first occurrence If a field in the current record can have more than one value, we add an occurrence of the field for each value (e.g. a book can have multiple authors so we add an occurrence of the author.name (au) field for each author). An occurrence can be a member of a group of fields, and adding an occurrence of the field adds all members of the group at once. of a field displays in Result set View, but records are sorted by all the values in a field. As we see above, when an ascending sort is performed, this record is sorted by Bach, which obviously comes before Bakst.
When a descending sort is performed, this record will be sorted by MacLeod:
In this case the record with priref A record's unique identifier (aka Record number). The Record number field is found on the Management details (or Notes and description) panel in Collections version 5.0 onwards. Alternatively, add the priref column to Result set View. The field tag for the priref field is %0. 36 now appears to be sorted incorrectly, but it has been sorted by its second occurrence, Smith, which obviously precedes MacLeod in a descending sort.
Record count (in versions of Collections prior to 1.10.1)
Another consequence of sorting records on a field with multiple occurrences is that a record may be duplicated for every occurrence it has in the sort field. For example, if a sort is performed on creator and two creators are recorded, the record will be listed twice when the record set is sorted, once for each creator.
From Collections 1.10.1 onwards this is no longer an issue: a record is always only listed once in Result set View.
Why are my search results already sorted?
From Collections 1.17 onwards it is possible to have Collections remember your sorting, filtering and grouping choices by enabling the Retain sorting, grouping and filtering option (for each data source in which you sort, filter and/or group records):
Note: In versions of Collections older than 1.17, any sorting, filtering and/or grouping you applied in Result set View is lost when you perform a new search.
If your search results are already sorted in Result set View when you run a search, it is likely that the Retain sorting, grouping and filtering option is enabled and your previous sort is being applied automatically.
Automatic sorting of records in an Advanced search
As we see above, when viewing search results in Result set View it is a simple matter to sort records by values in one or more columns.
It is also possible to adapt a search statement specified on the Advanced search tab of the Search box so that the search results are already sorted when you view them in any of the Display Views.
Sorting search results: the sort option
When viewing search results in Result set View it is a simple matter to sort records by values in one or more columns.
It is also possible to adapt your search statement so that the search results are already sorted when you view them in any of the Display Views.
For example:
title = * AND creator= * sort creator
will return all records with a title and creator and sort them alphabetically (A to Z) by creator.
To sort records on more than one field, list fields in the desired sort order separated by commas, e.g.:
title = arc* sort author, title
Sort order
By default the sort order of fields is ascending: A to Z for text fields; 1 to 9 for numeric fields; earliest to latest for date fields.
Note: When the sort order is ascending, records with no value in the sort field will be listed first.
To reverse the order, add descending after the field name, e.g.:
title = arc* sort author descending
It is possible to sort on multiple fields in ascending and descending order. For example:
title = * AND dating.date.start = * sort dating.date.start, creator descending
will first sort records by date (earliest to latest) and any records with the same date will then be sorted by creator (Z to A).
Sorting your sampled records in an Advanced search
Rather than returning all records that match your search criteria, it is possible to return a random sample of matching records using the random operator in an Advanced search (full details here).
When you make use of the random operator in an Advanced search it is not possible to sort the records returned in Result set View using the sort options in a column header (i.e. by clicking the ellipsis and selecting a sort option):
In order to sort results returned with the random operator it is necessary to include the sort operator in your Advanced search statement. For example:
all sort object_number random 10
This search statement searches all records in the data source, randomly selects 10 of them and sorts them by object number in ascending order.
Tip: The random operator always follows the search statement.
Tip: By including the sort operator in the search statement it is possible to sort your random records on any indexed field, not only those displaying in Result set View.
