InfoPath Dev

Hi, I apologise if this has been asked before or if it is a stupid question.

I work for a manufacturing company and I'm building a form which needs to be filled out by the production crew each shift (two shifts per day) and from it our quality team want to be able to see information in a summary report of the average damaged product etc.. per product per day.

 What I have set up so far is a form with 3 views, view one gets filled in by Shift 1 and view 2 gets filled in by Shift 2 - These views are identical but with seperate fields so that there is no issues with fields being bound together.

The third view is the summary page for the quality team, on this view I have tables set up which pull information out of the other two views to show what products where produced each shift & I have formulas set up to calculate the averages of things such as damaged stock etc... for each individual product.

This all works fine, where I'm running into difficulty is I need to allow for the instance where the same product might be produced in Shift 1 and Shift 2. In this instance I need to be able to set up a formula to calculate the average of damaged stock etc... across both shifts.

I have set up some tables on the summary page to display this information and I have set up rules on the relevant fields in view 2 which set the values of the fields on the summary page (view 3) if there are two instances of the same product produced.

The fields in view 1 and view 2 where the data is entered are repeating tables to allow for several rows of data to be entered on each product (and each product is entered in a new table).

To calculate the averages of two occurances of the one product I set up the formula: (Sum(Field1) + Sum(Field2)) / (Count(field1) + Count(field2))

This formula works perfectly if there is only one row of data in each repeating table however if I add another row to either table the result is recalculated and it only seems to use the newest value entered.

So for example if a field in table A has a value of 20 and a field in Table B has a value of 10, on the summary page the average is calculating correctly to 15. But if I go back and add another row to table B and put in a value of 7 the average on the summary page changes to 13.5 which is incorrect as it should be 12.33 (20+10+7 = 37 / 3 =12.33).

What it seems to be doing is recalculating once I enter the 7 and ignoring the 10 that is already there and adding up 20+7 = 27 / 2 =13.5

 I tried to work around the issue by adding a footer column to both repeating tables with totals fields which sum each of the required columns and display their total value.

I also added a field to count the number of entries. Both of these fields work perfectly - as I add new lines the count field increases by 1 each time and the totals fields update to reflect any new data entered.

So I then re-wrote my formula using these fields instead but for some reason, even though these fields contain the correct information, and are the total of all values in each particular column, the average shown on the summary page still only calculates on whatever the last value entered into the table is!

I have been over and over my formula and I can't work out why it is doing this.

Has anyone ever come across this before? Any help would be much appreciated as I am completely stumped by this.

 Thanks in advance.

Hi-

Your formula seems to be correct: (Sum(Field1) + Sum(Field2)) / (Count(field1) + Count(field2))

I'm attaching a form that implements this formula and it seems to work for me. Can you check?

You might be facing an issue specific to your form, sadly...

Hi,

Thanks for your quick response, however I am still having trouble with this.

I noticed In your example, as soon as a figure is entered in the first field, the calculation field updates and shows the average of that number. I.e I typed 20 in Field 1 and the calculated field showed 10 since there are two fields by default for the count. How have you set this up? My formula does not display anything unless both fields hold information,

 I was also thinking, could the problem lie in the field I have set my formula up in? I have the formula set up on a field in my second form (view) and it sets the value of what in your example would be the "calculation" field which sits in my 3rd form (view).

So my formula is: (Sum(FieldA) + sum(.)) / (count(FieldA) + count(.))

With Field A being a field in View 1 and (.) obviously being the current field in View 2.

Would this make any difference to the outcome of the calculation?

Cheers

 The location (view) of the field should not matter, as long as your formula points to the correct xpath.

I've set up my sample to use a calculated field, but you're likely using rules. If your formula is not firing when expected, you might need to check the conditions on your rules. You should also set up the rule on all fields, such that:

if I type in to FieldA, the rule executes

if I type into FieldB, the rule executes again and the calculation is updated

if I go back to FieldA, the rule  executes again and the calculation is updated

etc.

So make sure the rule fires every time that a value is entered.

Hi,

Thanks again for your quick response.

I am using rules to set the field's values, I have a feeling that the problem I am experiencing may exist in the conditional formatting that I have set up but I can't for the life of me work out how to fix it to make it work!

Basically what I have is 3 repeating tables in View 1 and 3 repeating tables in View 2 and these use my rules to populate 3 static tables in View 3.

So basically I have the rules set up in View 2 so if a specific field in View 2 is equal to a specific field in View 1 the rule will trigger and set the value of a specific field in View 3.

To use an example if my forms tables and fields are as below:

View 1 :                                                                     View 2:                                      View 3:               

Table 1 - Field A                                                      Table 4- Field D                         Table 7- Field G

Table 2 -Field B                                                       Table 5 - Field E                        Table 8- Field H

Table 3- Field C                                                       Table 6 - Field F                         Table 9 - Field I

I would have a rule set up on Table 4 that:

 If Field D is equal to Field A, set the value of Field G

If Field D is equal to Field B, set the value of Field G

If Field D is equal to Field C, set the value of Field G

Similarly I would have a rule set up on Table 5 that:

If Field E is equal to Field A, set the value of Field H

If Field E is equal to Field B, set the value of Field H

If Field E is equal to Field C, set the value of Field H

And so on for Table 6.

Does that make sense? do you think there is a problem in these conditions that would be causing the issues?

There are 10 fields on each table that I have set up with formulas using these conditions - so that is a lot of formulas!!

Any further insight you have would be much appreciated.

Cheers

L

Hi,

I just wanted to let you know I have solved my problem, I still couldn't get my rules to work to set the values correctly so I just built enough tables on my summary page to cover the results of any combination of  fields and followed your example of using expression boxes and it works perfectly.

Thanks once again for all of your help!