[STFF-104] Specification Details

Data Types Specifications 

The Data Types in the report that are outlined within this article are as follows: 

• Assessment
• Client Contact
• Client Field Interaction
• Client File
• Client Location
• Client Note
• Client ROI
• Coordinated Entry Event
• Profile Created
• Profile Deleted
• Profile Edited
• Program Annual Update
• Program Current Living Situation
• Program Enrollment
• Program Exit
• Program Follow-up
• Program Status Update
• Referral
• Service Provided/Updated
• Unit/Bed Assignment
  
  

Each Data Type above has a section within the article and is linked appropriately. 

Assessment

For "Assessment" staff activity, all rows are selected from the "common_logs" table and "client_assessment_demographics" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 8

• and activity DateTime (common_logs.date) is within [report date range]

• and client_assessment_demographics.ref_agency = CURRENT_AGENCY

• and client_assessment_demographics record is not deleted

• and client record is not deleted

Assessment Deleted

For "Assessment Deleted" staff activity, all rows are selected from the "client_assessment_demographics" table where:

• client_assessment_demographics.ref_user_updated = STAFF_ID

• and client_assessment_demographics.ref_agency_deleted = CURRENT_AGENCY

• and client_assessment_demographics.last_updated is within [report date range]

• and client_assessment_demographics.deleted = 1

Client Contact Created

When a client contact is added or an existing client contact is updated, a corresponding row is added to the "client_contacts" table.

For "Client Contact" staff activity, all rows are selected from the "client_contacts" table where:

• client_contacts.ref_user= STAFF_ID

• and activity DateTime (client_contacts.added_date) is within [report date range]

• and Client Contact is not deleted

• and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction

• and client record is not deleted

Client Contact Deleted

When a client contact is deleted, the "client_contacts" table is updated with "client_contacts.deleted = 1 AND client_contacts.ref_user_updated = staff_id".

For "Client Contact Deleted" staff activity, all rows are selected from the "client_contacts" table where:

• client_contacts.ref_user_updated = STAFF_ID

• and client_contacts.deleted = 1

• and activity DateTime (client_contacts.last_updated) is within [report date range]

• and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction

• and client record is not deleted

Client Contact Edited

When an existing client contact is updated, a current row from the "client_contacts" table is updated.

For "Client Contact Created/Edited" staff activity, all rows are selected from the "release_of_information" table where:

• client_contacts.ref_user= STAFF_ID

• and client_contacts.added_date <> client_contacts.last_updated

• and activity DateTime (client_contacts.added_date) is within [report date range]

• and Client Contact is not deleted

• and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction

• and client record is not deleted

Client Field Interaction

 When a Field Interaction is added, a corresponding row is added to the "geolocation_mapping," “geolocations,“ and “client_field_interactions" tables.

For "Field Interaction" staff activity, all rows are selected from the "client_field_interactions" table where:

• client_field_interactions.ref_user = STAFF_ID

• and client_field_interactions.ref_agency = CURRENT_AGENCY

• and activity DateTime (client_field_interactions.added_date) is within [report date range]

• and client record is not deleted

Client Field Interaction (Deleted)

When a Field Interaction is deleted, a corresponding row is added to the "common_logs" table. Also, the deleted field from geolocations is updated using the deleted = 1 value.

For "Field Interaction" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 40

• and common_logs.prev_record includes text "Field Interaction."

• and common_logs.curr_record includes the text "deleted."

• and there is a correspondent record in the geolocations table where geolocations.deleted = 1

• and geolocations.ref_agency_deleted = CURRENT_AGENCY

• and activity DateTime (common_logs.date) is within [report date range]

• and client record is not deleted

Client File

When a new client file is added a corresponding row is added to the "files" table and to the "client_files" table.

For "Client File" staff activity,  all rows are selected from the "files" table and the "client_files" table where:

• files.ref_user = STAFF_ID

• and activity DateTime (files.added_date) is within [report date range]

• and client_files.ref_agency = CURRENT_AGENCY

• and record from the "client_files" table is not deleted

• and client record is not deleted

