InfoPath 2010

Re: Handling Negative values in the Sum Function

IbisDev — Wed, 09 May 2012 17:06:54 GMT

Should have done this first. Created a test table and manually entered values. Sum function worked as expected. I did notice that the product of a negative multiplicaiton was shown as a - and not a () which is an accounting treatment. I changed the format of the results fields in the SharePoint list from currency to number which produces a - sign instead of () and this time the Sum function worked correctly. Interesting however that you can format the field as currency in InfoPath and it will show the () in the form column, but apparently it ignores that formatting in the calculation. So, my form looks correct from a format standpoint and I get the correct value. Thanks for taking the time to respond and help me work through this. Hope the information can be used by others.

Bob

Re: Handling Negative values in the Sum Function

IbisDev — Wed, 09 May 2012 16:14:09 GMT

Thanks for the reply. The SharePoint List column I am totaling is a Text field formatted as Currency. Value in the field is actually created by a Workflow as text, because I ran into problems using a calculated field elsewhere. The actual value is the product of "Hours" X "Rate". Because SharePoint lists do not produce a Subtotal that you can access and use anywhere I have a workflow that adds and accumulates to give me a subtotal. However, if a user entered a wrong amount and wants to go in and reduce the number of hours, I require them to make a negative entry which allows the workflow to adjust the subtotal and keep things orderly. (You can't just edit the hours, because I use the existing Balance as the base to accumulate new entries to, so the original amount is already in the balance, they could just edit the balance, but I don't want to lose the audiit trail). The resulting entry in SharePoint looks like Hours: -1 Rate: 200 Subtotal: (200). This is the same way it appears in the InfoPath Form Repeating Table.

The filtered column would be 200+(200)+200 should equal 200, but the Sum functon comes up with 400. If were coming up with 600 I could imagine that it wasn't recognizing the parantheses or that it is a negative value, but it just seems to ignore the value completely.

Hope this wasn't too scattered or rambling. I agree it should be netting the values as expected.

Thanks,

Bob

Re: Handling Negative values in the Sum Function

Jimmy — Wed, 09 May 2012 15:41:24 GMT

The sum() function should certainly be taking negative values into account. Has your experience been otherwise? Perhaps you could to into specifics a bit more so that we might identify the issue?

Handling Negative values in the Sum Function

IbisDev — Wed, 09 May 2012 14:31:07 GMT

I am populating a Repeating Table from a SharePoint List. I want to use the Sum Function to total a column of numbers. The column can contain negative values. From what I can determine, the Sum Function totals the Positve values and ignores the Negative values. I am using a rule to filter the list and I apply the same rule to the Sum field. This works fine except for not coming up with the "net" total of the column. Is there a setting somewhere that will make the Sum Function in InfoPath behave the way it does in Excel?

Thanks,

Bob Blasingame