Calculating the GROUP BY total in a SharePoint Column (InfoPath) - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Calculating the GROUP BY total in a SharePoint Column (InfoPath)

Last post 08-10-2018 10:57 AM by Hilary Stoupa. 14 replies.
Page 1 of 1 (15 items)
Sort Posts: Previous Next
  • 08-09-2018 08:59 AM

    Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    I am trying to do something here, which I do not know if it is possible. I have a custom list in SharePoint 2010 where the "Course" name is unique. I am using "Group By" in the view which shows me the totals for each Course (circled in red via screenshot attached here) Am I able to calculate these group by values (in red) in another SharePoint column. The reason being: I have another column "Limit" which I want to compare the group by to. So for instance...I want to compare this new column to my "Limit" column. And if the new column is greater than or equal to Limit, then my InfoPath form will not allow a submit. Ultimately, on my InfoPath form, I want to compare two values, the Limit (which I have already) vs each Course's Group By value. This will be sufficient because each Course is unique. I'm looking to do this: When Course = X, the group by value circled in red is populated in a hidden field on an infopath form. Then I will compare that number to my Limit field. Please help :) !!!!!
  • 08-09-2018 10:46 AM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    If the course name is unique, how is there more than 1 in a group?
    Hilary Stoupa

  • 08-09-2018 11:00 AM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    I should add - here's my guess of what would work here - add a data connection to the list with the grouped view that includes the course name field. Set the query to not run when the form opens (that's a checkbox toward the end of the data connection wizard). In the form, when the user selects a course, add a rule to set the course name in the query fields to the selected course (if this is a lookup, be sure you set that to the list ID field) and query the data connection. Then you can just use a count formula to check the count of returned rows & compare it to the limit. Attach your form to a reply under the options tab if you need a hand w/ that (after adding the data connection, 'cause I can't do that for you :-) )
    Hilary Stoupa

  • 08-09-2018 11:01 AM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    The Course field is set as unique. So there can never be the same value in Course. For instance, if there are two sections of a Math course, they would have to name them differently such as "Math - AM" and "Math - PM" ........ Multiple people can register for each course, but there is a specific 'Limit' of people who can sign up for each. I have the Limit set in another field, but the number of people in the "Group By" is the number that I want to compare the Limit to.
  • 08-09-2018 11:16 AM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    Hilary, I've added the data connection. You will see 'Test Connection....' You'll notice some other customization in there, but if you could assist with what you've described above I would greatly appreciate! The Limit field is being pulled in from another SP list and is in a hidden field. It is being set when a user chooses a course. Thanks again :) xsn attached
  • 08-09-2018 11:28 AM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    Try this one - save it locally, right click and select design to open in design mode, then preview and select a course. Since I can't preview it, and am making a bunch of guesses about functionality, I put the new data connection on the form so you can try setting the course manually and clicking the query button to check the data results. I added calculated fields showing the count and how I would check that against the attendee limit for the selected course, so you can use that for a condition someplace.
    Hilary Stoupa

  • 08-09-2018 11:57 AM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    This is awesome. You are correctly pulling the count number I am looking to compare in your "Count of List items for Course". Now I am just trying to wrap my head around & understand how all of this is being pulled so I truly understand it and know going forward. Then I can set my additional conditions ( i.e. Limit vs Count; if Count is greater than or equal to Limit, then disable submit) ----- something like that. :) Thank you so much for the help
  • 08-09-2018 12:09 PM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    So, in general - here's what is going on. The list you submit to has all the attendees for a course. We query that very same list, using the query field to get back only data for the course the user has just selected in the form. That way we can count the rows returned (and also - we aren't querying for more data than we need!) and figure out how many attendees have already submitted a form for that course. That gives us the number we can compare back to the course list attendee limit. :-)
    Hilary Stoupa

  • 08-09-2018 12:37 PM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    I think I understand. So how am I able to now compare the calculated value to the 'Attendee Limit' control. Calculated values are slightly different and do not display in dropdown of data/query fields right?
  • 08-09-2018 01:28 PM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    Here's a copy w/ a rule on the Submit button to disable it - it is basically the same XPath as the calculated value next to the text "At Limit" which I hope is currently displaying true / false correctly? In the first dropdown for the condition, you can select the repeating data row in the connection to the main list, then change that (in the dropdown at the bottom of the field picker) to "the number of occurrences" for your comparison.
    Hilary Stoupa

  • 08-09-2018 01:48 PM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    Hmm. I think there may be a problem in the Testing Purposes section; in the table which shows the lines for each and the Attendee Limit, the Attendee Limit is not being updated when I update that field in the corresponding list. If i update an Attendee Limit from say 10 to 5, my 'Attendee Limit' control that I have on my main data connection (the one that I wish to compare to the Count of those registered) is updated appropriately to 5, but yours in the repeating table still says the original value of 10. Then this is making the true/false not work correctly either, and as a result the new submit disable is not working either. Thinking its because maybe the values for the Attendee Limit are not recalculated if the number is modified in the other data connection??
  • 08-09-2018 03:35 PM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    Are you re-querying the data in the form? If you go update the list, and you have the form open, and the query has already run - it won't automatically get the latest data. It looks like your Training_CourseDetailsList data connection only runs on load, that's the list w/ the attendee limit, right?
    Hilary Stoupa

  • 08-10-2018 06:25 AM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    Correct, that is the list with the Attendee Limit. But it runs every time the form is opened. So I've set a rule,that when course is selected, set Attendee Limit control as attendee limit for that particular course. and that works when I make an update to the list. But not automatically. I am sure to make the update to the list, then re-open the form and try again...the attendee limit in your repeating table still has the original value of although I've changed it last night... just curious how to get that one to update appropriately.
  • 08-10-2018 06:54 AM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    I think I may have figured out another workaround. I can set a fields value as the xpath you created : count(xdXDocument:GetDOM("Test Connection - Compare Grouped By vs Limit")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW) And that seems to be pulling in the group by value correctly! Now I think I can compare this number to the Attendee Limit and disable submit or so other things. Do you see any downside to doing it that way?
  • 08-10-2018 10:57 AM In reply to

    Re: Calculating the GROUP BY total in a SharePoint Column (InfoPath)

    Well - I personally wouldn't set the attendee limit in the main data source, but that's just me. I would just compare back to the original course list, which you have a secondary data connection to. In this copy, I've added a calculated value on the form that shows the attendee limit for the selected course from the secondary data source that has the courses from the course list.
    Hilary Stoupa

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