Client File Deleted

When Client File is deleted, the "client_files" table is updated with "client_files.deleted = 1 AND client_files.ref_agency_deleted = current_agency AND client_files.ref_user_updated = staff_id".

For "Client File Deleted" staff activity, all rows are selected from the "client_files" table where:

• client_files.ref_user_updated = STAFF_ID

• and client_files.ref_agency_deleted= CURRENT_AGENCY

• and client_files.deleted = 1

• and activity DateTime (client_files.last_updated) is within [report date range]

• and client record is not deleted

• and file record is exists

Client Location

When a client location is added, a corresponding row is added to the "client_addresses" table.

For "Client Location" staff activity, all rows are selected from the "client_addresses" table where:

• client_addresses.ref_user= STAFF_ID

• and client_addresses.ref_agency = CURRENT_AGENCY

• and activity DateTime (client_addresses.added_date) is within [report date range]

• and client location is not deleted

• and client record is not deleted

Client Location Deleted

When Client Location is deleted, the "client_addresses" table is updated with "client_addresses.deleted = 1 AND client_addresses.ref_agency_deleted = current_agency AND client_addresses.ref_user_updated = staff_id".

For "Client Location Deleted" staff activity, all rows are selected from the "client_addresses" table where:

• client_addresses.ref_user_updated = STAFF_ID

• and client_addresses.ref_agency_deleted= CURRENT_AGENCY

• and client_addresses.deleted = 1

• and activity DateTime (client_addresses.last_updated) is within [report date range]

• and client record is not deleted

Client Note

When a new client note is added, a corresponding row is added to the "client_notes" table.

When an existing client note is updated, a corresponding row is added to the "client_notes_log" table. 

For "Client Note" staff activity, all rows are selected from the "client_notes" and the "client_notes_log" table where:

• the client note is not deleted

• and client_notes.ref_agency = CURRENT_AGENCY

• and (client_notes.ref_user or client_notes_log.ref_user ) = STAFF_ID

• and activity DateTime (client_notes.date or client_notes_log.date) is within [report date range]

• and client note is not deleted

• and client record is not deleted

Client Note Deleted

When Client Note is deleted, the "client_notes" table is updated with the "client_notes.deleted = 1 AND client_notes.ref_agency_deleted = current_agency AND client_notes.ref_user_updated = staff_id".

For "Client Note Deleted" staff activity, all rows are selected from the "client_notes" table where:

• client_notes.ref_user_updated = STAFF_ID

• and client_notes.ref_agency_deleted= CURRENT_AGENCY

• and client_notes.deleted = 1

• and activity DateTime (client_notes.last_updated) is within [report date range]

• and client record is not deleted

Client ROI

For "Client ROI" staff activity, all rows are selected from the "release_of_information" table where:

• release_of_information.ref_user= STAFF_ID

• and release_of_information.ref_agency = CURRENT_AGENCY

• and activity DateTime (release_of_information.added_date) is within [report date range]

• and client ROI is not deleted

• and client record is not deleted

Client ROI Deleted

For "Client ROI Deleted" staff activity, all rows are selected from the "release_of_information" table where:

• release_of_information.ref_user_updated = STAFF_ID

• and release_of_information.ref_agency_deleted= CURRENT_AGENCY

• and release_of_information.deleted = 1

• and activity DateTime (release_of_information.last_updated) is within [report date range]

• and client record is not deleted

Coordinated Entry Event

When an existing Coordinated Entry Event is created or updated, a corresponding row is added to the "common_logs" table.

For "Coordinated Entry Event” staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 9

• and a Coordinated Entry Event is manually entered OR inferred

• and activity DateTime (clients_log.date) is within [report date range]

• and programs.ref_agency = CURRENT_AGENCY where the “programs” is a program linked to the client enrollment that is linked to the Coordinated Entry Event

• and the client record/event is not deleted. The referral should not be deleted for cases where the Coordinated Entry Event is inferred.

Coordinated Entry Event (Deleted)

