Conditional Formatting
Within the F1 lookup lists for most RTA master files; we have included an option to conditionally format your data. The purpose of Conditional Formatting is to allow the end user to highlight, sort, view and stylize their data into understandable, reportable information.
When prompted for a master F1 lookup list, simply right-click within the desired column header and select Conditional Formatting to open your options. The conditional formats within the F1 lookup list are:
Highlight Cell Rules
Unique Duplicate Rules
Manage Rules
Highlight Cell Rules
The purpose of the Highlight Cell option in Conditional Formatting is to bring attention to certain areas of data within a specified range. It can be used to highlight:
“Greater than”
“Less Than”
“Between”
“Equal to”
“Text that contains”
And “Custom” fields
Within these Highlight Cells, the user can set values that emphasize groups of data in accordance with the parameters they have set. For example, when in the Parts Master File, you are able to right click in the Total on Hand column and select Conditional Formatting > Highlight Cell Rule > Less Than… and set your less than value to 1, choosing what the result will be (red fill). This option will highlight parts with less than 1 on hand. See Below:
There is also a checkbox when in the Rules screen to “Apply Formatting to Entire Row”.
Top/Bottom Rules
This formatting option will allow the end-user the chance to sort the data-list by the Top or Bottom of the data range. Although the titles illustrate top 10 or 10%, the use will be able to directly identify/sort based on a specific set of numeric or alphabetical quantities. While recognizing these top/bottom data cells will highlight the data, the user will also be able to sort the list by top/bottom in most cases using the F1 list headers.
Top/Bottom Rules include:
Top 10 Items…
Top 10%...
Bottom 10 Items…
Bottom 10%...
Above Average…
And Below Average…
An example of how to use the Top/Bottom Rules would be in the case you would want to view your top 10% most expensive inventory parts. In the past, you’d have to sort your inventory based on part cost, the run your own calculations to find that percentage. Now the user can open the parts master F1 lookup list, Right-Click on Average price, Conditional Formatting > Top/Bottom Rules > Top 10%..., then fill parameters to 10% and a fill option such as Green Fill with Green Text. In this situation, I have chosen to select Apply Formatting to Entire Row.
The Result:
As a reminder, the user has the power to grab the top, bottom or percent desired with a specific numeric range (top 10%, bottom 50, etc.)
Unique/Duplicate Rules
This Conditional Formatting tool allows the user to identify either unique or duplicate data within the specified column. While RTA can assist in preventing the creation of duplicate part numbers, vehicle numbers and PO/WO numbers within a facility, this tool can be used to identify trends and differences within the data range.
One example where this filter can be useful is to identify duplicate information that may have been overlooked, such as a duplicated license plate or VIN number in the vehicle master file. If I open my vehicle master file and right-click on the License Plate column, then chose Conditional Formatting > Unique/Duplicate Rules > Duplicates… then I will see which data fields have duplicated with another in the same column. Example:
Not only can we now see if any License Plates have been Duplicated, but it is also illustrated to us which have not been filled in properly in this case.
Data Bars, Color Scales and Icon Sets
Under Conditional Formatting > Data Bars/Color Scales/Icon Scales within the F1 lookup lists, the use has rights to categorize and illustrate the identified data cells in a visual representation based on the selected format.
Data Bars are color-specific greater than/less than bars within the data cells to assist in data volume amounts based on set parameters.
Using Color Scales, end-users can apply multiple format rules to a column and additionally set a two-color scale format for the same column. This format indicates value magnitudes using cell background colors.
Icon Sets place a “filler” icon within the data box to indicate how big or small a column cell value is. For example, using the Star Icon:
Clear Rules
When Conditional Formatting has been applied and is in need of removal (either at the specific columnar level, or from the entire list), you can use the Clear Rules option. This will reset values to the way they were before any formatting rules were applied. That process is Conditional Formatting > Clear Rules > Clear Rules from This Column/Clear Rules from All Columns
For more information on the technical setup and design of Conditional Formatting, please see our source material at https://docs.devexpress.com/WindowsForms/114603/controls-and-libraries/data-grid/getting-started/walkthroughs/appearance-and-conditional-formatting/tutorial-conditional-formatting