Kaizen 255 Building a Real-Time Operational Dashboard with Zoho CRM Queries

Kaizen 255 Building a Real-Time Operational Dashboard with Zoho CRM Queries



Hello Everyone,

Welcome back to another edition of the Kaizen series, where we uncover powerful ways to extend and customize Zoho CRM.

In the previous Query Kaizens, we explored how Queries can retrieve CRM data, invoke REST APIs, and even update CRM records. In Kaizen #250, we demonstrated how a Kiosk used a Query to identify the most suitable Field Engineer and automatically assign that engineer to a Service Request during a Blueprint transition.

In this Kaizen, we'll use a Query serializer to transform the raw Query response into enriched business data by calculating additional fields at runtime. This demonstrates that serializers can do much more than rename columns. They can derive new values from existing CRM data and present actionable insights that go beyond what a standard report typically provides.

Quote

Business Scenario

Zylker Home Services receives hundreds of installation, maintenance, and repair requests every week. The Service Manager reviews ongoing Service Requests to monitor work progress, identify overdue jobs, and ensure Field Engineers are assigned effectively.
While Zoho CRM reports can display the available data, the manager often needs additional operational insights that are not stored in CRM. For example:
  1.  How many days has a request been overdue? 
  2.  Which requests should be treated as high priority? 
  3.  Has the service request breached its SLA? 
  4.  Does the request require immediate escalation? 
  5.  Is the target completion date falling on a weekend? 
These insights are derived by evaluating multiple fields, rather than by simply displaying existing CRM fields.
Instead of exporting data or manually interpreting reports, the Service Manager wants a single dashboard that not only displays Service Request information but also computes these decision-making indicators in real time, enabling quicker and more informed operational decisions.

Solution Overview

The dashboard is powered by a Query that retrieves CRM data and a serializer that computes operational indicators dynamically for every execution.

Idea

Architecture Overview

Presentation Layer

A single Kiosk is embedded on the Service Manager's CRM Home page. The Kiosk displays a tabular operational dashboard containing all Service Requests.

Data Access Layer

The Kiosk is powered by a Module Query on the Service Applications module.
The Query retrieves live CRM data, while the serializer transforms and enriches the response before it reaches the Kiosk by:
  1.  Renaming fields 
  2.  Computing overdue duration 
  3.  Deriving SLA status 
  4.  Determining request priority 
  5.  Identifying requests requiring escalation 
  6.  Flagging weekend target dates 
  7.  Replacing missing values with meaningful messages 

Data Layer

The dashboard reads live data directly from the Service Applications module and its related Field Engineer records, ensuring managers always view the latest CRM data without synchronizations or exports.

Implementation

Step 1: Create the Query

Navigate to: Setup → Developer Space → Queries

Configure the Query using the following settings to retrieve Service Application records for the operational dashboard.

Source: Zoho CRM → Module
Module: Service Applications
Fields:
  1.  Name 
  2.  Service Request Type 
  3.  Zone 
  4.  Target Completion Date 
  5.  Reason For Delay 
  6.  Field Engineer Name 
  7.  Field Engineer Status 
  8.  Field Engineer Specialization 
  9.  Field Engineer Phone 
No additional filtering is applied so that both assigned and unassigned Service Requests are returned.


Serializer

The serializer calculates these additional fields dynamically whenever the Query executes. They are recalculated each time the Home page loads rather than being retrieved as stored CRM fields. The serializer not only renames fields but also enriches the response by calculating additional operational information.
It derives: 
  1. Overdue By 
  2. Priority 
  3. SLA Status
  4. Escalation Required
  5. Weekend Warning
const today = new Date();

