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
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:
Still can't find what you're looking for?
Write to us: support@zohoforms.com
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?
Zoho DataPrep Resources
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Bigin?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
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 ...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 ...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 ...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 ...