ReportSQL - Documents on Cases

Case Data > All Documents is great, but a bit limited, and overkill for some things.

The Document table is self-referential for folders, and therefore complicate to query.

Display the folder name instead of just the ID

Text formula field from the Case Data > All Documents subtable

(select name from folder where id = %tableAlias%.id)

Count of the number of documents on a case

Numeric formula field from the Case Data table

(WITH RECURSIVE folder_hierarchy AS (
    -- Base case: Only start with folders belonging to the matter
    SELECT 
        id AS folder_id, 
        module_id AS matter_id 
    FROM folder 
    WHERE module = 'matter' AND module_id = %tableAlias%.id

    UNION ALL

    -- Recursive step: Find all subfolders of those specific folders
    SELECT 
        f.id, 
        fh.matter_id
    FROM folder f
    INNER JOIN folder_hierarchy fh ON f.parent_id = fh.folder_id
),
all_matter_documents AS (
    -- 1. Documents linked directly to the matter
    SELECT 
        id AS document_id
    FROM document
    WHERE module = 'matter'
      AND module_id = %tableAlias%.id

    UNION

    -- 2. Documents found within the folder tree of Matter 49
    SELECT 
        d.id AS document_id
    FROM document d
    JOIN folder_hierarchy fh ON d.folder_id = fh.folder_id
)
SELECT 
    COUNT(document_id) AS total_documents
FROM all_matter_documents)

Count of the number of documents on a case with "cat" in the name

Text formula field on the Case Data table

title   is also a text field. type  is an integer from the Document Type lookup list.

(WITH RECURSIVE folder_hierarchy AS (
    -- Base case: Only start with folders belonging to the matter
    SELECT 
        id AS folder_id, 
        module_id AS matter_id 
    FROM folder 
    WHERE module = 'matter' AND module_id = %tableAlias%.id

    UNION ALL

    -- Recursive step: Find all subfolders of those specific folders
    SELECT 
        f.id, 
        fh.matter_id
    FROM folder f
    INNER JOIN folder_hierarchy fh ON f.parent_id = fh.folder_id
),
all_matter_documents AS (
    -- 1. Documents linked directly to the matter
    SELECT 
        id AS document_id
    FROM document
    WHERE module = 'matter'
      AND module_id = %tableAlias%.id
      AND name ILIKE '%cat%'

    UNION

    -- 2. Documents found within the folder tree of Matter 49
    SELECT 
        d.id AS document_id
    FROM document d
    JOIN folder_hierarchy fh ON d.folder_id = fh.folder_id
    WHERE d.name ILIKE '%cat%'
)
SELECT 
    COUNT(document_id) AS total_documents
FROM all_matter_documents)

Count the number of documents in a folder named "Intake Documents"

Numeric formula field on the Case Data table

(
WITH RECURSIVE folder_hierarchy AS (
    -- Base case: Find the specific starting folder
    SELECT 
        id AS folder_id, 
        module_id AS matter_id 
    FROM folder 
    WHERE module = 'matter' 
      AND module_id = %tableAlias%.id
      AND name = 'Intake Documents' -- Target folder name

    UNION ALL

    -- Recursive step: Find all subfolders beneath "Intake Documents"
    SELECT 
        f.id, 
        fh.matter_id
    FROM folder f
    INNER JOIN folder_hierarchy fh ON f.parent_id = fh.folder_id
),
all_matter_documents AS (
    -- We only look for documents inside the folder_hierarchy 
    -- (We skip the "direct to matter" documents since they aren't in the specific folder)
    SELECT 
        d.id AS document_id
    FROM document d
    JOIN folder_hierarchy fh ON d.folder_id = fh.folder_id
)
SELECT 
    COUNT(document_id) AS total_documents
FROM all_matter_documents
)