Calculated List Columns in InfoPath 2010

The Situation

When you use InfoPath 2010 to customize the forms associated with a SharePoint 2010 list, there are a few “gotchas” when it comes to calculated columns.

1. Calculated columns don’t show validation formatting and text tips when using browser-enabled forms.

   

   The Validation Formatting is Missing from Calculated Fields.

   This is fairly easy to fix. You can simply change the control to a text box and you’ll get your validation formatting back.

   

   Change the Calculated Control to a Text Box to Restore Validation Formatting

2. But, then…. Text boxes bound to calculated columns of type “number” do not conform to the number formatting set in SharePoint.

   

   Text Boxes bound to calculated columns incorrectly showing decimal places.

   And, of course, you can’t control the formatting of a text box bound to a calculated column.

   

   The format button is grayed out on text boxes bound to calculated columns.

The Work-Around

There are a couple of potential work-arounds.

Option 1

Don’t use calculated columns when you’re customizing a list with InfoPath forms.
The benefit of doing this is that you can use text boxes set to read-only in the Infopath form and rules on another field or fields to run your calculations. You get all the formatting control you want, and validation formatting will display correctly. You will be storing a static value instead of having SharePoint run calculations behind the scenes. But, if you’re working with a content type and you’re not sure that every place it’s going to be utilized will be using InfoPath forms, you’re now relying on your users to do the calculations by hand, which is not ideal. And, you have to be very cautious with all your action rules in the InfoPath form to make sure you’re doing your calculations accurately. And, if a calculation changes, you’re updating an InfoPath form instead of a site or list column. Because I’m using content types and all these calculated columns are site columns, I chose to keep them as calculated columns.

Use Form Load rules to format numbers on load.

InfoPath doesn’t seem to have any problems with a new form. The issue only seems to arise with an edit or view form. So, you can add rules on form load that checks to see if “Created” is blank. It will always be blank when a new form is opened. When an edit form is opened, it will not be blank. If it is not blank, format each of your calculated columns.

Format Calculated Columns on Form Load

In my case, I know that InfoPath is just padding my numbers with a decimal point and a bunch of zeros. So, I can use a simple floor() function to knock off all the decimal placings and return a whole number. (I could have also used ceiling().)

The edit form now correctly displays whole numbers for my calculated columns.

After adding form load rules, fields display correctly.

 

There may be other gotchas I haven’t found yet. Have you found any?

 

Advertisement