return result.map(record => {

    let overdueDays = null;
    let priority = "Not Available";
    let slaStatus = "Unknown";
    let escalationRequired = "Cannot Determine";
    let weekendWarning = "N/A";

    if (record.Target_Completion_Date) {

        const targetDate = new Date(record.Target_Completion_Date);

        overdueDays = Math.max(
            0,
            Math.floor((today - targetDate) / (1000 * 60 * 60 * 24))
        );

        // Priority
        if (overdueDays >= 15) {
            priority = "🔴 Critical";
        } else if (overdueDays >= 7) {
            priority = "🟠 High";
        } else if (overdueDays > 0) {
            priority = "🟡 Medium";
        } else {
            priority = "🟢 On Track";
        }

        // SLA Status
        if (overdueDays === 0) {
            slaStatus = "🟢 Within SLA";
        } else if (overdueDays <= 3) {
            slaStatus = "🟡 Approaching SLA";
        } else {
            slaStatus = "🔴 SLA Breached";
        }

        // Escalation Required
        escalationRequired =
            overdueDays > 7
                ? "🔴 Escalate Immediately"
                : "No";

        // Weekend Warning
        const day = targetDate.getDay();
        weekendWarning =
            (day === 0 || day === 6)
                ? "⚠ Weekend Target"
                : "Working Day";
    }

    return {
        "Service Request": record.Name,

        "Service Request Type": record.Service_Request_Type,

        "Zone": record.Zone,

        "Target Completion Date":
            record.Target_Completion_Date || "Date Not Provided",

        "Overdue By":
            overdueDays !== null
                ? (overdueDays > 0 ? overdueDays + " day(s)" : "Not Overdue")
                : "Date Not Provided",

        "SLA Status": slaStatus,

        "Priority": priority,

        "Escalation Required": escalationRequired,

        "Weekend Warning": weekendWarning,

        "Reason For Delay":
            record.Reason_For_Delay || "Not Provided",

        "Field Engineer Name":
            record["Field_Engineer.Name"]|| "Not Assigned",

        "Field Engineer Status":
            record["Field_Engineer.Status"]|| "Engineer Not assigned",

        "Field Engineer Specialization":
            record["Field_Engineer.Specialization"]|| "Engineer Not assigned",

        "Field Engineer Phone":
            record["Field_Engineer.Phone"] || "Phone Not Available"
    };
});

The following table summarizes the additional business insights derived by the serializer, describing how it is calculated.

Derived Field
How the Serializer Calculates It
Example Output
Overdue By
Calculates the difference between the current date (today) and the Target Completion Date. If the request is not overdue, it displays Not Overdue. If the target completion date is unavailable, it displays Date Not Provided.
12 days(s)
Not OverdueDate
Priority
Determines the priority based on the calculated Overdue By value. Requests overdue by 15 or more days are marked Critical, those overdue by 7–14 days are marked High, those overdue by 1–6 days are marked Medium, and requests that are not overdue are marked On Track.
🔴 Critical
🟠 High
🟡 Medium
🟢 On Track
SLA Status
Uses the calculated overdue duration to determine whether the request is Within SLA, Approaching SLA, or SLA Breached. If the target completion date is unavailable, the serializer returns Unknown.
🟢 Within SLA
🟡 Approaching SLA
🔴 SLA Breached
Escalation Required
Checks whether the request has been overdue for more than 7 days. If so, the serializer flags it for escalation; otherwise, it indicates that escalation is not required.
🔴 Escalate Immediately
Weekend Warning
Determines the day of the week from the Target Completion Date using getDay(). If the date falls on Saturday or Sunday, the serializer displays a warning; otherwise, it indicates a normal working day.
⚠ Weekend Target
Working Day
Null Value Handling
Replaces missing values with meaningful messages.
Not Provided 
Phone Not Available.

Step 2: Associate Queries with Kiosks

Navigate to Setup → Customization → Kiosk Studio and create a Kiosk.
  1.  Add the screen state. 
  2.  Choose Get data via Queries as the data element. 
  3.  Select the corresponding Query. 
  4.  Configure the table columns using the serializer output. 
  5.  Save and publish the Kiosk. 

Step3: Associate the Kiosk to Manager's Home Page

Navigate to Setup -> Customization -> Customize CRM Home.
  1.  Edit the Service Manager Home dashboard. 
  2.  Click Kiosk from the Dashboard Components. 
  3.  Drag and Drop the Kiosk created in Step2. Arrange the Kiosk according to your preferred layout. 
  4.  Save the dashboard. 

Service Manager Home Page

The Service Manager Home page displaying the Service Request Summary dashboard powered by Zoho CRM Queries and serializers.






