How To: Use Expressions to Perform Calculations in a List View

This article illustrates how you can add expressions on list view controls to calculate values. You learn how to configure an expression that multiples the values of two controls, then displays the result in a third control. For example, you have a sales order form where you want to automatically calculate a row total for each item entered. The expression multiplies the item amount by the quantity and displays the total for that row as shown below.

Using an expression to calculate row totals

Scenario

You have created a sales order form for your customers to submit new orders. The form contains an editable list to add, edit, and remove items. For convenience and accuracy, you want to provide a row total for each item entered (the cost of the item multiplied by the quantity). You do this by adding an expression that takes the item cost and multiplies it by the quantity, then displays the total amount for each row.

Steps

In this scenario, begin by building a SmartBox SmartObject to store your list items when you save them. Next, create a view from the SmartObject that contains the controls for adding, editing, and deleting list items. Add an expression that calculates the row total for each line item entered. Finally, create a form that contains the view.

Setup Steps

Use the following steps set create the scenario from start to finish. If you want to add an expression to a control without following the scenario, go to Add an Expression.

1. Create a SmartObject that has at least one number property, one decimal property, and one text box property.
   1. Create a new SmartObject in K2 Designer. Name the SmartObject
      Sales Orders SmartObject.
      Select the SmartBox option. This option creates a table in the K2 database. Click Create.
      
   2. Click Add to add the properties shown in the image below. Make sure to change the data type where necessary. Click OK to return to the SmartObject properties. Click Finish to save the SmartObject and exit.
      
2. Design an editable list view based on the SmartObject.
   1. Right-click the Sales Orders SmartObject and  select Design View. The list view displays any existing records from the Sales Orders SmartObject and contains controls to interact with new and saved content.
   2. Name the new view
      Sales Orders View.
      Change the View Type to List View. Confirm the option to Call this method when the form loads is checked. At runtime, this method retrieves saved records from the SmartObject and loads them onto the form. Click Create.
      
   3. Click Create Labels and Controls to allow K2 to generate the layout table and add controls for you.
   4. On the Create Labels and Controls page, include all the fields except ID. Check the option to Enable list editing. Allow the user to add, edit, and remove rows which allows people using the view to add, update, and delete line items from the list view.
      

Add an Expression

The following step describes how to add an expression to a control in an editable list. In this scenario, you add and configure the expression for the Row Total (text box) control that multiplies the Item Cost (decimal control) by the Item Quantity (number control). The Row Total (text box) control updates automatically.

3. Add an expression to the text box control that multiples Item Cost by Item Quantity.
   1. Select the Row Total control in the Add/Edit Item section. In the Properties pane, locate the Expressions option found in the General section. Click the ellipses to open the expression editor.
      
   2. Click Add and specify a Name for the expression
      Calculate Row Total.
      
   3. Next, configure the expression using K2 operators. Operators include functions for working with text, date and time, conversions, and standard operators. Click the Operators tab near the lower left of the page. Expand the Operators node. Drag the Multiply operator into the Expression Details box.
      
   4. Now that you have the operator in place, you can add the references to the two controls: Item Cost (decimal) and Item Quantity (number). Select the Context tab (next to the Operators tab). Expand the Sales Orders View node, then expand the Controls > Add/Edit Row nodes. Drag the Item Cost Text Box control into the first Expression Details box. Drag the Item Quantity Text Box control into the second Expression Details box. Click OK to save the expression configuration. Click OK to save the expression.
      

   Notice the Row Total control shows the Calculate Row Total expression as its default value.
   

   5. Click Finish to save and exit the view.
4. Design a form from the view.
   1. Views must be contained within a form to be opened in a browser. Right-click the Sales Orders View and  select Design Form.
   2. Name the form
      Sales Orders Form.
      
      
   3. Click Finish to save and exit the form.
5. Test the expression.
   1. To test the expression, open the form using the Runtime URL.
      
   2. Click Add to enter items, costs, and quantities. Press <Enter> on your keyboard to set the row total. The row total updates with the calculated result.
      

Review

Expressions perform operations on different types of data. Use expressions to add convenience and help ensure accurate and consistent data input. Common uses include performing mathematical calculations, formatting text (such as date/time), and converting data (such as string to number). In this scenario, you used an expression to multiply cost by quantity, resulting in a total for each line item in the order.