• Auditing Level
• Conditional Formatting
• Include Row Selection Checkboxes
• Hide a Field from Users
• Show/Hide Columns on Insert and/or Update
• Use a Format String for Display
• Use a Text Expression for Display
• Set a Display Width for a Column
• Display Columns in Specific Order in Entry Forms
• Set PHI Level
• URL to Open in New Tab
• URL Thumbnail Display
• Suppress URL and Details Links

• Join a Query to a Table
• Lookup to a Custom SQL Query (Mark a Key Field)
• Lookup Display Column
• Lookup to Query in Another Folder
• Filtered Lookups
• Lookup Sort Order
• Multi-value Foreign Keys (Lookups)

• Downloadable Examples

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

Conditional formats can be specified (1) in the field editor interface (the table definition) and/or (2) as part of a table's metadata XML. When specified in both places, the metadata XML takes precedence over the table definition.

In the metadata XML, it is important that the <filters> element is placed first within the <conditionalFormat> element. Modifiers to apply to that filter result, such as bold, italics, and colors must follow the <filters> element.

The following adds a yellow background color to any cells showing a "Pulse" 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>

Learn more:

• Conditional Formats
• XML elements available for conditional formats
• tableInfo.xsd - The XSD/XML source for table metadata.

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>

Hide a Field From Users

If you want to hide a field from users, such as in the grid customizer, 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>

Setting <isHidden> to false will show the column in question in places like the grid view customizer.

Show/Hide Columns on Insert and/or Update

You can control the visibility of columns when users insert and/or update into your queries using syntax like the following:

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="MyTableName" tableDbType="NOT_IN_DB">
    <columns>
        <column columnName="ReadOnlyColumn"> 
          <shownInInsertView>false</shownInInsertView>
          <shownInUpdateView>false</shownInUpdateView>
      </column>
    </columns>
  </table>
</tables>

Be aware that if you don't show a required field to users during insert, they will not actually be able to insert data unless the value is otherwise provided.

Two other XML parameters related to hiding columns and making values editable are:

<isHidden>true</isHidden>
  <isUserEditable>false</isUserEditable>

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.

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

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>

Set PHI Level

As an alternative to using the UI, you can assign a PHI level to a column in the schema definition XML file. In the example below, the column DeathOrLastContactDate has been marked as "Limited":

<column columnName="DeathOrLastContactDate">
    <formatString>Date</formatString>
    <phi>Limited</phi>
</column>

Possible values are:

• NotPHI
• Limited
• PHI
• Restricted

Review the PHI XML Reference.

URL to Open in New Tab

A field can use a URL property to open a target URL when the value is clicked. To make this target open in a new tab, use the following syntax to set the target="_blank" attribute. In this example, the link URL has been set using the UI for the "City" column:

<column columnName="City">
  <urlTarget>_blank</urlTarget>
</column>

URL Thumbnail Display

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

Suppress URL and Details Links

Each row in a data grid displays two links to a details page.

To suppress these links, add an empty tableURL element <tableUrl/> to the table XML metadata. You can also use this option to suppress linking on a table that looks up into this one.

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="Technicians" tableDbType="NOT_IN_DB">
    <tableUrl></tableUrl>
    <columns>
    </columns>
  </table>
</tables>

Join a Query to a Table

If you have a query providing calculations for a table, you can add a field alias to the table, then use that to join in your query. In this example, the query "BMI Query" is providing calculations. We added an alias of the "lsid" field, named it "BMI" and then added this metadata XML making that field a foreign key (fk) to the "BMI Query".

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

Once Physical Exam is linked to BMI Query via this foreign key, all of the columns in BMI Query are made available in the grid customizer. Click Show Hidden Fields to see your wrapped field (or include <isHidden>false</isHidden>), and expand it to show the fields you want.

Because these fields are calculated from other data in the system, you will not see them when inserting (or editing) your table.

Lookup to a Custom SQL Query (Mark a Key Field)

If you want to create a lookup to a SQL Query that you have written, first annotate a column in the query as the primary key field, for example:

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

You can then add a field in a table and create a lookup into that query. This can be used both for controlling user input (the user will see a dropdown if you include this field in the insert/update view) as well as for surfacing related columns from the query.

Lookup Display Column

By default, a lookup will display the first non-primary-key text column in the lookup target. This may result in unexpected results, depending on the structure of your lookup target. To control which display column is used, supply the fkDisplayColumnName directly in the XML metadata for the table where you want it displayed:

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="Demographics" tableDbType="NOT_IN_DB">
    <columns>
      <column columnName="PrimaryPhysician">
        <fk>
          <fkDisplayColumnName>DesiredDisplayColumnName</fkDisplayColumnName>
          <fkTable>ListName</fkTable>
          <fkDbSchema>lists</fkDbSchema>
        </fk>
      </column>
    </columns>
  </table>
</tables>

Lookup to Query in Another Folder

If the target query is in another container, you can use the <fkFolderPath> to specify another path on the same server as in this example:

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="Demographics" tableDbType="NOT_IN_DB">
    <columns>
      <column columnName="PrimaryPhysician">
        <fk>
          <fkFolderPath>/ProjectName/FolderName</fkFolderPath>
          <fkTable>ListName</fkTable>
          <fkDbSchema>lists</fkDbSchema>
        </fk>
      </column>
    </columns>
  </table>
