https://www.youtube.com/watch?v=EXpKh4QPpZI
How to Use Conditional Formatting
Tip: Ask FIVA to write the formula for you!
The best way to utilize Conditional Formatting is to also utilize Custom View presets to truly optimize a page view for a specific workflow. Creating different views that each serve one specific purpose is better than having a single view try to do too many different things. Learn how to configure custom views here.
Follow the steps below to conditionally format a list view.
1). On a list screen, please select the Customize column in (View Name) link in the Views drop-down menu. For this example, the View Name is 'Default.
2). Click on the highlighted field to select the column to set conditional formatting.
For this example, we will set the conditional formatting on the Fulfillment Status field on the sale screen.
3. Click the Conditional formatting tab and create the conditional formatting rule.
How to Create a Basic Formula
The conditional formatting relies on a formula, and if the formula is "true", then it will apply the format to that cell.
A list of formulas can be found here, but the easiest to use is ifs(), which will be used in all of the examples below.
The basic way to use an ifs() formula is as follows.
This is the field you're referencing. If it's for the column itself, you can always just use cellValue. If it's a different column, you'll need to edit the column you want to reference and use the column ID.
This is the operator. You can do any type of argument. Greater than, less than, equals, does not equal, etc.
This is last part of the equation. In the example above, we're saying eligible to ship equals backordered. If it's a word, it always needs to be in quotations. If it's a number, it can just be the number.
This comma is required to tell the formula that the equation part is finished.
This is required to tell the formula what to do if it's true. In the example above, if the status is backordered, it will make the text. Multiple formatting options can be used here, separated by a semi-colon (no space after the semi-colon).
This is required if you want to do multiple rules. If you're just doing one rule, you do not need this. In the example above, we're telling the formula if it equals backordered, make the text yellow, if that isn't true, then it moves to the next line to test and checks if it equals incomplete. If that isn't true, then it goes to the next row, and so on and so on.
Available Colors and Font Weight Options
There are nine colors to choose from, each with 5 shading choices, for a total of 45 potential color options for both background and text coloring. Background coloring can also be combined with text coloring for even more combinations.
In addition to color formatting, the text weight can also be formatted.
The font weight options are:
text-light
text-bold
normal (do not specify the text weight, the formula will default to using normal weight)
The color choices are:
Red
Orange
Yellow
Green
Blue
Purple
Brown
Pink
Gray
White (text only, no background)
Black (Background only, no text)
Using Red Text as an example, the shading options are (replace "text" with "background" for background formatting):
text-red-x-dark
text-red-dark
text-red
text-red-light
text-red-x-light
Examples
Changing Background Color
If the Fulfillment Status field is the following:
1. "Backordered" = Yellow background
2. "Incomplete" = Red background
3. "Fully Shipped" = Green Background
Using the formula shown below will output the following:
ifs(
orderEligibleToShip == "Backordered", "background-yellow",
orderEligibleToShip == "Incomplete", "background-red",
orderEligibleToShip == "Fully shipped", "background-green"
)Note: the formulas are case-sensitive. As a result, Backordered cannot be written as backordered.
Changing Text Color
If you wanted to change the conditional formatting to change the text color, then you would use the following formula.
ifs(
orderEligibleToShip == "Backordered", "text-yellow",
orderEligibleToShip == "Incomplete", "text-red",
orderEligibleToShip == "Fully shipped", "text-green"
)
Changing Background and Text Colors (Multiple Arguments)
ifs(
number(productSalesVelocity) > 100, "background-green",
number(productSalesVelocity) > 10, "text-green"
)
HINT: For any formula involving a number column, use the number() function to ensure the field is converted to a number (see above).
Changing Background and Text Colors (Single Argument)
ifs(
number(cellValue)>0, "background-green-x-dark;text-white;text-bold"
)To combine formatting options in a formula, each format needs to be separated by a semi-colon. For example, the formula above has a dark green background, white text, and the text is bold.
Formatting Using Date Comparisions
ifs(
and(
dateDiff(today(), receiveDate, "days") > 0,
shipmentsStatusSummary != "Fully received"
),
"text-red"
)This formula compares today's date (referencing using today()) against the estimated receive date column (receiveDate). The third argument can be days, weeks, or months. The argument subtracts the first date (today) from the second value (receiveDate). If it is in the past, it will be a positive amount (one week ago was 7 days away). If it is in the future, it will be a negative number.
This formula also uses an "and()" function. This allows us to do the date comparision in conjunction with checking against a different column. The formula above says if the date is in the past AND the shipment summary is not Fully received, then make the text red.
Additional date formatting formulas can be found here.
Shading an Entire Column
ifs(
true, "background-green"
)This formula is used to apply the formatting to every row in the column. This can be used to shade a column a specific color, or change the text formatting, regardless of the value.
If you need assistance with a specific formula, please send your suggestion to servicedesk@descartes.com
Additional Tools to Improve the Finale Experience
In addition to Conditional Formatting, there are several other features that are extremely powerful in optimizing your Finale experience: