Query Builder in Specify 7

Create a Query in Specify 7 :queries_:

One of the expected benefits entering data into a structured database is to be able to retrieve the data efficiently and consistently. Specify’s Query Builder offers a flexible interface for defining and running searches. Queries are a precise request for information retrieval and can restrict the returned records based on the contents of any Specify data field.

The results from queries can then be used to create Record Sets or Reports.

Step 1: Click on :queries_:Queries in your navigation bar.

Step 2: Click on New.

Step 3: Select the base table associated with the query you want to make.

The Collection Object table is the one we will use for this example.

Step 4: Select a field from the mapper then click the + button to add it to the query.

msedge_QFaNxlT4K4

The tables that have icons and the > symbol to the right of their name are relationships. In my above example, I chose Accession > Cataloger > First Name. This is a relationship from the Collection Object base table through the Agent table. This relationship is named in the schema Cataloger.

For Specify 7.6.1 or earlier

In earlier versions of Specify 7, you must click the + to add lines to the query.

For each blank field that you have added, you will have a picklist like this one to pick your fields:

The → to the left of some items indicate that they are tables within your chosen base table.

Because I chose the → Accession # item in the previous list, I now have the fields from the Accession table. For this example, I chose Accession # as the value I wish to query from this list.

Pick all the fields you wish to query as well as every field you want to see the value of associated with your query.

Some tables can be aggregated or formatted, outputting the structure specified in the schema once queried. Individual elements can be chosen if you wish to see or query those results.

Step 6: Choose your operators for each field.

image

These vary from each field however these are the major ones you will see time and time again while building a query.

Operator Explanation
Any Any value from this field is going to result from this query
Like You can insert one of two wildcard characters (% or _) anywhere in a field to narrow a search using character substitution.

% represents zero or more characters. For example, d%t will find any word that begins with d and ends with t (dot, dust, dealt, etc.). Tes% will find any word that starts with Tes.
_ represents a single character. It differs from ‘%’ in that is only ever a single character. c_t will only find words that start with c, contain one of any other character, and end in t (cat, cot). sp____y will find any word that starts with sp, has 4 of any other character, and end in y (specify).

The wildcards can be used in conjunction. For example, to find all words that must be at least 3 characters in length: ___%
To find all words that start with C and must have at least two following characters, and end with any single character followed by el (such as Caramel): C__%_el
=, >, < These are equal to, greater than, less than, greater than or equal to, and less than or equal to, and function as expected with your field’s value.
Between Given a Start and End Value, only return results which are in a range from the Start Value to the End Value. For numeric fields, BETWEEN 0 AND 12.23 would allow values 0, 0.111, 6, 12.23, but exclude numbers like -1, 12.231, 100. For text fields, the Start and End Values only match the beginning of the text, and the search is done in a case-insensitive manner. BETWEEN climb AND swim will return words climber, jump, Dance, rest, swan, etc. but NOT return words clamber, clima, swim, tuck, etc. To determine the range, each character is sorted ‘alphabetically’ based on its ASCII decimal value.
In You can create a non-consecutive list that can be separated with commas or spaces to be searched. Querying 1,3,5,7 will pull the data for your field when it has a value of any of those entered.
If you are using the “In” operator on a field that is based on a Pick List, you can hold down Ctrl ( on Mac) to select multiple items from the presented list rather than typing in a comma-separated list of items.
Contains This finds every instance of a specific string associated with that field
Empty This will only pull data from items that have said field empty
True/False If the value is true or false.

This icon is the Negate button. image This essentially says if your search does NOT meet your query criteria, it will appear.

By clicking on the + button to the left of a field picklist, you can add an OR search term.

image

This will return all results that start with “Sp*” OR contains “ane”. You can remove this OR component by clicking on the trash can icon.

Step 7: Sort and organize your query.

Sorting Your Query

The checkmark button hides or unhides a specific field from the query. This will result in the item either appearing or not appearing once you run the query.

Hidden:
image

Unhidden:
image

The button on the left is the default state of the sort button. If you press once, it sorts ascending. Twice, descending.

image

These buttons will move the field up or down in your query. This affects how it appears once you run the query.

image

Step 8: Sort and organize your query.

Here is our example collection object query.

These options allow you to modify the query’s results:

image

Count shows the count of results without the query creating them.

Distinct allows concise queries to pull a list of once-off items (such as geography information or localities) without having duplicate entries. Checking distinct will take any duplicate records and link them together.


Without distinct checked there will be a lot of individual records, many of which are duplicates. When distinct is checked any duplicates will be linked together so the :linkout_: will open a new tab to a singular record like before but the 4 lines will allow you to view all the duplicate records like browse in forms.

Query runs the query.

Once the query has been run, you will see these 4 options:

image

Create CSV creates a comma-separated value file that can be used in other programs like Excel.

Create KML creates a Google Earth file from a query that pulls geographic data.

Create Record Set makes a record set that can be referenced later and used for reports and labels.

Browse In Forms opens up a record set-like interface to browse the query results in.

You will notice in this view that there are 3 options visible in the top right:

image

Edit Query returns you to the top to modify it from the builder interface.

Save Query saves the current query as it is.

Save As allows you to preserve the original query and creates a new one with all current changes.

Step 9: Save and use your query.

Now that we have the query working the way we want it to, I can save and start using it.

This is an example output when I restrict my query to search Collection Objects using Cat # from 500-504.

All elements can be interacted with and work as hyperlinks to the respective information. Click Save once you are satisfied and now you have a query!

Query Options

image

Click on the :pencil2: icon to see the query options.

image

You can change the name of your query, define a label or report based on your query, or export it in the Darwin Core Archive format.

Here are some example outputs:

Export query for DwCA definition. This is useful for publishing data to aggregators.

  • Define report based on query.

  • Define label based on query.

image

Name your new label or report. Once you click Create, you will be redirected to the App Resources page.

For both the label and report, you can modify the automatically created file within Specify or click Download to save the report to your computer. It will be downloaded in a jrxml file that can be modified in Jaspersoft Studios. You can use the Load File option to reupload it once you have customized it to your liking.


Simple Query Interface

You can switch to the ‘Simple Query’ interface on any query by clicking on the Basic View button in the ribbon at the top of the page.

You can switch back by clicking the Detailed View button in the same space.

It is worth mentioning (I just figured this out by accident after some struggling) that if you are using the “in” operator on a field that is based on a picklist, you will have to hold down “CTRL” in order to select multiple items from the presented list rather than typing in a comma-separated list of items.

1 Like

Thank you @Plarson!
I’ve added that to the instructions! :smile:

1 Like

Another non-intuitive query behavior I have discovered, only today, (and I’m not sure if this is intentional or a bug) is that querying with the negate button active and then querying again with the negate button inactive does not return all possible records - it ignores all records where that field is totally blank even though a blank field necessarily satisfies the ‘negate’ condition.

For example if I query records where field number contains “SMP” I get 977 results. When I query records where field number does NOT contain “SMP” I get 1511 results. Looks like I have only 2488 records - but there are another 3541 records with blank field number that didn’t appear in either search. This has caused me a lot of confusion and probably reporting wrong numbers to granting agencies in progress reports. I propose that the “negate” operation should include records where the queried field is empty since they logically fall under that category. Or at least make this behavior more explicit in the interface somehow.

Update: This only seems to be true for Specify7 - Specify6 includes empty results when you query for ‘NOT’ values.

Hi @Plarson,

I’ve written up an issue on the Specify 7 repository so that we can revisit this behavior in the future:

Thanks for your comment!

1 Like