2

It is easy to calculate a sum, an average or a maximum accross set of fields using built-in xpath functions.

But is it possible to calculate a product?

Assuming I have repeating elements like

<my:table>
    <my:row>
        <my:value>10</my:value>
    </my:row>
    <my:row>
        <my:value>20</my:value>
    </my:row>
    <my:row>
        <my:value>30</my:value>
    <my:row>
</my:table>

(the number of my:rows may vary).

i need to have a formula multiplying all my:values: 10*20*30 (and working with any number of rows).

Any ideas?

Please do not suggest code solutions, I need this for a restricted form.

Stas Slabko
  • 506
  • 5
  • 14

2 Answers2

1

Using conditional hack described here, I finally assembled the solution.

Inside my:row, create a running product field my:valueCumul, populated using a formula to refer to previous row, and leveraging the conditional hack to override NaN for the first row:

../my:value * 
concat(
(count(../preceding-sibling::my:row[1]) = 0) * 1,
substring(../preceding-sibling::my:row[1]/my:valueCumul, 1, (count(../preceding-sibling::my:row[1]) = 1) * string-length(../preceding-sibling::my:row[1]/my:valueCumul))
)

Outside the table you can easily refer to my:valueCumul in the last row to get total product:

../my:row[count(../my:row)]/my:valueCumul

The nice features of this solution are:

  • Works in a browser form in any sharepoint environment
  • Is not impacted by "16 calculations" threshold
  • The formula is robust to row removal and shifts
Stas Slabko
  • 506
  • 5
  • 14
0

You can't with the built in functions in InfoPath.

However, don't write off a code solution so fast. If you only access data elements in the form itself (nothing EXTERNAL) then you can leave the form as restricted security and the code will run with no problems - the user doesn't know the difference.

It is fairly trivial to attach to the right event, grab the nodes, and loop through them while multiplying. You don't even have to use managed code - use one of the script options. Comment back (or update your original post) if you run into any troubles and we can help resolve them.

ktharsis
  • 3,160
  • 1
  • 19
  • 30
  • don't write off a no-code solution as well: think about adding an sibling value element to a row, that would accumulate (running) product with `my:value * ../preceding-sibling::my:row[1]/my:value` , resulting with the total product in the last row... I just need to find out a trick to overcome IP's "16 calculations" threshold. – Stas Slabko Nov 17 '11 at 15:58
  • Give it a shot. You have a few more edge cases to take care of (there is no preceding for the first node, removing a node in the middle might screw up the calculation, etc) but should work if you find/handle them all. – ktharsis Nov 18 '11 at 15:12
  • many sharepoint deployments does not allow neither sandboxed solutions nor administrator-approved forms, in this case you cannot deploy a form with code. – Stas Slabko Jan 24 '12 at 17:01