All lists in BaccS support a conditional formatting feature. This feature allows you apply user-defined styles (font and color settings, icon, etc.) depending of some conditions. For example, you can simply change the background depending on the value of a particular column, or draw an icon to highlight an important point, or visualize a value changing depending on previous row, or even draw a small chart behind a numeric value. Here is a small example, where all these cases are shown:

In this example rows have different background depending on status, sent and paid jobs has its own icon, missing PO numbers are highlighted with bright yellow, and the amount column has a small chart which compares job amount with the largest job.


There are two methods to setup conditional formatting rules: via appearance rules editor and via table context menu. The first method allows to edit appearance rules for some entity type, and these rules will be applied in all forms in the program: list forms and edit forms. For example, if to take Translation job, we have the following forms: job list, recent jobs in the dashboard, job editing form, job list in the customer editing form, list of available jobs in invoice creation form, etc. When appearance rules are created using Appearance rules editor, those rules will be applied to all such forms.

Second method allow to edit rules specifically for one concrete table. For example, if you will create a rule for the general job list, those rules won't be applied to the list of job in the Dashboard.

Be careful when mixing these methods. The first has priority, and if row background color will be set using a rule created in the Appearance rules editor, it won't be possible to override this color on the base of individual table rules.

Both described methods are explained below.


Setting up conditional formatting rules using appearance rules editor

To access Appearance rules editor, use the corresponding button which you can find in each list view:

When this button clicked, you will see the following window:

It contains all rules applied to the current list view. You may delete and edit existing rules, as well as create new ones. Rule editing window looks as following:

In the Rule name field you specify any logical name of the formatting rule.

Priority field allows to specify priority of the rule, when multiple rules are applied to the same column/field. Lower value means higher priority.

Object type field is filled automatically and specifies type of the object which this rule will be applied to.

Criterion filed contains an expression which specifies when to apply this rule. To edit expression, click on the '...' button. You will see default expression editor, which you may use to visually build criteria of any complexity.

Target items field specifies columns/fields to apply edited rule. To apply a rule to all fields (to highlight the whole row in the table), use asterisk symbol (*). If you wish to apply a rule to one or multiple fields, list them here, separating by comma. List of available fields is provided in the Available fields list at the bottom.

Rule context field specifies where to apply edited rule: in list views, in detail views (edit forms), or in both contexts.

In the Background color, Foreground color, Font style fields you specify applied formatting.

Visibility fields allow to hide a column or fields when rule criteria is met.

Field edit enable checkbox specifies availability to edit target items when rule criteria is met.


To better understand how appearance rules work, check default rules in the translation jobs list. It contains a number of built-in rules of different types.


In the criteria expression editor you can visually build criteria by selecting necessary fields and their values. However, sometimes this editor can't help to compose complex criteria, In this case, you can manually build rule criteria by switching to the Text tab of the criteria editor:


Here is an example. What if we need to highlight a job which must be delivered in 3 days from today? In this case we can use special formulas. List of all formulas can be found on the help page of components I've used to create this software. But mainly you will need formulas to process date values, and here they are:

FUNCTION

DESCRIPTION

EXAMPLE

AddDays(DateTime, DaysCount)

Returns a date-time value that is the specified number of days away from the specified DateTime.

AddDays([Deadline], 30)

AddHours(DateTime, HoursCount)

Returns a date-time value that is the specified number of hours away from the specified DateTime.

AddHours([Deadline], 2)

AddMilliSeconds(DateTime, MilliSecondsCount)

Returns a date-time value that is the specified number of milliseconds away from the specified DateTime.

AddMilliSeconds(([Deadline], 5000))

AddMinutes(DateTime, MinutesCount)

Returns a date-time value that is the specified number of minutes away from the specified DateTime.

AddMinutes([Deadline], 30)

AddMonths(DateTime, MonthsCount)

Returns a date-time value that is the specified number of months away from the specified DateTime.

AddMonths([Deadline], 1)

AddSeconds(DateTime, SecondsCount)

Returns a date-time value that is the specified number of seconds away from the specified DateTime.

AddSeconds([Deadline], 60)

AddYears(DateTime, YearsCount)

Returns a date-time value that is the specified number of years away from the specified DateTime.

AddYears([Deadline], -1)

Now()

Returns the current system date and time.

AddDays(Now(), 5)

Today()

Returns the current date. Regardless of the actual time, this function returns midnight of the current date.

AddMonths(Today(), 1)

UtcNow()

Returns the current system date and time that is expressed as Coordinated Universal Time (UTC).

AddDays(UtcNow(), 7)

It is easy to find out that the resulting formula (for 3 days) will have the following form:

[Deadline] > Now() And [Deadline] <= AddDays(Now(), 3)

The first part is needed to avoid highlighting jobs with deadline before today. When you start typing this formula, expression editor will help you to pickup fields from the drop-down menus.

Setting up conditional formatting rules via table context menu

To set-up conditional formatting rules, right click on any column name and select Conditional formatting. You will see a sub-menu, where it is possible to select formatting from a pre-defined set, or click Manage rules to show a customization window:

This window contains an ordered list of active rules. Here you can see conditions, a small resulting example, assigned column, a parameter that allows you to apply the rule to the whole row and checkbox to turn the rule on and off.

As soon as any rule is created for one column, by default it is applied only to this column. Thus, if you deselect Apply to the row box for the third rule, we'll get the following result:

A red background is drawn only in the Status column.

To create a new rule, click the New rule button. You will see the following window:

Depending on the Rule type selection, you'll see different options of the rule in the bottom part of the window. You can find details about each Rule type below.

1. Format cells based on their values. It is usually applied to numeric values and allows to apply formatting based on minimum and maximum values. For example (picture above), you may change cell background from red (minimum) to green (maximum). Instead of 2-color scale, you may select from 3-color scale, data bar and icon sets. When format style is selected, simply specify range thresholds in percents or numbers, select desired colors and click OK.

2. Format only cells that contain type allows you to define formatting based on a field value. This formatting type is intended for numeric and date values.

Especially it will be useful for date values, because here you can select one or multiple values from a wide list of predefined date rules:

Nice example of this rule type usage is highlighting of jobs with upcoming deadlines: today, tomorrow, this week, next week, etc.

3. Format only top or bottom ranked values. As it is clear from the name, this rule type allows to highlight n rows with maximum or minimum values. Useful to highlight largest and smallest jobs.

4. Format only values that are above or below average. Again, no additional description is needed here and the meaning is clear from the name of this rule.

5. Format only unique or duplicate values. This rule type will be useful to point out on data which may be forgotten to be changed. For example, to highlight rows with repeated PO numbers.

6. Use formula to determine which cells to format. This is the most powerful rule type allowing you specify complex conditions. Rule criteria builder is similar to filter builder. You may specify number of conditions using values of multiple fields and apply different logical operators to them. Here is an example, which shows how to highlight Paid jobs with Website or Software localization specializations:


When you apply any filter to the list, and have some conditional rules applied which depend on numeric values, those formatting rules will be recalculated, and you will see another picture.




This concludes the information about conditional formatting. Use it as often as possible, because in addition to it being useful it simply makes your management tool more beautiful and interesting to use.

Created with the Personal Edition of HelpNDoc: Free Kindle producer