Table of Contents

guest
2020-11-27
       Query Metadata
         Query Metadata: Examples

Query Metadata


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




Query Metadata: Examples


This topic provides examples of query metadata used to change the display and add other behavior to queries.

Auditing Level

Set the level of detail recorded in the audit log. The example below sets auditing to "DETAILED" on the Physical Exam table.

<tables xmlns="http://labkey.org/data/xml">
<table tableName="Physical Exam" tableDbType="NOT_IN_DB">
<auditLogging>DETAILED</auditLogging>
<columns>
...
</columns>
</table>
</tables>

Conditional Formatting

The following adds a yellow background color to any cells showing a value greater than 72.

<tables xmlns="http://labkey.org/data/xml">
<table tableName="Physical Exam" tableDbType="NOT_IN_DB">
<columns>
<column columnName="Pulse">
<columnTitle>Pulse</columnTitle>
<conditionalFormats>
<conditionalFormat>
<filters>
<filter operator="gt" value="72" />
</filters>
<backgroundColor>FFFF00</backgroundColor>
</conditionalFormat>
</conditionalFormats>
</column>
</table>
</tables>

Wrapping/Surfacing Calculated Columns

The following example shows how to surface columns of calculated values in other datasets.

Suppose you have weight data recorded in one dataset (Physical Exam) and height data recorded in another dataset (Demographics). You want to accomplish three things with this data: (1) convert the height data, which is records in inches, into meters, (2) calculate the BMI (Body Mass Index) from the height and weight data, and (3) surface these calculated columns in the Physical Exam dataset.

The following query accomplishes the first two tasks, by including two calculated columns:

  • Height_m (which converts the base data in inches to meters)
  • BMI (which uses the formula Height/Weight^2 to calculate the BMI)
BMI Query
SELECT "Physical Exam".ParticipantId,
"Physical Exam".LSID,
"Physical Exam".Weight_kg,
Datasets.Demographics.Height_inch*0.0254 AS Height_m,
Weight_kg/POWER(Datasets.Demographics.Height_inch*0.0254, 2) AS BMI
FROM "Physical Exam"

How do you accomplish the third task, surfacing the calculated columns so they can be displayed in the Physical Exam table?

To do this, use query metadata to wrap the BMI query as a lookup (= foreign key) from the Physical Exam dataset. Notice that we have included the LSID column in the BMI query to function as a key column. The following metadata on Physical Exam adds a wrapped column over LSID as a lookup to the BMI query.

Query Metadata on Physical Exam

<tables xmlns="http://labkey.org/data/xml">
<table tableName="Physical Exam" tableDbType="NOT_IN_DB">
<columns>
<column columnName="BMI" wrappedColumnName="lsid">
<fk>
<fkDbSchema>study</fkDbSchema>
<fkTable>BMI Query</fkTable>
<fkColumnName>LSID</fkColumnName>
</fk>
</column>
</columns>
</table>
</tables>

Now that Physical Exam is linked to BMI Query via the lookup/foreign key, all of the columns in BMI Query are made available to the grid customization GUI:

Lookup Display Column

The following example shows how to specify an alternate display column for a lookup field.

<tables xmlns="http://labkey.org/data/xml">
<table tableName="Demographics" tableDbType="NOT_IN_DB">
<columns>
<column columnName="Gender">
<columnTitle>Gender Code</columnTitle>
<fk>
<fkDisplayColumnName>GenderCode</fkDisplayColumnName>
<fkTable>Genders</fkTable>
<fkDbSchema>lists</fkDbSchema>
</fk>
</column>
</columns>
</table>
</tables>

Include Row Selection Checkboxes

To include row selection checkboxes in a query, include this:

<tables xmlns="http://labkey.org/data/xml">
<table tableName="Demographics" tableDbType="NOT_IN_DB">
<columns>
<column columnName="RowId">
<isKeyField>true</isKeyField>
</column>
</columns>
</table>
</tables>

URL Display Columns

Using query metadata provided in a module, you can display images with optional custom thumbnails in a data grid.

