General

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

Hilary Stoupa — Thu, 09 May 2019 15:06:27 GMT

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

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

scottfrase — Thu, 09 May 2019 12:56:20 GMT

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.

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

Hilary Stoupa — Wed, 08 May 2019 21:12:20 GMT

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.

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

scottfrase — Wed, 08 May 2019 18:03:08 GMT

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.