Create a Query in Specify 7
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 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.
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 older versions of Specify 7
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.
These vary from each field however these are the major ones you will see time and time again while building a query.
|Any||Any value from this field is going to result from this query|
|Like||You can insert a wildcard character (*) at the beginning or end of the field to search all items that start or end with an unknown value. Querying *3 will pull all results that end in a 3.|
|=, >, <||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||You can give it two values and it will pull all data in between|
|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” 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. 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.
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.
The button on the left is the default state of the sort button. If you press once, it sorts ascending. Twice, descending.
These buttons will move the field up or down in your query. This affects how it appears once you run the query.
Step 8: Sort and organize your query.
Here is our example collection object query.
These options allow you to modify the query’s results:
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.
Query runs the query.
Once the query has been run, you will see these 4 options:
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:
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!
Click on the icon to see the query options.
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.
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.