Queries are more than a mechanism for retrieving CRM records. Combined with serializers, they become a lightweight presentation layer capable of enriching, transforming, and deriving business insights from live CRM data before it is displayed to users.

In this Kaizen, we built a real-time operational dashboard powered by a single Query. Rather than simply displaying stored CRM fields, the serializer calculated metrics providing Service Managers with actionable information the moment they log into CRM.
This demonstrates that Queries can go beyond traditional reporting by not only retrieving data but also preparing it for consumption in a way that supports faster and more informed operational decision-making.

We hope you found this post useful. 
We would love to hear from you! Write to us at support@zohocrm.com or let us know in the comments section.

Happy Querying!


    • Sticky Posts

    • Announcing the new SKILL.md for Zoho CRM and the updated OAS repository!

      We are introducing a new zoho-crm skill to make working with Zoho CRM Developer tools (like APIs, functions, widgets, client scripts, queries etc) easier and faster, with the help of AI in your preferred AI harness like Claude Code, Codex, Cursor, VSCode

    • Kaizen #261 : Using Zoho CRM Skill to generate complex COQL queries

      Hello everyone, In the previous Kaizen posts, we introduced the zoho-crm SKILL.md and discussed how it can help you when you are working with Zoho CRM APIs and Client Script. In this Kaizen, we will focus on using the Zoho CRM `SKILL.md` with GitHub Copilot

    • Kaizen #256 - Build an Arrival Readiness Web Tab in Zoho CRM

      Hi everyone! Welcome back to the Kaizen series! In the post, we discuss a use case in hospitality industry: how an Arrival Readiness web tab widget can be used to let reception staff identify and resolve issues before arrival of guests. Use case In the

    • Kaizen #198: Using Client Script for Custom Validation in Blueprint

      Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

    • Kaizen #226: Using ZRC in Client Script

      Hello everyone! Welcome to another week of Kaizen. In today's post, lets see what is ZRC (Zoho Request Client) and how we can use ZRC methods in Client Script to get inputs from a Salesperson and update the Lead status with a single button click. In this

    • Recent Topics

    • Solution to Import OST File into Office 365.

      In today’s digital workplace, organizations are rapidly moving toward cloud-based email systems for better flexibility, security, and collaboration. Microsoft 365 has become one of the most widely used platforms for business communication. A common requirement

    • How can I show an uploaded image larger/full size in a Zoho Forms response PDF?

      Hi,  I noticed that in a response PDF for Forms, the uploaded image is shown in a tiny box as seen in my screenshot. Is there any way I can make that appear larger in the response PDF? thanks.

    • Live connection to DS files in Zoho Creator / ChatGPT

      As an idea, we are using ChatGPT to build with Zoho Creator (outside of Zia), which is works brilliantly to assist write deluge, provide test suggesitons etc. Currently we are uploading the DS files which are very point in time, it would be great to have

    • Meet PageHealth by Zoho PageSense: a free way to find out what's holding your page back

      We all know the feeling. You've spent days tweaking headlines, moving buttons, and rewriting copy, and you're still not sure why visitors aren't sticking around. Revamping your website is hard, and figuring out what's actually getting in the way is even

    • Export Exchange Online Archive Mailboxes to PST for Microsft 365 Users

      With Microsoft 365 it is possible to archive older emails in Exchange Online in order to keep the main mailbox tidy. If however you need to export the archived emails to a PST file for the purposes of backup, migration, compliance or for use offline,

    • Task Module quick create settings

      Hello, requesting the Task module receive a quick create customization settings. We have added custom fields and have to select "More fields" at the bottom of the pop-up to access the custom fields.

    • Your employees changed departments. Did your IT systems notice?

      An employee joins your Marketing team. HR adds their details to the organization's user directory. The IT admin, in turn, creates their Zoho One account, assigns the required applications, adds them to groups and provides access based on the organization's

    • AI Calling

      Hi, does anyone have experience or any thoughts on AI Calling. Getting AI to make calls on behalf of your company. We are looking into it, but are finding it difficult to get info and providers that can assist.

    • Email Forwarding to Zoho Desk Failing After Migration from Gmail to Outlook (Microsoft)

      We recently migrated from Gmail to Microsoft Outlook. Since the migration date, emails sent by our customers are no longer being received by Zoho Desk. I tried setting up the email forwarding rules in Outlook similar to what we had in Gmail, but it still

    • Option to Disable Knowledge Base Section in Feedback Widget Popup Hello Zoho Desk Team

      Hello Zoho Desk Team, How are you? We are actively using Zoho Desk and would like to make more use of the Feedback Widget. One of the ways we implement it is through the popup option. At the moment, the popup always displays the Knowledge Base section,

    • How to Fix an Outlook OST File That Cannot Be Opened

      Have you at any time attempted to open Outlook only to find that your OST file cannot be accessed? It can be very annoying, particularly since the file holds important emails, attachments, and other mailbox data. This problem might arise due to file corruption,

    • Record positive and negative bank transactions in the same expense account

      Hello, I hosted a promotional event and paid a vendor $600 for food catering (bento boxes). Twenty people attended the event, and each person paid $30 for a bento box, resulting in 20 separate $30 transactions deposited into the bank. I coded the $600

    • Is it possible to log text messages from iphone into Zoho CRM

      My team is more and more utilizing text messaging to communicate with clients. Does Zoho have a solution for logging these text messages when clicking "Send Text Message" from the Zoho app. Much like when you call, you get prompted to log a call, can

    • CRM Address Field - States with Countries in brackets

      Hi, Could you please remove the country in brackets from the Global State List, or at least allow us to edit these entries? It looks like the brackets were added during the initial data population, perhaps by someone building the list in a spreadsheet

    • 👍 Zoho CRM's Notes now gets Reactions and a new look

      Available in SA and JP DCs. Rolling out to other DCs in phases. Hello everyone, Notes help users capture important updates, collaborate with teammates, and maintain context for records. Now with Note Reactions, users can quickly acknowledge updates, express

    • How to mass find and delete duplicate files from OneDrive

      It is easy to save documents, photos, videos, and all other types of files using Onedrive, but if you upload the same files more than once then your OneDrive storage can end up being filled with a large number of duplicate files. Manually searching out

    • Different Report sharing filters within same Dashboard

      I have a question about sharing filters for a dashboard which contains separate reports built from the same query. Below are the pieces of the puzzle: - “Sales Query”: line-level query containing sales for a team of 5 sales reps; there’s no need for the

    • Specifying time increments on a Date/Time field

      Hi all, Im using a Date/Time field in my CRM module. At the moment the increments are 30 minutes. How can I change these to be 15 minutes? Thanks

    • Make Your Workflows Smarter with Zia Agents

      Hello, Enterprise Support Community, Workflow rules can now go beyond predefined actions. Zia Agents can now be invoked directly from workflow rules in Zoho CRM, allowing you to bring contextual, AI powered actions into your automated processes. Choose

    • How can I add a Custom Field (or Customer_name) to a picklist?

      Our workflow is Shopify-->SO -->Picklist-->Package-->Shipment. We would like to have the Picklist template to include a custom field "Customer_Notes" (we have created this field already). This way we get alerted when a customer has notes on their account.

    • DYK 14: Create Tasks with AI

      Did you know AI can suggest tasks within your Zoho Projects portal? Breaking a project into clear, actionable work items takes time and manual effort. Especially when the same team handles both planning and execution. AI task suggestions in Zoho Projects

    • #11 Nithya's First Overseas Client

      Nithya designs for clients in three countries and has never met any of them. Her first overseas invoice took a full evening. The second took four minutes. The difference was not a clever tax trick. She stopped treating every international customer as

    • Other languages in Schedulers (Zoho Books)

      Hello everyone, currently we have create functions in other languages, like nodejs etc but on schedulers we can use only deluge.. Are other languages on schedulers roll out? Kind regards, Sakis Oikonomou

    • New in Office Integrator: Extract data from images into your spreadsheet

      Hi users, We are pleased to introduce the Data from Picture feature in Zoho Office Integrator's spreadsheet editor. It intelligently detects and organizes text and numbers from any image into structured cells using OCR. With this feature, users can turn

    • Edit PDFs using Zoho PDF Editor

      Hello users, In a previous article, we covered the organizing capabilities of Zoho PDF Editor. Here, we'll provide a quick recap of its editing features, including a few new additions. A few highlights of Zoho PDF Editor's editing capabilities: Add and

    • Partner with HDFC And Sbi Bank.

      Hdfc and sbi both are very popular bank if zoho books become partner with this banks then many of the zoho books users will benefit premium features of partnered banks.

    • Multiple subscription plan line items in Zoho Billing quotes

      I run an IT company and sell a lot of licences. A client might have multiple subscription plans including software licences in different categories. A client might have Microsoft licences, antivirus licences, web hosting and maintenance package services.

    • Mail Merge templates in Sandbox

      Rolled out for all DCs Hello everyone, You can now create and test Mail Merge templates directly in Sandbox, making it easier to validate document generation without affecting your live organization. Why this matters Mail Merge templates are often used

    • Dashboard Table of “My New Work Orders” Changes View

      Hi FSM Team, We noticed an inconsistency with the Dashboard Table of “My New Work Orders.” Initially, the table is configured to use the “My New Work Orders” view. When we go to the Home page, the table displays a different set of records. When we return

    • Tips & Tricks #6 | Zoho Vault: Audita actividad, Gestiona accesos, Comparte credenciales & mucho más.

      ¿Sabías que Zoho Vault puede hacer mucho más por la seguridad de tu equipo? Las contraseñas forman parte del día a día de cualquier persona y organización. Aplicaciones, herramientas internas, cuentas de clientes, servicios externos, plataformas de marketing,

    • Inquiry Regarding Customer Hierarchy & Sub-Customer Creation via Zoho API

      Dear Zoho Support Team, Our mutual client has requested support for implementing the Customer Hierarchy (Parent/Sub-Customer structure) within our existing workflow. At the moment, all invoices are being created in Zoho under the main (parent) customer

    • Which Zoho CRM tasks would you want an AI assistant to handle?

      I’ve started delegating my Zoho CRM work to AI – and many tasks now take me half the time. It has been so useful that we’re now working to make it available to everyone. Instead of clicking through records and tabs, I simply describe what needs to be

    • Introducing MCP for Zoho CommunitySpaces

      Hello everyone, Your AI assistant can now share the community workload with you. Zoho CommunitySpaces supports MCP (Model Context Protocol), so you can link an AI assistant like Claude, ChatGPT, or Cursor to your community and run it through plain language

    • CV-Library: The Newest Source Booster in Zoho Recruit!

      We’ve expanded your sourcing toolkit — CV-Library, one of the UK’s largest and most trusted online job boards, is now available as a Source Booster in Zoho Recruit. This gives recruiters instant access to millions of UK-based candidate profiles, all without

    • Tip 52#: Ways to improve data flexibility using related list

      A related list offers an ideal solution to string related data across entities together. It enables you to pull records from any module or external source and float them as a tab within your project, sprint, epic, work item, or release detail view layout.

    • Email Blast

      Hi, How do we send an email blast to thousands of customers? The email addresses are not yet saved in Zoho Desk.

    • Zoho ERP | Product updates | September 2026

      Hello users, A new month, a fresh set of updates in Zoho ERP. This month brings Netting, enhancements across our verticals, and more updates to help streamline your everyday work. Explore this month’s updates and see what’s new. Introducing Netting If

    • Introducing Zoho POS for Restaurants

      Hello Community, We’re thrilled to announce the launch of Zoho POS for Restaurants, unveiled at the 30th milestone edition of Zoholics India 2026. Running a restaurant involves multiple teams and workflows. A single table journey, from seating a guest

    • WhatsApp Business Message Pricing Update: Effective October 1, 2026

      Hi everyone, Starting October 1, 2026, Meta is updating how WhatsApp Business messages are charged. This guide explains the new per-message pricing model, how message categories work, and what changes for service messages specifically. How Meta charges

    • Zoho Calendar integration for Bigin

      Hello everyone, We're excited to introduce Zoho Calendar's integration in Bigin! For a while now, users have been able to sync events in Bigin with Google Calendar and Outlook Calendar - but with this new topping, it syncs with Zoho Calendar as well.

    • Next Page