Hi,

I made a hard table that contains a foreign key to a Labkey userid (beyond the standard userid fields of createdby, modifiedby, owner). In the module schemas/<schema>.xml metadata I declared the foreign key. In my grid views, I could not get the userid lookup to occur; the integer value was displayed, not the displayname field. I know there are a lot of restrictions on access to core.users and related tables and I thought I was just not getting some of that right. However, I eventually realized that by declaring the foreign key in the module queries/<schema>/<query>.query.xml metadata, I was able to get the lookup to work. It is unclear to me if there is some reason why the foreign key declaration in the schema/<schema>.xml file did not work or if this behavior is unexpected.

I made a minimal reproduction that is contained in the attached module file. In this module, two identical hard tables are created. UserMetadata.SchemaMetadata has the foreign key declared in schemas/UserMetadata.xml. UserMetadata.QueryMetadata has the foreign key declared in queries/UserMetadata/QueryMetadata.query.xml. In both tables, "associateduser" is the userid field I am trying to get the foreign key metadata applied to such that the lookup of displayname occurs. In the Labkey query browser, the associateduser field in UserMetadata.SchemaMetadata does not show a lookup column, while the same field in the UserMetadata.QueryMetadata table has "core.Users.UserId (DisplayName)" as the Lookup column.

I did my testing on Labkey v18.2, Postgres v10.4, Tomcat v8.5.24.

thanks,
-Will