Advanced cell coloring in reports

Applies to version: 2020.3.x and above; author: Józef Cyran

 

Introduction

In the latest version of WEBCON BPS, the report coloring has been enhanced for additional functionalities (a description of the basic report coloring modes can be found here). There is the ability to go through a path without having to view the specific instance – just click on a properly configured cell. This configuration also allows you to set the cell style, as well as special display functions.

 

Business case

An example that presents the advanced functionality of reports coloring is the process of listing stored products (e.g. bicycle models). Each workflow instance represents a warehouse with the serial numbers of its products. The warehouse can be closed (e.g. when it is full) preventing the insertion and removal of products from it. Continue reading to see how to achieve this workflow automation with WEBCON BPS.

 

Workflow diagram

 

Fig. 1. The magazine is available for editing only at the registration and opened step

 

 

Form fields

 

Fig. 2. The total amount of products is set by form rules executed on value change

 

Fig. 3. The quantity of products is set by form rules executed on value change

 

Fig. 4. Instance representing the warehouse

 

The report configuration - warenhouses

The Warehouses report includes all warehouses with the number of stored products.

 

Fig. 5. The basic view contains only the total number of products

 

Fig. 6. The extended view displays also the number of individual models

 

This report allows you to see the number of products and go through the path from the report level, and close or open the warehouse depending on the step in which they are located – you can use the buttons added to the cell.

 

Fig. 7. In the View tab go to the configuration and select Coloring mode -> Advanced and then paste the JSOn code with cell stylling

 

Fig. 8. JSON with cell styling

 

 

”content”: ”=CurrentField” displays the basic cell value

The function ”=if(CurrentField == ‘Open’, ‘lightgreen’, ‘tomato’)” works in a similar way to Excel – first give the condition i.e. that the basic cell value is equal to the “Open” string of characters and then, enter the values accordingly for the fulfilled and unfilled condition.

In the children array there additional html elements that you want to display. In our case these are two buttons but only one will be visible at a time – depending on which step the instance is in. Use the ‘if’ function to define a value in the “display” attribute – thank to this only the correct button will be displayed following the path available in a given step.

”action”: {

                   ”type”: ”quickPath”,

                   ”params”: {

                       ”pathId”: 1220

                   }

               }

The above JSON fragment indicates the action of going through the path and the path ID. To work correctly, select the “Available as quick path” option.

 

Fig. 9. Quick paths allows you to go from the level withoult entering to a given instance

 

Fig. 10. Bars displaying the number of products are configured by selecting the color, minimum and maximum value

 

In the advanced mode you can use with the bar function:

Fig. 11. The Bar function contains the following arguments: the value specyfying the lenght of the bar, color and minimum and maximum value

 

 

Report configration - Model 1

 

Fig. 12. Lock icon indicates whether the warehouse is closed or opened

 

The report contains all model 1 products that are in the warehouses. The background cell column indicates the number of products in the warehouse.

 

Fig. 13. Available set of colors

 

In the advanced mode, use the ‘scale’ function to use with the above functionality.

 

‘Scale’ function

Syntax: scale(value, class_prefix, min, max)

The function finds in which of the 10 ranges (from minimum to maximum), the given value is located.

 

Icon

UI Fabric icons are available at: https://developer.microsoft.com/en-us/fluentui#/styles/web/icons#available-icons (icons should be searched for in the Fabric Core set).

 

Fig. 14. The configuration of the column containing the warehouse code with an icon that presents the step in which they are located

 

 

Functions formatting the report cells

Expressions

In the expressions you can use defined operations and functions:

• *, /, +, – operators and other mathematical operators
• + operator to linking the text strings
• Other mathematical functions described here
• ‘if’ function (condition, value_if true, value_if false). These functions can be nested within themselves
• Functions dedicated to creating a graphical representation of ‘bar’ and ‘scale’ data
• Functions connected with operations on dates: date, now, today and others
• Functions connected with operations on the text: includes and startsWith
• Functions connected with operation on picker form fields: pickerCompare, pickerCompareByIdi pickerCompareByName
• Context variables as: CurrentUser.BPSID and CurrentUser.DisplayName returning information about a current user

 

Functions connected with date and time support

