Antonio,
In your fourth post you mentioned having a table at the bottom of your form
containing the same average fields that are in the footer of each of the
sections. I'm not quite sure what kind of structure you've set up for these,
but judging from the XPath to the fields in the error message, I'm guessing
that you are creating the rules on the average fields in the "summary" table
at the bottom of the form.
Try creating the rules on the ORIGINAL average fields and see if that works,
since this might be the difference between what you and I are doing.
---
S.Y.M. Wong-A-Ton
:
I tried to enter the formula on the averageOrg field and this is the error.
"The expression is not a node-set.
-->(.+../my:averageContentRating+../my:averageContentRating+../my:averageCommSkills+../my:averageQuest)<--/4"
--
Antonio Machin
Office Automation Analyst 3
Information Technology
Baptist Health South Florida
:
When I tried the formula you suggest it did not work
and I got an error that state "the formula contains one or more errors....".
Did you click on the [Show Details >>] button when you got that error? If
you did, what did it say? And did you use the "Insert Formula" dialog box
along with the [Insert Field or Group...] button when composing the formula?
Or did you just type it in? The only things you need to type in yourself are
the brackets, the plus (+), and the division (/) signs.
You must create 4 rules in total; one on each of the following fields:
- averageOrg
- averageContentRating
- averageCommSkills
- averageQuest
The rule on averageOrg must set the value of averageOfAverages to:
(averageOrg + averageContentRating + averageCommSkills + averageQuest) / 4
If you select the fields using the [Insert Field or Group...] button, your
resulting formula might look something like:
(. + averageContentRating + averageCommSkills + averageQuest) / 4
where ., averageContentRating, averageCommSkills, and averageQuest are
underlined.
If you then check the "Edit XPath (advanced)" checkbox at the bottom of the
"Insert Formula" dialog box, you may get something like
(. + ../../my:Ratings2/my:averageContentRating +
../../my:Ratings3/my:averageCommSkills + ../../my:Ratings4/my:averageQuest)
div 4
The rule on averageContentRating must also set the value of
averageOfAverages to:
(averageOrg + averageContentRating + averageCommSkills + averageQuest) / 4
Again, it may look something like:
(averageOrg + . + averageCommSkills + averageQuest) / 4
when you are done and the "Edit XPath (advanced)" checkbox is NOT checked.
Create the same rule for averageCommSkills and averageQuest.
Why 4 rules? Since you want to update the averageOfAverages field whenever
one of the 4 average fields changes, you need to create 4 rules, one on each
of the changing fields (=average fields).
Just a remark: I would not put the averageOfAverages field under Ratings4,
since it logically does not pertain to Ratings4. A better place to put it
would be under myFields or at least outside of all of the groups. But this
should not affect your calculations.
---
S.Y.M. Wong-A-Ton
:
Thanks again for helping with this.
I'm having some trouble understanding your instructions. I've added the
"averageOfAverages" field at the bottom of the form. Your instructions
indicate to add a rule to the "first average field" that sets the value of
the average of averages feild to the average of the 4 average
fields.......this is what has me confused.
Here is my data source structure to see if this helps.
- Ratings1 (Group)
- Org (folder)
- description1 - (field)
- orgRating
- helperField1
- averageOrg
- Ratings2
- Content
- description2
- contentRating
- helperField2
- averageContentRating
- Ratings3
- CommSkills
- description3
- commSkillsRating
- helperField3
- averageCommSkills
- Ratings4
- Quest
- description4
- questRating
- helperField4
- averageQuest
- averageOfAverages
On the bottom of my form I have a table that contains the same fields that
are in the footer of each of the sections; they display all the averages from
each of the 4 sections. I placed the new field, averageOfAverages on the
bottom of that table. When I tried the formula you suggest it did not work
and I got an error that state "the formula contains one or more errors....".
I tried the following formulas (using the information above)
(averageOrg+averageContentRating+averageCommSkills+averageQuest)/4
(orgRating+contentRating+commSkillsRating+questRating)/4
None worked....
I appreciate you looking into this.
--
Antonio Machin
Office Automation Analyst 3
Information Technology
Baptist Health South Florida
:
Ah... I see you have 4 repeating tables. We can keep it simple in this case,
since you do not need to use the AVG function to calculate the average of
averages, which the scenario I depicted assumed. Sorry if I missed that in
your first post...
Try the following:
- Put the average fields back into the footers of the repeating tables and
delete the repeating section (from your previous post).
- Add a field to your form that will display the average of averages. Make
its data type Decimal (double).
- Add a rule to the first average field that sets the value of the average
of averages field to the average of the 4 average fields.
Example for rule on average field in first repeating table:
Action: Set a field's value
Field: averageOfAverages
Value: ( AvgField1 + AvgField2 + AvgField3 + AvgField4 ) / 4
Make sure that you use the Formula dialog box when composing the formula
above to avoid getting errors of fields not being found, etc. etc.
- Repeat the previous step for the rest of average fields.
That should do it. Let me know if this works for you.
---
S.Y.M. Wong-A-Ton
:
I created a repeating section and placed the average fields from the 4
sections into the repeating section by dragging them onto it. Then, I
created a decimal feild to place on the footer of the section and I created
the helperField for this, too (did the rules). When display the preview, I
am able to popluate the section with the averages from 4 sections......now
I'm stuck getting the overall to work.
--
Antonio Machin
Office Automation Analyst 3
Information Technology
Baptist Health South Florida
:
Unfortunately, I do not have step by step instructions for this one. Perhaps
you should tell us what you have already tried and where you are currently
experiencing challenges.
---
S.Y.M. Wong-A-Ton
:
Thank you for taking the time to reply to my message. I tried, but I am
unable to figure out how to get the average of the averages. Would you
happen to have step by step instructions? Or direct me to some?
--
Antonio Machin
Office Automation Analyst 3
Information Technology
Baptist Health South Florida
:
If you want to come up with an average of averages, you'll need to place the
average field in a repeating group. You could create a repeating section and
place the repeating table in this. Make sure that the field that will display
the calculated average is in the repeating section. Then you can use the same
technique as with the helperField to calculate the average of averages. That
is, place another helperField outside of the repeating section. The one
within the repeating section should calculate the average of ratings and the
one outside the repeating section should calculate the average of averages.
---
S.Y.M. Wong-A-Ton
:
I created an evalution form from scratch. The form has four sections that is
using the repeating tables to calculate the average for each section. Is
there a way to combine all section and come up with an average of all the
averages? Can I apply a wieghted average, too?
I followed the steps from the following website and it worked out just
fine....I just need that extra step.
http://enterprise-solutions.swits.net/infopath/avg-function-infopath.htm
--
Antonio Machin
Office Automation Analyst 3
Information Technology
Baptist Health South Florida
