Skip to content

«Worfklows» has been renamed «protocol lists»

This section is outdated. We have renamed "workflows" (meaning an applied sequence of protocols) to "protocol lists". LabID now uses the term "workflow" to refer to data analysis workflows.

Hands-on: Advanced (Excel) Batch Upload for Biomaterial

  • 20 min
  • Medium
Overview

In the hands-on Batch operations using excel templates, we learned how to efficiently create and edit large lists of items using excel-based templates. Excel-based batch creation and editing are also available for samples and work the same way. Due to the inter-connection nature of samples, there are additional sample-specific features to know that will ease your work.

Walkthrough

To perform this tutorial, you will need the material available here. Please download the archive and unzip it on your computer.

Step 1. Batch create samples with protocols

The first advanced feature concerns setting protocols in the excel batch-create template. You might remember that we could set multiple values to the antibody's Target Organisms property ( a field that does accept multiple selections from a controlled vocabulary list) by adding ~~~ (3 times the ~ symbol) between the value e.g. :

value1 ~~~ value2 ~~~ value3

where the value1, value2 & value3 must match (case-sensitive) field options available in LabID

In the protocol situation, the approach is similar but the protocol unique ID must be used. This allows referring to existing protocols unambiguously e.g.

ID1 ~~~ ID2 ~~~ ID3

will results in a sample-specific workflow of 3 protocols with the indicated order_i.e._

ID1 ID2 ID3

Let's put this into practice!

  • Open the Batch_Create_SAMPLE.xlsx file (available in the data pack) in excel. Notice that this is a simplified version of the excel batch creation template (optional columns removed for clarity)

    A simplified sample batch create template

    A simplified sample batch create template
    In yellow the columns to customize according to your trainee number and group project

  • In the name column: replace all the TraineeXX with your ow e.g. Trainee10

  • In the project column: fill in your project nam e.g. Tea Project

  • As explained earlier, for protocols we need unique IDs. Navigate to the Protocol menu and click the All sub-menu to list all your protocols independently of their type. Make sure to display only your protocols by setting the ownership filter to Personal .

  • Open the Column Settings panel using the button and add the ID and Type columns (in the first position). Close the Column Settings ( at top-right) and optionally save your new table configuration with

    Custom protocol list table

    Custom protocol list table
    Drag and Drop the ID & Type columns in the first positions

  • Notice how the ID is partially displayed in the list table. The full ID can be easily copied from the detail page.

    Protocol table lists truncated IDs for layout reasons

    Protocol table lists truncated IDs for layout reasons
    It is not safe to copy ID from the protocol table as it may be truncated (compare display and tooltip content)

  • Click on the Growth protocol name A simple and cost-effective ... to open it in a separate tab (use right-click) and use the to copy the full ID to the clipboard.

    Copying the protocol ID from detail page

    Copying the protocol ID from detail page

  • Paste the ID in the Workflow column of the excel spreadsheet (only in the first row) and type in ~~~ right after.

Warning

To replicate the IDs in multiple rows, do NOT drag the bottom-right cell corner as this modifies the content if the ID ends with a number (auto-increment number feature).

  • Do the same operation to grab the ID of the Poly(A) mRNA isolation extraction protocol and paste it right after the ~~~ in the excel cell. Validate the cell content by clicking enter; then copy/paste the cell to the other rows.

    Warning

    You will not have the same protocol unique IDs as those on the below picture!

    Final excel sheet to batch create samples with a 2-protocols workflow

    A 2-protocols workflow
    Copy the workflow cell content to the other rows; do NOT drag the bottom-right cell corner (red arrow) as this can modify the content (auto-increment number feature)

  • Make sure to save the excel spreadsheet!

  • Navigate to the Biomaterial menu and click the Sample sub-menu to bring the sample list, mouse over the button above the table and click on the Batch create with spreadsheet link.

  • Drag'Drop the excel file to batch upload the samples; check the results.

Tip

Check Batch operations using excel templates hands-on for more details on batch create

What if LabID contains multiple projects identically named?

LabID accepts the project name in the PrimaryProject column provided that this project name corresponds to a unique project that you can read. If this is not the case, LabID will emit an error. The safer option to indicate the primary project is to paste its unique ID.

Step 2. Batch creation of children samples

The next trick addresses the common issue of processing many samples in parallel and the need to create as many child samples i.e. one child sample for each existing sample. The first way to deal with such a situation is to use the sample editor in the context of one experiment. Here we see a second option that is independent of the experiment context.

The trick is basically to export the existing sample in a batch edit template and transform the edit template into a creation template.

  • Navigate to the Biomaterial menu and click the Sample sub-menu

  • Select the three Blood Sample... we just created and export them for batch edit.

  • Open the exported template in excel (or equivalent) and :

    • remove all non-mandatory columns but the Parent one
    • copy the IDs in the Parent column (green arrow)
    • remove the ID column (red cross). Note that without an ID column, the template is not an edit template anymore as the ID is needed to match up the excel content with an item existing in LabID.
    • Adapt the sample name i.e. add the prefix polyA-RNA (squared in orange)
    • Save the document (e.g. as easy_child_creation.xlsx)
    Converting a batch edit template into a batch create template

    Converting an batch edit template into a batch create template
    Only mandatory and necessary columns are kept to ease conversion

  • Navigate to the Biomaterial menu and click the Sample, mouse over the button above the table and click on the Batch create with spreadsheet link.

  • Drag'Drop the easy_child_creation.xlsx excel file to batch upload the samples; check the results on the sample list page

    Tip

    Customize the sample table to have the Created column at first and use it to sort the sample by date from most recent to oldest

    Handson

    Converting an batch edit template into a batch create template

    Why did we remove all the non-mandatory columns?

    The mandatory columns as well as the Parent one are common to all samples and sub-types (e.g. sequencing library). Exporting a template for Sample can be easily converted into a more specific sample type (e.g. sequencing library) by copying these base columns into a more specific template where only the mandatory columns are kept: those are the same plus additional ones (e.g. the barcode column in case of a sequencing library)

    Challenge: try to convert your easy_child_creation.xlsx excel file into a sequencing library template and batch-create child libraries to the blood samples.

    Hint: the sheet name must be changed to SEQUENCINGLIBRARY and the barcode column must be added (check how a batch create template looks for sequencing library).

Congrats! You have completed this hands-on.

Next we will learn how to deal with duplicated samples.