InfoPath Dev

Hi,

I am trying to find out whether there is a way to do advanced calculations/transformations in Infopath using something like a WHERE or IF statement. I have a repeated section, I want to say:

- Where field X in repeated section = a, add these values and display in a field
- Where field X in repeated section = b, add these values and display in a seperate field

I have a system where information is entered on a monthly basis. However I want to display it on a quarterly basis. I have one repeating section where each instance is differentiated by the data in the fields only (the fields are all the same). However when I combine the data, I only want to combine instances with the same identifyer. As it stands, I am only able to calculate totals across all instances of the field. Not only the instance that has the same identifyer. (I hope this makes sense).

A simplified example of my form:
Report Month: January
Report Quater: Q3
---------------repeating section-------------
Metric Title: Number of Visitors [Identifyer]
Metric Value: 100

-----------------------------------
----------- second instance of repeating section--------------
Metric Title: % Of satisfied visitors [Identifyer]
Metric Value: 80
-------------------------------------
=====================================================

Form 2
Report Month: February
Report Quater: Q3
---------------repeating section-------------
Metric Title: Number of Visitors [Identifyer]
Metric Value: 230

-----------------------------------
----------- second instance of repeating section--------------
Metric Title: % Of satisfied visitors [Identifyer]
Metric Value: 86
-------------------------------------
=====================================================

Results

Report Quater: Q3
---------------repeating section-------------
Metric Title: Number of Visitors [Identifyer]
Metric Value: 100

-----------------------------------
----------- second instance of repeating section--------------
Metric Title: % Of satisfied visitors [Identifyer]
Metric Value: 80
-------------------------------------
---------------third instance of repeating section-------------
Metric Title: Number of Visitors [Identifyer]
Metric Value: 230

-----------------------------------
----------- fourth instance of repeating section--------------
Metric Title: % Of satisfied visitors [Identifyer]
Metric Value: 86
-------------------------------------
=====================================================

When I total this I get the total of all "Metric Values" not just the ones with a similar title. I want to achieve a consolodated report while preserving the ability to change the metric titles (types) in the future.

I hope all this makes sense. I would really appreciate any help.

-- Jay Whittaker --

Hi Jay,

Have you tried code behind? If you loop through the nodes of your repeating section, you can write code to customize running totals and stats based on your logic.

Regards,

Franck Dauché

I am not sure how to edit the code behind. Also I think that this would mean that I have to search for an idetifyer by name. However if I do this I will have to recode every time a new element is added / changed.

-- Jay Whittaker --

Hi Jay,

Looks like you would just need to loop through your repeating sections, so, you wouldn't have to change the code each time a row is added. You can display the metrics on a separate View and use the MSE to write your code. Go to Tools / Programming / MSE.

There you can loop through a repeating section using:
var nodeList = XDocument.DOM.selectNodes("//my:group1/my:group2");
if (nodeList.length>0)
{
for (var i=0;i<nodeList.length;i++)
{
var node = nodeList.selectSingleNode("my:field1");
XDocument.UI.Alert(node.text);
}
}

Regards,

Franck Dauché

So would this code search through the results and calculate totals where the data in "My:field1" matches (between instances of repeating sections). Therefore allowing me to have a dynamic totalling of values even if a new category is added in "My:field1"?

-- Jay Whittaker --

Hi Jay,

Yes, a Category for you is just a row in the repeating section. Now, if each Category has a unique Id (let's say an attribute). You can loop through repeating sections and add values to running totals based on Ids. You can get creative here, based on your business requirements.

Regards,

Franck Dauché