In SharePoint lists that contain calculated columns, you may have noticed that when a new form is being created or edited, the results of calculated columns do not display in the form itself, so the user cannot see the result of the calculation until after he or she has saved that item. The calculated field can then be seen as a column in the list, or can be seen when viewing the display form of the item.
Now in SharePoint 2010 and InfoPath 2010, there is the ability to customize any SharePoint list with InfoPath. Because of this capability, you can dynamically display the calculated results in the form as the user is filling it out!
In this example, there is an “Expenses” list with a sales figure for each type of expense for a trip. There is a calculated column, called Total, which shows a sum of all expenses for the trip. Here’s how:
1. This is the “Total” calculated column in the list:
2. Create items in the list, and notice the calculated field in the display form:
3. In the SharePoint list, on the List tab of the ribbon, click the Customize Form button, which will instantly convert the SharePoint list form to an InfoPath form. Click the Quick Publish button in the toolbar at the top left of InfoPath.
4. Notice that now when viewing the form, the calculated field does not display at all:
5. Click the “Customize Form” button again. Add a new row to the bottom of the table in the InfoPath Form. From the “Insert a field” panel on the right, insert the “Total” field into the table. Right-click this field, and choose “Calculated Value Properties”. Change the format to decimal and currency and click OK.
6. The trick now is that we want the formula to be dynamically calculated as the currency fields are being filled out. Double-click to select the “Total” field, and click the Default Value button in the ribbon in InfoPath:
7. On the Field or Group properties box, click the Function (Fx) button next to the Default Value box.
8. Use the Insert Field or Group button to insert the fields to recreate the same formula that exists in the calculated column:
9. Click OK, leave the box checked that says “Refresh value when formula is recalculated,” and click OK again. Quick Publish the form again.
10. Now, when viewing the form in the browser, you’ll see the total field displayed, and when filling out a new item, the total will be calculated as the form is being filled out, which will look something like this:
Now you’ll not only be able to work around the bug in which calculated columns aren’t included in InfoPath forms of SharePoint 2010 lists, you’ll also be able to make the calculations dynamic in the form.
Laura Rogers is a consultant with SharePoint911.
