Adding a non-native Query Combo Box

[jason_m]

Overview

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 Loan form.

These steps, however, are general steps that can be applied and extended for any Table you wish to create a Query Combo Box for.

Set Up

Disclaimer

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”.

Preparation

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 specify_datamodel.xml or specify_tableid_listing.xml

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 125.

Files to Change

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.

Required

File Name	Path (relative to “…\Specify\config\”)
specify_datamodel.xml
typesearch_def.xml	…\backstop\
dialog_defs.xml	…\backstop\

Optional (If Search View functionality is desired)

File Name	Path (relative to “…\Specify\config\”)
search.views.xml	…\backstop\
search_config.xml	…\backstop\

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.

1) Type Search Definition (typesearch_def.xml)

The 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 "#%";

where # 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.

Additionally, 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 stationFieldNumber, startDate, localityName (from Locality), and fullName (from Locality.Geography), ordered by stationFieldNumber.
Here is an example of what a search on this Query Combo Box might look like:

Format Syntax

Note the format="" attribute— there is one %s for each displaycol. 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.
Likewise, %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 ...

And the %s2 is approximately

... WHERE dsp.disciplineId = DSPLNID AND LOWER(ce.stationFieldNumber) LIKE "#%" ORDER BY stationFieldNumber

where # 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.)

2) Dialog Definitions (dialog_defs.xml)

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_defs.xml:

<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 .java file extension).
For example, every reference to the AddressOfRecord class has to be formatted like edu.ku.brc.specify.datamodel.AddressOfRecord

3) Specify Datamodel (specify_datamodel.xml)

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 dialogdefs.xml

4) Adding the Query Combo Box to a Form

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 typesearch_def.xml.

For example, if you instead wanted the Query Combo Box to search using the Agent Type Search definition, you would replace the initialize="name=AddressOfRecord" to 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.

Adding a QCBX Search View

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:

Thus, when 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 specify_datamodel.xml and dialog_defs.xml, as well as new entries in searchviews.xml and search_config.xml (as a reminder, both of these new files are within ...\Specify\config\backstop\).

1) Search Views (search.views.xml)

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 <views> element.

<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 <viewdefs> element.

<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>

Important Note:
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.
See the AgentSearch view 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 search_config.xml

2) Search Config (search_config.xml)

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 typesearch_def.xml!

<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 <table> element.

• The id="" attribute is usually a number. To prevent confusion between id and tableid, I have made the id attribute “AORSearch”. Either case is valid: there is only 1 restriction regarding the id attribute— that no two <table> elements have the same id

• Use the name of your own search (as you defined in search.views.xml) for the value of the name="" attribute

• You can modify the SQL statement to fit your needs, but keep it enclosed within<![CDATA[]]>

• The children of the <captions> element define the columns that would be displayed as a result of the Query (This is much like displaycols in typesearch_def.xml)

• Each <caption> element requires a tableid and col attribute. The tableid of a caption should be from which table it is displaying/getting the data from the sql search and col should be the column/field name you wish to display from the tableid (This is case-sensitive!)

• %s takes 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 caption’s col attribute. (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

3) Dialog Definition and Specify Datamodel changes

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_defs.xml:

<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 view and 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 class and idfield to match the data for your own table as well.
The 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.

Within the <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"/>

Conclusion

---

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 Country text field is visible on the form, it is non-functional because it was never added in the search_config.xml!