When an existing Coordinated Entry Event is deleted, "client_events" table is updated with "client_events.deleted = 1 AND client_events.ref_agency_deleted = current_agency AND client_events.ref_user_updated = staff_id".

For "Coordinated Entry Event Deleted” staff activity, all rows are selected from the "common_logs" table where:

• client_events.ref_user_updated = STAFF_ID

• and client record is not deleted

• and agency project is not deleted

• Coordinated Entry Event is manually entered

  • and client_events.last_updated is within [report date range]

  • and client_events.ref_agency_deleted= CURRENT_AGENCY

• Coordinated Entry Event is inferred

  • and referrals.last_updated is within [report date range]

  • and referrals.ref_agency_deleted= CURRENT_AGENCY

Profile Created

When a new client record is added, a corresponding row is added to the "common_logs" table.

For "Profile Created" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID
• and common_logs.source_table = 12
• and it is the profile creation record
• and activity DateTime (common_logs.date) is within [report date range]
• and if the client is private, then clients.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
• and the client record is not deleted

Profile Deleted

When the client record is deleted, the "clients" table is updated with "clients.deleted = 1.

For "Profile Deleted" staff activity, all rows are selected from the "clients" table where:

• clients.deleted = 1
• and clients.last_updated is within [report date range]
• and if the client is private, then clients.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction

Profile Edited

When an existing client record is updated, a corresponding row is added to the "common_logs" table.

For "Profile Edited" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID
• and common_logs.source_table = 12
• and it is the profile update record
• and activity DateTime (clients_log.date) is within [report date range]
• and if the client is private, then clients.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
• and the client record is not deleted

Program Annual Update

When a status update is added or an existing status update is updated, a corresponding row is added to the "common_logs" table. 

For "Program Annual Update" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 10

• and common_logs.screen_type = 6

• and activity DateTime (common_logs.date) is within [report date range]

• and client_programs.ref_agency = CURRENT_AGENCY

• and client record is not deleted

• and the client's project stay is not deleted

• and agency project is not deleted

Program Annual Update Deleted

For "Program Annual Update Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

• client_program_demographics.ref_user_updated = STAFF_ID

• and client_program_demographics.screen_type = 6

• and client_program_demographics.deleted = 1

• and client_program_demographics.last_updated is within [report date range]

• and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY

• and client's project stay is not deleted

• and client record is not deleted

• and agency project is not deleted

Program Current Living Situation

When a current living situation is added or an existing current living situation is updated, a corresponding row is added to the "common_logs" table. 

For "Program Current Living Situation" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 10

• and common_logs.screen_type = 3

• and common_logs.status_screen_type= 2

• and activity DateTime (common_logs.date) is within [report date range]

• and client_programs.ref_agency = CURRENT_AGENCY

• and client record is not deleted

• and the client's project stay is not deleted

• and agency project is not deleted

Program Current Living Situation Deleted 

When a client's project current living situation screen is deleted, the "client_program_demographics" table is updated with "client_program_demographics.deleted = 1 AND client_program_demographics.ref_agency_deleted = current_agency AND client_program_demographics.ref_user_updated = staff_id".

For "current living situation Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

• client_program_demographics.ref_user_updated = STAFF_ID

• and client_program_demographics.screen_type = 3

• and client_program_demographics.status_screen_type= 2

• and client_program_demographics.deleted = 1

• and client_program_demographics.last_updated is within [report date range]

• and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY

• and client's project stay is not deleted

• and client record is not deleted

• and agency project is not deleted

Program Enrollment

When a  project enrollment is created or updated, a corresponding row is added to the "common_logs" table.

For "Program Enrollment" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 10

• and common_logs.screen_type = 2

• and activity DateTime (common_logs.date) is within [report date range]

• and client_programs.ref_agency = CURRENT_AGENCY

• and client record is not deleted

• and client project stay is not deleted

• and agency project is not deleted

Program Enrollment Deleted

When client project stay is deleted, "client_programs" table is updated with "client_programs.deleted = 1 AND client_programs.ref_agency_deleted = current_agency AND client_programs.ref_user_updated = staff_id".

