Field Types and Properties: /Documentation

Field Types and Properties

The set of fields that make up a data structure like a list, dataset, assay, etc. can be edited using the Field Editor interface. You will find instructions about using the field editor and using properties, options, and settings common to all field types in the topic: Field Editor

This topic outlines the field formatting and properties specific to each data type.

Field Types Available by Data Structure

Fields come in different types, each intended to hold a different kind of data. Once defined, there are only limited ways you can change a field's type, based on the ability to convert existing data to the new type. To change a field type, you may need to delete and recreate it, reimporting any data.

The following table show which fields are available in which kind of table/data structure. Notice that Datasets do not support Attachment fields. For a workaround technique, see Linking Data Records to Image Files.

Field Type	Dataset	List	Sample Type	Assay Design	Data Class
Text (String)	Yes	Yes	Yes	Yes	Yes
Text Choice	Yes	Yes	Yes	Yes	Yes
Multi-Line Text	Yes	Yes	Yes	Yes	Yes
Boolean	Yes	Yes	Yes	Yes	Yes
Integer	Yes	Yes	Yes	Yes	Yes
Decimal (Floating Point)	Yes	Yes	Yes	Yes	Yes
DateTime	Yes	Yes	Yes	Yes	Yes
Date	Yes	Yes	Yes	Yes	Yes
Time	Yes	Yes	Yes	Yes	Yes
Calculation	Yes	Yes	Yes	Yes	Yes
Flag	Yes	Yes	Yes	Yes	Yes
File	Yes	No	Yes	Yes	No
Attachment	No (workaround)	Yes	No	No	Yes
User	Yes	Yes	Yes	Yes	Yes
Subject/Participant(String)	Yes	Yes	Yes	Yes	Yes
Lookup	Yes	Yes	Yes	Yes	Yes
Sample	Yes	Yes	Yes	Yes	Yes
Ontology Lookup	Yes	Yes	Yes	Yes	Yes
Visit Date/Visit ID/Visit Label	No	No	Yes	No	No
Unique ID	No	No	Yes	No	No

The SMILES field type is only available in the Compounds data class when using LabKey Biologics LIMS.

Changes Allowed by Field Type

Once defined, there are only limited ways you can change a field's data type, based on the ability to safely convert existing data to the new type. This table lists the changes supported (not all types are available in all data structures and contexts):

Current Type	May Be Changed To:
Text (String)	Text Choice, Multi-line Text, Flag, Lookup, Ontology Lookup, Subject/Participant
Text Choice	Text, Multi-Line Text, Flag, Lookup, Ontology Lookup, Subject/Participant
Multi-Line Text	Text, Text Choice, Flag, Subject/Participant
Boolean	Text, Multi-Line Text
Integer	Decimal, Lookup, Text, Multi-Line Text, Sample, User
Decimal (Floating Point)	Text, Multi-Line Text
DateTime	Text, Multi-Line Text, Visit Date, Date (the time portion is dropped), Time (the date portion is dropped)
Date	Text, Multi-line Text, Visit Date, DateTime
Time	Text, Multi-line Text
Calculation	Cannot be changed
Flag	Text, Text Choice, Multi-Line Text, Lookup, Ontology Lookup, Subject/Participant
File	Text, Multi-Line Text
Attachment	Text, Multi-Line Text
User	Integer, Decimal, Lookup, Text, Multi-Line Text, Sample
Subject/Participant(String)	Flag, Lookup, Text, Text Choice, Multi-Line Text, Ontology Lookup
Lookup	Text, Multi-Line Text, Integer, Sample, User
Sample	Text, Multi-Line Text, Integer, Decimal, Lookup, User
Ontology Lookup	Text, Text Choice, Multi-Line Text, Flag, Lookup, Subject/Participant
Visit Date	Text, Multi-Line Text, Date Time
Visit ID	Text, Multi-Line Text, Decimal
Visit Label	Text, Text Choice, Multi-Line Text, Lookup, Ontology Lookup, Subject/Participant
Unique ID	Flag, Lookup, Multi-Line Text, Ontology Lookup, Subject/Participant, Text, Text Choice

If you cannot make your desired type change using the field editor, you may need to delete and recreate the field entirely, reimporting any data.

Validators Available by Field Type

This table summarizes which formatting and validators are available for each type of field.

