View source for Query the Database

{{topic|AquilaCRS}}


{{todo|Make the sidebar image much shorter}}

==Selecting the Query==

The Navigation bar to the left of the [[Dashboard]] page contains a list of queries & searches.  To initiate a query just press on the option you require.  The options differ only in the data tables they search. For instance, the '''IBID Query''' allows you to query the IBID set of data tables.

When you select the Query of your choice, you may need to wait for a few seconds initially whilst all the fields and their parameters are transferred from the server.

[File:NavigationBarSmall.jpg]]

Queries have two elements:
* The Filter : this is a set of conditions that determine which rows are returned from the database. E.g. ''Sex = Male''
* Output Fields: this is a set of tickboxes that determine which fields are listed in the query output.

Both elements can be saved to a file before running the query so you can reload & reuse them at a later date.  See ''Saving & Re-Using Filters & Output Field List'' below.

==Entering Criteria==

The Filter is defined on a row-by-row basis & you can enter multiple conditions.

* To add your first condition use the '''press the button to add a new condition'''.
** To remove a condition, press the '''...''' button and select ''Remove Row''

* Click on the field name (in green) to open a list of fields in the query.  
** You can select with the mouse, or by starting to type the field name as it's shown.
** Fields are often prefixed with their section names, e.g. Patient.Given Name.

* Click the operator (in red) to select the comparison type. 
** In character fields, ''Like'' can be used instead of ''Equals'' to partially match the value you enter.  
*** E.g. ''Patient.Town like chester'' will match ''manchester'' and ''chester'', but ''Patient.Town equals chester'' will only match Chester.
** In name fields, ''Like'' will perform a ''sounds like'' match.
* Click on the value (in blue) to change it.  The type of editor you're presented with depends on the type of the field you're comparing. E.g. a date field will show a date editor, a drop-down field will show the same drop-down options.


By default, the conditions you enter are combined using the logical operator '''and'''.  This is shown at the top of the filter, at the ''<Root>'' level.  This means that all conditions you specify must be met for a row of data to be returned.  Set this to ''Or'' to have rows returned where a row matches ''any'' condition.

[[File:Ibid query filter definition sample.png]]

A filter definition can be saved to a file before running the query so you can reload & reuse it at a later date.  See ''Saving & Re-Using Filters & Output Field List'' below.


=== Filter Groups ===

In some circumstances you may have a complex filter to define that requires all of certain conditions to be met and any of some other conditions. We would achieve this effect by using a ''condition group''.  A group of conditions has their own operator (and/or) that is treated separately from the <root> conditions.

This is best demonstrated by example:

[[File:Ibid query filter definition sample complex.png]]

The above conditions can be summarised as follows:

  (Record type is acute major) OR (Intubated AND Inhale Severity is Severe or Moderate)

or in more database-like form:

 ([Record Type] = 'Acute major') OR ( ([Intubated] = TRUE ) AND ( ([Inhale Severity] = 'Severe') OR ([Inhale Severity] = 'Moderate') ) )

==Selecting Fields to Output==

As the ''Filter'' section defines which rows to return, so the Output Fields list defines which columns to return.  The list of available fields are displayed in a hierarchical view, grouped by logical section.  

You can select to return all the columns for a specific section by ticking that section. You can also open the branch for that section and (de)select fields individually.  Sections that have only some fields selected display a "greyed" tick.

Type keywords into the Search box and click the (magnifying Glass) search icon, this will return results containing the relevant keyword. Choose the words or phrase required by clicking the tick box.

To add fields to the filter right click on the tickbox item and choose "Add Field to Filter" this will populate the filter fields too. 

[[File:AddFiledFromFile.jpg]]

To save bandwidth in what are often overworked networks, it's always a good idea to spend a few minutes carefully selecting only those fields that you need for your analysis.

{{todo|From this point down to the line stating ''Close - the close button will close the query'' really needs to be in the Query Results page}}

Once the query has been run and the results returned, details of the search fields are displayed below the toolbar. 

[[File:Queryresults.jpg]]

Please note; The Query brings back results for each Ibid Record or Dependency record the patient has had, in cases where the patient has has several ibid or dependency records each entry will occupy a separate row. Not to be mistaken as duplicate records.

On the toolbar there are several options as follows-

*Edit the Query - This will return the user to the original Query whilst still displaying the query results, filter and fields can be added as needed, the query can then be re run.

*Open Folder - Highlight the patient name and click the "Open Folder" button, the selected patient folder will be displayed.

*Open Demographics - Highlight the patient name and click the "Open Demographics" button, the selected patient demographics will be displayed.

*Max Records - Results will be returned for the first 250 records, clicking the "Max Records" box will return all record with the selected fields 

*Refresh - Refreshes data in the search

*Colors, Drop Down Box - where numerous searches are open, each search can be allocated a colour for ease of identification, Choose from a host of colours

[[File:SearchColours.jpg]]

*Help - The help button will take you to Aquila Help.

*Close - The close button will close the query 

A field list definition can be saved to a file before running the query so you can reload & reuse it at a later date.  See ''Saving & Re-Using Filters & Output Field List'' below.

== Saving & Re-Using Filters & Output Field List ==

The Query form allows you to save & load both the filter and output fields for future use.  Note that the files contain only the filter definition or the field list - there is no search results in the files so the files created can be shared via email or other methods.

On the filter screen, press the '''Options''' button to reveal a list of save/load options. In all cases, use a meaningful filename to allow easier recalling. 

[[File:IBID Query Options Filter Options.png]]

=== Options ===

* Load Filter from File: Opens a previously saved filter & replaces any current conditions in the filter window with those from the file.
* Save Filter to File: Saves the conditions in the filter to a file.  This file can be re-used by any AquilaCRS installation, so feel free to share assuming there is no patient identifiable information in the conditions.
* Load Fields from File: Opens a previously saved list of fields & replaces any current selections with those from the file.
* Save Fields to File: Saves the current output fields selection to a file.  As with the filter, the file can be shared amongst any AquilaCRS installation.

== Hints & Tips ==

* Make more room on the filter/field list screen by dragging the divider to the right and choosing Tabbed View from the options menu.
* The Filter can be navigated using the keyboard arrow keys.  Watch for the dotted focus rectangle and start typing when the focus is on the right element.

==Query Results==

See [[Query results]] for more information

[[Category:User Help]]
[[Category:AquilaCRS]]