I find that, programmatically, SharePoint is a witches brew of different technologies which work together as long as you don't need to do things your way.

In this instance I'm creating a list of Invoices which will serve as the parent item to a list of Invoice Items. Each Invoice Item row contains (among other fields) a field for quantity, the amount for each item, and the total. The total field is a calculated field, displaying the product of the amount and quantity fields.

Initially I had the total field set up as a number, which seemed to make sense considering I was working with an equation. Where it did not make any sense is when I put together a form which found all the Invoice Items for an Invoice and summed up the total field values to create an overall total for the Invoice.

In my initial tests everything actually ran fine until I tried putting in an amount of 1000 for a particular Invoice Item. Or, rather, put in an amount and quantity whose calculated total was 1000 or more. It turns out that by the time SharePoint does the calculation and hands off the value to the XSL for transformation, there is a comma in the value which the XSL sum function chokes on. I either received nothing on the web page or NaN, and I could not find a simple way to format the total field through the SharePoint interface.

I switched the total field to text format and that didn't help. I even switched the amount field to text and that didn't help. My original total field looked like this:

=Quantity*Amount

What I ended up doing after a mere 18 hours of research was keeping the amount field as a number, the total field as text, and using the following formula to calculate the total field:

=TEXT(VALUE(Quantity)*VALUE(Amount),"0.00")

The calls to VALUE are probably overkill, but in essence I told it to get the product and format it as a number with two decimal places. And, even though the total field is technically a text field, the sum function in the XSL operates on it properly.

This technique could also help out in a worst case scenario where you do an XSL calculation on a numeric field in SharePoint -- create a calculated field whose sole purpose is to transform the numeric field into a plain vanilla version.

Labels: nerdville, sharepoint