Field Type	Conditional Formatting	Regex Validators	Range Validators
Text (String)	Yes	Yes	No
Text Choice	Yes	No	No
Multi-Line Text	Yes	Yes	No
Boolean	Yes	No	No
Integer	Yes	No	Yes
Decimal (Floating Point)	Yes	No	Yes
DateTime	Yes	No	Yes
Date	Yes	No	Yes
Time	Yes	No	No
Calculation	Yes	No	No
Flag	Yes	Yes	No
File	Yes	No	No
Attachment	Yes	No	No
User	Yes	No	Yes
Subject/Participant(String)	Yes	Yes	No
Lookup	Yes	No	Yes
Sample	Yes	No	No
Ontology Lookup	Yes	Yes	No
Visit Date/Visit ID/Visit Label	Yes	No	No
Unique ID	Yes	No	No

Type-Specific Properties and Options

The basic properties and validation available for different types of fields are covered in the main field editor topic. Details for specific types of fields are covered here.

Text, Multi-Line Text, and Flag Options

Fields of type Text, Multi-Line Text, and Flag have the same set of properties and formats available:

Text Options/Multi-line Text Field Options/Flag Options: Maximum Text Length. Sets the maximum character count for the field. Options are:

Unlimited. Learn more below.
No longer than ___ characters: provide a value in the box. The default is 4000.

Name and Linking Options
Conditional Formatting and Validation Options: Conditional formats and regular expression validators.

When setting the Maximum Text Link, consider that when you make a field UNLIMITED, it is stored in the database as 'text' instead of the more common 'varchar(N)'. When the length of a string exceeds a certain amount, the text is stored out of the row itself. In this way, you get the illusion of an infinite capacity. Because of the differences between 'text' and 'varchar' columns, these are some good general guidelines:

UNLIMITED is good if:

You need to store large text (like, a paragraph or more)
You don’t need to index the column
You will not join on the contents of this column
Examples: blog comments, wiki pages, etc.

No longer than... (i.e. not-UNLIMITED) is good if:

You're storing smaller strings
You'll want them indexed, i.e. you plan to search on string values
You want to do a sql select or join on the text in this column
Examples would be usernames, filenames, etc.

Text Choice Options

A Text Choice field lets you define a set of values that will be presented to the user as a dropdown list. This is similar to a lookup field, but does not require a separate list created outside the field editor. Click Add Values to open a panel where you can add the choices for this field.

Text Choice Options:

Add and manage the set of Drop-down values offered for this field.
Details are available here: Text Choice Fields

Name and Linking Options
Conditional Formatting Options: Conditional formats are available.

Learn more about defining and using Text Choice fields in this topic:

Text Choice Fields

Note that because administrators can always see all the values offered for a Text Choice field, they are not a good choice for storing PHI or other sensitive information. Consider instead using a lookup to a protected list.

Boolean Options

Boolean Field Options: Format for Boolean Values: Use boolean formatting to specify the text to show when a value is true and false. Text can optionally be shown for null values. For example, "Yes;No;Blank" would output "Yes" if the value is true, "No" if false, and "Blank" for a null value.
Name and Linking Options
Conditional Formatting and Validation Options: Conditional formats are available.

Integer and Decimal Options

Integer and Decimal fields share similar number formatting options, shown below. When considering which type to choose, keep in mind the following behavior:

Integer: A 4-byte signed integer that can hold values ranging -2,147,483,648 to +2,147,483,647.
Decimal (Floating Point): An 8-byte double precision floating point number that can hold very large and very small values. Values can range approximately 1E-307 to 1E+308 with a precision of at least 15 digits. As with most standard floating point representations, some values cannot be converted exactly and are stored as approximations. It is often helpful to set on Decimal fields a display format that specifies a fixed or maximum number of decimal places to avoid displaying approximate values.

Both Integer and Decimal columns have these formatting options available:

Integer Options/Decimal Options:

Format for Numbers: To control how a number value is displayed, provide a string format compatible with the Java class DecimalFormat. Learn more about using Number formats in LabKey.
Default Scale Type: Choose Linear or Log.

Name and Linking Options
Conditional Formatting and Validation Options: Conditional formats and range validators are available.

Date, Time, and Date Time Options

Three different field types are available for many types of data structure, letting you choose how best to represent the data needed.

Date Time: Both date and time are included in the field. Fields of this type can be changed to either "Date-only" or "Time-only" fields, though this change will drop the data in the other part of the stored value.
Date: Only the date is included. Fields of this type can be changed to be "Date Time" fields.
Time: Only the time portion is represented. Fields of this type cannot be changed to be either "Date" or "Date Time" fields.

