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 the row.

Using an expression to calculate row totals

Try it yourself!

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 to 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 SmartBox SmartObject called Sales Orders SmartObject with the following properties: Item Name (Text), Item Cost (Decimal), Item Quantity (Number), and Row Total (Decimal).
   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 Sales Orders SmartObject. Select Create Labels and Controls, then include all the fields except ID. Enable list editing and allow users to add, edit, and remove rows.
   1. Right-click the Sales Orders SmartObject and select Design View.
   2. Name the 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. (The list view displays any existing records from the Sales Orders SmartObject and contains controls to interact with new and saved content. )
      
   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 control that multiplies the Item Cost by the Item Quantity. The Row Total control updates automatically.

3. Add an expression to the Row Total control that multiples the Item Cost by Item Quantity.
   1. Select the Row Total control in the Add/Edit Item section. In the Properties pane, locate the Expression option found in the General section. Click the ellipses to open the expression editor.
      
   2. Click Add and name 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 screen. 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 and name it Sales Orders Form.
   1. Views on their own are not fully functional. While you can run views to test them, you must contain them within a form to make them available to other users. 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 by running the form. Enter values in the Item Name, Item Cost, and Item Quantity fields, and confirm the expression executes to update the Row Total value.
   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.