Warning: This guide is intended for advanced users and assumes basic knowledge of Specify Forms, xml, and sql
This guide covers the steps to add a simple, fully functional Query Combo Box to a form in Specify 6.
More specifically, this guide will walk through the steps of creating an
AddressOfRecord Query Combo Box (which will query the
Address field) and adding it to the
These steps, however, are general steps that can be applied and extended for any Table you wish to create a Query Combo Box for.
Before editing, keep backups of the files you’ll change so you can always revert back to the way things were in case of irreversible damage.
In addition, keep copies of your changed files. For most files that will be changed, Specify 6 will overwrite your changes to them when Specify 6 is updated and you choose to “update current directory”.
You will need to know the table id and abbreviation of the table you wish to create the Query Combo Box for, as well as the field names (as they appear in the Database) you would like to query by your table by.
The table id and abbreviation can both be found in either
For our example, I will reference
specify_tableid_listing.xml. AddressOfRecord appears as thus:
Thus, AddressOfRecord is abbreviated by
aor and has a tableid of
Excluding your views.xml which contains your form views and form definitions, there will be 3 additional files that require changes. If the Search (and Search view) functionality for the QCBX is also desired, 2 files in addition to the required ones will need changed.
|File Name||Path (relative to “…\Specify\config\”)|
|File Name||Path (relative to “…\Specify\config\”)|
This guide is structured around the edits to the individual files, and aims to provide a sufficient explanation required to understand the edits and the purpose of the files.
Creating a Query Combo Box without its associated Search function () will be addressed first, but covered at the end of the guide.
typesearch_def.xml file contains the query that is ran when text is inputted into the Query Combo Box.
If you are only querying fields from the Table, only one added line is needed to accomplish the desired result (Specify 6 infers the query from the XML data):
<typesearch tableid="125" name="AddressOfRecord" searchfield="address" displaycols="address" format="%s" system="true" dataobjformatter=""/>
This produces the rough equivalent of the following SQL statement
SELECT address FROM AddressOfRecord aor WHERE LOWER(aor.address) LIKE "#%";
# is the input into the Query Combo Box.
You will use the table id of your chosen Table, instead of
125, which is the table id for AddressOfRecord.
searchfield="" should contain the field that is being queried, and
displaycols="" contains the fields which are shown when the query is ran.
The value you decide to put into the
name="" attribute is not required to comform to anything, but should be descriptive. By convention, the Table’s name is usually used as the Type Search definition name.
(NOTE: The value you choose to be the Type Search definition name will be used later when editing your
views.xml and adding the Query Combo Box to a form)
Type Search definitions support more complex queries and formats as well. For an example, here is the (slightly modified) Type Search definition for CollectingEvent.
<typesearch tableid="10" name="CollectingEvent" searchfield="stationFieldNumber" displaycols="stationFieldNumber,startDate,loc.localityName,geo.fullName" format="%s; %s; %s -- %s" dataobjformatter=""> SELECT %s1 FROM CollectingEvent ce LEFT JOIN ce.locality loc LEFT JOIN loc.geography geo JOIN ce.discipline as dsp WHERE dsp.disciplineId = DSPLNID AND %s2 ORDER BY stationFieldNumber </typesearch>
This Type Search definition searches the field
stationFieldNumber from the Collecting Event table, and displays the
localityName (from Locality), and
fullName (from Locality.Geography), ordered by
Here is an example of what a search on this Query Combo Box might look like:
format="" attribute— there is one
%s for each
displaycol corresponds to one
%s in the
format attribute, by the order they appear in
displaycols. This is how the search results will be displayed. Nearly any string can be used to format the output, but the most common one is to use a semicolon to separate the fields. (This CollectingEvent does not have a start date, and thus it is not displayed in the output).
In essence, the
%s1 in the query gets the ID of the table and each of the
displaycols and puts them in a comma separated list.
%s2 gets the searchfield and then performs a LOWER() on the searchfield, and LIKE “#%” operation on the input.
Thus the first part of the CollectingEvent Type Search Definition query is approximately
SELECT stationFieldNumber, startDate, loc.localityName, geo.fullName FROM CollectingEvent ...
%s2 is approximately
... WHERE dsp.disciplineId = DSPLNID AND LOWER(ce.stationFieldNumber) LIKE "#%" ORDER BY stationFieldNumber
# is the input in the Query Combo Box when the query is ran.
With this in mind, I could very easily add
country to the display columns of my AddressOfRecord Type Search definition.
<typesearch tableid="125" name="AddressOfRecord" searchfield="address" displaycols="address, country" format="%s , %s" system="true" dataobjformatter=""/>
Now when I add the Query Combo Box to the form, when I search for an AddressOfRecord by address, country will appear in the output, separated from address by a comma.
(NOTE: The query statements in advance Type Search definitions are not quite SQL statements: they are HQL statements. Be sure to use the correct table abbreviations when constructing HQL statements, as well as to use the
name of relationships and fields as they appear in
specify_datamodel.xml for your Table and its relationships rather than using the SQL table/field name.)
The Dialog Definitions file defines the displays surrounding a Query Combo Box: both the View/Edit and Search displays. At the moment, I only want to add a 'Display" view (Adding a Search View will be addressed at the end of this guide).
A display view defines what form the buttons will display.
Since I want to be able to create/edit a new AddressOfRecord with the AddressOfRecord Query Combo Box, I would add the following to the
<dialog type="display" view="AddressOfRecord" name="AddressOfRecordDisplay" class="edu.ku.brc.specify.datamodel.AddressOfRecord" helpcontext="CBXQViewEdit"/>
In this case the
view="" attribute corresponds to whatever view, and thus view definition, within your
views.xml file will be displayed.
By convention, the
name= attribute should be the name of your Table with “Display”, and the
class="" attribute should be the same class as stated from your table’s view definition.
If you are unsure about a class name, or can not find a table you are looking for, here are the Specify 6 Datamodel Java Classes from which the text in the
class="" attribute derive from.
There are many places in Specify 6’s xmls that reference these Java Classes. Be sure to spell the table name in the
class=""attribute exactly as it appears in the Specify 6 datamodel folder— without the
For example, every reference to the AddressOfRecord class has to be formatted like
Lastly, we need to tell Specify to relate our new Display view to the datamodel, and this is done through the
specify_datamodel.xml. This file describes the entire structure of the database and is useful because it allows other xml files-like
typesearch_def.xml to use this structure and create queries, show displays/relationships by tableid, etc.
Since I want to create a Query Combo Box for an AddressOfRecord, I would find AddressOfRecord in
specify_datamodel.xml, which appears like this:
First, make sure your own table has the
searchable="true" attribute. Now we want to make a child of the table and make an attribute called
newobjdlg and give it the value of the Display Name you defined in
dialogdefs.xml. In my case, this would look like:
(Added space between the fields for better visibility).
Be sure to replace
"AddressOfRecordDisplay" to whatever value you gave the
name="" attribute of the display in your
And now we are ready to add our new Query Combo Box to a form! As initially stated, the goal was to add an AddressOfRecord Query Combo Box to the Loan form.
Of course, make sure your parent table has a relationship to the table you wish to make into a Query Combo Box and that this relationship exists in the schema configuration.
The Query Combo Box can now be added just as easily as you would add any other Query Combo Box! On my Loan View Definition, I will add the following row:
<row> <cell type="label" labelfor="aorqcbx"/> <cell type="field" id="aorqcbx" name="addressOfRecord" uitype="querycbx" initialize="name=AddressOfRecord" colspan="10"/> <!-- <cell type="subview" id="aor" name="addressOfRecord" viewname="AddressOfRecord" colspan="13"/> --> </row>
Below the new Query Combo Box, I have commented out how a subview could’ve been added as well.
The most important thing to note here is that the name being passed into the
initialize="name=#"attribute should be the same as the name of the Type Search Definition you created in Step 1 when you edited the
For example, if you instead wanted the Query Combo Box to search using the Agent Type Search definition, you would replace the
initialize="name=Agent" and the resulting Query Combo Box will use the Agent Type Search definition instead.
Now when the Loan form is viewed, our Query Combo Box works!
We can now query Address Of Record using its Address field and add that to to our Loan form! Note that country is also displayed, separated by a comma, from the address. Similarly, the edit and add buttons both show the Address Of Record form!
If you are content with a Query Combo Box that can successfully Query a table and create/edit records of that table, then feel free to stop here.
If you would like the Search functionality of the Query Combo Box-that is, if you want to be able to perform a Query on the table, and your selection becomes the value of the field, without having to go to the Query Builder- the process on how to add the functionality is described below.
This step of the guide assumes basic knowledge of how form modifications work. See our Helpcast Introduction To Form Modification for a more detailed overview of modifying forms.
Notice the Query Combo Box is missing a button that is present on other Query Combo Boxes, the Search button: .
Here is an example of a Search View that already exists within Specify, the CollectingEvent Search View:
OK is pressed, the selected CollectingEvent will populate the CollectingEvent Query Combo Box which initiated the Search.
If you would like this functionality for your custom Query Combo Box, you would need to make additional changes to
dialog_defs.xml, as well as new entries in
search_config.xml (as a reminder, both of these new files are within
The layout of search.views.xml is very similar to other view.xmls (such as
common.views.xml), and there is a very good reason for this. The Search Dialog is a form! The same logic that displays your forms also displays Search Views.
There are still some differences, however. Since the view and viewdef most likely will not be in the
search.views.xml to begin with, some work is needed to define your own view and viewdef.
First, a “view” needs to be defined.
Since I want a Search View for Address of Record, I will add the following view as a child of the
<view name="AddressOfRecordSearch" class="edu.ku.brc.specify.datamodel.AddressOfRecord" usedefbusrule="false" resourcelabels="false"> <desc><![CDATA[The AddressOfRecord Search Form]]></desc> <altviews> <altview name="AddressOfRecordSearch" viewdef="AddressOfRecordSearch" mode="edit" validated="true" default="true"/> </altviews> </view>
By convention, the
name of a view should be your Table Name + Search, but this is not a strict enforcement: the view name can be whatever you wish. Be sure to change every reference of
AddressOfRecord to whatever your Table Name is.
Now a definition is needed for this view. For now, I would content to be able to find my AddressOfRecords just through the Address field.
And thus, I would contruct the following
<viewdef> and make it a child of the
<viewdef type="form" name="AddressOfRecordSearch" class="edu.ku.brc.specify.datamodel.AddressOfRecord" gettable="edu.ku.brc.af.ui.forms.DataGetterForHashMap" settable="edu.ku.brc.af.ui.forms.DataSetterForHashMap"> <desc><![CDATA[The AddressOfRecord Search Form]]></desc> <enableRules/> <columnDef>p,3dlu,p,3dlu,p,3dlu,p,3dlu,p</columnDef> <rowDef>p,2dlu,p,2dlu,p,2dlu,p,2dlu,p,2dlu,p,2dlu,p,2dlu,p,2dlu,p</rowDef> <rows> <row> <cell type="separator" label="Address of Record Info" colspan="7"/> </row> <row> <cell type="label" labelfor="1"/> <cell type="field" id="1" name="Address" uitype="text" cols="20"/> </row> </rows> </viewdef>
When defining the
name=""attributes of fields in a Search View Definition, use the name of the field as it appears in the database. Do not use the Schema name.
AgentSearchview definition to see how to access relationships.
If I wanted to add a Country field on the same row as my Address to search by, I would add the following xml to my view definition
<cell type="label" labelfor="2"/> <cell type="field" id="2" name="Country" uitype="text" cols="20"/>
However, forms do not contain any logic to perform queries on the database. This new form and form definition only tells Specify what fields to display on the Search View. To configure the Query being run, you need to modify
The search_config file contains the SQL statement that would be run to query your table. There are 2 main xml elements, all related to searches being done on the database.
For our purposes, we will only add/modify children under the
<generic> element, as these Search Configs are primarily for Search Views.
I will add the following table as a child of the
<generic> element (The same space where all other Search View Configs are defined).
NOTE: This is entirely separate from the HQL statement you wrote in
<table id="AORSearch" tableid="125" name="AddressOfRecordSearch" priority="1" color="30, 144, 144"> <detailView icon=""> <sql> <![CDATA[SELECT AddressofRecordID, Address FROM addressofrecord WHERE (%s)]]> </sql> <captions> <caption tableid ="125" col="AddressOfRecordID" visible="false"/> <caption tableid="125" col="Address"/> </captions> </detailView> </table>
There are some important things to consider in a
id=""attribute is usually a number. To prevent confusion between
tableid, I have made the
idattribute “AORSearch”. Either case is valid: there is only 1 restriction regarding the
idattribute— that no two
<table>elements have the same
Use the name of your own search (as you defined in
search.views.xml) for the value of the
You can modify the SQL statement to fit your needs, but keep it enclosed within
The children of the
<captions>element define the columns that would be displayed as a result of the Query (This is much like
<caption>element requires a
tableidof a caption should be from which table it is displaying/getting the data from the sql search and
colshould be the column/field name you wish to display from the
tableid(This is case-sensitive!)
%stakes all of the inputs on the form and performs the SQL LOWER() and OR operations on them.
You are able to use aliases both within the sql statement and when defining a
colattribute. (See FieldNotebookPageSearch— id=“126”, for an example of this)
If you wish to use a numeric identifier (
id) for your
<table>element, the last used numeric ID was 136
Finally, we need to tell Specify to use our new Search View for AddressOfRecord Query Combo Boxes.
Firstly, we need to add a “search” type dialog to
dialog_defs.xml. This is very similar to the
display dialog type we did for the AddressOfRecord view/edit view.
Thus, I will add the following line to
<dialog type ="search" viewset="Search" view="AddressOfRecordSearch" name="AddressOfRecordSearch" class="edu.ku.brc.specify.datamodel.AddressOfRecord" idfield="addressOfRecordId" helpcontext="CBXQSearch"/>
You will have to change both the
name attributes to whichever name you used when creating the Search View and Search View definition.
You will of course also need to change the
idfield to match the data for your own table as well.
idfield attribute is case insensitive, but it is recommended to use camel case for better readability.
Finally, we need to connect the Search definition to the AddressOfRecord datamodel. This is done in
specify_datamodel.xml, and the process is almost identical to adding the Display Dialog.
<display> element for your
<table>, add a
searchdlg attribute with the name of your Search View as its value.
Here is the final display element for ease of copy and pasting
<display searchdlg="AddressOfRecordSearch" newobjdlg="AddressOfRecordDisplay"/>
And thus, we have made a fully-functional Query Combo Box in Specify 6! If I open Specify and navigate to my AddressOfRecord Query Combo Box on my Loan form, I see the is now visible.
I can now open the Search View and run a query to find an AddressOfRecord!
Note that while the
Countrytext field is visible on the form, it is non-functional because it was never added in the