Date and Time Options:

Use Default: Check the box to use the default format set for this folder.
Format for Date Times: Uncheck the default box to enable a drop down where you can select the specific format to use for dates/times in this field. Learn more about using Date and Time formats in LabKey.

Name and Linking Options
Conditional Formatting and Validation Options: Conditional formats are available for all three types of date time fields. Range validators are available for "Date" and "Date Time" but not for "Time" fields.

Calculation (Premium Feature)

Calculation fields are available with the Professional and Enterprise Editions of LabKey Server, the Professional Edition of Sample Manager, LabKey LIMS, and Biologics LIMS.

A calculation field lets you include SQL expressions using values in other fields in the same row to provide calculated values. The Expression provided must be valid LabKey SQL and can use the default system fields, custom fields, constants, operators, and functions. To use field names containing special characters in a calculated field, surround the name with double quotes. String constants use single quotes. Examples:

Operation	Example
Addition	numericField1 + numericField2
Subtraction	numericField1 - numericField2
Multiplication	numericField1 * numericField2
Division by value known never to be zero	numericField1 / nonZeroField1
Division by value that might be zero	CASE WHEN numericField2 <> 0 THEN (numericField1 / numericField2 * 100) ELSE NULL END
Subtraction of dates/datetimes (ex: difference in days)	TIMESTAMPDIFF('SQL_TSI_DAY', CURDATE(), MaterialExpDate)
Relative date (ex: 1 week later)	TIMESTAMPADD('SQL_TSI_WEEK', 1, dateField)
Conditional calculation based on another field	CASE WHEN FreezeThawCount < 2 THEN 'Viable' ELSE 'Questionable' END
Conditional calculation based on a text match	CASE WHEN ColorField = 'Blue' THEN 'Abnormal' ELSE 'Normal' END
Text value for every row (ex: to use with a URL property)	'clickMe'
Text concatenation (use fields and/or strings)	City \|\| ', ' \|\| State
Addition when field name includes special characters	"Numeric Field Name" + "Field/Name & More"

Once you've provided the expression, use Click to validate to confirm that your expression is valid.

The data type of your expression will be calculated. In some cases, you may want to use casts to influence this type determination. For example, if you divide two integer values, the result will also be of type integer and yield unexpected and apparently "truncated" results.

Once the type of the calculation is determined, you'll see additional formatting options for the calculated type of the field. Shown above, date and time options.

Name and Linking Options
Conditional Formatting and Validation Options: Conditional formats are available.

File and Attachment Options

File and Attachment fields are only available in specific scenarios, and have display, thumbnail, and storage differences. For both types of field, you can configure the behavior when a link is clicked, so that the file or attachment either downloads or is shown in the browser.

Name and Linking Options
Conditional Formatting and Validation Options: Conditional formats are available.

File

The File field type is only available for certain types of table, including datasets, assay designs, and sample types.
When a file has been uploaded into this field, it displays a link to the file; for image files, an inline thumbnail is shown.
The uploaded file is stored in the file repository, in the assaydata folder in the case of an assay.
For Standard Assays, the File field presents special behavior for image files; for details see Linking Assays with Images and Other Files.
If you are using a pipeline override, note that it will override any file root setting, so the "file repository" in this case will be under your pipeline override location.

Attachment

The Attachment field type is similar to File, but only available for lists and data classes (including Sources for samples).
This type allows you to attach documents to individual records in a list or data class.
For instance, an image could be associated with a given row of data in an attachment field, and would show an inline thumbnail.
The attachment is not uploaded into the file repository, but is stored as a BLOB field in the database.
By default, the maximum attachment size is 50MB, but this can be changed in the Admin Console using the setting Maximum file size, in bytes, to allow in database BLOBs. See Site Settings.

Learn about using File and Attachment fields in Sample Manager and LabKey Biologics in this topic:

Attach Images and Other Files

Choose Download or View in Browser for Files and Attachments

For both File and Attachment fields, you can set the behavior when the link is clicked from within the LabKey Server interface. Choose Show in Browser or Download.

Note that this setting will not control the behavior of attachments and files in Sample Manager or Biologics LIMS.

Inline Thumbnails for Files and Attachments

When a field of type File or Attachment is an image, such as a .png or .jpg file, the cell in the data grid will display a thumbnail of the image. Hovering reveals a larger version.

