Lookups/Links to primary key/normal values in SQL queries

LabKey Support Forum
Lookups/Links to primary key/normal values in SQL queries max diesner  2020-08-24 02:36
Status: Active
 

Hi,

we have no for some time a community edition labkey server running in the lab and it is just awesome. I have recently setup some SQL queries for keeping track of our Lab inventory which is also working quite nicely. However there is a minor thing that keeps bugging me. The resulting query table does not copy automatically the lookup/ Text link for a certain value from the underlying table (list). I checked the documentation and it states that you can easily get this done by applying the lookup through the Metadata properties. Thus, i accordingly changed the column to a lookup from the appropriate underlying column. I also checked the Metadata code and can see that the appropriate <fk></fk> part has been added to the metadata code. Nevertheless the resulting table still does not show the appropriate text links. Do you need to check some option that i am missing or does the LEFT JOIN statement in the SQL code overrides any potential lookups for certain values? I am trying to connenct primary keys for your information. When i am referencing from a table where the value is already a lookup it is copying the textlink right away.

I am looking forward to hear from you!

Cheers Max

 
 
chetc (LabKey Support) responded:  2020-09-15 13:29
Status: Closed

Hello Max,

As far as I know there is no option that needs to be enabled for this. Your approach is correct, if the lookup doesn't appear automatically you can opt to set up the link through metadata.

You may have already seen this doc but take a look https://www.labkey.org/Documentation/wiki-page.view?name=lookups

Would it be possible for you to show me your query and associated metadata. Having this will allow me troubleshoot further.

Thanks,
Chet

 
max diesner responded:  2020-10-05 03:11
Status: Active

Hi Chet,

i just had time to revisit this problem on our server.

so here is some example code where it is also not working. I have a list with orders. The primary key is Ordernumber Labkey, the query should gives back just the entries with the status on unprocessed (to implement it in a dropdown menue elsewhere:


SELECT Orders."Ordernumber Labkey",
FROM Orders
WHERE Orders."Processed?"=true


This gives back the correct list with entries but the look up is not linked to the original list as explained earlier.

I also edited the Metadata XML file as follows:


<tables xmlns="http://labkey.org/data/xml">
<table tableName="Orders-unprocessed" tableDbType="NOT_IN_DB">
<columns>
<column columnName="Ordernumber Labkey">
<fk>
<fkColumnName>Ordernumber</fkColumnName>
<fkTable>Orders</fkTable>
<fkDbSchema>lists</fkDbSchema>
</fk>
</column>
</columns>
</table>
</tables>


Which is not altering anything. I am probably missing something but am not sure what.

looking forward to your help!

Max

 
chetc (LabKey Support) responded:  2020-10-22 14:10
Status: Closed

Hello Max,

I tried the XML you sent and it seems to work as expected on my end. Ill walk you through what I have setup and then we can see if there are any discrepancies.

I have one list
List name - Orders
(orderDesign.png)
(orderData.png)

There is a query for this list called unprocessed-Orders
(unprocessedQueryXML.png)
(unprocessedQuery.png)
(unprocessedQueryResult.png)

When I added your XML (with proper columnName changes) the view of that query was able to render the lookups correctly.
(lookupResults.png)

You should make sure that the fkColumnName is specified properly. If you are on a newer version of LK you could try and setup the lookup via the field editor UI. https://www.labkey.org/Documentation/wiki-page.view?name=lookupDefine.

If you still aren't able to get it to work, send me screenshots of the list design and the query xml and I will try to troubleshoot further. A folder export would be good as well. https://www.labkey.org/Documentation/wiki-page.view?name=importExportFolder

Thanks,
Chet

 
max diesner responded:  2020-12-12 00:17
Status: Active

Hi Chet,

thanks for the answer. I finally had last week time to review everything again and with your walk through i have found my mistake - I had a space in the primary key column name - rookie mistake as i have found out. Sry for the inconvinience. Nevertheless, everything is working as it should, i also understand and can setup the XML metadata as i want which solved quiet some problems ;). So thanks again!

I have a follow up question; is there an easy way to setup dependent lookups, e.g.:

Lookup 1
Machine 1
Machine 2

Dependent Lookup 2
(Machine 1)
Column 1
Column 2

or depending on the selection in lookup 1
(Machine 2)
Column 1
Column 2
Column 3

Another example directly from the server would be when adding a single list module on a labkey page. There you can select a list and then a second dropdown menue can be selected in order to select from the list of the corresponding list views.

Is there a native way through the XML data to set this up or has this to be implemented via for example Javascript?

Thanks again for your awesome work!

Max

 
chetc (LabKey Support) responded:  2020-12-30 13:46
Status: Closed

Hello Max,

No worries, glad you were able to get that working!

I don't follow what you're asking in regards to dependent lookups.

  1. Is there more than one machine (LabKey Server) in your scenario?
  2. Are you trying to setup a list with lookups and then filter the lookup columns via a pre-defined view?
  3. Or were you hoping to change where the lookup is pointing to based on the selected view?

https://www.labkey.org/Documentation/wiki-page.view?name=lookupDefine

Thanks,
Chet