The conditional capability allows you to change the color of the text or the background text based on conditions you set to highlight values more and bring them to your attention.

Quick Jump to:

• How to Create a Basic Formula
• Available Colors and Formatting Options
• Changing Background Color Example
• Changing Text Color Example
• Conditional Formatting Using Background and Text Colors in Separate Arguments
• Conditional Formatting Combining Background and Text Colors in a Single Argument
• Conditional Formatting Comparing Dates
• Shading an Entire Column

 

How to Use Conditional Formatting

Please watch the video for a quick introductory overview.

 

The conditional formatting can be created for a specific column using the instructions below:

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.  

1. 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.
2. This is the operator.  You can do any type of argument. Greater than, less than, equals, does not equal, etc.
3. 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.
4. This comma is required to tell the formula that the equation part is finished.  
5. 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).
6. 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 Formatting Options

There are 9 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
• normal (do not specify the text weight, the formula will default to using normal weight)
• text-bold

The color choices are:

• Red
• Yellow
• Green
• Blue
• Purple
• Orange
• Brown
• Pink
• Grey
• White (text only, no shades)
• Black (Background only, no shades)

 

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:

Text weights:

 

 

Changing Background Color Example

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" 
  )

Please note that the formulas are case-sensitive.   As a result, "Backordered" cannot be written as "backordered."

 

 

 

 

Changing Text Color Example

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"
  )

 

 

Other Formula Examples

Conditional Formatting Using Background and Text Colors in Separate Arguments

ifs(
  number(productSalesVelocity) > 100, "background-green",
  number(productSalesVelocity) > 10, "text-green"
  )

 

HINT:  For any formula involving a number column, please ensure you use the number() function to ensure the field is converted to a number.  Please see above for example.

 

 

 

 

 

 

Conditional Formatting Combining Background and Text Colors in a 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.

 

 

Conditional Formatting Comparing Dates

 

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 there are other functionalities that you would like to see or if you need assistance with a specific formula, please send your suggestion to service@finaleinventory.com

We are always open to feedback to improve this feature.