For "Program Enrollment Deleted" staff activity, all rows are selected from the "client_programs" table where:

• client_programs.ref_user_updated = STAFF_ID

• and client_programs.deleted = 1

• and client_programs.last_updated is within [report date range]

• and clients.ref_agency_deleted = CURRENT_AGENCY

• and client record is not deleted

• and agency project is not deleted

Program Exit

For "Program Exit" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 10

• and common_logs.screen_type = 4

• and activity DateTime (common_logs.date) is within [report date range]

• and client_programs.ref_agency = CURRENT_AGENCY

• and client record is not deleted

• and client project stay is not deleted

• and agency project is not deleted

Program Exit Deleted

When an exit screen is deleted or deleted exit screen is updated, a corresponding row is added to the "common_logs" table.

For "Program Exit Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

• client_program_demographics.ref_user_updated = STAFF_ID

• and client_program_demographics.screen_type = 4

• and client_program_demographics.deleted = 1

• and client_program_demographics.last_updated is within [report date range]

• and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY

• and client project stay is not deleted

• and client record is not deleted

• and agency project is not deleted

OR all rows are selected from the "client_programs_log" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 10

• and common_logs.screen_type = 4

• and activity DateTime (common_logs.date) is within [report date range]

• and JSON_EXTRACT( common_logs.prev_record, "$.program_date" ) != "null"

• and JSON_EXTRACT( common_logs.prev_record, "$.program_date" ) != ""

• and JSON_EXTRACT( common_logs.prev_record, "$.program_date" ) IS NOT NULL

• and ( JSON_EXTRACT( common_logs.curr_record, "$.program_date" ) = "null"

  • or JSON_EXTRACT( common_logs.curr_record, "$.program_date" ) = ""

  • or JSON_EXTRACT( common_logs.curr_record, "$.program_date" ) IS NULL
    )

• and client_programs.ref_agency = CURRENT_AGENCY

• and client record is not deleted

• and client project stay is not deleted

• and agency project is not deleted

Program Follow-up

When a follow-up screen is added or an existing follow-up screen is updated, a corresponding row is added to the "common_logs" table.

For "Program Follow-up" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 10

• and common_logs.screen_type = 5

• and activity DateTime (common_logs.date) is within [report date range]

• and client_programs.ref_agency = CURRENT_AGENCY

• and client record is not deleted

• and client project stay is not deleted

• and agency project is not deleted

Program Follow-up Deleted

When client follow-up screen is deleted, the"client_program_demographics" table is updated with "client_program_demographics.deleted = 1 AND client_program_demographics.ref_agency_deleted = current_agency AND client_program_demographics.ref_user_updated = staff_id".

For "Program Follow-up Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

• client_program_demographics.ref_user_updated = STAFF_ID

• and client_program_demographics.screen_type = 5

• and client_program_demographics.deleted = 1

• and client_program_demographics.last_updated is within [report date range]

• and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY

• and client project stay is not deleted

• and client record is not deleted

• and agency project is not deleted

Program Status Update

When a status update is added or an existing status update is updated, a corresponding row is added to the "common_logs" table.

For "Program Status Update" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 10

• and common_logs.screen_type = 3

• and activity DateTime (common_logs.date) is within [report date range]

• and client_programs.ref_agency = CURRENT_AGENCY

• and client record is not deleted

• and client project stay is not deleted

• and agency project is not deleted

Program Status Update Deleted

When a status update screen is deleted, the "client_program_demographics" table is updated with "client_program_demographics.deleted = 1 AND client_program_demographics.ref_agency_deleted = current_agency AND client_program_demographics.ref_user_updated = staff_id".

For "Program Status Update Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

• client_program_demographics.ref_user_updated = STAFF_ID

• and client_program_demographics.screen_type = 3

• and client_program_demographics.deleted = 1

• and client_program_demographics.last_updated is within [report date range]

• and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY

• and client project stay is not deleted

• and client record is not deleted

• and agency project is not deleted

Client Referral

When a client referral is added, a corresponding row is added to the "referrals" table.

For "Client Referral" staff activity, all rows are selected from the "referrals" table where:

• referrals.ref_user= STAFF_ID

• and referrals.ref_agency = CURRENT_AGENCY

• and activity DateTime (referrals.date) is within [report date range]

• and referrals record is not deleted

• and client record is not deleted

Client Referral Deleted

When Client Referral is deleted, the "referrals" table is updated with "referrals.deleted = 1 AND referrals.ref_agency_deleted = current_agency AND referrals.ref_user_updated = staff_id".

For "Client Referrals Deleted" staff activity, all rows are selected from the "referrals" table where:

• referrals.ref_user_updated = STAFF_ID

• and referrals.ref_agency_deleted= CURRENT_AGENCY

• and referrals.deleted = 1

• and activity DateTime (referrals.last_updated) is within [report date range]

• and client record is not deleted

Service Provided/Service Updated

When a new service is added or updated, a corresponding row is added to the "common_logs" table.

For "Service Provided" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 11

• and activity DateTime (common_logs.date) is within [report date range]

• and client_services record is not deleted

• and client_services.ref_agency = CURRENT_AGENCY

• and service item record is not deleted

• and service record is not deleted

• and client record is not deleted

In addition, if the conditions below are true the service is pulled as “Service Provided;” in other cases, the service is pulled as “Service Updated”

• and ( IFNULL( JSON_LENGTH( common_logs.prev_record ), 0 ) = 0

  • or IF( common_logs.prev_record IN ( ““, [““], “null“ ), 0, 1 ) = 0 )

• and JSON_EXTRACT( common_logs.curr_record, "$.id" ) IS NOT NULL

Service Deleted

For "Service Deleted" staff activity, all rows are selected from the "client_services" table where:

• client_services.ref_user_updated = STAFF_ID

• and client_services.deleted = 1

• and client_services.ref_agency_deleted= CURRENT_AGENCY

• and client_services.last_updated is within [report date range]

• and service item record is not deleted

• and service record is not deleted

• and client record is not deleted

Unit/Bed Assignment

When an existing Unit/Bed Assignment is created or updated, a corresponding row is added to the "common_logs" table.

For "Unit/Bed Assignment (Enrollment)" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 34

• and Unit/Bed is Occupied through a direct assignment through an enrollment

• and activity DateTime (clients_log.date) is within [report date range]

• and unit_occupancy.ref_agency = CURRENT_AGENCY

• and the client record/assignment is not deleted

For "Unit/Bed Assignments(Referral)" staff activity, all rows are selected from the "common_logs" table where:

• common_logs.ref_user = STAFF_ID

• and common_logs.source_table = 34

• and Unit/Bed is Occupied through a referral to the unit/bed

• and activity DateTime (clients_log.date) is within [report date range]

• and unit_occupancy.ref_agency = CURRENT_AGENCY

• and the client record/assignment is not deleted

Unit/Bed Assignment (Deleted)

When Unit/Bed Assignment is deleted, "unit_occupancy" table is updated with "unit_occupancy.deleted = 1 AND unit_occupancy.ref_agency_deleted = current_agency AND unit_occupancy.ref_user_updated = staff_id".

For "Unit/Bed Assignment (Enrollment)" staff activity, all rows are selected from the "common_logs" table where:

• unit_occupancy.ref_user_updated = STAFF_ID

• and unit_occupancy.deleted = 1

• and Unit/Bed is Occupied through a direct assignment through enrollment

• and unit_occupancy.last_updated is within [report date range]

• and unit_occupancy.ref_agency_deleted= CURRENT_AGENCY

• and client record is not deleted

• and agency project is not deleted

For "Unit/Bed Assignment (Referral)" staff activity, all rows are selected from the "common_logs" table where:

• unit_occupancy.ref_user_updated = STAFF_ID

• and unit_occupancy.deleted = 1

• and Unit/Bed is Occupied through a referral to the unit/bed

• and unit_occupancy.last_updated is within [report date range]

• and unit_occupancy.ref_agency_deleted= CURRENT_AGENCY

• and client record is not deleted

Updated: 05/15/2024