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. <%

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

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

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

  56.  //Iterate for every month
  57.  for each  month in list
  58.  {
  59.   %>
  60. <tr>
  61. <!--Get the month name from the monthName list-->   <td><%=monthName.get(month - 1)%></td>
  62. <%

  64.   //Find the total sales using the SalesByMonth function
  65.   sum = thisapp.SalesByMonth(month);
  66.   if(sum >= 5000)
  67.   {

  69.   //Indicate any sales more than 5000 in green
  70.    %>
  71. <td class="green"><%=thisapp.SalesByMonth(month)%></td>
  72. <%
  73.   }
  74.   else if(sum < 5000 && sum >= 2000)
  75.   {

  77.   //Indicate any sales in range 2000 - 5000 in blue
  78.    %>
  79. <td class="blue"><%=thisapp.SalesByMonth(month)%></td>
  80. <%
  81.   } 
  82.   else {

  84.   //Indicate any sales less than 2000 in red
  85.    %>
  86. <td class="red"><%=thisapp.SalesByMonth(month)%></td>
  87. <%
  88.   }
  89.   %>
  90. </tr>
  91. <%
  92.  }
  93.  %>
  94. </tbody>
  95.  </table>
  96. <%
  97. }%>

See how it works