• Sign In
  1. Messages

Custom List metadata on export/import list archive.

LabKey Support Forum

View Message

view list print
Custom List metadata on export/import list archive. Anthony Corbett  2013-01-21 06:52
Status: Closed
 

I ran in to an issue, it was easy enough to fix, but was wondering if I happen to miss some option during an export of a list archive.

I created a list which references study.DataSets.dataSetId as a foreign key. When designing the list this table was not an option to pick from for a lookup. Therefore, in order to wire up the lookup so queries could use the dot notation to the Datasets columns I had to manually add the following table metadata XML through the Schema Browser:

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="CohortVisit_eCRFSections" tableDbType="TABLE">
    <columns>
      <column columnName="dataSetId">
        <datatype>integer</datatype>
        <columnTitle>Data Set</columnTitle>
        <fk>
          <fkDbSchema>study</fkDbSchema>
          <fkTable>DataSets</fkTable>
          <fkColumnName>DataSetId</fkColumnName>
        </fk>
      </column>
    </columns>
  </table>
</tables>

This worked great and I can now build queries against the list that auto join the DataSet rows using the DataSetId. However, once I exported an archive of the lists from our dev server to production the metaData did not follow it as part of the export.

Did I not export this correctly. Just wondering for future reference and to document any 'gotcha' for our dev team.

Much appreciated!

-
Anthony Corbett
 
 
jeckels responded:  2013-01-23 16:24
Hi Anthony,

As you may have noticed, the reason you couldn't choose the Datasets query as the lookup target is that it has a two-column primary key (Container and DatasetId). Although a DatasetId is enough to uniquely identify a dataset within a given container, it's not necessarily unique throughout the whole server. The normal lookup editor works with single-column primary keys.

List archives don't include information from the XML metadata customization. It will only include the metadata shown in the list designer.

Folder archives (Admin->Folder->Folder Management->Export) will include XML metadata for custom queries, but do not currently include metadata overrides for things non-custom queries (like lists, datasets, etc).

Thanks,
Josh
 
Anthony Corbett responded:  2013-01-24 08:32
Thanks for the reply. I will make sure to let all our devs know that doing custom FK lookups using the table XML should be used sparingly and if used to remember to manual apply it once a list is exported and re-imported somewhere else. Especially if they build queries that depend on this FK lookup being in place. :)
 
jeckels responded:  2013-01-24 08:49
Hi Anthony,

Are you familiar with the Validate Queries button in the query browser? It provides a quick and easy way to make sure that all of the fields that your custom queries expect actually existing in the underlying schema, and would catch this kind of problem with missing lookups. It might be a good step to add to any workflows that migrate lists or other data across servers.

Thanks,
Josh
 
Anthony Corbett responded:  2013-01-24 09:10
Thanks Josh! No, I was not familiar with it and sounds great to add to our validation to make us more confident in our migration tasks. Does it check all queries across all schema at once or is it run one schema at a time? Does it validate custom queries that go across multiple schemas, say lists and study?


Great tip! Thanks
 
adam responded:  2013-01-24 09:17
Yes, it validates queries across all schemas, including cross schema custom queries. Go to the schema browser and click "validate queries" to see the various options and try it out.

Adam
 
Ben Bimber responded:  2013-01-24 09:51
Hi Anthony,

I'd recommend also checking 'Validate metadata and views' when you do this. I originally wrote that to help with the sort of custom module issues you're having. It will additionally inspect table metadata to find things like lookups to non-existent tables, or custom views that include invalid columns.

It has the potential to spit out a lot of information, and will probably include some warnings about system tables (assay, flow or samples) that should be ignored. However, it could help identify problems easily missed otherwise.