• date(value) – takes a date in ISO format as a text (like the constructor of a Date() object in JavaScript) and returns a Unix time marker (as a number of milliseconds since 1970)
• datePart(value, [‘year’|’month’|’day’|’hour’|’minutes’|’seconds’]) – takes a date in ISO format as a text (like the constructor of a Date() object in JavaScript) and returns the selected date fragment
• days(value) – replaces the given number of days to the number of milliseconds
• hours(value) – replaces the given number of hours to the number of milliseconds
• minutes(value) – replaces the given number of minutes to the number of milliseconds
• seconds(value) – replaces the given number of seconds to the number of milliseconds
• now – returns a Unix time marker a current date and time
• today – returns a Unix time marker for the beginning of the current day

 

Functions connected with picker value comparison

• pickerCompare – comparing both names and identifiers
• pickerCompareById – comparing identifiers
• pickerCompareByName – comparing names

 

Each function takes a variable that refers to the picker column or one of the special system columns in their first argument.

The second argument is a string of the id#nazwa form. When it comes to picker fields in multiple choice mode, these values should be entered after a semicolon e.g. id1#nazwa1;id2#nazwa2;id3#nazwa3

 

Picker variables

Picker variables are special types of variables that are objects with Id and Name properties. Variables of this type are all choice column (AttChoose_N, where N is a number of a given attribute) and the following system variables:

• DET – process
• STP – step
• DOCTYPE – form type
• SUBDOCTYPE – sub-type form
• WF – workflow

 

Example of use:

Depending on the step ID the ContactCard and ContactCardSettings icon will be taken. Notice that when referring to the Id and Name, the value in the comparison operator is set as text (in apostrophes) – STP.Id == ‘212’

“attributes”: { “iconName”: “=if(STP.Id == ‘212’, ‘ContactCard’, ‘ContactCardSettings’)” }

 

Supported HTML attributes and elements

HTML elements

• div
• span
• a
• img
• svg
• path
• button

HTML attributes

• href
• src
• class
• target
• title
• role
• style
• iconName

 

Supported CSS classes and styles

CSS styles

• align-items
• background
• background-color
• background-image
• border-bottom-color
• border-bottom-left-radius
• border-bottom-right-radius
• border-bottom-style
• border-bottom-width
• border-bottom
• border-collapse
• border-color
• border-left-color
• border-left-style
• border-left-width
• border-left
• border-radius
• border-right-color
• border-right-style
• border-right-width
• border-right
• border-spacing
• border-style
• border-top-color
• border-top-left-radius
• border-top-right-radius
• border-top-style
• border-top-width
• border-top
• border-width
• border
• bottom
• box-decoration-break
• box-shadow
• box-sizing
• caption-side
• clear
• clip
• color
• cursor
• direction
• display
• empty-cells
• fill
• float
• font-family
• font-size-adjust
• font-size
• font-stretch
• font-style
• font-variant
• font-weight
• font
• hanging-punctuation
• height
• justify-content
• left
• letter-spacing
• line-height
• margin-bottom
• margin-left
• margin-right
• margin-top
• margin
• max-height
• max-width
• min-height
• min-width
• opacity
• outline-color
• outline-style
• outline-width
• outline
• overflow-style
• overflow-x
• overflow-y
• overflow
• padding-bottom
• padding-left
• padding-right
• padding-top
• padding
• position
• punctuation-trim
• right
• table-layout
• text-align-last
• text-align
• text-decoration
• text-indent
• text-justify
• text-outline
• text-shadow
• text-transform
• text-wrap
• top
• transform
• unicode-bidi
• vertical-align
• visibility
• white-space
• width
• word-break
• word-spacing
• word-wrap i -ms-word-wrap
• z-index

CSS classes

• webcon-conditions – defines the spacing for the cell background
• webcon-databar – an example of using in the section on the bar function
• webcon-databar__bar – defines the positioning of the data bar
• webcon-databar__bar–0 do webcon-databar_bar–100 – defines the width of the data bar in percent
• webcon-databar__content – defines the positioning for text “above” the data bar
• webcon-scale-green–1 do webcon-scale-green–10 – the background color from transparent to green, the subsequent classes differ in color saturation
• webcon-scale-green-to-red–1 do webcon-scale-green-to-red–10 – the background color from green, through transparent to red, the subsequent classes differ in color saturation – with the two middle classes having the smallest
• webcon-scale-red–1 do webcon-scale-red–10,
• webcon-scale-red-to-green–1 do webcon-red-to-green-red–10