Conditional formatting and custom columns

Conditional formatting comparison functions do not have the capability for complex calculations, such as "number of days since a certain date" or "value ÷ value". However custom columns can manage such calculations.

Thus, the combination of conditional formatting and custom columns enables you to monitor customised scenarios such as: The Assessment report is due 5 business days after the referral date. In this situation you can draw attention to cases where time is running out to complete the report on time.

What is a custom column?

A custom, or calculated column appears in your Case List alongside other standard Case Manager columns, such as Client, Category, and so on. Customised SQL code is written to perform calculations according to the criteria that you specify. Contact our Professional Services team if you would like a quote for the development of a custom column. Further information is at the bottom of this page.

Custom columns and conditional formatting in action

The challenge

The Assessment report is due 5 business days after the referral date. You need to draw attention to cases where the report has not been done and time is running out.

The solution

You can indicate with different colours that:

  • the report has not been created and today is 3 business days or less since the referral
  • the report has not been created and today is 4 business days since the referral
  • the report has not been created and today is 5 or more business days since the referral

As displayed in the screenshot above:

  1. The first custom column calculates the number of business days between today's date and the case's referral date.

    2. Thus, it calculates the number of business days since referral.

  2. The second custom column displays the creation date of the most recent document at the case that was created with the Assessment report template.

    3. Thus, it determines whether an assessment report has been created.

In this situation you are only concerned about cases where there is no assessment report yet, i.e. the creation date in column B is empty (null).

Three conditional formatting rules are created:

  1. no report and today is 3 business days or less since referral
  2. no report and today is 4 business days since referral
  3. no report and today is 5 or more business days since referral

The Rules Manager displayed above specifies that for each of the three rules:

  1. The Ass doc date column is initially evaluated and is the column that is formatted.

    2. In the Case List screenshot at the top of the page you can see that this column is formatted.

  2. You could apply the formatting (but not the evaluation) to a separate column, such as Client, if desired.

Each of the three rules is very similar and looks for cases where there is no report (Ass doc date is null). Then it evaluates the number of business days since referral.

For example, the formula for the 3rd rule (above) specifies that if the creation date of the most recent document created with the Assessment report template is empty (there is no assessment report for this case) And the number of business days since referral ≥ 5, then a red fill is displayed.

The formulae for the 2nd and 1st rules are:

Together the three rules provide a clear visual indication that assists you to ensure that assessment reports are created within the required time frame.

Note that Days since referral is an existing Case Manager column. However, in this situation we need to calculate the number of business days since the referral date, hence the need for a custom column.

Other examples

The formatting explained in the worked example could be improved by being applied to a custom column displaying the percentage of estimate left.

The formatting varies between three key values:

  • Red when the percent left is 0%
  • Yellow when the percent left is 50%
  • Green when the percent left is 100%

More about custom columns

You can connect with our Professional Services team by contacting Case Manager Support.

To commission a calculated column you start by getting a quote. The cost depends on the complexity of the calculation. For example days/week/years are quicker to calculate than business days. Public holidays cannot be detected.

Custom columns are cheaper to create in a batch because logging in to install them on your server takes time.