ReportSQL - Notes

The notes table doesn't follow modern conventions.

type is like module, like 'matter'
type_id is like module_id
note_type_id is lookup_note_type

Days since note entered

* numeric formula; ex. from multiple rows per note table

now()::date - %tableAlias%.timestamp_create::date

Last Edit Was by Original Poster

* boolean formula on Case Data > Case Notes (one row per case)

case when %tableAlias%.user_id = %tableAlias%.update_user_id then true else false end

Pull all email addresses case notes were sent to

* Text Formula on multiple notes per case table

(select string_agg(email, ', ')::text from note_email join notes on notes.id = note_email.note_id where notes.id = %tableAlias%.id)

Pull all users a Message was sent to

* Text Formula on Case Data > Associated Messages

(select string_agg(concat_ws(' ', person.first, person.middle, person.last), ', ' order by person.last, person.first) from message join message_recipients on message.id = message_recipients.message_id join users on message_recipients.user_id = users.id join person on person.id = users.person_id where message.id = %tableAlias%.id)

Most recent note variations

Text formula from Case Data

Grab body , subject , date_posted , etc.

(select body from notes where id = (select max(id) from notes where type = 'matter' and type_id = %tableAlias%.id))

Note of a certain Type (note_type_id)

(select body from notes where id = (select max(id) from notes where type = 'matter' and type_id = %tableAlias%.id and note_type_id = 3422749))
##
You can get the note_type_id by name, but then the expression will break if the name is changed:
(select id from lookup_note_type where name = 'Monthly case review note')

Pull certain note type (Notes as Columns, but here for an example)

array_accum appears to have been replaced by array_agg

(SELECT array_to_string(array_accum(body), E'\n') FROM notes WHERE note_type_id = 223127 AND type = 'matter' AND type_id = %tableAlias%.matter_id GROUP BY type_id)

Number of Days between posted and updated on case notes

* numeric formula on Case Data > Case Notes (one row per note)

* %tableAlias%.date_update::date - %tableAlias%.date_posted

Untested: Most recent note per date_posted, not timestamp_create

(select max(date_posted) from notes left join matter on notes.type_id = matter.id where matter.id = %tableAlias%.id)

User Name in System Log > User Notes (one row per note)

Because there's no Person subtable.

Name of the user record the note is on.

(select person.first from person left join users on person.id = users.person_id where users.id = %tableAlias%.type_id) || ' ' || (select person.last from person left join users on person.id = users.person_id where users.id = %tableAlias%.type_id)

Days Since Most Recent Note

• As of 2024-07-19 - this is available as Case Data > Days Since Most Recent Note
• Replacement for Case Data > Most Recent Note Time > Days Since Most Recent Note
• Numeric on Case Data

(select now()::date - (select max(date_posted) from notes where type = 'matter' and type_id = %tableAlias%.id))

Value in "Data transfer summary:" case note

• Text formula field from Case Data > Case Notes (Multiple Rows per Case)
• This example pulls the "Race: " line.

(SELECT *
FROM
    (SELECT unnest(string_to_array(body, E'\n')) AS line
    FROM notes 
    WHERE id = %tableAlias%.id
    ) t 
WHERE line LIKE '%Race: %')