SharePoint 2010 – Dynamic Calculations in a List Form
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 does cannot see the result of the calculation until after they have 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:
![ch7[1]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/72-ch71_41d633d7.png?resize=508%2C290 "ch7[1]")
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. ![ch7[2]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/72-ch72_41d633d7.png?resize=72%2C30 "ch7[2]"){kind=small}
4. Notice that now when viewing the form, the calculated field does not display at all:
![ch7[5]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/72-ch75_41d633d7.png?resize=373%2C346 "ch7[5]")
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:
![ch7[7]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/72-ch77_41d633d7.png?resize=412%2C331 "ch7[7]")
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 re-create the same formula that exists in the calculated column, as so:
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:
![ch7[8]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/72-ch78_41d633d7.png?resize=319%2C374 "ch7[8]")
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 be able to make the calculations dynamic in the form.
Yipes! But you have to use infopath to do it. Wouldn’t it be easier to use jquery and maybe either a CEWP or a RenderingTemplate with the javascript embedded to do this. I mean after all you’re trading XSLT sum function for a javascript sum function and you don’t have to involve another rendering system i.e. Infopath. What if the client doesn’t have infopath services or infopath client installed, does it still work? Still a nice tip. Amazed at all the new stuff you can do with 2010.
Brian, this requires browser forms, which requires SharePoint Server 2010 Enterprise. On the client side, it requires the person modifying the form to have InfoPath 2010 locally (but not the end users). Sure, you can use jquery, cewp, or whatever else if you prefer, but how many people know that stuff? InfoPath list forms make it incredibly easy and powerful to make modifications like this as well as many other much more powerful things. You can add data connections behind the scenes for performaing a litany of dynamic functions. Unlike a CEWP with JS, it works every time the form is opened as opposed to needing to add it to every page/view. PS. IPFS is not a client-side feature, so users can’t have that installed anyway.
Good to know. I do most of my forms in simple SharePoint list with calculated columns. If the form needs to be more dynamic then I have to use web-compatible InfoPath forms. If SharePoint 2010 packs more capabilities on their SharePoint lists I’ll be happy to make solutions everyday with them instead of doing it in InfoPath. Not that I don’t like InfoPath I just want to do things quick and simple. Nice post.
Great post! I got this working, but when adding the total field in the form, I can’t get it to format as a currency? As of now, it’s just a number. I’m sure I’m missing something here. Any help would be appreciated! Thanks in advance!
eric, I had the same problem. I added a textbox then bound it to the total field. That was a mistake. Just drag the total field to the table and you will see a box bordered by dotted lines. Right click on the box, click the last item: ‘Calculated Value Properties’. You’ll then see there are formatting options.
Yep, right click and go to “calculated value properties” where you’ll see formatting options.
Hi Laura, I need some help on how to do calculation on a sum up total. For example, Department 1 Purchase Received Percentage(This is a calcuted column) Category 1 10 8 80% Category 2 5 3 60% Total 15 11 73% Department 2 Purchase Received % Category 1 8 4 50% Category 2 5 2 40% Total 13 6 46% Grand Total 28 17 61% I can get the total count and be able to get the category percentage, but I cannot do the total percentage and grand total percentage. Is there a way to do this calculation in SharePoint? Please help!!! You can send me an email to wing113@yahoo.com
I need to add dynamic calculations in the edit and new form. I am on SharePoint 2010 enterprise, but our info path forms are disabled. I have tried to find JavaScript to do this and have tried numerous ways but unsuccessful at getting this to work. Would you be able to help finding some JS to help do this?
Jeb, Sorry I don’t know anything about JavaScript or jQuery, but you may want to try those avenues.
Thanks Laura. It is very difficult and I have the first phase of what I need in JavaScript. Info path looks much easier, I really wish I had the enterprise SharePoint version. The company I work for should have it by June 2012. Just for purposes of others saying why not just use Jquery. It is very very difficult and info path makes this easy.
Yes InfoPath is much better for people who don’t know code.
It doesn’t work for me, I am using a calculated column to generate the unique value for the reference to record. I concatenated fields like “DeptID”&&”SharePoint Record ID”, it doesn’t refresh after submitting the record.and I manually need to refresh the page to get record ID updated.
Hello!
Didn’t work for me either – SPO/O365.
Clearly the solution is labeled that it is specifically for SharePoint 2010 with InfoPath 2010. Definitely not even remotely something applicable in SPO, especially since InfoPath isn’t even a thing anymore. I wrote that almost TEN years ago.