Query SharePoint list column and return calculated sum - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Query SharePoint list column and return calculated sum

Last post 02-11-2014 08:27 PM by RaYvA. 1 replies.
Page 1 of 1 (2 items)
Sort Posts: Previous Next
  • 01-25-2014 12:51 PM

    • BrynC
    • Not Ranked
    • Joined on 01-20-2014
    • Posts 2

    Query SharePoint list column and return calculated sum

    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   

  • 02-11-2014 08:27 PM In reply to

    • RaYvA
    • Top 100 Contributor
    • Joined on 05-08-2013
    • Melbourne, Australia
    • Posts 86

    Re: Query SharePoint list column and return calculated sum

    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

Page 1 of 1 (2 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.