Difference between revisions of "Referencing Other Sections in XML Calculations"
From SmartWiki
Mark Bridger (talk | contribs) |
|||
| (13 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | The following is a step-by-step walkthrough of creating an XML worksheet that calculates the totals across sections. | + | <table class="wikitable"><tr><td style="padding: 10px;"><big>This page has been deprecated</big></td><td style="padding: 10px;">See [[Custom Field Type: Special - XML Data]]</td></tr></table> |
| + | |||
| + | {{JavaScript Syntax}}The following is a step-by-step walkthrough of creating an XML worksheet that calculates the totals across sections. | ||
[[Image:XMLExample-Cross-SectionReferences.png|link=|600px]] | [[Image:XMLExample-Cross-SectionReferences.png|link=|600px]] | ||
| Line 11: | Line 13: | ||
# Click into the ''Staff'' section and then into the column heading of the first row whose total should be referenced.<br />Take note of the ''Node Name''.<br />Also note that the ''Has Total'' option has been selected.<br />[[Image:XMLExample-StaffNodeName.png|link=|600px]] | # Click into the ''Staff'' section and then into the column heading of the first row whose total should be referenced.<br />Take note of the ''Node Name''.<br />Also note that the ''Has Total'' option has been selected.<br />[[Image:XMLExample-StaffNodeName.png|link=|600px]] | ||
# Now click into the ''Overview'' section and click into the node that should contain the total for Year 1 from the ''Staff'' section. | # Now click into the ''Overview'' section and click into the node that should contain the total for Year 1 from the ''Staff'' section. | ||
| − | # In the ''Content'' section of ''Cell Properties'', enter the reference to the Year 1 total for ''Staff'' using the following syntax: <br />=[''section ID''_'''total'''_''Node Name'']<br />In the above example, this will be ''=[1440_total_Year-1]<br />[[Image:XMLSampleReferenceInOverviewToStaffTotal.png|link=|600px]] | + | # In the ''Content'' section of ''Cell Properties'', enter the reference to the Year 1 total for ''Staff'' using the following syntax: <br />=[''section ID''_'''total'''_''Node Name'']<br />In the above example, this will be ''=[1440_total_Year-1] |
| + | #You may also use @section.''sectionname''.id@ to avoid hard-coding section IDs | ||
| + | <br />[[Image:XMLSampleReferenceInOverviewToStaffTotal.png|link=|600px]] | ||
| + | |||
# Repeat for each section total to be referenced. | # Repeat for each section total to be referenced. | ||
| Line 21: | Line 26: | ||
Multiple sections can be referenced to provide aggregate totals:<br /> | Multiple sections can be referenced to provide aggregate totals:<br /> | ||
[[Image:XMLExample-ReferencingMultipleSections.png|link=|600px]] | [[Image:XMLExample-ReferencingMultipleSections.png|link=|600px]] | ||
| + | |||
| + | <pre style="white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;">Note: JavaScript syntax can also be used, such as | ||
| + | |||
| + | =Math.round((([1,6]+[1,3])/[1,5])*100)/100 | ||
| + | |||
| + | In order to round an expression to two decimal places. | ||
| + | </pre> | ||
| + | |||
| + | ==Conditional Statements== | ||
| + | |||
| + | Conditional JavaScript statements can be used. For example, to return the value of cell ''[1,3]'' if the value ''other'' is chosen in cell ''[1,2]'' and otherwise return the value of cell ''[1,2]'', enter the following in the ''Content'' section: | ||
| + | |||
| + | <pre style="white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;">= (([1,2]=='other') ? [1,3] : [1,2])</pre> | ||
==See Also== | ==See Also== | ||
* {{XML}} | * {{XML}} | ||
* [[XML Section Builder]] | * [[XML Section Builder]] | ||
| − | |||
| − | |||
| − | |||
Latest revision as of 16:33, 19 December 2016
| This page has been deprecated | See Custom Field Type: Special - XML Data |
| This feature uses JavaScript syntax |  |
In the example above, the Overview section contains the totals of the values entered under the Staff and Equipment sections.
To create these total and subtotal references:
- Edit the XML custom field
- Click on the Section Builder button
You will see the list of Sections at the left.
- Find the Section ID of each section to be referenced (in this example, Staff and Equipment), by floating your cursor over the section name:
The Section ID will be contained in the URL shown at the bottom of the page.
The Section ID of the Staff section in this example is 1440. - Click into the Staff section and then into the column heading of the first row whose total should be referenced.
Take note of the Node Name.
Also note that the Has Total option has been selected.
- Now click into the Overview section and click into the node that should contain the total for Year 1 from the Staff section.
- In the Content section of Cell Properties, enter the reference to the Year 1 total for Staff using the following syntax:
=[section ID_total_Node Name]
In the above example, this will be =[1440_total_Year-1] - You may also use @section.sectionname.id@ to avoid hard-coding section IDs
- Repeat for each section total to be referenced.
The =[section ID_total_Node Name] syntax works because the Year 1 column in the Staff section has the Has Total option selected.
To return a reference to an XML section that does not use totals, use the following syntax:
=[section ID_Node Name of Row_Node Name of Column]
Multiple sections can be referenced to provide aggregate totals:
Note: JavaScript syntax can also be used, such as =Math.round((([1,6]+[1,3])/[1,5])*100)/100 In order to round an expression to two decimal places.
Conditional Statements
Conditional JavaScript statements can be used. For example, to return the value of cell [1,3] if the value other is chosen in cell [1,2] and otherwise return the value of cell [1,2], enter the following in the Content section:
= (([1,2]=='other') ? [1,3] : [1,2])
