How do I easily find the contents of plate?

Diamond
Diamond
  • Updated

Issue

How can you look up the contents of a plate in a single lookup?

 

Environment

Inventory, Insights

 

Resolution steps

 

Create an insights query with clickable chips

WITH
 well_position AS (
   -- Convert row/column indices to well position format (A1, B2, etc)
   SELECT
     id,
     plate_id,
     chr(65 + row_index) || (column_index + 1) AS well_position,
     row_index,
     column_index
   FROM
     yourtenantorgname.container$raw
   WHERE
     plate_id IS NOT NULL
 ),
 filtered_plates AS (
   -- Apply user-defined filters BEFORE assigning row numbers
   SELECT
     id,
     barcode,
     name
   FROM
     yourtenantorgname.plate$raw
   WHERE
     archived$ = FALSE
     AND (id = ANY (NULL) OR NULL IS NULL)
 ),
 unique_plate_numbers AS (
   -- Assign row numbers ONLY to plates that match the user filter
   SELECT
     barcode AS plate_barcode,
     ROW_NUMBER() OVER (
       ORDER BY
         barcode ASC
     ) AS plate_number
   FROM
     filtered_plates
 )
SELECT
 upn.plate_number,
 fp.barcode AS plate_barcode,
 fp.name AS plate_name,
 wp.well_position,
 e.id AS sample_name
FROM
 filtered_plates fp
 JOIN well_position wp ON wp.plate_id = fp.id
 JOIN yourtenantorgname.container$raw c ON c.id = wp.id
 LEFT JOIN yourtenantorgname.container_content$raw cc ON cc.container_id = c.id
 LEFT JOIN yourtenantorgname.entity$raw e ON e.id = cc.entity_id
 LEFT JOIN unique_plate_numbers upn ON upn.plate_barcode = fp.barcode
ORDER BY
 upn.plate_number,
 wp.row_index,
 wp.column_index;  



 

 

Was this article helpful?

Have more questions? Submit a request