Tables and queries can have associated XML files that carry additional
metadata information about the columns in the query. Example uses of query metadata include:
- Data display formatting
- Define the column display title
- Add URLs to data in cells
- Add custom buttons menu items that navigate to other pages or call JavaScript methods.
- Disable the standard insert, update, and delete buttons.
- Color coding for values that fall within a numeric range
- Configure lookups on columns
- Create an alias field, a duplicate of an existing field to which you can apply independent metadata properties
You can edit or add to this metadata using the following options covered in this topic:
Another way to edit query metadata is using an XML file in a module. Learn more in this topic:
Modules: Query Metadata
Edit Metadata Using the User Interface
The metadata editor offers a subset of the features available in the
field editor and works in the same way offering fields with properties, configuration options, and advanced settings.
- Open the schema browser via (Admin) > Developer Links > Schema Browser.
- Select an individual query/table in the Query Schema Browser and click Edit Metadata.
- Use the field editor interface to adjust fields and their properties.
- Note that there are some settings that cannot be edited via metadata override.
- For instance, you cannot change the PHI level of a field in this manner, so that option is inactive in the field editor.
- You also cannot delete fields here, so will not see the icon.
- Expand a field by clicking the on the right. It will become a .
- 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 Temp" (instead of "Average Temp").
- You could directly Edit Source or View Data from this interface.
- You can also click Reset to Default to discard any changes.
- If you were viewing a built-in table or query, you would also see an 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:
<tables xmlns="http://labkey.org/data/xml">
<table tableName="TestDataset" tableDbType="NOT_IN_DB">
<columns>
<column columnName="date">
<isHidden>true</isHidden>
</column>
</columns>
</table>
</tables>
Other metadata elements and attributes are listed in the tableInfo.xsd schema available in the
XML Schema Reference.
Note 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.
For additional information about query metadata and the order of operations, see
Modules: Query Metadata.
Alias Fields
Alias fields are wrapped duplicates of an existing field in a query, to which you can apply independent metadata properties.
You can use alias fields to display the same data in alternative ways, or alternative data, such as mapped values or id numbers. Used in conjunction with lookup fields, an alias field lets you display different columns from a target table. An example use case is described in the following support board discussion:
Specimen Repository Alias GlobalUniqueID.
To create an alias field:
- Click Alias Field on the Edit Metadata page.
- In the popup dialog box, select a field to wrap and click Ok.
- A wrapped version of the field will be added to the field list.
- You can make changes to relabel or add properties as needed.
- Notice that the details column indicates which column is wrapped by this alias.
Related Topics
