Zoho Table Structure
- extendedReach Data Tables in Zoho
- Video Overview
- Linking Tables
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:
|Activities||Activities conducted on cases|
|Adjustments||Financial adjustments made to cases|
|Appointments||All appointment information for cases|
|Authorizations||Financial authorizations made for cases|
|Cases||Cases + Referrals of all statuses|
|Census||Cases that were active within a particular year, particular month, or on 1st day of any month|
|Claims||Claims made or to be made for cases|
|FundingOrganizations||Includes all information regarding funding organizations|
|GeneralActivities||General activities for cases|
|Insurance||Includes all information regarding a case’s insurance information|
|Invoices||Lists all invoice information|
|PaymentApplied||Payments that have been applied|
|Payments||General payments that have not yet been applied|
|PrepaidFunding||Includes all prepaid funding information|
|Relationships||Case relationships located on the People tab|
|Revisions||Revision tracked fields and effective dates|
|StaffDocuments||Agency Staff Documents|
|StaffTraining||Agency Staff Training|
|TreatmentGoals||Treatment goals for Cases|
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 Authorizations to that table, the fields for Authorizations will be available on the left-hand side.
Image: “Linked Tables”