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
|
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
|
Products
|
Products
|
||
|
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
|
Products
|
Products
|
||
|
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:
- 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:
- 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.
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.
- float SalesByMonth(int month)
- {
- //Compare the passed month value to the Order_Month value
- sales = New_Order[Order_Month == month].sum(Net_Total);
- return sales;
- }
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.
- <%{
- %>
- <style>
- .salesTable
- {
- width: 96%;
- border-collapse: collapse;
- margin: 1% 2%;
- }
- .salesTable td
- {
- padding: 10px;
- font-size: 14px;
- text-align: center;
- border-bottom: 1px solid #f1f1f1;
- }
- table.salesTable > thead > tr > th
- {
- font-weight: 600;
- font-size: 15px;
- background: #ececec;
- }
- td.green {
- background: #228B22;
- color: white;
- }
- td.red {
- background: #d10000;
- color: white;
- }
- td.blue {
- background: #89CFF0;
- color: white;
- }
- </style>
- <table class="salesTable">
- <thead>
- <tr>
- <th>Month Name</th>
- <th>Sales</th>
- </tr>
- </thead>
- <tbody>
- <%
- //Create a list
- list = List();
- //Create the numbers 1-12 to indicate the month numbers
- list.addAll({1,2,3,4,5,6,7,8,9,10,11,12});
- monthName = List();
- //Create the month name list
- monthName.addAll({"January","February","March","April","May","June","July","August","September","October","November","December"});
- //Iterate for every month
- for each month in list
- {
- %>
- <tr>
- <!--Get the month name from the monthName list--> <td><%=monthName.get(month - 1)%></td>
- <%
- //Find the total sales using the SalesByMonth function
- sum = thisapp.SalesByMonth(month);
- if(sum >= 5000)
- {
- //Indicate any sales more than 5000 in green
- %>
- <td class="green"><%=thisapp.SalesByMonth(month)%></td>
- <%
- }
- else if(sum < 5000 && sum >= 2000)
- {
- //Indicate any sales in range 2000 - 5000 in blue
- %>
- <td class="blue"><%=thisapp.SalesByMonth(month)%></td>
- <%
- }
- else {
- //Indicate any sales less than 2000 in red
- %>
- <td class="red"><%=thisapp.SalesByMonth(month)%></td>
- <%
- }
- %>
- </tr>
- <%
- }
- %>
- </tbody>
- </table>
- <%
- }%>
See how it works
Related Links
Access your files securely from anywhere
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 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 Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
Zoho FSM Video Tutorials
Centralize Knowledge. Transform Learning.
All-in-one knowledge management and training platform for your employees and customers.
New to Zoho Writer?
Create. Review. Publish.
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
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.
New to Zoho Survey?
Zoho Sheet Resources
Zoho Forms Resources
New to Zoho Sign?
Zoho Sign Resources
Sign, Paperless!
New to Zoho TeamInbox?
Zoho TeamInbox Resources
New to Zoho ZeptoMail?
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
New to Bigin?
Related Articles
Functionality-based URLs
Overview In the topic Zoho Creator URL Patterns we learned about the default URLs to access a Zoho Creator application and its forms, reports and pages. In this topic, we will learn about the functionality based URLs to set default values for form ...Understanding record formatting
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 ...Style-based URLs for embedded forms and reports
In this topic we list out the parameters used in customizing the look and feel of Zoho Creator forms and reports. These parameters can be applied to the Forms and Reports embedded in your Pages. Form properties Description Parameter/Value Display ...Conditions to filter records based on Creator fields
In a nutshell Learn about the criteria that you can set to filter records and run workflows within the Creator application. 1. Overview Conditions help in filtering records from a Zoho Creator report. Based on the criteria set in a condition, actions ...Configuring display for panels and panel elements
What does this page cover Learn to configure the display properties of panels and panel elements to showcase static and dynamic data, visual images and icons, and CTA buttons on a Page component. Availability Panels can be created in all plans of ...
New to Zoho LandingPage?
Zoho LandingPage Resources