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 #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

    • Kaizen #222 - Client Script Support for Notes Related List

      Hello everyone! Welcome to another week of Kaizen. The final Kaizen post of the year 2025 is here! With the new Client Script support for the Notes Related List, you can validate, enrich, and manage notes across modules. In this post, we’ll explore how

    Nederlandse Hulpbronnen


      • Recent Topics

      • Virtual Option for Fields

        Hi, I would like to be able to choose another option other than Read-Only or Disabled, such as Virtual. And with Virtual, the field is shown on the form and avilable in rules, but NOT saved to the Database. A use case is having multiple Large Lists of

      • Upcoming update to field values in Zoho Books - Zoho Analytics integration

        Hello Users, We'd like to inform you of an upcoming update to the tax_category values in the Zoho Books integration for Zoho Analytics from October 20, 2026. What's Changing? tax_category field values are being renamed to align with the conventions already

      • Prefix & Suffix on Single Line, Number, etc.

        Hi, I would like to have the same Prefix and Suffix that was added to the Unique ID on Text and Number Fields. Use case could be as basic as temperature, as per another Idea I have to use Single Line (Text) for a number that might have leading zeros today,

      • 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

      • Handle Leading Zeros in a Number Field

        Hi, If I use a Number Field, set with Min 7 Digits and Max 7 Digits, and enter 0000001, it will result in 1 and an error as it removes the leading zeros, the same with entering 0012340 will result in 12340 and error. So I have to use a Text Field and

      • Introducing Microsoft Word Integration in Zoho Contracts

        We are excited to announce a new feature that brings contract authoring and negotiation in your familiar environment — the Microsoft Word Integration. What This Integration Brings The Microsoft Word Integration connects Zoho Contracts with the Microsoft

      • Kaizen #259 - Working with Zoho CRM APIs using zoho-crm skill

        In the previous Kaizen, we introduced the zoho-crm skill and discussed how it can work with different CRM developer capabilities. The zoho-crm skill makes it easier to work with Zoho CRM without having to remember every API endpoint, request structure,

      • How can I populate dropdown data with information from another source or app?

        I want to maintain a list of items in another app (say in excel or another database) and sync those as items in a drop down menu, instead of copy pasting to import. Is this kind of a feature available?

      • Zoho Community Digest - September 2026 | Part 1

        Hi everyone, and welcome back! September opens with a strong set of updates. Zoho CRM brings workflow automation down to the subform row level and ships a new SKILL.md for AI-assisted development, Zoho Desk introduces conditional branching with Automation

      • Tip #88 – Manage Incoming Support Requests Efficiently with the Service Queue – 'Insider Insights'

        Hello Zoho Assist Community! Not every team has a dedicated IT department or a fully built-out sysadmin setup. For smaller teams, when something breaks, there's no internal ticket system to log into, no helpdesk queue to route through, and no clear way

      • Delete CRM Portal

        How do I delete portals from my CRM? I created one just to test, it is not in use and is disabled but it's existence is preventing me from marking fields in modules as "required" unless I make it 'read/write' in the portal first. I'd rather just delete

      • Creating new Teams meeting from CRM doesn't enable Team functions in the meeting

        Hi I'm trying to set up the meeting integration and I've seen that when I create a Meeting in the CRM and set the location to Online and the Provider to Teams, and complete the boxes, add a participant etc, whilst the meeting is created in Teams, the

      • Zoho Books | Product updates | July 2026

        Hello users, We’re excited to bring you the latest updates in Zoho Books for July 2026! This month's release introduces Terminal Payments, CMP-08 filing for composition taxpayers, SEPA Credit Transfer support, and Self-Billed Credit Notes and Debit Notes

      • Calendar invites from Contacts not being assigned to Account in CRM

        Hi all It's that time of year again when I try to get calendar and meetings sorted in CRM. I have two way sync enabled. I have the option set to check for customer meeting invitation mail and to add them as meetings. However, whilst those meetings show

      • Kaizen #258 - Getting Started with zoho-crm SKILL.md

        Howdy tech wizards, Welcome to a fresh week of Kaizen. This week, we are taking a look at the zoho-crm SKILL.md, an Agent Skill designed to help AI coding agents work with Zoho CRM’s developer capabilities. What is zoho-crm SKILL.md? The zoho-crm skill

      • Zoho Tables is now live in Australia & New Zealand!

        Hey everyone! We’ve got some great news to share — Zoho Tables is now officially available in the Australian Data Center serving users across Australia and New Zealand regions! Yes, it took us a bit longer to get here, but this version of Zoho Tables

      • Dashboard/Component filter by probability

        Hi all Can I request the ability to add a Component or Dashboard filter for Deal Probability? Would be useful to be able to see data of deals more than 60% probable. Olly

      • Stock (on-hand) Items not updated after using Composite items

        Hi there, I created a Composite item (consist of 3 items). After I created the Composite item, I invoiced it and shipped the items. However the actual stock on hand of the 3items didn't change at all. Have you guys encountered this? Thank you Regards,

      • Is there a way to show contact emails in the Account?

        I know I can see the emails I have sent and received on a Contact detail view, but I want to be able to see all the emails that have been sent and received between all an Accounts Contacts on the Account Detail view. That way when I see the Account detail

      • Important changes for users with Zoho accounts in the UAE and other Data Centers

        What's changing? Previously, the same email address could be used to create separate Zoho accounts in both the UAE data center and another Zoho data center (such as US, EU, IN, AU, JP, CA, SA or SG). With this change, an email address can be associated

      • 👍 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

      • Conditional Layouts On Multi Select Field

        How we can use Conditional Layouts On Multi Select Field field? Please help. Moderation update: Multi-select picklist fields are now supported in Layout Rules. Additionally, Layout Rules is now available in the Professional Edition. These updates have

      • Zoho Marketing Automation WhatsApp Campaign Import Sync for Zoho Analytics

        WhatsApp is a critical channel in modern marketing, yet WhatsApp Campaign metrics from Zoho Marketing Automation currently cannot be natively imported into Zoho Analytics via the default advanced analytics connector. Integrating this into the standard

      • merge the Multiple POs to single PO if Vendor of PO"s --in Zoho Inventory

        HI Merge the Multiple POs to single PO if Vendor of PO"s are Same ----in Zoho inventory Please provide any work around to achive this .

      • Delug script

        I have been looking at auto-update a amount (home currency) field from another module. Zoho native multicurrency was used in the other module (we have 4 here). Custom script was input with no error, but the field was not updated on trigger. Script as

      • Is there a CRM Deluge function available to convert an RTF (rich text field) to plain text (with no formatting tags)?

        I know that we can run reports so that RTF fields can either show as plain text or the text or the text with the formatting fields included (which is wonderful, btw, as it helps me adjust tags when I need to troubleshoot and just see what I need to see

      • 【Zoholics Japan 2026】ITreview 口コミ投稿キャンペーンを実施します！

        ユーザーの皆さま、こんにちは！ 2026年9月25日（金）開催の「Zoholics Japan 2026」会場にて、 Zoho CRM・Zoho Workplace・Zoho Mail を対象とした「ITreview 口コミ投稿キャンペーン」を実施します！ Zoholics にご来場いただく皆さま、ぜひこの機会に普段お使いの Zoho 製品について、率直なご感想をお聞かせください。 【キャンペーン内容】 対象製品： ・Zoho CRM ・Zoho Workplace ・Zoho Mail キャンペーン実施時間：

      • Contact removed when picking ticket template.

        hi new to Desk rolling out to company, replacing Freshdesk. Is there way to keep the in context contact when selecting a template? When you choose a template you lose the contact!

      • Using IMAP configuration for shared email inboxes

        Our customer service team utilizes shared email boxes to allow multiple people to view and handle incoming customer requests. For example, the customer sends an email to info@xxxx.com and multiple people can view it and handle the request. How can I configure

      • Zoho Tables is now available in Zoho One!

        Hello Zoho One users, We’re excited to announce that Zoho Tables is now included as a part of Zoho One suite! As teams grow, managing projects, approvals, inventories, campaign trackers, and operational workflows across multiple spreadsheets become difficult.

      • Zoho Publish is now available in Zoho One!

        Hello Zoho One users, We’re excited to announce that Zoho Publish is now included as part of the Zoho One suite! As businesses grow, managing Google Business Profiles across many locations becomes challenging. Business information needs to stay accurate,

      • Global Sets for Multi-Select pick lists

        When is this feature coming to Zoho CRM? It would be very useful now we have got used to having it for the normal pick lists.

      • Bank Transaction Rules Link Under Each Bank Account

        Hello, can you'll move the "transaction rules" button or link back under each bank account? It is now on Bank Overview, if I am working on a specific bank account, I don't want to go out to overview to check the rules. That button displays rules for all

      • Nested notebooks

        Dear Sir/Madam, I would like to know if it is possible to nest notebooks. It would be very helpful when there are too many, as it would improve organization. Thank you for your response. Best regards.

      • Zia Agents looks promising, but I still cannot deploy my first agent or connect WhatsApp after weeks of support tickets

        Hi Everyone, I am posting here because I am stuck and need practical help from someone who has successfully deployed a Zia Agent with WhatsApp. Zia Agents looks like a very promising product. I have watched the platform expand quickly, and I have noticed

      • Zoho CRM

        Cuándo voy a adjuntar un archivo .pdf en un registro en el campo Archivo obtengo el siguiente error:

      • Implement Meeting Polls in Zoho Bookings

        Dear Zoho Bookings Support Team, We'd like to propose a feature enhancement related to appointment scheduling within Zoho Bookings. Current Functionality: Zoho Bookings excels at streamlining individual appointment scheduling. Users can set availability

      • Recording Salaries and wages in zoho books with bank fees

        Hello Community, I am posting this questions to understand the best way to record the salary and payroll expenses in zoho books. The way it works here, For example if I have 3 employees and each employee salary is lets say $1000. I usually use the bank

      • API - Available Stock Definitions

        Okay, Zoho team... your copywriters fell down on the job for this one :) I think these warrant a bit more explanation as to what they include and what they don't.

      • [BUG] WebTabs in ZohoCRM now have a spurious "\" displayed along with some additional HTML Head code included

        An example of the issue can be seen below:

      • Next Page