</tables>

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.

Lookup Sort Order

When you include a lookup, the values presented to the user will be sorted by the lookup's display column by default. If you want to present them to the user in a different order, you can choose another column to use for sorting the values.

For example, consider a simple list with these values, in this example it is named "CustomSortedList":

If you have another table looking up into this list for selecting from "theDisplay", users will see a dropdown with "a, b, c, d" listed in that order. If instead you want to present the user "c, b, d, a" in that order, you'd use this XML on the target list (above) in order to force the sorting to be on the "theOrder" column. When you view the list, you will see the rows displayed in this custom order. Note that no custom XML is needed on the table that includes the lookup to this list.

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="CustomSortedList" tableDbType="NOT_IN_DB">
    <columns>
        <column columnName="theDisplay">
        <sortColumn>theOrder</sortColumn>
      </column>
    </columns>
  </table>
</tables>

Multi-Value Foreign Keys (Lookups)

LabKey has basic support for multi-value foreign keys. The multi-valued column renders as a comma separated list in the grid and as a JSON array when using the client API. To set up a multi-value foreign key you need a source table, a junction table, and a target value table. The junction table needs to have a foreign key pointing at the source table and another pointing at the target value table.

For example, you can create a Reagent DataClass table as the source, a ReagentSpecies List as the junction table, and a Species List as the lookup target table. Once the tables have been created, edit the metadata XML of the source table to add a new wrapped column over the primary key of the source table and create a lookup that targets the junction table. The wrapped column has a foreign key with these characteristics:

1. Annotated as multi-valued (<fkMultiValued>junction</fkMultiValued>).
2. Points to the junction table's column with a foreign key back to the source table (fkColumnName).
3. Indicates which foreign key of the junction table should be followed to the target value table (fkJunctionLookup).
4. This example shows the wrappedColumnName "Key", which is the default used for a list. If you are wrapping a column in a DataClass, the wrappedColumnName will be "RowId".

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="Book" tableDbType="NOT_IN_DB">
    <columns>
      <column columnName="Authors" wrappedColumnName="Key"> 
        <isHidden>false</isHidden>
        <isUserEditable>true</isUserEditable>
        <shownInInsertView>true</shownInInsertView>
        <shownInUpdateView>true</shownInUpdateView>
        <fk>
          <fkDbSchema>lists</fkDbSchema>
          <fkTable>BookAuthorJunction</fkTable>
          <fkColumnName>BookId</fkColumnName>
          <fkMultiValued>junction</fkMultiValued>
          <fkJunctionLookup>AuthorId</fkJunctionLookup>
        </fk>
      </column>
    </columns>
  </table>
</tables>

When viewing the multi-value foreign key in the grid, the values will be separated by commas. When using the query APIs such as LABKEY.Query.selectRows, set requiredVersion to 16.2 or greater:

LABKEY.Query.selectRows({
  schemaName: 'lists',
  queryName: 'Book',
  requiredVersion: 17.1
});

The response format will indicate the lookup is multi-valued in the metaData section and render the values as a JSON array. The example below has been trimmed for brevity:

{
  "schemaName" : [ "lists" ],
  "queryName" : "Book",
  "metaData" : {
    "id" : "Key",
    "fields" : [ {
      "fieldKey" : [ "Key" ]
    }, {
      "fieldKey" : [ "Name" ]
    }, {
      "fieldKey" : [ "Authors" ],
      "lookup" : {
        "keyColumn" : "Key",
        "displayColumn" : "AuthorId/Name",
        "junctionLookup" : "AuthorId",
        "queryName" : "Author",
        "schemaName" : [ "lists" ],
        "multiValued" : "junction"
      }
    } ]
  },
  "rows" : [ {
    "data" : {
      "Key" : { "value" : 1 },
      "Authors" : [ {
        "value" : 1, "displayValue" : "Terry Pratchett"
       }, {
        "value" : 2, "displayValue" : "Neil Gaiman"
       } ],
      "Name" : { "value" : "Good Omens" }
    }
  }, {
    "data" : {
      "Key" : { "value" : 2 },
      "Authors" : [ {
        "value" : 3, "displayValue" : "Stephen King"
      }, {
        "value" : 4, "displayValue" : "Peter Straub"
      } ],
      "Name" : { "value" : "The Talisman" }
    }
  } ],
  "rowCount" : 2
}

There are some limitations of the multi-value foreign keys, however. Inserts, updates, and deletes must be performed on the junction table separately from the insert into either the source or target table. In future versions of LabKey Server, it may be possible to perform an insert on the source table with a list of values and have the junction table populated for you. Another limitation is that lookups on the target value are not supported. For example, if the Author had a lookup column named "Nationality", you will not be able to traverse the Book.Author.Nationality lookup.

Downloadable 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

• Query Metadata
• Override Column Metadata with SQL Annotations
• Query Metadata XML Reference
• Custom Button Bars
• Premium Resource: Display Calculated Columns from Queries (Premium Resource)