ReportSQL - Other Cases for Client ID

Useful if you are displaying a case and want to see any other case numbers for the same Client ID. In other words, humans have associated the cases.

• Text formula field on the top level Case Data table.
• Should also work on deeper tables, like Timekeeping > Cases, and so on.
• Note the "other cases" part of this that does not repeat the case number already shown in the row. If you'd rather have all cases, just remove the and identification_number <> %tableAlias%.identification_number  

(select string_agg(identification_number, '; ' order by identification_number) from matter where client_id = %tableAlias%.client_id and identification_number <> %tableAlias%.identification_number)

The column would look like this:

20-0000009; 22-0000026; 24-0000348; 24-0000579

Same thing, but the case numbers are clickable links:

(select string_agg('<a href="/matter/dynamic-profile/view/' || id || '">' || identification_number || '</a>', '; ' order by identification_number) from matter where client_id = %tableAlias%.client_id and identification_number <> %tableAlias%.identification_number)

Same thing, but the list includes client names, and handles group client names:

(select string_agg('<a href="/matter/dynamic-profile/view/' || m.id || '">' || m.identification_number || coalesce(' (' || nullif(case when m.is_group then coalesce(nullif(p.organization_name, ''), trim(concat_ws(' ', p.first, p.middle, p.last, p.suffix))) else coalesce(nullif(trim(concat_ws(' ', p.first, p.middle, p.last, p.suffix)), ''), p.organization_name) end, '') || ')', '') || '</a>', '; ' order by m.identification_number) from matter m left join person p on m.person_id = p.id where m.client_id = %tableAlias%.client_id and m.identification_number <> %tableAlias%.identification_number)

So the column will look like this:

20-0000009 (Kate Landry Anderson); 22-0000026 (Starbuck Amelia Truise); 24-0000348 (Kumiko Valeria Redding); 24-0000579 (Nastasya Padma Lee)

But wait, how can a bunch of associated cases have different client names? The above example is anonymized test data, so it's a bit extreme, but associated cases each point to a different person record, so client names can differ.

Jane Robinson might have a case from 5 years ago, and her current case is Jane Smith. But at some point a human has said "Yes, these are the same person, so I am going to associate the cases."