List and Query Management

Introduction

You define and execute queries using the Query Builder window or by using form based query . When a query is executed the query definition and the hit list is saved for you to use again in the future. This is done using the Lists and queries window. This window is open by default, but if it is closed, you can open it by choosing Window -> Lists and queries. The window contains nodes representing queries and lists, as shown here:
images/download/attachments/1805177/lists-and-queries-window.png

You can manage lists and queries using the toolbar and the node's contextual menus. By managing your queries and their results, you can do the following:

  • Create lists of favourite compounds - please also see the cherry picking functions Cherry Picking

  • Combine results of multiple queries in powerful ways

  • Compare the result of a query to the previous times you ran it

  • Restrict the results of a search to the contents of a particular list

  • Add/remove lists to/from the current results

  • Generate lists of values from any field

Managing Lists

Lists can be used to manage lists of values from a field. Lists can be given names, saved, restored, edited, imported, and exported. New lists can be made from logical combinations of other lists. For instance, you can create a list of your favourite compounds that you can save and examine the next time you use IJC. Lists of values from one field can also be converted to those of a different field.

Temporary and Permanent lists

Lists belong to one of two categories - temporary and permanent. Temporary lists do not exist when you restart IJC, while permanent lists persist across restarts. Permanent lists are saved to the database and continue to exist until you choose to delete them. Temporary lists can be made permanent. Both types can be renamed or deleted. To convert a temporary list to a permanent one:

  • Right click on the list and choose 'Make permanent' or 'Make temporary'

  • Drag the list from the temporary folder to the permanent folder (or vice-versa)
    When you execute a query a temporary list is automatically created for you. These lists are temporary as a large number of them might be generated. You can make any of these temporary lists permanent if you want to re-use it in later IJC sessions. You can also convert the IDs to those of a different field.

Lists and Fields

As we have seen, a temporary list is created as a result of running a query. Such a list corresponds to a list of the values from the ID field of the Entity. These IDs are the IDs of the row, which correspond to the primary key values in the database. This is the only type of field for which temporary lists can be created. However (since IJC 5.3.2), lists of values from fields other than the ID field can also be managed by IJC as permanent lists, and values of lists for one field can be converted to those for a different field. Ways of creating and editing lists are described later.

Lists for each field are displayed in a folder for each field. Only the ID field folder is shown by default. Other fields are only shown if there are any lists for the field.

List management is most useful for fields that have unique values, like the ID field of the entity. The value of the ID field uniquely identifies a row in the database table. Support for permanent lists for fields other than the ID field was added in IJC 5.3.2 primarily to manage the situation where there was an additional field that is a more meaningful identifier (e.g. the compound ID), However, the values in the field do not have to be unique, and in some cases this can be useful. For instance you can run a search to generate a list of IDs, then convert that list to a list of the molecular formula field to generate a list of all the formulae within that set of results. When that list is applied all structures with any of those formulae will be seen. It is important to realise that this sort of operation is not commutative. e.g. converting a list of Field1 to one for Field2 and then back again to Field1 will not result in the original IDs if the values of either field are not unique.

List management supports only integer and text fields. It is not thought likely that other types of fields will be useful. Also, not all text fields will be suitable for list management. It is designed only for values that are simple single line values. More complex multi-line or lengthy text strings will not be suitable.

Creating and editing lists

There are a number of ways of creating or editing lists:

Automatic creation when queries are executed

  • A temporary list of the ID field is automatically created whenever a query is executed.

Manually entering the IDs

  • You can edit a list by right clicking on it an choosing 'Edit list'. The list editor opens allowing you to manually specify the values. These can be typed in or pasted from the clipboard. The syntax is simple text with each value on a separate line.

    images/download/attachments/1805177/lists-queries-edit-list.png

Importing/exporting the values from a file

  • A list can be created by importing from a file. The syntax is simple text with each value on a separate line. Similarly, values in a list can be exported to a file. Perform these operations by choosing 'Import list' or 'Export list' from the right click context menu of the list or by using the icon in the toolbar.

Since the 15.10.5.0 release the user has to have the ROLE_EXPORT_DATA assigned to be able to export permanent lists. The user roles are described here.

Converting the values from a different field

  • Values for a list of one field can be converted to values for a different field. This is most commonly used to convert temporary lists generated when a query is executed to a list of values for a different field (e.g. your compound IDs) but can be used to convert between any field. To perform this either:
    Drag and drop the list to the folder for the required field
    or
    Right click context menu of the list and choose 'Convert list'. The convert list dialog will open:

    images/download/attachments/1805177/lists-convert-dialog.png

Using list operations

You can perform operations on two or more lists at the same time. Select multiple lists and then open the drop-down shown below:

images/download/attachments/1805177/lists-queries-op1.png

A list of operations is shown. They are as follows:

  • Intersection

  • Union

  • XOR

  • A and not B

  • B and not A
    When you make a choice from the above operations, the following dialog is shown, with the corresponding operation selected:

images/download/attachments/1805177/lists-queries-op2.png

You can apply the operation to an existing list (overwrite one of the input lists) or you can create a new list for the results. Use the "Save result to" area, at the bottom of the above dialog, for this purpose. List operations can only be used for lists of the same field (but remember that lists can be converted from one field to another). When using the ID field you can choose whether the new list is to be temporary or permanent.

Managing Queries

You can also manage queries. For example, you can recall previous queries and run them again. When a query is executed, a temporary query is created, allowing you to re-execute it at a later time. Like lists, there are two types of query - temporary and permanent. Like lists, they can be renamed or deleted. Temporary queries can be made permanent and vice versa.

