Display different colors based on record value

Display different colors based on record value


Display different colors of the record in a report or page based on its value.

Use Case  

In a sales management application, the business owner wants to track the purchases and the sales. As a new purchase is made, the corresponding invoice is added to the system, using which the products are tracked. When a new order is placed, the products are displayed from the products inventory derived from the invoices. The administrators want to track the purchases and the sales, to find if they have reached their targets and budgets. They want to indicate using a combination of color s to draw attention to the purchase amounts and sales done every month.

Steps to follow  

1. Create the forms with the following details:
Form Link Name
Field Type
Field Name
Field Link Name
New Order
Customer Name
Order Date
  • Single Line
  • Number
  • Currency
  • Currency
  • Product Name
  • Quantity
  • Rate
  • Sub-Total
  • Product_Nam
  • Quantity
  • Rate
  • Sub_Total
Net Total
Formula (Created from Order Date to get the month number [1 - 12]) : Order_Date.getMonth()
Order Month
Add Invoice
Supplier Name
Invoice Date
  • Single Line
  • Number
  • Currency
  • Currency
  • Product Name
  • Quantity
  • Rate
  • Sub-Total
  • Product_Name
  • Quantity
  • Rate
  • Sub_Total
Net Total
Multiple forms can be created as a part of the order management system. For brevity, only these two forms are created here. We shall set the Order Date field's default value in the New Order form and the Invoice Date field's default value in Invoice form as zoho.currentdate.
We shall now color code the values in reports.
2. Let us create a workflow to execute during the load of the New Order form to disable the Order Date field to avoid the modification of the date.

3. Add the below code in the Deluge Editor:
  1. disable Order_Date;

4. In the same way, we'll create another workflow to disable the Invoice Date field in the Add Invoice form during its load.
5. Add the below code in the Deluge Editor:
  1. disable Invoice_Date;

We shall do two different color ing techniques: one based on reports and another based on pages. For this example, we shall color the records of Add Invoice form using reports and color the records of New Order form using pages.

6. Let's create a list report named All Invoices based on the Add Invoice form.
7. Assuming the limit of any invoice value to be 5000, we want to know which invoices went higher than that. So, let's create a conditional formatting for those records which have a Net Total value in the Add Invoice form greater than 5000 to have a special formatting as below:
So, when this report is accessed, the invoice values greater than 5000 are highlighted. No need for the stakeholders to filter the records to find the data.
Let us now proceed to color coding the values using pages. We will highlight those sales figures that are less than the monthly limit. To find that we need to find the sum of all sales done in a month. Let us begin by creating a function to find that.
8. We will now create a function to get the total sales done each month. Create a function named 'SalesByMonth':
9. Add the below code to get the total sales in the month number passed to this function as an argument.
  1. float SalesByMonth(int month)
  2. {
  3. //Compare the passed month value to the Order_Month value
  4.  sales = New_Order[Order_Month == month].sum(Net_Total);
  5.  return sales;
  6. }

10. Now, we shall create a page named 'Orders By Month' and add an HTML Snippet to it.
11. In the editor, we shall add the below code to display the total sales every month in a tabular format.
  1. <%{
  2.  %>
  3. <style>
  4.  .salesTable
  5.  {
  6.   width: 96%;
  7.   border-collapse: collapse;
  8.   margin: 1% 2%;
  9.  }
  10.  .salesTable td
  11.  {
  12.   padding: 10px;
  13.   font-size: 14px;
  14.   text-align: center;
  15.   border-bottom: 1px solid #f1f1f1;
  16.  }
  17.  table.salesTable > thead > tr > th
  18.  {
  19.   font-weight: 600;
  20.   font-size: 15px;
  21.   background: #ececec;
  22.  }
  23.  td.green {
  24.   background: #228B22;
  25.   color: white;
  26.  }
  27.  td.red {
  28.   background: #d10000;
  29.   color: white;
  30.  }
  31.  td.blue {
  32.   background: #89CFF0;
  33.   color: white;
  34.  }
  35.  </style>
  36.  <table class="salesTable">
  37.  <thead>
  38.  <tr>
  39.  <th>Month Name</th>
  40.  <th>Sales</th>
  41.  </tr>
  42.  </thead>
  43.  <tbody>
  44. <%

  45.  //Create a list
  46.  list = List();

  47.  //Create the numbers 1-12 to indicate the month numbers
  48.  list.addAll({1,2,3,4,5,6,7,8,9,10,11,12});
  49.  monthName = List();

  50.  //Create the month name list
  51. monthName.addAll({"January","February","March","April","May","June","July","August","September","October","November","December"});

  52.  //Iterate for every month
  53.  for each  month in list
  54.  {
  55.   %>
  56. <tr>
  57. <!--Get the month name from the monthName list-->   <td><%=monthName.get(month - 1)%></td>
  58. <%

  59.   //Find the total sales using the SalesByMonth function
  60.   sum = thisapp.SalesByMonth(month);
  61.   if(sum >= 5000)
  62.   {

  63.   //Indicate any sales more than 5000 in green
  64.    %>
  65. <td class="green"><%=thisapp.SalesByMonth(month)%></td>
  66. <%
  67.   }
  68.   else if(sum < 5000 && sum >= 2000)
  69.   {

  70.   //Indicate any sales in range 2000 - 5000 in blue
  71.    %>
  72. <td class="blue"><%=thisapp.SalesByMonth(month)%></td>
  73. <%
  74.   } 
  75.   else {

  76.   //Indicate any sales less than 2000 in red
  77.    %>
  78. <td class="red"><%=thisapp.SalesByMonth(month)%></td>
  79. <%
  80.   }
  81.   %>
  82. </tr>
  83. <%
  84.  }
  85.  %>
  86. </tbody>
  87.  </table>
  88. <%
  89. }%>

