Specimen Repository Alias GlobalUniqueID

LabKey Support Forum
Specimen Repository Alias GlobalUniqueID Anthony Corbett  2013-04-22 12:09
Status: Closed
 
In a current use case I have to need to alias the current GlobalUniqueId column in the Specimen Repository. In this case a shared resource lab is taking in specimens with a Customer ID and internally using their own Internal ID. When results and data are returned to the Customer there is a need to map back to the CustomerID in the copy to study assay results dataset grid view.

In 13.1 I saw the addition of alias for ParticipantIds and didn't know if there were plans to do the same for Specimen GloablUniqueIds?

Ideally, there would be a second column "GlobalUniqueIDAlias" available in any grid where SpecimenID is a look up and this column could be brought into the grid view. Places where this would be helpful are the assay results grid and the assay dataset when copied to study.

So in the short term I'm wondering if I can use a different field in the Specimen Repository as a convention for the CustomerID and which one would be the best choice in terms of being able to customize grid views to bring in this column. I tried the 'type_type' column but this column is not available as a column in the customize view for the assay results or assay study datasets. I also tried the 'class_id' column but this is restricted to 4 characters in length.

Any suggestions?

Thanks,

Anthony
 
 
jeckels responded:  2013-05-03 10:41
Hi Anthony,

We don't have an equivalent option for GlobalUniqueId aliases like we just added for participants.

I don't know the specimen schema well enough to suggest an existing column to repurpose. However, you might be able to load a side table with the mapping data, and then use XML-based metadata overrides to configure a lookup from the specimen queries to your extra data.

You'd need to configure the metadata in your side table such that the GlobalUniqueId is marked as the one key field (even if that's not the case in the underlying table itself). You'd need to have zero or one rows per GlobusUniqueId, or when you add the lookup column you'll get row multiplication.

Then, go to the schema browser and navigate to the specimen table. Click Edit Metadata in the upper right. Click the Alias Field button. Select the GlobalUniqueId column. Configure this wrapped column to be a lookup to your side table.

Then, you should be able to use Customize View to join in your alias column when you're looking at the specimen query.

Thanks,
Josh
 
Anthony Corbett responded:  2013-05-06 09:04
Josh,

Thank you for the reply. I've got this working; the SpecimenDetails.GlobalUniqueId has been wrapped (alias-ed) as an additional column with custom XML to create a FK to Samples."Study Specimens". When I'm looking at the DataGrid for the SpecimenDetails query I can now bring in other columns from the Sample Set (this is where we've decided to add the 'customerId' alias for the specimen).

However, this new wrapped column is not available through the drill down from the SpecimenID column from the assay results DataGrid or from the study dataset once the assay results have been copied to the target study.

How can I achieve this functionality?


There seems to be some disconnect between the study module's Specimen and the experiment module's Samples. Though a 'shadow' sample is created for each study specimen there is no generic linkage between the two the allow automatic fk lookups between the Vail.GlobalUniqueID and the Samples."Study Specimens".Name

I understand that the target study also plays a role in resolving this lookup and it really is a composite key from an assay results table. Would it be possible to add in to the SpecimenID lookup that currently exists a way to get the to Sample."Study Specimens" and bring in the columns from that sample set? This would be a great generic and flexible option to place any additional metadata about the Specimen/Sample.

Thanks,

Anthony