In the
Tutorial: Lists, we provide an importable list archive as a shortcut to setting up multiple lists and lookups. This topic describes how you could manually create the same lists and lookups starting from individual spreadsheets also included in the same LabKeyDemoFiles package downloaded in
the tutorial setup step.
Import Files into Lists
Create the lists individually by importing spreadsheets. If you already imported the tutorial list archive, create this new set of lists in another folder, such as a subfolder of your List Tutorial folder.
- Create a new "Manual Lists" folder and add a Lists webpart on the left.
- Click Manage Lists link in the Lists web part.
- Click Create New List
- Name: "NIMHDemographics"
- Click the Fields section to open it.
- Drag and drop the file [LabKeyDemoFiles]/Lists/NIMH/NIMHDemographics.xls into the target area.
- This will populate the list of fields by inferring names and types from the file.
- Select Subject ID from the Key Field Name drop down menu.
- Assume the inferred fields are correct and scroll down to the Import data from this file upon list creation? section.
- You will see the first three rows of data.
- The Import Data selector is a slider, not a button. To import the data, do not click it.
- Scroll down and click Save. This will create the list design and simultaneously import the data from the spreadsheet.
- Repeat the Create New List steps for each of the following:
- Name: "NIMHPortions"
- File to drag and drop (from the same location) NIMHPortions.xls
- Key Field Name: "PortionID"
- Leave the "Import Data" selector active and click Save.
- Repeat Create New List for:
- Name: "NIMHSamples"
- File to drag and drop: NIMHSamples.xls
- Change the Data Type for the SampleID field to be Integer (otherwise you cannot set it as the key).
- Key Field Name: "SampleID"
- Leave the "Import Data" selector active and click Save.
- Repeat Create New List for:
- Name: "NIMHSlides"
- File to drag and drop: NIMHSlides.xls
- Key Field Name: "SlideID"
- Leave the "Import Data" selector active and click Save.
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, you can simply examine the list designs where you will already see these changes.
- In the Lists web part, click NIMHSamples.
- Click Design.
- Click the Fields section to open it.
- Scroll down and for the SubjectID field, click on the Type property and select Lookup.
- Under Lookup Definition Options, select:
- Folder: "Current Folder".
- Schema: "lists"
- Table: "NIMHDemographics (Integer)"
- Scroll down and click Save.
- Notice that the values in the Subject ID column are links; clicking them opens the matching row from the NIMHDemographics list.
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.
| List | Field | Table for Lookup |
|---|
| NIMHPortions | SubjectID | NIMHDemographics(Integer) |
| NIMHPortions | SampleID | NIMHSamples(Integer) |
| NIMHSlides | SubjectID | NIMHDemographics(Integer) |
| NIMHSlides | SampleID | NIMHSamples(Integer) |
| NIMHSlides | PortionID | NIMHPortions(Integer) |
If you view, for example, the NIMHSlides lists, you will now see links 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.
- Check the box at the top of the column on the left to select all 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 you downloaded (or the one from our demo files).
- Click Import List Archive.
