Where to Insert Conditions for Changing the Table’s Appearance

Required Permissions

This section requires the Application Designer permission from your Domain's administrator.

Check What are the Design Tasks? to learn more about permissions to work with Design Mode and other tools.

Abstract

• Inserting Formulas to Change Row Background and Text Color
• Inserting Formulas to Change Cell Color
• Inserting Formulas to Change Header Color

Learn where to insert Formulas in the Layout panel to change the rows, cells, and headers colors according to certain conditions.

Designers can configure table's elements to change colors according to criteria specified in a Formula.

Note

The process to configure a Formula to customize the table headers, cells, and rows' colors is the same for both Master and Detail tables.

Inserting Formulas to Change Row Background and Text Color

Learn where to insert a Formula to change a row's color or text according to the specified condition.

When the Formula condition will be satisfied, the row's background or text colors change.

1. In Design Mode, go to the Layout panel.

   

2. Click on the table's tab for which you want to insert a Formula managing text or background colors.

   

3. Click on the Toggle Advanced Settings toggle switch.

   

4. You find the buttons to insert the Formulas for the Row's background and text color under the Row Colors label.

   

   Click on the Background button to open the Row background Color window and insert a Formula for the row background. The inserted formula will manage rows' background color.

   

   

   Click on the Text button to open the Row foreground Color window and insert a Formula for the text's color. The inserted formula will manage the rows' text color.

   

   

5. Click on the Set Formula button in either the Row background Color or Row foreground Color window (depends on the choice made at the previous step 4).

   

   

6. Insert the Formula in the Formula Editor. You can insert a HTML Supported Code to specify a particular color in a Formula between quotation marks (" ").

7. Once typed the formula, click on the Confirm button in either the Row background Color or Row foreground Color windows.

   

8. After the formula's insertion, the buttons display the F(x) tag. F(x) points that a formula manages that appearance element.

   

   Click on the Save button to save the changes. You can find the Save button in the upper-left corner of the Design Mode.

   

Success

You have configured a Formula to manage the rows' background and text color according to certain conditions.

Example

The Warehouse Orders application manages the orders and shipping from a warehouse.

Each Master table's row stands for an order. Each Row has a Lookup drop-down list called Order Status. Order Status records the unique Id for each possible order status. (E.g. The "Placed" status has the Id 1 and the "Cancelled" status has the Id 2.)

Users need an aid to identify orders already completed, waiting to be picked up or cancelled while skimming throught the Master table's pages.

A solution is employing a formula that changes the rows' color according to the order status.

In Design Mode, the Designer accesses the Layout sub-panel.

They choose the Master table Warehouse Orders.

The Designer clicks on the Toggle Advanced Settings toggle-switch.

Then they click on the Background button under the Row Colors label.

The action opens the Row background Color window.

The Designer clicks on the Set Formula button and inserts the following formula:

/*
The formula checks the value of the Order Status Lookup and
changes the row's color according to its value.

The =SWITCH() function takes a field value and 
compares it with one or more values. According to the case,
=SWITCH() yields a specific value in turn. (In this
example, the yielded values are color names.)

The =SWITCH() first parameter is the system variable $CURRENTSTORAGE.OrderStatusId.
$CURRENTSTORAGE.OrderStatusId refers to the value of theOrderStatusId
Numeric field. (OrderStatusId is the name of the Lookup
Order Status' field on the Storage .)

=CASE() is a function defining cases for the =SWITCH() 
function. The first parameter of =CASE() defines the value that 
$CURRENTSTORAGE.OrderStatus must have. The second parameter of
=CASE() defines what value to yield if $CURRENTSTORAGE.OrderStatusId
has the value specified in the first parameter.

In the example, =CASE(6; "green") means that 
    - If OrderStatusId is 6 (i.e. Completed)
    - color the row with "green"

Likewise =CASE(5; "yellow") means that 
    - If OrderStatusId is 5 (i.e. Shipping from the Warehouse)
    - color the row with "yellow"

=CASE(2; "red") means that
    - If OrderStatusId is 6 (i.e. Cancelled)
    - color the row with "red"

Usually, the =SWITCH() function accepts a =DEFAULT() function
as parameter.
=DEFAULT() specifies a default value if no value satisfies
one of the =CASE(), but is optional. 
If you don't specify =DEFAULT(), the formula won't color the row 
if the value is not listed in one of the =CASE().

If OrderStatusId has values different than 2, 5, or 6, the row
will maintain the standard color on the table and nothing happens.
*/
=SWITCH(
   $CURRENTSTORAGE.OrderStatusId;
   =CASE(6; "green");
   =CASE(5; "yellow");
   =CASE(2; "red")
)

Then the Designer save the configuration and all the changes in Design Mode.

Now, the application highlights of green the Completed orders, yellow the orders that are shipping from the Warehouse, and red the Cancelled orders.

Inserting Formulas to Change Cell Color

The procedure is the same explained in the section Change Table Headers or Cells Colors.

The only exception is clicking on the Set Formula button in the Color Settings of Cell Bg Color window instead of choosing from the palette or the default color options.

Tip

You can insert a HTML Supported Code to specify a particular color in a Formula between quotation marks (" ").

Tip

The Advanced Settings' preview highlights which cells' color are managed by a Formula

Example

The Warehouse Stock application records the items' catalogue and their quantity in the warehouse.

Users might need something highlighting when an item is out of stock without checking the Availability or Quantity fields. A solution is using a Formula highlighting the item's name of red when is not available and the quantity is 0.

In Design Mode, the Designer accesses the Layout sub-panel

Then the Designer chooses the Master table Products and clicks on the Cell Bg Background button for the Product Name field. (Product Name is the String field recording the item's name.) The action opens the Color Settings window for that field.

They click on the Set Formula button to insert the following formula:

/*
The =SWITCH() function takes a field value and 
compares it with one or more values. According to the case,
=SWITCH() yields a specific value in turn. (In this
example, the yielded value is a color name.)

The =SWITCH() first parameter is the system variable $CURRENTSTORAGE.WarehouseQuantity.
$CURRENTSTORAGE.WarehouseQuantity refers to the value of the WarehouseQuantity
Numeric field. WarehouseQuantity records the number of items available. 

=CASE() is a function defining cases for the =SWITCH() 
function. The first parameter of =CASE() defines the value that 
$CURRENTSTORAGE.OrderStatus must have. The second parameter of
=CASE() defines what value to yield if $CURRENTSTORAGE.WarehouseQuantity
is 0.

In the example, =CASE(0; "red") means that 
    - If the item's quantity is 0
    - color the Product Name cell with "red" 

This =SWITCH() formula has only one case since it must
check just a single value and yield "red" only in that
case.

Usually, the =SWITCH() function accepts a =DEFAULT() function
as parameter.
=DEFAULT() specifies a default value if no value satisfies
one of the =CASE(), but is optional. 
If you don't specify =DEFAULT(), the formula won't color the row 
if the value is not listed in one of the =CASE().

*/

=SWITCH($CURRENTSTORAGE.WarehouseQuantity; =CASE(0; "red"))

The Designer saves the configuration and the changes in Design Mode.

Now, the application highlights with red the field Product Name if the quantity is 0 and the item is out of stock.

Inserting Formulas to Change Header Color

The procedure is the same explained in the section Change Table Headers or Cells Colors.

The only exception is clicking on the Set Formula button in the Color Setting window for the Header Bg Color instead of choosing from the palette or the default color options.

Tip

You can insert a HTML Supported Code to specify a particular color in a Formula between quotation marks (" ").