Table of Contents

guest
2019-05-22
     Lists
       Tutorial: Lists
         List Tutorial: Setup
         Create a Joined Grid
         Add a URL Property
       Create Lists
         Create a List Design
         Import a List Archive
       Choose a Primary Key
       Edit a List Design
       Populate a List
       Manage Lists
       Connect Lists

Lists


A List is a flexible, user-defined table that is defined and managed via the LabKey Server web UI. Lists can be used for a variety of purposes:
  • A place to store and edit data entered by users via forms or editable grids
  • Defined vocabularies, which can be used to constrain choices during completion of fields in data entry forms
  • Simple workflows that incorporate discussions, documents, and states
  • Read-only resources that users can search, filter, sort, and export
The design, or schema, of a list is the set of fields (columns and types) and can be defined in several ways. Lists can be linked via lookups and joins to draw data from many sources. Lists can be indexed for search, including optional indexing of any attachments added to fields. Populated lists can be exported and imported as archives for easy transfer between development, staging and production folders or servers.

Topics

List Web Parts

You need to be an administrator to create and manage lists. You can directly access the list manager by selecting (Admin) > Manage Lists. To make the set of lists visible to other users, and create a one click shortcut for admins to manage lists, add a Lists web part to your project or folder.

Lists Web Part

  • Enter > Page Admin Mode.
  • Choose Lists from the <Select Web Part> pulldown at the bottom of the page.
  • Click Add.
  • Click Exit Admin Mode.

List-Single Web Part

To display the contents of a single list, add a List - Single web part, name it and choose the list and view to display.




Tutorial: Lists


This tutorial introduces you to the simplicity of lists, plus the power of lookups, URL properties and joins for generating insights into your results.

This tutorial can be completed using a free 30-day trial version of LabKey Server.

This "Rats of NIMH" demo uses four lists containing information about animal subjects and blood samples from those animals. Samples were subdivided into portions, then into slides, on which experiments were run. Gaining insight into these results requires joining them with information about samples and demographics to get the full picture.

Lookups and URL properties help you to navigate your data more easily. A lookup can let you display the name of the subject instead of displaying an obscure SubjectID. Lookups also let you link sample data to other information (such as demographic data). URL properties can help you link directly to relevant visualizations or outside sources from subject data. Joins help you gain insight across different lists of data by letting you view data from related lists together in common views.

Completing this tutorial requires administrative permissions, which you will have if you create your own server trial instance in the first step. The covered features, however, are not restricted to admin users.

Tutorial Steps

View the interactive example of the lists used in this tutorial.

Related Topics

First Step




List Tutorial: Setup


Set Up

  • Log in to your server and navigate to your "Tutorials" project. Create it if necessary.
    • If you don't already have a server to work on where you can create projects, start here.
    • If you don't know how to create projects and folders, review this topic.
  • Create a new subfolder named "List Tutorial". Accept all defaults.

Import a List Archive

  • In the Lists web part, click Manage Lists.
  • Click Import List Archive.
  • Click Choose File and select [LabKeyDemoFiles]/Lists/NIMH/ListDemo_NIMH_ListArchive.lists.zip from the unzipped demo files.
  • Click Import List Archive.

The list archive includes four lists interconnected with some specific fields defined as lookups to other lists. You can continue the tutorial using the newly imported set of lists.

Related Topics

  • Connect Lists: Walkthrough the process used to create the list archive you imported.

Start Over | Next Step




Create a Joined Grid


The imported list archive includes four lists interconnected with some lookups between them. These lookups allow us to create joins between these lists which pays off when we investigate our experimental results in the NIMHSlides list. The Slides list shows an interesting pattern in positive/negative stains, but you won't see this pattern unless you first join in some information from another list (Demographics).

  • Click the NIMHSlides list from the Lists web part (or Available Lists grid).

The grid shows all the uploaded slide data. You may or may not be able to spot a pattern at this point. Familiarity with the novel might help in this very simple example, but we can also use additional data to help us.

