Query Metadata

2024-03-28

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:
  • Column titles and data display formatting
  • Add URLs to data in cells or lookups to other tables
  • Add custom buttons that navigate to other pages or call JavaScript methods, or disable the standard buttons
  • Color coding for values that fall within a numeric range
  • Create an alias field, a duplicate of an existing field to which you can apply independent metadata properties

Topics

You can edit or add to this metadata using a variety of options:

Another way to customize 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) > Go To Module > Query.
  • Select an individual schema and query/table in the 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 . It will become a .
  • To change a column's displayed title, edit the Label property.
  • In the image above, the displayed text for the column has been changed to the longer "Average Temperature".
  • 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).
  • Edit as desired. A few examples are below.
  • Click the Data tab to test your syntax and see the results.
  • Return to the XML Metadata tab and click Save & Finish when finished.

Examples:

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.

Apply a Conditional Format

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.

Hide a Field From Users

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>

Use a Format String for Display

The stored value in your data field may not be how you wish to display that value to users. For example, you might want to show DateTime values as only the "Date" portion, or numbers with a specified number of decimal places. These options can be set on a folder- or site-wide basis, or using the field editor in the user interface as well as within the query metadata as described here.

Use a <formatString> in your XML to display a column value in a specific format. They are supported for SQL metadata, dataset import/export and list import/export. The formatString is a template that specifies how to format a value from the column on display output (or on export if the corresponding excelFormatString and tsvFormatString values are not set).

Use formats specific to the field type:

  • Date: DateFormat
  • Number: DecimalFormat
  • Boolean: Values can be formatted using a template of the form positive;negative;null, where "positive" is the string to display when true, "negative" is the string to display when false, and "null" is the string to display when null.
For example, the following XML:
<tables xmlns="http://labkey.org/data/xml">
<table tableName="formattingDemo" tableDbType="NOT_IN_DB">
<columns>
<column columnName="DateFieldName">
<formatString>Date</formatString>
</column>
<column columnName="NumberFieldName">
<formatString>####.##</formatString>
</column>
<column columnName="BooleanFieldName">
<formatString>Oui;Non;Nulle</formatString>
</column>
</columns>
</table>
</tables>

...would make the following formatting changes to fields in a list:

Use a Text Expression for Display

The stored value in your data field may not be how you wish to present that data to users. For example, you might store a "completeness" value but want to show the user a bit more context about that value. Expressions are constructed similar to sample naming patterns using substitution syntax. You can pull in additional values from the row of data as well and use date and number formatting patterns.

For example, if you wanted to store a weight value in decimal, but display in sentence form you might something like the following XML for that column:

<column columnName="weight">
<textExpression>${ParticipantID} weighed ${weight} Kg in ${Date:date('MMMM')}.</textExpression>
</column>

This example table would look like the first three columns of the following, with the underlying value stored in the weight column only the numeric portion of what is displayed:

ParticipantIDDateWeight(Not shown, the stored value for "Weight" column)
PT-1112021-08-15PT-111 weighed 69.1 Kg in August.69.1
PT-1072021-04-25PT-107 weighed 63.2 Kg in April.63.2
PT-1082020-06-21PT-108 weighed 81.9 Kg in June.81.9

Set a Display Width for a Column

If you want to specify a value for the display width of one of your columns (regardless of the display length of the data in the field) use syntax like the following. This will make the Description column display 400 pixels wide:

<column columnName="Description">
<displayWidth>400</displayWidth>
</column>

Display Columns in Specific Order in Entry Forms

The default display order of columns in a data entry form is generally based on the underlying order of columns in the database. If you want to present columns in a specific order to users entering data, you can rearrange the user-defined fields using the field editor. If there are built in fields that 'precede' the user-defined ones, you can use the useColumnOrder table attribute. When set to true, columns listed in the XML definition will be presented first, in the order in which they appear in the XML. Any columns not specified in the XML will be listed last in the default order.

For example, this XML would customize the entry form for a "Lab Results" dataset to list the ViralLoad and CD4 columns first, followed by the 'standard' study columns like Participant and Timepoint. Additional XML customizations can be applied to the ordered columns, but are not required.

<tables xmlns="http://labkey.org/data/xml">
<table tableName="Lab Results" tableDbType="NOT_IN_DB" useColumnOrder="true">
<columns>
<column columnName="ViralLoad"/>
<column columnName="CD4"/>
</columns>
</table>
</tables>

XML Metadata Filters

The filtering expressions available for use in XML metadata are listed below.

XML operatorDescription
eqEquals
dateeqEquals for date fields
dateneqDoes not equal, for date fields
dateltLess than, for date fields
datelteLess than or equal to, for date fields
dategtGreater than, for date fields
dategteGreater than or equal to, for date fields
neqornullNot equal to or null
neqDoes not equal
isblankIs blank
isnonblankIs not blank
gtGreater than
ltLess than
gteGreater than or equal to
lteLess than or equal to
betweenBetween two provided values
notbetweenNot between two provided values
containsContains a provided substring
doesnotcontainDoes not contain a provided substring
doesnotstartwithDoes not start with a provided substring
startswithStarts with a provided substring
inEquals one of a list you provide
hasmvvalueHas a missing value
nomvvalueDoes not have a missing value
inornullEither equals one of a list you provide or is null
notinDoes not equal any of a list you provide
notinornullDoes not equal any of a list you provide or is null
containsoneofContains one of a list of substrings you provide
containsnoneofDoes not contain any of the list of substrings you provide
memberofIs a member of a provided user group
exp:childofIs a child of
exp:lineageofIs in the lineage of
exp:parentofIs a parent of
concept:subclassofOntology module: Is a subclass of a provided concept
concept:insubtreeOntology module: Is in the subtree under a provided concept
concept:notinsubtreeOntology module: Is not in the subtree under a provided concept
qProvide a search string; matches will be returned.
whereEvaluating a provided expression returns true.
inexpancestorsofSee Lineage SQL Queries.
inexpdescendantsofSee Lineage SQL Queries.

Learn more about filtering expressions generally in this topic: Filtering Expressions.

Use SQL Annotations to Apply Metadata

You can directly specify some column metadata using annotations in your SQL statements, instead of having to separately add metadata via XML. These annotations are case-insensitive and can be used with or without a preceding space, i.e. both "ratio@HIDDEN" and "ratio @hidden" would be valid versions of the first example.

@hidden is the same as specifying XML <isHidden>true</isHidden>

SELECT ratio @hidden, log(ratio) as log_ratio

@nolookup is the same as <displayColumnFactory><className>NOLOOKUP</className></displayColumnFactory>. This will show the actual value stored in this field, instead of displaying the lookup column from the target table.

SELECT modifiedby, modifiedby AS userid @nolookup

@title='New Title': Explicitly set the display title/label of the column

SELECT ModifiedBy @title='Previous Editor'

@preservetitle: Don't compute title based on alias, instead keep the title/label of the selected column. (Doesn't do anything for expressions.)

SELECT ModifiedBy as PreviousEditor @preservetitle

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.

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.

You can also apply independent XML metadata to this wrapped field, such as lookups and joins to other queries.

Troubleshooting

In cases where the XSD for a given type includes a <sequence> element, the order in which you specify elements matters. If you have elements out of order, you may see an error suggesting that you need instead whatever the "next" expected element could be.

Learn more with the example in this topic:

Related Topics