ReportSQL - Notes

The notes table doesn't follow the typical naming conventions:

type is module, like 'matter', 'outreach', etc.
type_id is module_id, an integer
note_type_id is lookup_note_type

Days since note entered

* numeric formula field from a multiple rows per note table like Case Data > Case Notes

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')

If you want to include notes from timeslips on a case, substituting subject , date_posted , etc. for body

(select body
from notes
where id = greatest(
    (select max(id)
     from notes where type = 'matter' and type_id = %tableAlias%.id),
    (select max(notes.id)
     from notes
     join service on notes.type_id = service.id
     where notes.type = 'service'
     and service.module = 'matter'
     and service.module_id = %tableAlias%.id
    )
  )
)

Days Since Most Recent Note

• As of 2024-07-19 - this is available as as a field - Case Data > Days Since Most Recent Note
• Numeric formula field on Case Data

Here's the expression for reference:

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

If you want to include case (matter) and timekeeping (service) notes:

(select now()::date - date_posted
 from notes
 where id = greatest(
    (select max(id)
     from notes
     where type = 'matter'
     and type_id = %tableAlias%.id),
    (select max(notes.id)
     from notes
     join service on notes.type_id = service.id
     where notes.type = 'service'
     and service.module = 'matter'
     and service.module_id = %tableAlias%.id
    )
  )
)

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)

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: %')