When you export a grid containing these inline images to Excel, the thumbnails remain associated with the cell itself.

Bulk Import into the File Field Type

You can bulk import data into the File field type in LabKey Server, provided that the files/images are already uploaded to the File Repository, or the pipeline override location if one is set. For example suppose you already have a set of images in the File Repository, as shown below.

You can load these images into a File field, if you refer to the images by their full server path in the File Repository. For example, the following shows how an assay upload might refer to these images by their full server path:

ImageName	ImageFile
10001.png	http://localhost:8080/labkey/_webdav/Tutorials/List%20Tutorial/%40files/NIMH/Images/10001.png
10002.png	http://localhost:8080/labkey/_webdav/Tutorials/List%20Tutorial/%40files/NIMH/Images/10002.png
10003.png	http://localhost:8080/labkey/_webdav/Tutorials/List%20Tutorial/%40files/NIMH/Images/10003.png
10004.png	http://localhost:8080/labkey/_webdav/Tutorials/List%20Tutorial/%40files/NIMH/Images/10004.png

On import, the Assay grid will display the image thumbnail as shown below:

User Options

Fields of this type point to registered users of the LabKey Server system, found in the table core.Users and scoped to the current container (folder).

Name and Linking Options
Conditional Formatting and Validation Options: Conditional formats and range validators.

Subject/Participant Options

This field type is only available for Study datasets and for Sample Types that will be linked to study data. The Subject/Participant ID is a concept URI, containing metadata about the field. It is used in assay and study folders to identify the subject ID field. There is no special built-in behavior associated with this type. It is treated as a string field, without the formatting options available for text fields.

Name and Linking Options
Conditional Formatting and Validation Options: Conditional formats and regular expression validators.

Lookup Options

You can populate a field with data via lookup into another table. This is similar to the text choice field, but offers additional flexibility and longer lists of options.

Open the details panel and select the folder, schema, and table where the data values will be found. Users adding data for this field will see a dropdown populated with that list of values. Typing ahead will scroll the list to the matching value. When the number of available values exceeds 10,000, the field will be shown as a text entry field.

Use the checkbox to control whether the user entered value will need to match an existing value in the lookup target.

Lookup Definition Options:

Select the Target Folder, Schema, and Table from which to look up the value. Once selected, the value will appear in the top row of the field description as a direct link to the looked-up table.
Lookup Validator: Ensure Value Exists in Lookup Target. Check the box to require that any value is present in the lookup's target table or query.

Name and Linking Options
Conditional Formatting Options: Conditional formats are available.

A lookup operates as a foreign key (<fk>) in the XML schema generated for the data structure. An example of the XML generated:

<fk>
  <fkDbSchema>lists</fkDbSchema>
  <fkTable>Languages</fkTable>
  <fkColumnName>LanguageId</fkColumnName>
</fk>

Note that lookups into lists with auto-incrementing keys may not export/import properly because the rowIds are likely to be different in every database.

Learn more about Lookup fields in this topic:

Lookup Columns

Sample Options

Sample Options: Select where to look up samples for this field.

Note that this lookup will only be able to reference samples in the current container.
You can choose All Samples to reference any sample in the container, or select a specific sample type to filter by.
This selection will be used to validate and link incoming data, populate lists for data entry, etc.

Name and Linking Options
Conditional Formatting Options: Conditional formats are available.

Ontology Lookup Options (Premium Feature)

When the Ontology module is loaded, the Ontology Lookup field type connects user input with preferred vocabulary lookup into loaded ontologies.

Ontology Lookup Options: Learn more about configuring and using these fields here:

Ontology Lookup

Name and Linking Options

In addition, Concept Annotations are available with the Ontology module.

Conditional Formatting and Validation Options: Conditional formats and regular expression validators.

Visit Date/Visit ID/Visit Label Options

Integration of Sample Types with Study data is supported using Visit Date, Visit ID, and Visit Label field types provide time alignment, in conjunction with a Subject/Participant field providing participant alignment. Learn more in this topic: Link Sample Data to Study

Name and Linking Options
Conditional Formatting Options: Conditional formats are available.

Unique ID Options (Premium Feature)

A field of type "Unique ID" is read-only and used to house barcode values generated by LabKey for Samples. Learn more in this topic: Barcode Fields

Name and Linking Options
Conditional Formatting Options: Conditional formats are available.

LabKey Support

LabKey Support