Create a Joined Grid

  • On the NIMHSlides grid, select (Grid Views) > Customize Grid.
  • In the Available Fields panel, expand the node SubjectID by clicking on the icon next to it.
  • Place checkmarks next to the Name, Family, and Species fields. (Note that these fields are added to the Selected Fields panel.)
  • Click Save.
  • In the Save Custom Grid View popup menu, select Named and name this view NIMHSlideDataJoinedView. Click Save.
  • You'll now see additional columns in the grid view. To view an interactive example, see: NIMHSlideDataJoinedView

Find a Pattern in the Data

Do positive/negative stains correlate with any other characteristics in our subjects?

First, let's do the simplest possible data analysis: sorting the data.

  • Click the column header Stain Positive to bring up a menu of sorting and filter options.
  • Choose Sort Descending.
  • Examine the results and notice that almost all of the positive stains came from field mice.
  • Looks like we have a pattern worth further investigation.

Previous Step | Next Step




Add a URL Property


In this step, we explore two ways to use the URL property.

Create Links to Filtered Results

It can be handy to generate an active filtering link in a list or dataset. For example, here we use a URL property to turn the values in the species column into links to a filtered subset of the data. When you click one species value, you get a grid showing only subjects of the same species.

  • Click the List Tutorial link above the grid to return to the main folder page.
  • From the Lists web part, click NIMHDemographics to open the grid view of the list. Notice that in the "Species" column, the values are just text, not links (yet).
  • Click the column header Species, then Filter.
  • Click the label Rat to select only that single value.
  • Click OK.
  • Notice the URL in your browser, which might look something like this - the full path and your list ID number may vary, but the filter you applied is encoded at the end. This will show only rows where the species is Rat.

Next we'll modify the design of the list to create the links.

  • Click Design.
  • Click Edit Design.
  • Select the Species field by clicking in its Name box.
  • On the Display tab in the field properties box, enter this value for the URL field:
/list/grid.view?name=NIMHDemographics&query.Species~eq=${Species}
    • The filter portion of this URL replaces "Rat" with the substitution string "${Species}". (If we were to specify "Rat", clicking any species link in the list would filter the list to only show the rats!)
    • The "listId" portion of the URL has been replaced with "name=NIMHDemographics."
  • Scroll up and click Save.
  • Click Done.
  • Clear the "Species=Rat" filter using the in the filter bar, or clicking the column header Species and then clicking Clear Filter.

Now notice that the values in the "Species" column are all links. When you click one, you will get the filtering behavior we just defined.

  • Click Field mouse in any row and you will see the demographics list filtered to display only rows for field mice.

Create Links to Files

A column value can also include link to a file. All the values in a column could link to a fixed file (such as to a protocol document) or you can make row-specific links to files where a portion of the filename matches a value in the row such as the Subject ID in this example. Open this link in a new browser window:

Edit the URL, incrementing the file name 20023.png, 20024.png, 20025.png. These are simply cartoon images of our sample characters stored on a public server, but in actual use you might have slide images or other files of interest stored on your local machine and named by subjectId.

Here is a generalized version, using substitution syntax for the URL property, that you can use in the list design:

This generalized version is already included in the list design in our example archive.

  • Click the List Tutorial link near the top of the page, then click the NIMHDemographics list in the Lists web part.
  • Click Design.
  • Click Edit Design.
  • Select the CartoonAvailable field by clicking its Name box.
  • Notice the URL property for this field. It is the generalized version of the link above.
  • Scroll up to click Cancel, then Done to return to the grid for this list.
  • Observe that clicking true in the CartoonAvailable column opens an image of the 'subject' in that row. Click the 'true' link for the SubjectID 20022 to see the same image you opened earlier.

Related Topics

Previous Step




Create Lists


A list is a basic way of storing information. The list design, or schema, is the set of columns and types, which forms the structure of the list. The design can be created and the list data populated in several ways.

Related Topics




Create a List Design


A list is a simple structure for storing data. The design, or schema, for a list is the set of fields (columns and types) which comprise the list. If you are an administrator, you can create a new list design by: You can also simultaneously create the design and populate the list from a spreadsheet|populateList#infer].

Once you have created a list, you can add data to it and can also export the design as a set of fields that can be used like a template for designing another list.

