Display different colors based on record value

Display different colors based on record value

Requirement  

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
Form Link Name
Field Type
Field Name
Field Link Name
New Order
New_Order
Name
Customer Name
Customer_Name
Date
Order Date
Order_Date
Subform
  • Single Line
  • Number
  • Currency
  • Currency
Products
  • Product Name
  • Quantity
  • Rate
  • Sub-Total
Products
  • Product_Nam
  • Quantity
  • Rate
  • Sub_Total
 
Currency
Total
Total
Percentage
Taxes
Taxes
Currency
Net Total
Net_Total
Formula (Created from Order Date to get the month number [1 - 12]) : Order_Date.getMonth()
Order Month
Order_Month
Add Invoice
Add_Invoice
Name
Supplier Name
Supplier_Name
Date
Invoice Date
Invoice_Date
Subform
  • Single Line
  • Number
  • Currency
  • Currency
Products
  • Product Name
  • Quantity
  • Rate
  • Sub-Total
Products
  • Product_Name
  • Quantity
  • Rate
  • Sub_Total
 
Currency
Total
Total
Percentage
Taxes
Taxes
Currency
Net 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


            Get started with Zoho Sign

            in a few quick steps!

            Download Help Guide





                        Still can't find what you're looking for?

                        Write to us:  support@zohoforms.com


                              




                            

                          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 SalesIQ Resources



                                                    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 ...
                                                                                      • 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 ...
                                                                                      • Understand Formatting Records

                                                                                        Formatting, in terms of reports, enables you to highlight certain field values in an attempt to grab attention to that data or to make that data stand out for easy visibility amidst the abundance of information in the report. This formatting is based ...
                                                                                      • Configure panel element's display value

                                                                                        Once you have added a panel template to the page builder, you can configure and customize all the elements in it including text, image and button in the panel builder. All the display elements in a panel can be configured here. This includes: ...
                                                                                      • Understand HTML snippets

                                                                                        Snippets are stand-alone, re-usable code pieces that can add additional functionality.  An HTML snippet is a small portion of source code in HTML. They can be used to build different elements (like a list view, different styled buttons, text display, ...
                                                                                      Wherever you are is as good as
                                                                                      your workplace

                                                                                        Resources

                                                                                        Videos

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



                                                                                        eBooks

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



                                                                                        Webinars

                                                                                        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