You can build expressions for data items with the Expression Editor. You can display the editor when creating a new data item or when editing an existing data item.
Note: Once you become familiar with creating expressions, you can type them in directly without using the Expression Editor. The Expression Editor only generates Expressions for internal cursors and VFP tables.
To build an expression for a data item:
- Run the Expression Editor.
- If creating a new data item, press Create on the Select Data Items tab then click Edit in the dialog box that appears, or
- If you’re editing an existing item, right-click a data item in either list on the Select Data Items tab and then click Edit Data Item.
- Highlight a data item in the Choose Item list. Depending on the data item type, different buttons will be available (see figure above).
- Click Insert. This data item now appears in the Expression Preview box. Note by default the expression appears in plain language so it is easier to understand unless you choose to see it in the actual database expression language. When editing an existing data item, it will always be shown as an actual expression.
- Now choose one of the buttons at the right of the Choose item box to build your expression. For example, you can choose calculations like add or subtract, or format your expression to remove trailing blanks, add spaces, or add text.
- Repeat steps 2-4 until your expression is complete.
- Click OK. Note: Once you click OK, the expression is converted to the actual expression language and you won’t be able to see it in plain language again.
See the table below for examples of expressions. Following the table are the procedures for creating the expressions listed in the table.
| What it Does | Plain Language | Actual Expression |
|---|---|---|
| Calculates gross profit. | Cars Sales Price – Cars Purchase Price | CARS.SALEPR-CARS.PURCHPR |
| Short cut way of always showing car make and model together. Eliminates having to choose two separate data items. | Cars Make followed by “/” followed by Cars Model | CARS.MAKE+”/”+CARS.MODEL |
| Shows the car make in uppercase. | Cars Make in upper case | (UPPER(CARS.MAKE)) |
| Shows cars colors followed by two spaces. | a. Cars Color followed by 2 spaces, OR b. Cars Color padded with 2 spaces | CARS.COLOR+” “+” ” OR (PADR(CARS.COLOR,2)) |
| Shows “Total Amount of Profit $999, 999.99” | “Total Amount of Profit” followed by 2 spaces followed by Gross Profit | “Total Amount of Profit”+” “+” “+(LTRIM(STR(CARS.SALEPR – CARS.PURCHPR))) |
Example 1: To calculate gross profit:
- Highlight the data item “Cars Sales Price” and click Insert.
- Click “-” (minus sign) in the Choose Calculation area.
- Highlight the data item “Cars Purchase Price” and click Insert.
- Click OK.
Example 2: To show car make and model together:
- Highlight the data item “Cars Make” and click Insert.
- Enter “/” in the “Insert your own text” box and click Insert.
- Highlight the data item “Cars Model” and click Insert.
- Click OK.
Example 3: To show car make in uppercase:
- Highlight the data item “Cars Make” in the Choose item list.
- Locate the “Apply formatting to selected item” drop-down box. Choose Convert to upper case.
- Click Apply.
- Click OK.
Example 4a: To show car color followed by two spaces:
- Highlight Cars Color from the Choose Item list and click Insert.
- Click the Space button twice (the Space button is in the Insert your own text area).
- Click OK.
Example 4b: To show car color padded with two spaces:
- Highlight Cars Color from the Choose Item list.
- Locate the “Apply formatting to selected item” drop-down box. Choose Add trailing spaces to the right.
- Click Apply. You’ll be prompted for a parameter, which is the number of spaces to add.
- Enter 2 and click OK.
- Click OK.
Example 5: To show “Total Amount of Profit” followed by 2 spaces followed by gross profit:
- Type “Total Amount of Profit” in the Insert your own text box and click Insert.
- Click Space twice.
- Highlight the data item “Gross Profit” and click Insert.
- Click OK.