Manually Define Fields

  • In the project or folder where you want the list, select (Admin) > Manage Lists.
  • Click Create New List.
  • Name the list, i.e. "Technicians" in this example.
  • Choose the appropriate Primary Key and Primary Key Type. Here we use the defaults.
  • Do not select the Import From File checkbox this time.
  • Click Create List.

Set Properties and Add Fields

  • Set the List Properties appropriately. We leave the defaults unchanged for this example.
  • The only currently defined data field is "Key," the default we left in place as the Primary Key.
  • Use the Add Field button below it to add additional fields to your list design. If you add an extraneous field, just click the "X" button to the left of the field row you would like to delete; you cannot delete the primary key field.
  • In this example, we've added three fields.
  • The Name, Label and Type for the three new fields:
    • Name: FirstName Label: First Name Type: String
    • Name: LastName Label: Last Name Type: String
    • Name: ID Label: ID Type: Integer
  • If desired, you can also set properties of list fields as you add them.
  • Scroll back to the top of the page and click Save.
  • Click Done to return to the grid of available lists.

Infer Fields from a Spreadsheet

Instead of creating the list fields one-by-one you can infer the list design from the column headers of a sample spreadsheet. Either upload the file or cut and paste the contents using this method. In this process, the data is not imported from the spreadsheet, merely the structure of it.

  • Click here to download this file: Technicians.xls
  • Select (Admin) > Manage Lists and click Create New List.
  • Name the list, i.e. "Technicians2" so you can compare it to the list created above.
  • Leave the key defaults, and click Create List.
  • Scroll down to the "List Fields", and click Infer Fields From File to open the popup as shown below.
    • Upload the sample file directly or paste the contents into the box.
  • Click Submit.
  • Scroll back to the top of the page and click Save.
  • Click Done to return to the grid of available lists.
  • Click "Technicians2" to see that the field names and types are inferred forming the header row, but no data was imported from the spreadsheet.

Note: Importing fields from a file will overwrite the existing list design, including deleting any data that has already been added to the list. Use only for new list creation. A warning message appears in the popup box to remind you.

Shortcut: Infer Fields and Populate a List from a Spreadsheet

If you want to both infer the fields to design the list and populate the new list with the data from the spreadsheet, follow this shortcut process:

  • Select (Admin) > Manage Lists.
  • Click Create New List.
  • Name the list, i.e. "Technicians3".
  • Leave the default key information again, but check the box for Import from File this time.
  • Click Create List.
  • Click Browse or Choose File and select the Technicians.xls file you downloaded.
  • You will see a preview of the fields that will be inferred, along with the first few rows of data. You may change types or labels of columns here if needed, or ignore a column when importing by unchecking the box next to the column name.
  • Click Import.
  • You will see the column headers, matching those in the other two lists you created above, as well as the three lines of data.

Import/Export Fields for a List Design

Once you have created and saved a list design, whether or not you have added data to the list itself, you can export the fields, such as to use as a template when creating another list. The new list could have changes or additional fields, but starting from an exported set of fields can reduce data entry and improve consistency.

  • Select (Admin) > Manage Lists.
  • Click the name of a list in the Lists web part (here, any one of the "Technicians" lists).
  • Click Design.
  • Scroll down to the List Fields section and click Export Fields.
  • Copy the contents of the popup window to your browser clipboard or a notepad. This representation of the schema can be used as a template when creating a new list.
  • Click Done in the popup, then Done in the list editor.

Use the exported set of fields to create a new list design.

Note: Once a list contains data, importing a new set of fields in this way will completely overwrite the list and cause any existing data to be deleted. This import option is intended for list creation, not for adding additional fields to a list.

  • Select (Admin) > Manage Lists.
  • Click Create New List, name it "Technicians4" and leave the key settings at their defaults. Do not check "Import from file".
  • Click Create List.
  • In the List Fields section, click Import Fields.
  • Paste the schema you exported above into the provided window:
  • Click Import and notice the list fields are populated as when you created them by other methods.
  • You could modify, add, or delete fields for the new list design if needed.
  • Click Save to save the new design.

Related Topics




Import a List Archive


You can copy all lists in a folder to another folder or another server using export and import. Export packages up all your lists into a list archive: a .lists.zip file that conforms to the LabKey list export format. The process is similar to study export/import/reload. Information on the list serialization format is covered as part of Study Archive Files and Formats.