For example, if you have a store of PNG images and want to display them in a grid, you can put them in a fixed location and refer to them from the grid by URL. The column type in the grid would be a text string, but the grid would display the image. Further, you could provide separate custom thumbnails instead of the miniature version LabKey would generate by default.

The module code would use a displayColumnFactory similar to this example, where the properties are defined below.

<column columnName="image"> 
<displayColumnFactory>
<className>org.labkey.api.data.URLDisplayColumn$Factory</className>
<properties>
<property name="thumbnailImageWidth">55px</property>
<property name="thumbnailImageUrl">_webdav/my%20studies/%40files/${image}</property>
<property name="popupImageUrl">_webdav/my%20studies/%40files/${popup}</property>
<property name="popupImageWidth">250px</property>
</properties>
</displayColumnFactory>
<url>/labkey/_webdav/my%20studies/%40files/${popup}</url>
</column>

Properties available in a displayColumnFactory:

  • thumbnailImageUrl (optional) - The URL string expression for the image that will be displayed inline in the grid. If the string expression evaluates to null, the column URL will be used instead. Defaults to the column URL.
  • thumbnailImageWidth (optional) - The size of the image that will be displayed in the grid. If the property is present but the value is empty (or “native”), no css scaling will be applied to the image. Defaults to “32px”.
  • thumbnailImageHeight (optional) - The image height
  • popupImageHeight (optional) - The image height
  • popupImageUrl (optional) - The URL string expression for the popup image that is displayed on mouse over. If not provided, the thumbnail image URL will be used if the thumbnail image is not displayed at native resolution, otherwise the column URL will be used.
  • popupImageWidth (optional) - The size of the popup image that will be displayed. If the property is present but the value is empty (or “native”), no css scaling will be applied to the image. Defaults to “300px”.
Using the above example, two images would exist in the 'mystudies' file store: a small blue car image (circled) provided as the thumbnail and the white car image provided as the popup.

Filtered Lookups

If you want to filter a lookup column during an insert or update operation, you can do so using XML metadata.

For example, consider a list of instruments shared across many departments, each one associated with a specific type of assay (such as elispot) and marked as either in use or not. If your department wants an input form to draw from this shared list, but avoid having an endless menu of irrelevant instruments, you can limit insert choices for an "Instrument" column to only those for the 'elispot' assay that are currently in use by using a filterGroup:

<column columnName="Instrument">
<fk>
<fkColumnName>key</fkColumnName>
<fkTable>sharedInstrumentList</fkTable>
<fkDbSchema>lists</fkDbSchema>
<filters>
<filterGroup>
<filter column="assay" value="elispot" operator="eq"/>
<filter column="inuse" value="true" operator="eq"/>
</filterGroup>
</filters>
</fk>
</column>

In the above example, the same filter is applied to both insert and update. If you wanted to have different behavior for these two operations, you could separate them like this:

<column columnName="Instrument">
<fk>
<fkColumnName>key</fkColumnName>
<fkTable>sharedInstrumentList</fkTable>
<fkDbSchema>lists</fkDbSchema>
<filters>
<filterGroup operation="insert">
<filter column="assay" value="elispot" operator="eq"/>
<filter column="inuse" value="true" operator="eq"/>
</filterGroup>
<filterGroup operation="update">
<filter column="inuse" value="true" operator="eq"/>
</filterGroup>
</filters>
</fk>
</column>

Filter groups like this can be applied to a list, dataset, or any other table that can accept an XML metadata override.

Other Examples

  • kinship.query.xml
    • Disables the standard insert, update, and delete buttons/links with the empty <insertUrl /> and other tags.
    • Configures lookups on a couple of columns and hides the RowId column in some views.
    • Adds a custom button "More Actions" with a child menu item "Limit To Animals In Selection" that calls a JavaScript method provided in a referenced .js file.
  • 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.
  • encounter_participants.query.xml
  • AssignmentOverlaps.query.xml
  • Aliquots.query.xml & performCellSort.html
    • Adds a button to the Sample Types 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 type's name. Edit the tableName attribute in the Aliquots.query.xml to match your sample type'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 type's name.

Related Topics