I. Overview
1) Through conditional formatting, you can change the display value of text during the preview.
2) This article shows the methods to set new value for text according to stock quantity and order quantity, so as to reveal the unique inventory status.
3) Prerequisites: Insert Data Columns, An Introduction of Conditional Formatting.
| You will learn |
|---|
|
II. Steps
1. Create a new dataset [ds1]
1) Create a new DB query dataset.
2) Drag the table [Product] into the DB query.
2. Drag data columns into cells
1) B3~E3: Drag data columns [Product_name], [SupplierID], [Inventory_quantity] and [Order_quantity] into cells.
2) B2~E2: Enter headers and fill them with grey.
3) B2~E3: Set as center alignment and add borders.
4) Preview.
3. Select D3 and add condition
4. Property setting
1) Click + to select the property: New Value.
2) Set the properties: String, Integer, Double Precision, Date, Boolean and Formula
3) Select the string: Low Stocks.
5. Add common condition
1) Operator: Less Than.
2) Value Type: Integer.
3) Value: 10.
4) Click + to add the common condition.
6. Preview
When the number of inventory is less than 10, the corresponding cell in the Inventory column is shown as low stocks.
7. Modify the applicable range to [Current Row] and add the formula condition:
1) Select the type: Formula. Click Edit.
2) Define the formula as $$$ < E3 and click OK. ($$$ represents the value of the current cell, and data column [Order_quantity] is inserted into E3.)
3) Select the logical operator: OR.
4) Click + to add the formula condition.
8. Preview
When the number of inventory is less than 10 or the order quantity, the corresponding cell in the Inventory column is shown as low stocks.
