SQL queries can have associated XML files that carry additional metadata
information about the columns in the query, allowing you to make changes including:
- Disable the standard insert, update, and delete buttons.
- Format display of numeric columns
- Color coding for values that fall within a numeric range
- Configure lookups on columns
You can edit or add to this metadata either using:
Edit Metadata using the User Interface
The metadata editor offers a subset of the features available in the field properties editor
and works in the same way.
- Open the schema browser via Admin > Developer Links > Schema Browser.
- Select an individual query/table in the Query Schema Browser and click Edit Metadata.
- When you click anywhere along the row for a field, you activate that field for editing and open the properties editor to the right, which includes three tabs:
- To change a column's displayed title, edit its Label property.
- In the image above, the displayed text for the column has been changed to read "Average Temperature" (instead of "Average Temp"). Notice the wrench icon on the left indicating unsaved changes.
- You could directly Edit Source or View Data from this interface.
- If you are viewing a built-in table or query, notice the Alias Field button -- this lets you "wrap" a field and display it with a different "alias" field name. This feature is only available for built-in queries.
- Click Save when finished.
Edit Metadata XML Source
The other way to specify and edit query metadata is directly in the source editor. When you set field properties and other options in the UI, the necessary XML is generated for you and you may further edit in the source editor. However, if you wanted to apply a given setting or format to several fields, it might be most efficient to do so directly in the source editor. Changes made to in either place are immediately reflected in the other.
- Click Edit Source to open the source editor.
- The Source tab shows the SQL query.
- Select the XML Metadata tab (if it not already open).
- In the screenshot below a conditional format has been applied to the Temp_C column -- if the value is over 37, display the value in red.
- Click the Data tab to see some values displayed in red, then return to the XML Metadata tab.
- You could make further modifications by directly editing the metadata here. For example, change the 37 to 39.
- Click the Data tab to see the result -- fewer red values, if any.
- Restore the 37 value, then click Save and Finish.
If you were to copy and paste the entire "column" section with a different columnName, you could apply the same formatting to a different column with a different threshold. For example, paste the section changing the columnName to "Weight_kg" and threshold to 80 to show the same conditional red formatting in that data. If you return to the GUI view, and select the format tab for the Weight field, you will now see the same conditional format displayed there.
Another example: the following XML metadata will hide the "Date" column:
<table tableName="TestDataset" tableDbType="NOT_IN_DB">
Other metadata elements and attributes are listed in the tableInfo.xsd schema available in the XML Schema Reference
that it is only possible to add/alter references to metadata entities that already exist in your query. For example, you can edit the "columnTitle" (aka the "Title" in the query designer) because this merely changes the string that provides the display name of the field. However, you cannot edit the "columnName" because this entity is the reference to a column in your query. Changing "columnName" breaks that reference.
- Data.query.xml - Configures columns with custom formatting for some numeric columns, and color coding for the QCFlag column. Adds multiple menu options under the "More Actions" button at the end of the button bar.
- Formulations.query.xml - Sends users to custom URLs for the insert, update, and grid views. Retains some of the default buttons on the grid, and adds a "Delete Formulations" button between the "Paging" and "Print" buttons.
- Aliquots.query.xml & performCellSort.html - Adds a button to the Sample Sets web part. When the user selects samples and clicks the button, the page performCallSort.html is shown, where the user can review the selected records before exporting the records to an Excel file.
- To use this sample, place Aliquots.query.xml in a module's ./resources/queries/Samples directory. Rename Aliquots.query.xml it to match your sample set's name. Edit the tableName attribute in the Aliquots.query.xml to match your sample set's name. Replace the MODULE_NAME placeholder with the name of your module. Place the HTML file in your module's ./resources/views directory. Edit the queryName config parameter to match the sample set's name.
For a detailed reference on query metadata, see tableInfo.xsd