Form Library, calculate total hours by type, like SQL SUM() with GROUP BY - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Form Library, calculate total hours by type, like SQL SUM() with GROUP BY

Last post 05-09-2019 08:06 AM by Hilary Stoupa. 3 replies.
Page 1 of 1 (4 items)
Sort Posts: Previous Next
  • 05-08-2019 11:03 AM

    • scottfrase
    • Not Ranked
      Male
    • Joined on 11-02-2016
    • Kittery, Maine
    • Posts 8

    Form Library, calculate total hours by type, like SQL SUM() with GROUP BY

     I'm using a Form Library with InfoPath, and I want to calculate the total Hours by Type, similar to SQL's SUM() GROUP BY.  This application will be used to track the learning hours for students over a 4-year period to total how many hours they've accumulated toward a Type of skill (A1, A2, ...).


    <my:WrkExprFields>
     <my:EmployeeName>Fife Barney</my:EmployeeName>
     <my:MonthlyExperience>
      <my:PeriodEndDate>2019-01-31</my:PeriodEndDate>
      <my:Tasks>
       <my:Type>A1</my:Type>
       <my:Hours>80</my:Hours>
      </my:Tasks>
      <my:Tasks>
       <my:Type>A2</my:Type>
       <my:Hours>4</my:Hours>
      </my:Tasks>
      <my:Tasks>
       <my:Type>A3</my:Type>
       <my:Hours>10</my:Hours>
      </my:Tasks>
      <my:Tasks>
       <my:Type>A4</my:Type>
       <my:Hours>12</my:Hours>
      </my:Tasks>
      <my:Tasks>
       <my:Type>Holiday</my:Type>
       <my:Hours>8</my:Hours>
      </my:Tasks>
     </my:MonthlyExperience>
     <my:MonthlyExperience>
      <my:PeriodEndDate>2019-02-28</my:PeriodEndDate>
      <my:Tasks>
       <my:Type>A2</my:Type>
       <my:Hours>80</my:Hours>
      </my:Tasks>
      <my:Tasks>
       <my:Type>A5</my:Type>
       <my:Hours>16</my:Hours>
      </my:Tasks>
      <my:Tasks>
       <my:Type>A4</my:Type>
       <my:Hours>4</my:Hours>
      </my:Tasks>
      <my:Tasks>
       <my:Type>Vacation</my:Type>
       <my:Hours>40</my:Hours>
      </my:Tasks>
     </my:MonthlyExperience>
    </my:WrkExprFields>

    I'd like to have an InfoPath view totaling the hours by Type.

    Type      Hours
    ====      =====
    A1        80
    A2        84
    A3        10
    A4        16
    A5        16
    Holiday   8
    Vacation  40

    I was able to successfully create a table with Text Boxes which had Default Values something like:sum(my:MonthlyExperience/my:Tasks[my:Type = "A1"]/my:Hours). 

    The problem I'm having is that the user specified that the Type field could have any combination of letter and number (many combinations), so I'm hoping for a solution that is more dynamic like a SQL with SUM() and GROUP BY.  I was hoping to use an XPath like: concat(distinct-values(/my:MonthlyExperience/my:Tasks/my:Type),' ',sum(my:MonthlyExperience/my:Tasks/my:Hours)), but InfoPath doesn't support the distinct-values function.

    Scott Fraser
  • 05-08-2019 02:12 PM In reply to

    Re: Form Library, calculate total hours by type, like SQL SUM() with GROUP BY

    So - you can get close to this with XPath and conditional formatting - but sorting is a whole 'nother story. It is possible to modify an XSLT to sort, but it is tricky and I'm not sure if it supported in the browser (not sure if you are using browser forms or not). Here's a sample with the data grouped - I added your data a secondary data connection, but the general idea should be the same. Save locally, right click and select Design, then you can preview. There is conditional formatting on the repeating table, and calculated value for the sums.
    Hilary Stoupa

  • 05-09-2019 05:56 AM In reply to

    • scottfrase
    • Not Ranked
      Male
    • Joined on 11-02-2016
    • Kittery, Maine
    • Posts 8

    Re: Form Library, calculate total hours by type, like SQL SUM() with GROUP BY

     Amazing!  It worked!  InfoPath Designer complained a bit that 'Binding a non-repeating control to a repeating field or group is not supported in Web browser forms', but it worked anyway.

     Thank you Hilary!!  Your advice on this site has helped me many times.  And as InfoPath reaches its end-of-life, Qdabra FormsViewer is definitely on my to-do list to look into as we have many applications developed with InfoPath. 

    Scott Fraser
  • 05-09-2019 08:06 AM In reply to

    Re: Form Library, calculate total hours by type, like SQL SUM() with GROUP BY

    So - that binding complaint is generally harmless - IP has a bug, because these calculated values should not actually be bound to the control, and this can be fixed with minor XSLT mods. I've not had that error create issues for me, except if it's in a print view - blog post here: https://www.infopathdev.com/blogs/hilary/archive/2012/01/19/ipfs-design-checker-errors-amp-print-preview.aspx
    Hilary Stoupa

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