See how it works             

    Zoho CRM Training Programs

    Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

    Zoho CRM Training
      Redefine the way you work
      with Zoho Workplace

        Zoho DataPrep Personalized Demo

        If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.

        Zoho CRM Training

          Create, share, and deliver

          beautiful slides from anywhere.

          Get Started Now

            Zoho Sign now offers specialized one-on-one training for both administrators and developers.

            BOOK A SESSION

                                    You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.

                                        Manage your brands on social media

                                          Zoho Desk Resources

                                          • Desk Community Learning Series

                                          • Digest

                                          • Functions

                                          • Meetups

                                          • Kbase

                                          • Resources

                                          • Glossary

                                          • Desk Marketplace

                                          • MVP Corner

                                          • Word of the Day

                                            Zoho Marketing Automation

                                              Zoho Sheet Resources


                                                  Zoho Forms Resources

                                                    Secure your business
                                                    communication with Zoho Mail

                                                    Mail on the move with
                                                    Zoho Mail mobile application

                                                      Stay on top of your schedule
                                                      at all times

                                                      Carry your calendar with you
                                                      Anytime, anywhere

                                                            Zoho Sign Resources

                                                              Sign, Paperless!

                                                              Sign and send business documents on the go!

                                                              Get Started Now

                                                                      Zoho TeamInbox Resources

                                                                              Zoho DataPrep Resources

                                                                                Zoho DataPrep Demo

                                                                                Get a personalized demo or POC

                                                                                REGISTER NOW

                                                                                  Design. Discuss. Deliver.

                                                                                  Create visually engaging stories with Zoho Show.

                                                                                  Get Started Now

                                                                                                      • Related Articles

                                                                                                      • Add data to a different application

                                                                                                        Requirement Automatically add a record to a form of one application when a record is added in a different application. Use Case A Zoho creator account has two applications: Contact Management and Order Management. All the customer details maintained ...
                                                                                                      • Understand Record Comments

                                                                                                        What Does This Page Cover? Learn about record comments and how your application users can read comments as well as add new comments to records. Note: Record comments is only applicable to List Report. i.e. Users can only read and write comments to ...
                                                                                                      • Hotel management

                                                                                                        This app has been published in accordance with Marketplace review process. For pricing details, you can refer to this page. Category - Hospitality Vendor - Zoho Corporation Pricing - Free Overview Say goodbye to thick registry books and run your ...
                                                                                                      • Insert a record during an approval process

                                                                                                        Requirement   Insert a record into a form when an approval is approved or rejected. Use Case   A charity uses an application to approve the students who need their services. Any student can apply for their program, but the charity reserves their ...
                                                                                                      • Statistical values on a report based on numerical fields

                                                                                                        Requirement Display the common statistical results on the reports for the numerical fields in a form, to help in identifying trends and decision-making process. Use Case In any sales-based business, crunching numbers is a big task. An order ...
                                                                                                        Wherever you are is as good as
                                                                                                        your workplace



                                                                                                          Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.


                                                                                                          Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.


                                                                                                          Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.

                                                                                                          CRM Tips

                                                                                                          Make the most of Zoho CRM with these useful tips.

                                                                                                            Zoho Show Resources