It is important understand the difference between lists and queries. This can be best explained in terms of what happens when you re-apply the list or query when the contents of the database have changed. The list is a static list of IDs. It will not reflect any changes to the database. The query will be re-run when it is re-applied and will reflect any changes to the database and so may generate different results. For instance, this allows you to ask questions like "What new results are in the database since I last ran this query?". To do this, save the list of results (e.g. make the list permanent and give it a sensible name) each time you run the query. Then compare today's results with those from last time by finding our what is in today's list that was not in the other list.

The Lists and Queries Window

By default, the Lists and Queries window is empty and the toolbar is disabled. The contents of the Lists and Queries window corresponds to the context of the current Data Tree. Queries belong to the Data Tree while lists belong to an entity, typically the Entity at the root of the particular Data Tree that is selected. Usually this corresponds to the Grid View or Form View that is currently selected. If, for instance you switch to a view of a different Data Tree then the Lists and Queries window contents will be updated so that it contains the queries for Data Tree and the lists for the Entity at the root of the Data Tree of that view. The Lists and Queries window has a selector combo box that lets you change the selected data tree and see the lists and queries for the entity at the root of that data tree.

The Lists and Queries window shows at least four folders, temporary and permanent queries and temporary and permanent lists for the ID field. If you have created permanent lists for other fields then folders for these will also be present. After you run a query , the temporary query node and the temporary list node each have subnodes. The temporary query node shows the executed queries and the temporary lists node shows the results of the query.

Queries and lists can be deleted and executed. Lists can also be edited, sorted, exported, and imported. These actions are available from the toolbar. By right-clicking on a query or a list, you are able to specify that they should become permanent. Details on all these activities are provided below.

Lists and queries window menu and toolbar operations

Operations can be performed on lists and queries either by using the toolbar in the Lists and queries window or by using the right click contextual menu of the individual list or query.

images/download/attachments/1805177/lists-queries-lists-contextual.png

images/download/attachments/1805177/lists-queries-queries-contextual.png

Context menu for lists

Context menu for queries

Actions common to lists and queries

  • Execute : Re-applies the list or query to the data tree, e.g. updates the results, in the main window to show the appropriate list or query.

  • Share : control how the list or query is shared with other users (only present for permanent lists and queries).

  • Make permanent/temporary : Converts between temporary and permanent lists. When you restart IJC only permanent lists will be present.

  • Delete list or query : Delete the list or query.

  • Rename : Assign a new name to the list or query.

  • Properties : Show the properties of the list or query.

Operations specific to lists

  • Add list to current result set : Add the IDs for that list to the current results.

  • Remove list from current result set : Removes the IDs for that list to the current results.

  • Edit list : Lets you edit the content of a list. The contents is just a list of IDs.

  • Convert List : Convert the values of this list to those for a different field.

  • Export list to file : Create a text file with the list IDs.

  • Validate list : Removes any IDs for the lis that are not in the database.

Creating queries

Queries are created using the query panel. See the documentation on Running queries for more details. When a query is executed, the query definition is saved as a new temporary query. The list of results is stored as a new temporary list.

Querying within a list

By default, a query is applied across the entire database. However, you can narrow the search so that it is applied to a list. As a result, you are able to perform queries within results attained from previous queries. Use the Domain drop-down in the Query window for this purpose. See the documentation on Running queries for more details.

Options for list and query management

To set options for managing lists and queries, choose Options under the Tools menu and then click Miscellaneous -> Lists and Query. You should now see the following:

images/download/attachments/1805177/lists-queries-options.png

You can set the following options:

  • Lists.

    • Maximum number of temporary lists : By default, no more than 10 temporary lists can be created. After that the oldest one is removed when a new one is added. To change this upper limit, specify a different number here.

    • Maximum number of rows in list : The default number of rows is 10,000. Depending on which of the two radio buttons you select, no more temporary lists are created after this point, or new temporary lists over this point are truncated. Note: if you increase this limit you may run into memory limitations.

  • Queries.

    • Temporary queries history limit : By default, no more than 10 temporary queries are present.

    • Do not create temporary query if it returns no hits : By default, even if no hits are returned, a temporary query is created, which you can then rerun. If you select this checkbox, no such temporary query will be created.

    • Do not create temporary query if it returns all rows from database: By default, if all rows are returned, a temporary query is created. This, however, is in most cases a query that you do not want to run again, therefore a temporary query is not created. Select this checkbox to have the temporary query created in this instance too.

Sharing lists and queries

If you are using a multi-user database then you can use lists and queries that other people have created. You can do this in one of two ways:

Sharing the list or query

One user can share a list or query with other users by changing the visibility of the item. This allows direct access to the other person's list or query, but does not let the second user change the first user's list or query. The second user will however see any subsequent changes that the first user makes (the details are loaded when the user first connects to the database). Details of sharing items are described in the document Sharing items with others .

Copying the list or query

To do this right click on the Permanent Lists or Permanent Queries node and select Copy list/query from other user. A dialog will open that allows you to specify the user and the list/query.

images/download/attachments/1805177/copy-list.png

Note: This process creates a COPY of the list or query. Any changes the other user subsequently makes will not be reflected in your list/query. You would need to take a fresh copy.

Cherry Picking Lists

The new cherry picking functionality is now available. With this feature you can add and remove molecules from the working list easily by using shortcut or the toolbar icon if you like. Please, see the documentation on Cherry Picking for more details.