• Sign In
  1. Messages

Linking by SpecimenID

LabKey Support Forum (Inactive)

View Message

view list print
Linking by SpecimenID jdutra  2012-07-25 13:42
Status: Closed
 
When we create an assay and copy it to study, the copied to study dataset links to the specimen information by specimen ID, and we can use that to customize the dataset view by adding specimen information such as the draw timestamp or derivative type.

However, in a regular dataset, there is apparently no way to do this. I've tried several different methods of linking, and sometimes I can even get to a point where I can choose fields from the specimen data to add to the view, but the columns just end up empty.

We have at least two situations where it would be extremely useful to be able to customize views to include data based on SpecimenID:
1. We would like to add some specimen fields to "Assay" datasets that are stored as regular clinical datasets (because the data is entered through a wiki page GUI) instead of being copied to study.
2. We are storing an assortment of metadata about specimens that cannot be stored in labkey's specimen tables. We would like to be able to add elements of that metadata to the normal specimen views, and vice versa.

I know we can always write queries to join on SpecimenID, but then we lose native functionality such as having a link in the dataset webpart or being able to easily select specimens and add them to a request.

Is it possible to link based on SpecimenID and I'm just not doing it right? If not, would you consider adding that functionality in a future version?

As always, thanks so much for your help!
- Jen
 
 
jeckels responded:  2012-07-26 15:45
Hi Jen,

I think that the basic pieces are in place to support this, but the system doesn't automatically wire it up. Doing it manually should be possible and a nice feature to support, but it's somewhat tricky.

One of the difficulties here is that in the physical database the primary key for specimen data is a RowId column, an auto-incrementing INT. Within a study, the GlobalUniqueId will be unique. However, despite the column's name, it's possible for studies in different projects/folders to reuse the same GlobalUniqueId.

To hack something together, here's what I'd recommend trying:

1. Create a custom query, SpecimenDetailWrapper:

SELECT *
FROM SpecimenDetail

2. In the XML Metadata tab in the query editor, set this following as the metadata for the SpecimenDetailWrapper query. It makes the GlobalUniqueId field the sole key field, so that you'll be able to use it as a lookup target.

<ns:tables xmlns:ns="http://labkey.org/data/xml">
  <ns:table tableName="SpecimenDetailWrapper" tableDbType="NOT_IN_DB">
  
    <ns:columns>
      <ns:column columnName="ParticipantId">
        <ns:isKeyField>false</ns:isKeyField>
      </ns:column>
      <ns:column columnName="Visit">
        <ns:isKeyField>false</ns:isKeyField>
      </ns:column>
      <ns:column columnName="RowId">
        <ns:isKeyField>false</ns:isKeyField>
      </ns:column>
      <ns:column columnName="Available">
        <ns:isKeyField>false</ns:isKeyField>
      </ns:column>
      <ns:column columnName="GlobalUniqueId">
        <ns:isKeyField>true</ns:isKeyField>
      </ns:column>
    </ns:columns>
    <ns:titleColumn>GlobalUniqueId</ns:titleColumn>
  </ns:table>
</ns:tables>

3. Save your query and metadata.
4. In your dataset, point your SpecimenId column at the SpecimenDetailWrapper query as its lookup target.

This will let you customize the view and add columns from the specimen data. I'm not sure it will support your scenario of editing specimen requests, but it might.

Thanks,
Josh