How to Build a Custom Report: Joining Service Notes Datasets with Intake and Cases Datasets

This article will walk you through the steps needed to join Service Notes Recipients with the Intake or the any of the Cases Datasets.

In order to report on service note information with any intake or case information, you will have to join datasets to have access to all the needed fields. For more information about joining datasets, check out this Knowledge Base article

Note: The field resource_id refers to the record id that the note originates from. This id could be a case id, intake id, or provider id. 

How to join Service Notes or Service Notes Recipients with any dataset with case_id (Cases, Case Involvements, Case Involvements Recipients, Case Assignees): 

  1. Start with the Service Notes or Service Notes Recipients dataset. 
  2. Join your starting dataset, either Service Notes or Service Notes Recipients, with the Note Resources dataset using note_id = note_id. You must include the fields note_id and resource_id in your join. 
  3. Join the case dataset you wish to add by matching resource_id = case_id. You can then include any case-specific fields in your report.

Screen Shot 2023-01-25 at 11.08.05 AM

How to join Service Notes or Service Notes Recipients with any dataset with report_id (Intake): 

  1. Start with the Service Notes or Service Notes Recipients dataset. 
  2. Join your starting dataset, either Service Notes or Service Notes Recipients, with the Note Resources dataset using note_id = note_id. You must include the fields note_id and resource_id in your join. 
  3. Join the Intake dataset you wish to add by matching resource_id = report_id. You can then include any intake-specific fields in your report.

Screen Shot 2023-01-25 at 11.08.46 AM