Updating a total price of an order might be the most common business functionality in software. Every business that carries more than one product, or even a business with multiple options on a single product needs to be able to calculate the total cost from individual unit prices and quantities. This is such a common need that I’m surprised there wasn’t more support for this situation in Nintex Forms, before the TekDog Productivity Controls that is.
Within Nintex Forms, you can create a lookup to another list, let’s call it Products, that has a UnitCost field as currency. Then if you have sort of a “parent” list called Orders with the lookup and a TotalCost field, you can use a Calculated field control with a formula to calculate the total.
Ultimately the formula for the calculated Total Price control will look like this:
I’ll walk you through each part of the formula.
Starting with a formula that only displays the output of the lookup control, you can see that the lookup text displays the ID of the lookup list item along with the text and a semicolon and number sign.
This won’t work as-is because we want to match against the Products list with either the ID or the Title of the product, not both strung together. So if we improve the formula to use the parseLookup() runtime function with its single argument as the named ProductLookup control, we get only the text value.
We can now use the lookup() runtime function with Products as the list title parameter, Title as the column to filter on, the text output of the parseLookup() function as the value to filter on, and UnitCost as the output column. The result is a correctly calculated unit cost from the Products list.
Finally, we can multiple that entire formula by the Quantity named control for the total price.
This works great if your order only includes a shipment of a quantity of the same product. But what if you need to support orders where MORE than one type of product is to be shipped?
To do this, you should use the TekDog Subform and Aggregate Function controls to track the line items of the order in another SharePoint list with a one-to-many data relationship.
Using the TekDog Productivity Controls to update a total price for a complete order
In order to create the proper data relationship between an Order and individual product list items, we will need a new list called OrderLineItems. This new list will have a ProductLookup lookup field, a numeric Quanity field, and a currency LineItemTotal field. In this way each line item can be represented on the Orders form in a subform grid. Setting up the TekDog Productivity Controls in the Nintex Forms designer is easy.
No formula is required to calculate the grand total for the order. Calculating the sum of a column in another list is an option on a drop-down box in the TekDog Aggregate Function control.
Now we can use the controls in concert to have multiple items for each order.
The beautiful thing about having a one-to-many data relationship with the TekDog Subform and Add New Child List Item Button controls is that you can configure them to popup a Nintex Form editor right off the page, and return with updated values, including the grand Order Total.
We truly hope you find the same value that we do when using our Productivity Controls allowing you to do so much MORE with Nintex Forms!