Zoho Analytics: Table Structure

Content

  • Introduction
  • extendedReach Data Tables in Zoho
  • Video Overview
  • Linking Tables

 

Introduction

extendedReach data is represented in Zoho Reports as “Tables”. Think of “Tables” as Excel spreadsheets that related or tied to one another. Zoho reports is aware of these relationships, making it easier to build drag and drop reports. For instance, if you build a report using the Case Table – Zoho will automatically determine which Foster Home from the Homes Table is tied to each case in the report based on the case’s current placement.

The Model Diagram in Zoho displays a visual representation of tables and their relationships within extendedReach. The tables are named after the information they contain, case information in the cases table, reports in the reports table, etc.

 

extendedReach Data Tables in Zoho

The following tables are configured in Zoho:

Table Description Related to
Activities Activities ­ both Case and Homes Cases, Homes, Staff
Cases Cases + Referrals ­ of all statuses Homes, Staff
CaseRelationships Case Relationships Cases
Census Cases/Homes that were active within a particular year, particular month, or on 1st day of any month Cases, Homes
DaysCare-Receivables Days Care and Billing Information for Cases Cases, Homes
Events Expiration / Scheduled dates for Cases, Homes, Staff Cases, Homes, Staff
Financials Financial information such as A/R and A/P for Cases, Homes, and Funding Organizations Cases, Homes
HomeRelations Home Relationships Homes
Homes Homes / Facilities Staff
Medications Case Medications Cases
Payables Payments to Homes Cases, Homes
Placements Placement of Cases in Homes or Facilities Cases, Homes
PrivateNotes Notes posted to the Foster Home website Homes
Reports Reports ­- both Cases and Homes Cases, Homes, Staff
Revisions Revision tracked fields and effective dates Cases, Homes
Staff Agency Staff N/A
StaffDocuments Agency Staff Documents Staff
StaffTraining Agency Staff Training Staff
Trainings Scheduled Training Classes – Trainings completed by Homes are found in the Activities table N/A
TreatmentGoals Treatment goals for Cases Cases

 

Video Overview

 

Linking Tables

The tables will already be linked and ready to go when you are ready to create your report, but if your agency has created their own custom table or a query table, you will want to make sure they are linked.

To view linked tables, click on the Model Diagram button.

Image: “Model Diagram”

 

To find out if the tables are connected, hover your mouse over a table. If they are connected, the table you are hovering over will turn blue and the linked tables will appear orange. For example, when you hover over the Homes table, you will see that it is a different color from the rest of the linked tables. If there was a table that was not linked to the Homes table, it would be grey, rather than orange.

Image: “Example of linked tables in the model diagram”

 

If you need to link a table, click on the table you want to link on the left-hand side. Click on Edit Design to add Lookup Columns.

Image: “Accessible tables”

Image: “Editing design of tables”

 

The top 3 tables you need to link are the ERHomeID, ERStaffID, and ERCaseID. Locate the column that needs to be linked and double click on the cell under Lookup Column. Use the drop down menu to find the corresponding name (e.g. ERStaffID = ERStaffID).

Image: “Matching Id’s”

 

Once you are finished, press Save. Return to the Model Diagram view to ensure that the tables are linked. Remember, when the tables are linked, they will turn a specific color and will no longer be grey.

You can also check if the tables were properly linked by creating a new report and selecting the base table that you linked. For example, if you chose Cases as your base table and linked CaseRelationships to that table, the fields for CaseRelationships will be available on the left-hand side.

Image: “Linked Tables”

Was this article helpful?
0 out of 0 found this helpful