• Sign In
  1. Messages

Foreign key to assay.AssayList

LabKey Support Forum (Inactive)

View Message

view list print
Foreign key to assay.AssayList Will Holtz  2014-08-25 10:59
Status: Closed
 
I am creating a module with a table that I would like to contain a foreign key to assay.AssayList. I can manually create a list within Labkey that contains such a foreign key, but haven't been able to figure out how to create such a table from within my module. Assay.AssayList appears to be a query and not an actual table in the database, and this seems to be at the crux of my issue.

I have tried creating the table in my sql with:
CONSTRAINT FK_MyTable_AssayList FOREIGN KEY (Assay) REFERENCES exp.protocol(RowId)
This gets me the foreign key in the database, but when I go to the schema browser in Labkey, the 'lookup' column is not populated for the Assay field. When I have made other foreign keys between the tables that I have defined in my module, adding the foreign key constraint to the sql file has been enough to populate the lookup.

I have tried adding the foreign key definition to the table meta-data via the xml schema, without any success. When including the foreign key in the xml schema, I have tried all of the follow schema/table name pairings: assay/AssayList, exp/Protocol and exp/Protocols.

Thanks in advance for your help!
 
 
jeckels responded:  2014-08-25 18:33
Hi Will,

As you've seen, the assay.AssayList is a query that provides a filtered view of the exp.protocol table from the underlying database.

You should be able to use XML metadata to configure the lookup from your column to the AssayList table. I was able to get this to work for me locally, with XML like this:

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="AssayLookup" tableDbType="NOT_IN_DB">
    <columns>
      <column columnName="Lookup">
        <fk>
          <fkDbSchema>assay</fkDbSchema>
          <fkTable>AssayList</fkTable>
          <fkColumnName>RowId</fkColumnName>
        </fk>
      </column>
    </columns>
  </table>
</tables>

How are you trying to apply your metadata? Via the schema-level XML in a module? As a .query.xml file in a module? Through the schema browser? I think that the schema-level approach will likely not work (as it would be trying to target an assay.assaylist table in the actual database) but the others should work.
Thanks,
Josh
 
Will Holtz responded:  2014-08-26 11:02
Hi Josh,

Thanks for replying. I was attempting to apply the metadata via schema-level XML in a module. I can get the lookup to occur if I set up the metadata via the schema browser, but I'd prefer to have the module configure the lookup.

I'm confused about your mention of .query.xml file, as I am trying to create a table and not a query. I don't see how .query.xml can be applied to my problem. Can you please elaborate on that a bit?

thanks!
 
jeckels responded:  2014-08-26 13:45
Hi Will,

.query.xml files will be used for metadata for both "real" database tables and for custom LabKey SQL queries. It's legit, for example, to have an AssayLookup.query.xml file without a corresponding .sql file to go with it. It should go in a subdirectory that matches the schema name, as exposed through the query schema browser. Usually that matches with the underlying database schema name, but not always. This is the module-based equivalent to applying the XML though the schema browser.

For a map of where to put the file, look here:

https://www.labkey.org/wiki/home/Documentation/page.view?name=moduleDirectoryStructures

Thanks,
Josh