Export

To export all the lists in a folder to a list archive:

  • In the folder that contains lists of interest, select (Admin) > Manage Lists.
  • Select Export List Archive.
  • All lists in the current folder are exported into a zip archive.

Import

To import a list archive:

  • In the folder where you would like to import the list archive, select (Admin) > Manage Lists.
  • Select Import List Archive.
  • Browse to the .zip file that contains your list archive and select it.
  • Click Import List Archive.
  • The imported lists will then be displayed in the Lists web part.

Note: Existing lists will be replaced by lists in the archive with the same name; this could result in data loss and cannot be undone.

Auto-Increment Key Considerations

Exporting a list with an auto-increment key may result in different key values on import. If you have lookup lists make sure they use an integer or string key instead of an auto-increment key.

Related Topics

The Tutorial: Lists uses a list archive preconfigured to connect several lists with lookups between them. A walkthrough of the steps involved in creating that archive from independent lists is covered in Connect Lists.




Choose a Primary Key


Every item in a list has a key value that uniquely identifies the item. It could be a basic auto-incremented integer or a number or string uniquely assigned to the list items.

When creating a list, choose the:

  • Primary Key, the name of the column that holds the unique key. If data imported to the list does not already include a column by this name, it will be added. Default: "Key".
  • Primary Key Type:
    • Auto-Increment Integer (Default)
    • Integer
    • Text (String)
Once your list has been created, the field that holds the key is marked by a key in the list design editor. In the example list below (NIMHDemographics, from the Tutorial: Lists), the key is "SubjectID":




Edit a List Design


Editing the list design allows you change the structure and functions of a list, whether or not it has been populated with data. To see the list design, click Design above the grid view of the list. To edit the design, then click Edit Design. If you do not see these options, you do not have permission to edit the given list.

List Properties

The list properties contain metadata about the list and enable various actions including export and search.

The properties of the NIMHDemographics list in the List Tutorial Demo look like this:

  • Name: The displayed name of the list.
  • Description: An optional description of the list.
  • Title Field: Identifies the field (i.e., the column of data) that is used when other lists or datasets do lookups into this list. You can think of this as the "lookup display column." Select a specific column from the dropdown or leave the default "<AUTO>" selection, which uses this process:
    • Use the first non-lookup string column (this could be the key).
    • If there are no string fields, use the key.
  • Discussion Links: Optionally allow discussions to be associated with each list item. Such links will be exposed as a "discussion" link on the details view of each list item. Select one of:
    • None (Default)
    • Allow one discussion per item
    • Allow multiple discussions per item
  • Allowable Actions: These checkboxes determine whether Delete, Upload, Export and Print are allowed for the list. All are checked by default.
  • Full-Text Search Indexing. Determines how the list data, metadata, and attachments are indexed for full-text searching.

List Fields

You can add, delete or edit the fields of your list in this section. See Field Properties Reference.

Example. The field editor for the NIMHDemographics list in the List Tutorial Demo looks like this:

Customize the Order of List Fields

By default, the order of fields in the default grid is used to order the fields in insert, edit and details for a list. All fields that are not in the default grid are appended to the end. To see the current order, click Insert New for an existing list.

To change the order of fields, modify the default grid by selecting (Grid Views) > Customize Grid. See Customize Grid Views for further details.

List Metadata and Hidden Fields

In addition to the fields you define, there is list metadata associated with every list. To see and edit it, use the schema browser. Select (Admin) > Developer Links > Schema Browser. Click lists and then select the specific list. Click Edit Metadata.

List metadata includes the following fields in addition to any user defined fields.

NameDatatypeDescription
Createddate/timeWhen the list was created
CreatedByint (user)The user who created the list
Modifieddate/timeWhen the list was last modified
ModifiedByint (user)The user who modified the list
containerfolderThe folder or project where the list is defined
lastIndexeddate/timeWhen the list was last indexed
entityIdtextA unique identifier for this list

Finally, there are several built in hidden fields in every list. To see them, open (Grid Views) > Customize Grid and check the box for Show Hidden Fields.

