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;