Ă—
Get new posts in your inbox

A nifty feature in Zoho Reports is the way it supports relational data modeling. You can create or import a reporting database consisting of multiple tables which have relationships defined between them as in a relational database.

In relational databases world, tables are related using Foreign Key relationships. In Zoho Reports, this is done using the Lookup Column feature where a column in one table points to a column in another table.

Relating tables with Lookup columns

Let me explain this by creating a sample Employee database. This Database consists of two tables Department and Employee. The Department table has two columns – Department Name and Department ID.

The Employee table has the Employee names and their respective Employee IDs. The Department to which each Employee belongs to is referred by the third column Department ID

We define the Department ID in the Department table as the column that is being looked up by the Department ID column of the Employee table.

Click on the Edit Design button in the Employee table to define this relationship. Double-clicking on a cell in the Lookup Column lists the different tables & their columns. In our example, we choose the Department table’s Department ID.

Joining Tables with Query Table

Database tables which are connected to each other through such relationships allow you to fetch data combining the related tables. In Zoho Reports you can combine the data in such related tables by creating a Query Table which contains a SQL Select Join query. .
Enforces Data Integrity using Cascade-on-Delete

To ensure that the integrity of the data is maintained when data rows get deleted from related tables, Zoho Reports supports Cascade-on-Delete feature. That is, when rows are deleted in a parent table, then all the corresponding rows in the child table will be deleted automatically. In the above example, if any department gets deleted in the Department table (parent table), then all the employees will get deleted automatically from the Employee table (child table). For example, let’s say the Accounts department (row) is deleted.

Hope you find these relational modeling features in Zoho Reports useful. Give it a try and let us know your feedback in the comments or mail us at support at zohoreports dot com. We have planned for a lot more enhancements on relational modeling in the future. Stay tuned!

  1. leonordlp

    Hi! I’m using this function with my reports,but how can I add more than one table?? for example I’m having 4 tables:ORDERS
    RECEIVING
    PUTAWAY
    PROCESSINGSo this is my statement:SELECT o.OrderID, o.Customer as “Customer”, o.Subcustomer as “Subcustomer”, o.OrderNumber as “OrderNumber”, o.ShippingDate as “ShippingDate”, o.CancelDate as “CancelDate”, Function, “Unit Type”, “Total Units”
    FROM Orders o
    JOIN “RP RECEIVING1” ON “RP RECEIVING1”.OrderNumber = o.OrderNumber
    JOIN “RP PUTAWAY1” ON “RP PUTAWAY1”.OrderNumber = o.OrderNumber
    JOIN “RP PROCESSING1” ON “RP PROCESSING1”.OrderNumber = o.OrderNumberThe thing is it’s only adding data form the “RECEIVING” table… please help!

  2. leonordlp

    Hi! I’m using this function with my reports,but how can I add more than one table?? for example I’m having 4 tables:ORDERS
    RECEIVING
    PUTAWAY
    PROCESSINGSo this is my statement:SELECT o.OrderID, o.Customer as “Customer”, o.Subcustomer as “Subcustomer”, o.OrderNumber as “OrderNumber”, o.ShippingDate as “ShippingDate”, o.CancelDate as “CancelDate”, Function, “Unit Type”, “Total Units”
    FROM Orders o
    JOIN “RP RECEIVING1” ON “RP RECEIVING1”.OrderNumber = o.OrderNumber
    JOIN “RP PUTAWAY1” ON “RP PUTAWAY1”.OrderNumber = o.OrderNumber
    JOIN “RP PROCESSING1” ON “RP PROCESSING1”.OrderNumber = o.OrderNumberThe thing is it’s only adding data form the “RECEIVING” table… please help!

  3. Gabriel

    This is great. Now, if we could just have Zoho Creator be the User Interface builder for these tables ….

  4. Gabriel

    This is great. Now, if we could just have Zoho Creator be the User Interface builder for these tables ….