NameDatatypeDescription
Last Indexeddate/timeWhen this list was last indexed.
KeyintThe key field.
Entity IdtextThe unique identifier for the list itself.
FolderobjectThe container where this list resides.

Full-Text Search Indexing

You can control how your list is indexed for search depending on your needs. Choose one or more of the options:

  • Index each item as a separate document means that each item in the list will appear as a separate search result.
  • Index entire list as a single document means that the list as a whole will appear as a search result. Select one option:
    • Metadata only (name and description of list and fields)
    • Data only Note: for large lists with frequent updates, updating any item will cause re-indexing of the entire list.
    • Metadata and data Note: for large lists with frequent updates, updating any item will cause re-indexing of the entire list.
  • Index file attachments: Indexes the contents of documents uploaded to attachment fields.
When indexing either the entire list or each item separately, you also specify how to display the title and which fields to index. Note that you may choose to index *both* the entire list and each item, potentially specifying different values for each of these options.
  • Use the radio buttons to select how to display the list title:
    • Standard Title: The standard search result title is <List Name> - <Value of Title Field>
    • Custom Title: Enter a custom title in the box provided. The format can use substitution syntax to create a template that includes your choice of fields, for example: NIMHDemographics - ${SubjectID} ${Name}
  • Next specify which fields in the list should be indexed. Warning: Do not include fields that contain PHI or PII. Full text search results could expose this information.
    • Index all text fields: Values in all text fields will be indexed.
    • Index all fields (text, number, date and boolean): Values in all fields will be indexed.
    • Index using custom template: Choose the exact set of fields to index, for example: ${SubjectID} ${Name} ${Family} ${Mother} ${Father}.

Related Topics




Populate a List


Once you have created a list, there are a variety of options for populating it, designed to suit different types and complexity of data entry.

Insert Single Rows

One option for simple lists is to add a single row at a time:

  • Select (Insert data) > Insert new row.
  • Enter the values for each column in the list.
  • Click Submit.

Import Bulk Data

Paste Multiple Rows

You can also import multiple rows at once by cutting and pasting, by typing tab separated values directly into the window, or by uploading a file. To ensure the format is compatible, particularly for complex lists, you can first download a template, then populate it with your data prior to upload.

  • Select (Insert data) > Import bulk data.
  • Type the following into the Import Data text box, with a tab between fields. Include the header row. To enter comma-separated values, simply change the Format selection.
First NameLast NameID
JohnDoe1
JaneDoe2
JohnSmith3

  • Click Submit.
  • 3 data rows will be added to the list.

Import a File

Another way to upload data is to directly upload an .xlsx, .xls, .csv, or .txt file containing data. Toggle between the import data upload methods using the and buttons on the right as shown below:

Simultaneously Create and Populate a List from a Spreadsheet

If you want to both infer the fields to design the list and populate the new list with the data from the spreadsheet, follow this shortcut process:

  • Click Create New List.
  • Enter a name and choose the appropriate primary key information. If you specify a key field that is not present in the imported file, it will be added as an additional field.
  • Check the box to Import from File.
  • Click Browse or Choose File and select the file you downloaded.
  • You will see a preview of fields inferred - you may change types or labels here if needed.
  • Click Import.

Import Lookups By Alternate Key

When importing data into a list, you can use the checkbox to Import Lookups by Alternate Key. This allows lookup target rows to be resolved by values other than the target's primary key. It will only be available for lookups that are configured with unique column information. For example, tables in the "samples" schema (representing Sample Sets) use the RowId column as their primary key, but their Name column is guaranteed to be unique as well. Imported data can use either the primary key value (RowId) or the unique column value (Name). This is only supported for single-column unique indices. See Import Sample Sets.

View the List

Your list is now populated. You can see the contents of the list by clicking on the name of the list in the Lists web part. An example:

Hovering over a row will reveal icon buttons in the first column:

    • Edit the list item
    • View details about the list item.

Related Topics




Manage Lists


A list is a flexible, user-defined table. To manage lists, Select (Admin) > Manage Lists, or click Manage Lists in the Lists web part.

Manage Lists

