Re: Query SharePoint list column and return calculated sum

RaYvA — Wed, 12 Feb 2014 04:27:28 GMT

Hi, Yes you can do this from a list form.

If you have data connections to the list you can use count() and sum() functions to obtain the answer from the data connection once you have queried it.

For example, after querying the 'Commercial Owners' data connection this formula returns the count of the commercial owners:
count(xdXDocument:GetDOM("Commercial Owners")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Commercial_x0020_Owner)
Another example, after querying the 'Costs' data connection this formula returns the sum of the costs:
sum(xdXDocument:GetDOM("Costs")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Amount)

You can use these formulas in rules to update fields in your schema or you can use them in Calculated Values. Easier to use the 'Edit Formula' (Fx) button to help you. You coulds then also filter the return based on other selected fields in on your form or explicit filters:

Example, after querying the 'Costs' data connection this formula returns the sum of the costs ONLY for the 'Commercial Owner' who's values is 'RaYvA':
sum(xdXDocument:GetDOM("Costs")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Amount[../d:Commercial_x0020_Owner = "RaYvA"])

Hope this helps?

Cheers

Query SharePoint list column and return calculated sum

BrynC — Sat, 25 Jan 2014 20:51:25 GMT

Hi there,

I submitted this post under general, and never got a response - hopefully I'm not breaking any "forum" rules by re-posting in the Data Connections section - any help someone can point me to is greatly appreciated!

I'm working on an InfoPath / SharePoint List solution for my team that enables "portfolio management" of our projects. I did not originally set up the list form to enable repeating fields, because I wanted the additional functionality in my form, and I also wanted to limit one-to-many entries to keep things as simple as possible. Unfortunately, I think now I'm stuck - because my form isn't enabled for repeating fields, I can't seem to query the existing SharePoint list in the way I want to in order to return some data I'd like.

All data from form submissions and updates post to the same SP list, with views/perms set up in SP to hide "admin" fields from project managers. The form also queries this SharePoint list, where needed, to allow folks to select existing list items as appropriate (example - "related" projects are available in a multi-select list box that queries all the active projects already submitted). Where I'm stuck, though, is here:

I'm looking for a way to enable one of the admin forms to query the existing SP list and calculate all the project hours submitted (i.e. when an admin opens a newly-submitted project request form, the Total Project Hours field queries the existing SP list upon form load, adds up all the line items in the Project Hours SharePoint List Column, and returns a calculated total to the Total Project Hours form field so that admins can see how many project hours are currently being accounted for before approving and resourcing the newly submitted project). Short and sweet example (the actual form has about 150 fields across 8 different form views as the workflow I'm trying to manage is quite extensive, but hopefully the below gives a sense of what I'm trying to accomplish):

Project Managers submit the following in the request form:

Project Manager | Project Name | Estimated Hours

Anne Smith | Anne's Project | 40

Bob Jones | Bob's Project | 10

Ty Johnson | Ty's Project | 20

After posting to the list, an Admin's view would look something like this:

Projects Submitted | Total Hours allotted

3 | 70

I've done this before using repeating tables in a form, but try as I might I can't figure out a way to accomplish the same thing in a non-repeating list form. Is there any way to use a data connection to query a SharePoint list column, add up all the hours associated to each line item, and calculate and return a total back to the form (preferably a non-code solution, as I'm trying to keep this as out-of-the-box as possible, though I'm willing to customize with code if that's my only option)? Anyone have any ideas? I've searched on forums, how-to's, etc. until my fingers are numb - seems like something that would be an obvious option, but either it's so simple I'm completely missing it or it's just not possible because I didn't set things up the right way.

IF my only option is to use a form that allows repeating items/tables, is there any way to "convert" my list to do so without having to start over? I'm happy to do that instead, if at all possible - but don't want to have to re-do the roughly 80 man-hours of work I've put into this solution over the last 3 months. I'm also happy to provide additional context or details as needed - I appreciate the time! Thanks! - BrynC