An example list management page from an example HIV study:

  • (Grid Views): Customize how this grid is displayed.
  • Reports: Add a JavaScript or Crosstab Report about the grid of lists.
  • Create New List
  • (Delete): Select one or more lists using the checkboxes and click the button. Both the data and the list design are removed permanently from your server.
  • (Export)
  • Import List Archive
  • (Print): Print the grid of Lists.

Manage a Specific List

For each list shown in the Lists web part, you can:

  • View Design: View fields and properties that define the list, including allowable actions and indexing.
  • View History: See a record of all list events and design changes.
  • Click the Name of the list to see all contents of the list shown as a grid. Options offered include:
    • Create custom views and charts
    • Insert data into the list
    • Delete, export, or print the entire list.
    • Click Design to see the same information as when clicking View Design above.
    • Click Delete All Rows to empty the data from the list without actually deleting the list structure itself.

Related Topics:




Connect Lists


In the Tutorial: Lists, we provide an importable list archive as a shortcut to setting up multiple lists and lookups. This page describes how you could manually create the same archive from the same sample data package downloaded in the setup step.

Import Files into Lists

Create the lists individually by importing spreadsheets. If you already imported the tutorial list archive, you can skip this step, or create a new set of lists in another folder.

  • Click Manage Lists link in the Lists web part.
  • Click Create new list
    • Name: "NIMHDemographics"
    • Primary Key: "SubjectID"
    • Primary Key Type: Integer
    • Import from file: Check box.
  • Click Create List.
  • Browse or Choose File and locate [LabKeyDemoFiles]/Lists/NIMH/NIMHDemographics.xls.
  • Assume the inferred fields are correct and click Import.
  • Click Lists > above the grid and repeat the Create New List steps for each of the following:
    • Name: "NIMHPortions"
      • Primary Key: "PortionID"
      • Primary Key Type: Integer
      • Import from file: Check box.
      • Select and import the file named: NIMHPortions.xls
    • Name: "NIMHSamples"
      • Primary Key: "SampleID"
      • Primary Key Type: Integer
      • Import from file: Check box.
      • Select and import the file named: NIMHSamples.xls
    • Name: "NIMHSlides"
      • Primary Key: "SlideID"
      • Primary Key Type: Integer
      • Import from file: Check box.
      • Select and import the file named: NIMHSlides.xls

Set Up Lookups

There are columns in common between our lists that can be used as the basis for joins between these lists. In order to setup the lists for joins, we need to identify the columns in common.

The steps for editing a list design to make a field into a lookup of a value from another table are as follows. If you are already working with the tutorial list archive, simply examine the list designs where you will already see these changes.

  • In the Lists web part, click NIMHSamples.
  • Click Design then Edit Design.
  • Scroll down and for the SubjectID field, click on the Type property and select Lookup.
    • Folder: [current folder].
    • Schema: lists.
    • Table: NIMHDemographics(Integer).
    • Click Apply.
  • Scroll back up and click Save.

Repeat this process for each of the following lookups. You can add lookups to multiple fields of a given list at once before clicking Save for the list.

ListFieldTable for Lookup
NIMHPortionsSubjectIDNIMHDemographics(Integer)
NIMHPortionsSampleIDNIMHSamples(Integer)
NIMHSlidesSubjectIDNIMHDemographics(Integer)
NIMHSlidesSampleIDNIMHSamples(Integer)
NIMHSlidesPortionIDNIMHPortions(Integer)

If you view, for example, the NIMHSlides lists, you will now see hyperlinks in the SubjectID, SampleID and PortionID columns where you have set up lookups.

Export List Archive

Now that you have created a useful package of interconnected lists, you can package them as an archive for use elsewhere.

  • From the Lists web part, click Manage Lists.
  • Click Export List Archive.
  • All the lists in the web part will be included in the zip file that is downloaded.

Import a List Archive

Once you have created and exported an archive, you can reuse it later (as we did in the tutorial example) by importing the archive:

  • In the Lists web part, click Manage Lists.
  • Click Import List Archive.
  • Browse to and select the list archive: ListDemo_NIMH_ListArchive_XXXX.lists.zip in [LabKeyDemoFiles]/Lists/NIMH.
  • Click Import List Archive.