Difference between revisions of "Custom Field Type: Special - Advanced Data Table"

From SmartWiki
Jump to: navigation, search
m (sectionnodename.id to sectionname.id)
Line 1: Line 1:
==Overview==
+
=Overview=
XML (Extensible Markup Language) is a type of custom field used to create budgets and lists of information. This advanced custom field opens in a separate window from the main browser screen, it can have multiple sections, and it is robustly configurable (formulas, validation, reporting).
 
  
If you are looking to create a basic list or budget, you can also use the field called '''[[Custom Field Type: Special - In-Line Data Grid|Special - In-Line Data Grid]].'''
+
[[File:xmlSample.png|frame|none|middle|Example field configured as a budget sheet.|500px]]
  
[[File:xmlSample.png|The look of an XML|500px|border]]
+
This field type can be used to create worksheets or tables such as budget sheets, and lists of information. A wide range of complexity is supported from very simple tables to multi-section, multi-year budgets. This is accomplished through the ability to configure sub-sections within the same worksheet, and also define automatic cell calculations. This all adds up to a very robust, and flexible field.
  
Creating a XML is a multi step process. For convenience we have grouped how you do specific tasks under several groupings.
+
By default, the data for this field is stored in the structured XML (Extensible Markup Language) format. However, you also have the option to instead use this field as a presentation layer for lists of sub-activity records. For example, you may use this field on a UTA Level 1 record to display any associated UTA Level 2 records for a user to easily update the activities or create new activities.
  
'''Configuring - Essentials'''
+
If you are looking to create a basic list or budget, you can also use the field called '''[[Custom Field Type: Special - In-Line Data Grid|Special - In-Line Data Grid]].'''
*Creating a Special - XML Data custom field
 
*Create XML section with defined number of rows
 
*Create XML section with dynamic number of rows
 
*Display XML sections
 
  
'''Configuring - Advanced'''
+
{| class="wikitable"
*Creating formulas (calculations) in XML cells
+
| Summary
*Adding a formula to a XML cell (working with data in the same section)
+
|
*Adding a formula to a XML cell (working with data in the different sections)
+
|-
*Validate XML values when saving the worksheet
+
| Commonly Used in Markets (Philanthropy, Research, Insurance)
*Validating values when saving record (SmartCheck, Submit Logic, Browser Script)
+
| All
 
+
|-
'''Displaying the XML data'''
+
| Used By (User Type)
*Displaying XML data on a object
+
| All
*Access, Display and Format options for XML data
+
|-
*Styling XML data for web and print (PDF)
+
| Requires Administrator Setup
*Reporting on XML data
+
| Yes
 
+
|-
'''XML - Appendix'''
+
| Configuration Complexity (Low, Medium, High)
*Field options and settings
+
| High
*Example of an XML
+
|-
 +
| Time to Configure (Minutes, Hours, Days)
 +
| Hours
 +
|}
  
== Configuring - Essentials ==
+
= Configuration - Essentials =
  
=== Creating a Special - XML Data custom field ===
+
== Creating the field ==
  
Typically XML Data custom fields are created for UTA level 1, 2 or 3, (e.g. a grant application) or they are created for organizations. To create a XML custom field:
+
Typically XML Data custom fields are created for UTA level 1, 2 or 3, (e.g. a grant application) or they are created for organizations. To create the field:
# Navigate to the desired place, where you want to create the XML custom field (e.g. the UTA level 1).
+
# Navigate to the desired settings area where you want to create a worksheet (e.g. the UTA level 1).
 
# Click the '''New Field''' (+) button.
 
# Click the '''New Field''' (+) button.
 
# For Field Type select '''Special - XML Data'''.
 
# For Field Type select '''Special - XML Data'''.
# Enter a '''Field Name''' and '''Caption'''.
+
# Enter a '''Field Name''' and '''Save'''.
# Click '''Save'''.
 
  
 +
== Create a Simple Table ==
  
 +
Using the XML '''Section Builder''' you can create sections and define the attributes of each section and the cells within them. You can define the number of rows and columns, the display format, calculation formulas, and style your XML.
  
=== Create XML with defined number of rows ===
+
[[File:xml-sectionbuilder1.png|frame|none|middle|The section builder.|500px]]
Use the XML '''Section Builder''' to create sections, including defining the number of rows and columns displayed, formulas, and style your XML. Note: This feature is switched on by default, but you can manually enable the '''Section Builder''' if previously disabled.
 
  
[[File:xml-sectionbuilder1.png|The section builder|500px|border]]
+
# While editing the custom field click on the '''Section Builder''' button (the four square icon) in the action bar.
 +
# Create a '''New''' section and enter a '''Section Name''' (e.g. Expenses).
 +
# Enter '''Number of Rows''' and '''Number of Columns''' desired.
 +
# Once you '''Save''' you will be brought to the '''Cell Details''' tab.
 +
# Define a '''Display Name''' for each of the row and column headers.
 +
# Within each of the column headers you can define a '''Format''' for the column (e.g. plain text, currency, date). For numeric formats you may also specify a precision.
 +
# Note: to avoid confusion it is recommended to use unique node names of sections, rows, and columns within the field.
  
 +
== Create a Table with a Dynamic Number of Rows ==
  
# In the action bar click on the '''Section Builder''' button (it has four squares on it).
+
[[File:xmlDynamic.png|frame|none|middle|Example section set to dynamic rows.|500px]]
# Click the '''New''' button.
 
# Enter a '''Section Name''' (e.g. '''expense''').
 
# Click into the '''Section Node Name''' (the name auto populates. Note: '''Section Node Names''' are case sensitive).
 
# Enter '''Number of Rows''' desired.
 
# Enter '''Number of Columns''' desired.
 
# Click '''Save''' (this will bring you to the '''Cell Details''' tab).
 
# Click into the first cell of the '''Cell Grid''' at the top left.
 
# Enter a '''Display Name'''.
 
# Click into the top row of the column labeled '''1'''.
 
# Enter a '''Display Name''' and a '''Node Name'''.
 
# If desired choose the '''Format''' (e.g. text, number, comma, date, currency, percent). NOTE: If you select number, comma or percent you can also specify precision settings (e.g. Whole Number, Nearest Hundred). If you select currency you can also specify precision and currency settings (e.g. US Dollar, Euros).
 
# Repeat for the remaining column headers.
 
# Click into the first cell in the row labeled '''1'''.
 
# Enter a '''Display Name''' and '''Node Name'''.
 
# Repeat for the remaining row labels.
 
# Click the '''Build''' button.
 
# When you are done, navigate from the builder back to the field by clicking the '''Field''' button.
 
Note: Do not use the same node names across different sections, rows or columns in the xml.
 
  
=== Create XML with dynamic number of rows ===
+
In addition to defining a set number of columns and rows, you can also configure a section to handle a dynamic number of rows. The user entering the data can add rows as necessary at time of entry. This is done by entering '''0''' for the '''Number of Rows''', which in turn exposes the following configuration options:
The XML section builder can also be configured to allow the user to add additional rows of data using a plus button.  
+
* '''Maximum Number of Rows''': Set a maximum limit to the number of rows that can be entered, or leave as 0 for no limit.
 +
* '''Maximum Message''': The message to be displayed when the maximum limit is set and reached by a user. A default message is displayed if left blank.
  
[[File:xmlDynamic.png|The dynamic XML|500px|border]]
+
Setting the Number of Rows to 0 also exposes the following configuration options for each column header within the Cell Details tab:  
 +
* '''Has Total''': Display the total sum of a column.
 +
* '''Total Row Label''': For columns without a total display this option allows you to enter a label for the total sum of another column. For example, if column 3 was displaying a total, then you could configure the '''Total Row Label''' of column 2 to display 'Total'.
  
It is configured by entering '''0''' for the Number of Rows, which in turn exposes the following configuration options in the Section Details tab:
+
== Configure the Display of Tables ==
* '''Maximum Number of Rows''': if left as 0 the user can add as many rows as needed. If populated with a numeric value the user can only add up to and including that number of rows.
 
* '''Maximum Message''': message to be displayed if user tries to add more than the specified maximum number of rows. A default message is displayed if left blank.
 
  
Setting the Number of Rows to 0 also exposes the following configuration options in the Cell Details tab:
+
The presentation of the XML Data custom field is configured in the general settings page of the field. This is defined in the XSL Template (Extensible Style Sheet Language) option. The XSL Template includes a sample template option. Once you have inserted sample template you will need to specify which sections should appear and where they should appear.
* '''Has Total''': will appear when clicking into the section's column headers. Check '''Has Total''' to automatically sum and display all the items in that column.  
 
* '''Total Row Label''': will appear when '''Has Total''' is switched off. Text entered here will appear as a label on the row used to display the totals.  
 
  
Even though you set the number of rows to 0 you still need to configure one row of data which will be used to format the dynamically created rows.
+
[[File:xml-xsl.png|frame|none|middle|The XSL template and configuration.|500px]]
  
=== Display XML sections ===
+
# While editing the custom field locate the '''XSL Template''' option and insert the '''Sample Template'''.
The XML Data custom field has an XSL Template option within the overall XML Data custom field settings. The XSL Template is used to define how the XML will be displayed. XSL stands for Extensible Style Sheet Language.  
+
# Locate the below marker.
 +
<pre><!---- Insert section variable here ----></pre>
 +
# Below the marker, insert variable references to the each section with the following syntax (e.g. @section.Expenses@) in the order in which you wish the sections to appear. Note: the section node name is case sensitive.
 +
@section.''SectionNodeName''@
  
The XSL Template includes a sample template option. Once you have added the sample template you will need to specify which sections should appear and where they should appear.
+
= Configuration - Advanced =
  
[[File:xml-xsl.png|The XSL template|500px|border]]
+
== Creating formulas (calculations) in XML cells ==
 
 
# In the Section builder click the field button to return to the overall XML Data custom field settings.
 
# In the '''XSL Template''' click the '''Sample Template''' link.
 
# Replace <pre><!---- Insert section variable here ----></pre> with <pre>'''@section.''SectionNodeName''@'''</pre>
 
# Click '''Save'''.
 
 
 
 
 
The syntax '''@section.''SectionNodeName''@''' will display the section we created using the section builder. Note: you must replace '''SectionNodeName''' with the name you gave your section (e.g. '''@section.''expense''@'''), the section node name is case sensitive, and you must remove the html comments.
 
 
 
== Configuring - Advanced ==
 
 
 
=== Creating formulas (calculations) in XML cells ===
 
 
[[File:xml-formula.png|Adding a formula|500px|border]]
 
[[File:xml-formula.png|Adding a formula|500px|border]]
  
Line 108: Line 85:
  
  
=== Adding a formula to a XML cell (working with data in the same section) ===
+
== Adding a formula to a XML cell (working with data in the same section) ==
 
# Navigate to your XML custom field.
 
# Navigate to your XML custom field.
 
# Click the '''Section Builder''' button in the action bar.
 
# Click the '''Section Builder''' button in the action bar.
Line 118: Line 95:
  
 
The Syntax for basic calculations within a section:
 
The Syntax for basic calculations within a section:
{| border="1"
+
{| class="wikitable"
 +
|'''Operation'''
 +
|'''Symbol'''
 +
|'''Example'''
 
|-
 
|-
 
| Add
 
| Add
Line 137: Line 117:
 
|}
 
|}
  
=== Adding a formula to a XML cell (working with data in the different sections) ===
+
== Adding a formula to a XML cell (working with data in the different sections) ==
  
  
Line 149: Line 129:
  
  
=== Validate XML values when saving the worksheet ===
+
== Validate XML values when saving the worksheet ==
  
  
Line 185: Line 165:
  
  
=== Validating values when saving record (SmartCheck, Submit Logic, Browser Script) ===
+
== Validating values when saving record (SmartCheck, Submit Logic, Browser Script) ==
  
 
In addition to validating values when you save the XML worksheet you can also validate the XML worksheet when you save the record (e.g. save a level 1 grant application).
 
In addition to validating values when you save the XML worksheet you can also validate the XML worksheet when you save the record (e.g. save a level 1 grant application).
Line 192: Line 172:
  
  
==== SmartCheck Validation ====
+
=== SmartCheck Validation ===
 
Use SmartCheck for XML validation when possible. SmartCheck validation is a secure server side method. SmartCheck also displays all error messages in a context as well as in one central place.
 
Use SmartCheck for XML validation when possible. SmartCheck validation is a secure server side method. SmartCheck also displays all error messages in a context as well as in one central place.
  
Line 199: Line 179:
 
<pre>if("<ssEscape>@level1.xml.fieldname.sectionnodename.rownodename.columnnodename.nodevalue@</ssEscape>"=="")
 
<pre>if("<ssEscape>@level1.xml.fieldname.sectionnodename.rownodename.columnnodename.nodevalue@</ssEscape>"=="")
 
{result.isPassed=false;
 
{result.isPassed=false;
result.addMsg('xml_@fieldname.id@','YourAlertMessage'); }</pre>
+
result.addMsg('xml_@fieldname.id@','YourAlertMessage');   }</pre>
  
  
==== Submit Logic Validation ====
+
=== Submit Logic Validation ===
  
 
Use Submit Logic in conjunction with Submit Buttons to Validate a XML worksheet.
 
Use Submit Logic in conjunction with Submit Buttons to Validate a XML worksheet.
Line 210: Line 190:
  
  
==== Special - Browser Script validation ====
+
=== Special - Browser Script validation ===
  
 
You can enter JavaScript and jQuery into XML JavaScript Function input to create specific validations. Use the following syntax when referencing a value in your variables.
 
You can enter JavaScript and jQuery into XML JavaScript Function input to create specific validations. Use the following syntax when referencing a value in your variables.
Line 221: Line 201:
 
var saveFunc=saveXML;
 
var saveFunc=saveXML;
 
saveXML=function() {
 
saveXML=function() {
checkdupeval();
+
            checkdupeval();
}
+
            }
 
function checkdupeval() {
 
function checkdupeval() {
 
var result = 0;
 
var result = 0;
Line 235: Line 215:
 
if( a == b)
 
if( a == b)
 
{
 
{
alert ("Please list each population no more than once.");
+
    alert ("Please list each population no more than once.");
result = 1;
+
    result = 1;
 
}
 
}
 
}
 
}
 
if(result==0)
 
if(result==0)
saveFunc();  
+
    saveFunc();  
 
}
 
}
 
</pre>
 
</pre>
  
== Displaying XML Data==
+
= Displaying XML Data=
  
  
=== Displaying XML Data on an object ===
+
== Displaying XML Data on an object ==
 
[[File:xml-readonly.png|The XML displayed in a read only|500px|border]]
 
[[File:xml-readonly.png|The XML displayed in a read only|500px|border]]
  
Line 265: Line 245:
  
  
=== Access, Display and Format options for  XML Data  ===
+
== Access, Display and Format options for  XML Data  ==
 
The syntax options for accessing, displaying and formatting the data from the XML fields are detailed below.  
 
The syntax options for accessing, displaying and formatting the data from the XML fields are detailed below.  
  
Line 348: Line 328:
 
<pre>@xml.fieldname.sectionodename.rownodename[#(?criteria="~colunnnodename.nodevalue~" == "matching criteria")~columnnodename.nodevalue~ #]@</pre>
 
<pre>@xml.fieldname.sectionodename.rownodename[#(?criteria="~colunnnodename.nodevalue~" == "matching criteria")~columnnodename.nodevalue~ #]@</pre>
  
=== Styling XML data for web and print (PDF) ===
+
== Styling XML data for web and print (PDF) ==
 
[[File:xml-readonly-styled.png|The XML styled at 700 pixels wide in a read only|500px|border]]
 
[[File:xml-readonly-styled.png|The XML styled at 700 pixels wide in a read only|500px|border]]
  
Line 356: Line 336:
  
  
==== Styling  XML Data for Printing ====
+
=== Styling  XML Data for Printing ===
 
You can print your XML if you are displaying it in a '''Read Only - System Variable'''. Size your your XML to fit within 700 pixels wide (portrait) or 900 pixels wide (landscape) to avoid content cutting off, when a PDF is generated. If you have more than one section and more than a few columns, some of your content may cut off when printed if it is not sized. You will also notice columns between sections do not align. To fix this, we will use CSS in a SmartFolder and amend our '''Read Only - System Variable''', which is displaying the XML information.
 
You can print your XML if you are displaying it in a '''Read Only - System Variable'''. Size your your XML to fit within 700 pixels wide (portrait) or 900 pixels wide (landscape) to avoid content cutting off, when a PDF is generated. If you have more than one section and more than a few columns, some of your content may cut off when printed if it is not sized. You will also notice columns between sections do not align. To fix this, we will use CSS in a SmartFolder and amend our '''Read Only - System Variable''', which is displaying the XML information.
  
Line 393: Line 373:
  
  
==== Using different styles for Print and Web with XML Data ====
+
=== Using different styles for Print and Web with XML Data ===
  
 
You can have one look for your XML when it is onscreen and another when it is in PDF format for printing. To do this, create two '''Read Only - System Variables''' custom fields, create two CSS style sheets in '''SmartFolders''' and reference the desired style sheet in the desired '''Read Only - System Variables'''. Then on the '''Read Only - System Variables''' for viewing on screen for '''SmartFields''' View choose '''Exclude'''. This custom field will no longer be included in PDFs generated using the SmartFields variable. For the other custom field you want to print, choose '''Only display for SmartFields View'''.  Now one '''Read Only - System Variables''' will appear on screen and another when the PDF is created, and you can have a separate style sheet to control the look and feel of both.
 
You can have one look for your XML when it is onscreen and another when it is in PDF format for printing. To do this, create two '''Read Only - System Variables''' custom fields, create two CSS style sheets in '''SmartFolders''' and reference the desired style sheet in the desired '''Read Only - System Variables'''. Then on the '''Read Only - System Variables''' for viewing on screen for '''SmartFields''' View choose '''Exclude'''. This custom field will no longer be included in PDFs generated using the SmartFields variable. For the other custom field you want to print, choose '''Only display for SmartFields View'''.  Now one '''Read Only - System Variables''' will appear on screen and another when the PDF is created, and you can have a separate style sheet to control the look and feel of both.
  
=== Reporting on XML data ===
+
== Reporting on XML data ==
 
Extract XML value in a report using the following syntax in the report builder. This will return one value for static XML sections specific to the row and multiple values for Dynamic XML sections across all rows.
 
Extract XML value in a report using the following syntax in the report builder. This will return one value for static XML sections specific to the row and multiple values for Dynamic XML sections across all rows.
 
<pre>ExtractValue([this], '/worksheet/sectionnodename/rownodename/columnnodename')</pre>
 
<pre>ExtractValue([this], '/worksheet/sectionnodename/rownodename/columnnodename')</pre>
  
=XML Data - Appendix ==
+
= XML Data - Appendix =
  
=== Field options and settings ===
+
== Field options and settings ==
  
==== General Settings ====
+
=== General Settings ===
  
 
{{Custom Field ID}}
 
{{Custom Field ID}}
Line 432: Line 412:
  
  
==== Display ====
+
=== Display ===
  
 
{{Display Order}}
 
{{Display Order}}
Line 451: Line 431:
  
  
==== Options ====
+
=== Options ===
  
  
Line 465: Line 445:
  
  
==== Value Storage ====
+
=== Value Storage ===
  
 
{{Value Storage}}
 
{{Value Storage}}
  
=== Example of XML field ===
+
== Example of XML field ==
  
 
Here is an example of an XML field with one section (Expenses) 3 static rows (Salaries, Supplies and Total) and 3 columns (Amount, Notes and Date).
 
Here is an example of an XML field with one section (Expenses) 3 static rows (Salaries, Supplies and Total) and 3 columns (Amount, Notes and Date).
Line 475: Line 455:
 
<pre><?xml version="1.0" encoding="UTF-8"?>
 
<pre><?xml version="1.0" encoding="UTF-8"?>
 
<worksheet>
 
<worksheet>
<Expenses>
+
    <Expenses>
<Salaries>
+
        <Salaries>
<Amount>50000</Amount>
+
            <Amount>50000</Amount>
<Notes>People I work with</Notes>
+
            <Notes>People I work with</Notes>
<Date>2016-11-30</Date>
+
            <Date>2016-11-30</Date>
</Salaries>
+
        </Salaries>
<Supplies>
+
        <Supplies>
<Amount>30000</Amount>
+
            <Amount>30000</Amount>
<Notes>Things I need</Notes>
+
            <Notes>Things I need</Notes>
<Date>2016-11-30</Date>
+
            <Date>2016-11-30</Date>
</Supplies>
+
        </Supplies>
<Total>
+
        <Total>
<Amount>8000</Amount>
+
            <Amount>8000</Amount>
<Notes></Notes>
+
            <Notes></Notes>
<Date>2016-11-30</Date>
+
            <Date>2016-11-30</Date>
</Total>
+
        </Total>
</Expenses>
+
    </Expenses>
 
</worksheet></pre>
 
</worksheet></pre>
  
 
{{CustomFieldPageFooter}}
 
{{CustomFieldPageFooter}}
 
[[Category:XML]]
 
[[Category:XML]]

Revision as of 11:29, 16 June 2017

Overview

Example field configured as a budget sheet.

This field type can be used to create worksheets or tables such as budget sheets, and lists of information. A wide range of complexity is supported from very simple tables to multi-section, multi-year budgets. This is accomplished through the ability to configure sub-sections within the same worksheet, and also define automatic cell calculations. This all adds up to a very robust, and flexible field.

By default, the data for this field is stored in the structured XML (Extensible Markup Language) format. However, you also have the option to instead use this field as a presentation layer for lists of sub-activity records. For example, you may use this field on a UTA Level 1 record to display any associated UTA Level 2 records for a user to easily update the activities or create new activities.

If you are looking to create a basic list or budget, you can also use the field called Special - In-Line Data Grid.

Summary
Commonly Used in Markets (Philanthropy, Research, Insurance) All
Used By (User Type) All
Requires Administrator Setup Yes
Configuration Complexity (Low, Medium, High) High
Time to Configure (Minutes, Hours, Days) Hours

Configuration - Essentials

Creating the field

Typically XML Data custom fields are created for UTA level 1, 2 or 3, (e.g. a grant application) or they are created for organizations. To create the field:

  1. Navigate to the desired settings area where you want to create a worksheet (e.g. the UTA level 1).
  2. Click the New Field (+) button.
  3. For Field Type select Special - XML Data.
  4. Enter a Field Name and Save.

Create a Simple Table

Using the XML Section Builder you can create sections and define the attributes of each section and the cells within them. You can define the number of rows and columns, the display format, calculation formulas, and style your XML.

The section builder.
  1. While editing the custom field click on the Section Builder button (the four square icon) in the action bar.
  2. Create a New section and enter a Section Name (e.g. Expenses).
  3. Enter Number of Rows and Number of Columns desired.
  4. Once you Save you will be brought to the Cell Details tab.
  5. Define a Display Name for each of the row and column headers.
  6. Within each of the column headers you can define a Format for the column (e.g. plain text, currency, date). For numeric formats you may also specify a precision.
  7. Note: to avoid confusion it is recommended to use unique node names of sections, rows, and columns within the field.

Create a Table with a Dynamic Number of Rows

Example section set to dynamic rows.

In addition to defining a set number of columns and rows, you can also configure a section to handle a dynamic number of rows. The user entering the data can add rows as necessary at time of entry. This is done by entering 0 for the Number of Rows, which in turn exposes the following configuration options:

  • Maximum Number of Rows: Set a maximum limit to the number of rows that can be entered, or leave as 0 for no limit.
  • Maximum Message: The message to be displayed when the maximum limit is set and reached by a user. A default message is displayed if left blank.

Setting the Number of Rows to 0 also exposes the following configuration options for each column header within the Cell Details tab:

  • Has Total: Display the total sum of a column.
  • Total Row Label: For columns without a total display this option allows you to enter a label for the total sum of another column. For example, if column 3 was displaying a total, then you could configure the Total Row Label of column 2 to display 'Total'.

Configure the Display of Tables

The presentation of the XML Data custom field is configured in the general settings page of the field. This is defined in the XSL Template (Extensible Style Sheet Language) option. The XSL Template includes a sample template option. Once you have inserted sample template you will need to specify which sections should appear and where they should appear.

The XSL template and configuration.
  1. While editing the custom field locate the XSL Template option and insert the Sample Template.
  2. Locate the below marker.
<!---- Insert section variable here ---->
  1. Below the marker, insert variable references to the each section with the following syntax (e.g. @section.Expenses@) in the order in which you wish the sections to appear. Note: the section node name is case sensitive.
@section.SectionNodeName@

Configuration - Advanced

Creating formulas (calculations) in XML cells

Adding a formula

In the section builder you declare the formatting such as currency or numeric for specific columns as well as settings like precision. If you are collecting numeric or currency data you will likely want to do calculations on this information found within your XML (e.g. to sum the contents of column one, over two rows, enter =[1,1]+[2,1]).


Adding a formula to a XML cell (working with data in the same section)

  1. Navigate to your XML custom field.
  2. Click the Section Builder button in the action bar.
  3. Click the desired Section.
  4. Click the desired Cell.
  5. Under Cell Properties enter your desired formula into the Content input.
  6. Click Build.


The Syntax for basic calculations within a section:

Operation Symbol Example
Add + =[row#,column#]+[row#,column#]
Subtract - =[row#,column#]-[row#,column#]
Divide / =[row#,column#]/[row#,column#]
Multiply * =[row#,column#]*[row#,column#]

Adding a formula to a XML cell (working with data in the different sections)

Refer to cells in different sections using this syntax


[@section.sectionname.id@_rownodename_columnnodename]


e.g. =[@section.expense.id@_Total_Amount]-[@section.income.id@_Existing-funds_Amount]


Validate XML values when saving the worksheet

Clicking on a cell inside the section builder will present you with the cell properties. There are two cell properties used for the validation of cells when the xml worksheet is saved.

  • Validation Script: Validates the input field.
  • Validation Message: Content that is displayed, when the validation script is not true (e.g. Value must be greater than 100).


Examples

  • Total Amount greater than 0, for currency formatted column totals
ssParseNum([this])>0

Message: Total must be greater than $0.00


  • Date is at least 30 days in the future
datediff(ConvertDateStr([this],'@dateformat@'),'@date(currentdate)@','d') > 30

Message: Please select a date that is at least thirty 30 days from today.


  • Cell is not empty
[this].length>0

Message: Please provide a value.


  • Check the user selected something other than the default Combo Box value
[this]!="Default Combo Box Value"

Message: Please select a value. Note, Default Combo Box Value should be substituted with the default value you have configured, e.g. --- Select One ---.


  • Integer greater than 0 (Allow only an integer or if your field on the XML is formatted to display/use a comma.)
parseInt([this])>0

Message: Please provide a numeric value greater than 0.


Validating values when saving record (SmartCheck, Submit Logic, Browser Script)

In addition to validating values when you save the XML worksheet you can also validate the XML worksheet when you save the record (e.g. save a level 1 grant application).

When creating validation for an XML custom field use the Appear Mandatory option. Appear mandatory does not enforce the validation but will make the field look mandatory (add the asterisk and color) just like the mandatory option. Once you have checked Appear Mandatory, validate the XML worksheet using one of the following validation methods, when the record is saved.


SmartCheck Validation

Use SmartCheck for XML validation when possible. SmartCheck validation is a secure server side method. SmartCheck also displays all error messages in a context as well as in one central place.

Validate an XML table node is not empty and display a message.

if("<ssEscape>@level1.xml.fieldname.sectionnodename.rownodename.columnnodename.nodevalue@</ssEscape>"=="")
{result.isPassed=false;
result.addMsg('xml_@fieldname.id@','YourAlertMessage');    }


Submit Logic Validation

Use Submit Logic in conjunction with Submit Buttons to Validate a XML worksheet.

Check an XML node in not blank when saving the XML worksheet.

"<ssEscape>@level1.xml.fieldname.sectionnodename.rownodename.columnnodename.nodevalue@</ssEscape>"!=""


Special - Browser Script validation

You can enter JavaScript and jQuery into XML JavaScript Function input to create specific validations. Use the following syntax when referencing a value in your variables.

@xml.customfieldname.sectionnodename.rownodename.columnnodename@

The following function allows you to check for an already selected value from an existing row when using dynamic rows:

// REPLACE '7172_dsItem_Population' AND '7172_dsItem_Population_' WITH SPECIFIC XML FIELD ID

var saveFunc=saveXML;
saveXML=function() {
            checkdupeval();
            }
function checkdupeval() {
var result = 0;
var numOfRows = document.getElementsByName('7172_dsItem_Population'); //section node
var ctr=1;
for (ctr=1;ctr<numOfRows.length;ctr++)
{
var field1='7172_dsItem_Population_'+ctr;
var lastfield='7172_dsItem_Population_'+numOfRows.length;
var a = document.getElementById(field1).value;
var b = document.getElementById(lastfield).value;
if( a == b)
{
    alert ("Please list each population no more than once.");
    result = 1;
}
}
if(result==0)
    saveFunc(); 
}

Displaying XML Data

Displaying XML Data on an object

The XML displayed in a read only


The XML custom field opens in a modal window and the user must click a button to launch the modal window and see the information entered. You can however, display the information entered into a Special - XML Data custom field on your object, such as a grant application, without requiring the user to click a button and launch the modal window. To do this we use the Read Only – System Variables custom field. The same syntax can be used elsewhere in the system (e.g. include Display Only - Web Page View, Special MS Word Merge, visibility conditions, etc.) when referencing the xml values.


  1. Go to the desired location where you want to create the custom field.
  2. Click the New Field (+) button in the action bar.
  3. For Field Type select Read Only – System Variables.
  4. Enter a Field Name.
  5. For Variables enter @xml.CustomFieldName.SectionNodeName.html@ (replace the CustomFieldName and SectionNodeName with the actual names from your XML).
  6. Scroll Down to the Display section.
  7. For Caption select Hide Caption (we recommended that you hide the caption for the display so that you can utilize the full screen width).
  8. Click Save.
  9. Navigate back to the XML custom field and click Save (Display Fields Refreshed Upon Edit will be updated with the custom field ID of the Read Only we just created. From now on when a user edits the XML the read only will be automatically updated. If you miss this step it will only refresh when the object is saved).


Access, Display and Format options for XML Data

The syntax options for accessing, displaying and formatting the data from the XML fields are detailed below.


  • Extract the value from a specific individual cell using the field name.
@xml.fieldname.sectionnodename.rownodename.columnnodename@
  • Extract the value from a specific individual cell using the field id.
@xml.#fieldid#.sectionnodename.rownodename.columnnodename@
  • Extract numeric value and return in comma format (i.e. 1000 will display as 1,000).
@xml.fieldname.sectionnodename.rownodename[# ~comma(columnnodename.nodevalue)~ #]@
  • Extract a numeric value and return in currency format.
@xml.fieldname.sectionnodename.rownodename[# ~currency(columnodename.nodevalue)~ #]@
  • Extract a numeric value and return in a specific currency format.
@xml.fieldname.sectionnodename.rownodename[# ~currency_eur(columnodename.nodevalue)~ #]@

Further currency formatting options below

usd $300.00
eur €300.00
jpy ¥ 300
zar R 300.00
inr ₹300.00
frca 300,00 $


  • Extract a numeric value and return in currency format with two decimal places. If pulled from a dynamic XML section then you will see multiple values.
@xml.fieldname.sectionnodename.rownodename[# ~currency_eur(columnodename.nodevalue,2)~ #]@
  • Extract a value and return the number of characters in the field.
@xml.fieldname.sectionnodename.rownodename.columnodename.nodevaluelength@
  • Extract a numeric value and return in currency format with two decimal places for value pulled from a static XML section.
<!--@sscalculation(format("@xml.fieldname.sectionnodename.rownodename.columnodename.nodevalue@",2))-->
  • Extract a percentage value and format with two decimal places. If pulled from a dynamic XML section then you will see multiple values.
@xml.fieldname.sectionnodename.rownodename[# ~percentdisplay(columnodename.nodevalue,2)~ #]@
  • Extract date values and return with specific formatting using the following syntaxReturn the date in the user's date format
@xml.fieldname.sectionnodename.rownodename.formatdate(columnnodename.nodevalue)@
  • Return the year.
@xml.fieldname.sectionnodename.year(columnnodename.nodevalue)@
  • Return the month.
@xml.fieldname.sectionnodename.month(columnnodename.nodevalue)@
  • Return the month name.
@xml.fieldname.sectionnodename.monthname(columnnodename.nodevalue)@
  • Return the day.
@xml.fieldname.sectionnodename.day(columnnodename.nodevalue)@
  • Return the day of the week.
@xml.fieldname.sectionnodename.dayweek(columnnodename.nodevalue)@
  • Count the number of rows the user created in a dynamic XML section.
@xml.fieldname.sectionnodename.rownodename.nodecount@
  • Return the XML feed (code) from the field.
@fieldname.xmlvalue@
  • Return multiple rows for XML with dynamic sections.
@xml.fieldname.sectionnodename.rownodename[# ~columnnodename.nodevalue~ #]@
  • Return Filtered row values for XML with dynamic sections.
@xml.fieldname.sectionodename.rownodename[#(?criteria="~colunnnodename.nodevalue~" == "matching criteria")~columnnodename.nodevalue~ #]@

Styling XML data for web and print (PDF)

The XML styled at 700 pixels wide in a read only

If you enable the Section Builder and Apply Default Styles is enabled in each section of your XML custom field, the field will be automatically styled. If you have manually added CSS classes to your cells, you will need to remove those classes and check Apply Default Styles again and then click Build on your XML in section builder.

Administrators with CSS knowledge can modify the look and feel of their XMLs (e.g. make columns wider or thinner). To modify the look and feel of your XML, create an external CSS style sheet, upload the style sheet to a SmartFolder, add classes to the elements in your XML, and add a link to the style sheet in your Special - XML Data or Read Only - System Variable custom fields.


Styling XML Data for Printing

You can print your XML if you are displaying it in a Read Only - System Variable. Size your your XML to fit within 700 pixels wide (portrait) or 900 pixels wide (landscape) to avoid content cutting off, when a PDF is generated. If you have more than one section and more than a few columns, some of your content may cut off when printed if it is not sized. You will also notice columns between sections do not align. To fix this, we will use CSS in a SmartFolder and amend our Read Only - System Variable, which is displaying the XML information.

Wrap the content in your read only system variable with a div and give the div a class. We do this to control the width. Wrap the section syntax in a div and give it a class. We do this so we can control the size of cells and the fonts. e.g.

<div class="xml700"><div class="xmlTitleBar">Title Bar Name</div><div class="xml3col">@xml.fieldname.sectionnodename.html@</div></div>


Now create a CSS file. e.g.

@charset "utf-8";
/* CSS Document */
.LeftAlign {text-align:left;}
.RightAlign {text-align:right;} <!--Used to align numbers like money to the right-->
.xml700 {width:700px;} <!--Used to constrain the xml and title bars to 700px-->
.xml3col .xmlHead {font-size:12px;} <!--Used to change the header font size-->
.xml3col .xmlLabel {width:470px; min-width:470px; max-width:470px; font-size:12px;} <!--Used to set the first column to a set size and change the font size-->
.xml3col .xmlCell {width:80px; min-width:80px; max-width:80px; font-size:12px} <!--Used to set the rest of the columns to a set size and change the font size-->
.xml3col .xmlCell.note {width:150px; min-width:150px; max-width:150px; font-size:12px} <!--Used to set one column to a larger size than the other cells-->

Go to the desired SmartFolders and upload your CSS file. Use View URL to get the path to the CSS file. Then add the relative link back into your Read Only - System Variable. e.g.


<link href="/files/427076/f114840/filename.css" rel="stylesheet" type="text/css" />


Add the class RightAlign to the desired cells in the section builder.


If you want to make one cell larger than the others, add a custom class to that cell in the section builder e.g.

.xml3col .xmlCell.note {width:150px; min-width:150px; max-width:150px; font-size:12px}

I can add note to a cell that has xmlCell in a section wrapped in a div with a class xml3col and that cell will become 150 pixels wide.

Note: your cells must add up to 700 pixels if you are displaying your budget in a portrait PDF on letter size paper (8.5 x 11”).


Using different styles for Print and Web with XML Data

You can have one look for your XML when it is onscreen and another when it is in PDF format for printing. To do this, create two Read Only - System Variables custom fields, create two CSS style sheets in SmartFolders and reference the desired style sheet in the desired Read Only - System Variables. Then on the Read Only - System Variables for viewing on screen for SmartFields View choose Exclude. This custom field will no longer be included in PDFs generated using the SmartFields variable. For the other custom field you want to print, choose Only display for SmartFields View. Now one Read Only - System Variables will appear on screen and another when the PDF is created, and you can have a separate style sheet to control the look and feel of both.

Reporting on XML data

Extract XML value in a report using the following syntax in the report builder. This will return one value for static XML sections specific to the row and multiple values for Dynamic XML sections across all rows.

ExtractValue([this], '/worksheet/sectionnodename/rownodename/columnnodename')

XML Data - Appendix

Field options and settings

General Settings

Custom Field ID: Used internally to reference the user's input.

Field Type: Used to select the field type from list of available field types.

Field Name: Used internally to reference the user's input.

Description: Used for a general description, this field only appears on the configuration page.

XSL Template: Used to define how the XML will look.

XML Tag Mapping: Used to Tag Maps.

XML Javascript Function: Used for JavaScript and jQuery functions such as validations.

Show View XML Button: Used to display the XML button. This button opens the xml feed (code) in a new window.

Show Upload Button: Used to display the upload button. This button enables the user to upload an XML, and if the node structure matches that of the field, it will populate the cells with the correct values.

Show Up/Down Button: Used to display an up and down arrow on each row of a dynamic XML section. Enabling the user to move rows up and down.

Display fields refreshed upon edit: Displays the ID of custom fields that reference this field. When this field is modified the references will automatically update.

Custom Field Ids: Used to reduce variable processing time. Specify field ids of custom fields referenced from within the current field.


Display

Display Order: Used to define where this field appears relative to other fields. Lower numbers appear closer to the top of the page.

Tab Name: Used to display the field under a specific tab.

Caption: Used as a label or question before the field. If left blank, the Field Name will be displayed as the caption.

Caption Location: Used to control the position and visibility of the caption. See Examples Label_Display_Options

Instructions: Used to display text in the caption area. Instructions appear below the caption and are always visible unlike tool tips that appear on mouse over.

Button Label: Used to specify the text that appears on the button. You can also specify an icon.

Tool Tip: Used to display a text message when the user hovers over the question mark. These messages are intended to assist the user in filling in the desired field.

  • On New Record: Used to control when the field should be displayed.


Options

Appear Mandatory: Used to make the field look like it is required without doing validation. This is used in conjunction with other validation methods such as SmartCheck, Submit Logic or Browser Script.

Track Changes: Used to log changes to the field e.g. who updated the field when and what was the new and old values.

Enable Builder: Used to create sections, define rows, columns, formulas and styles in your XML.

Enable KML: Used to upload a KML file which is used to display geographic data on a map.

Disable Field from Global Search: Used to control the display of this field in the global search results.


Value Storage

Value Storage: Used to save values to another field on the same entity.

Example of XML field

Here is an example of an XML field with one section (Expenses) 3 static rows (Salaries, Supplies and Total) and 3 columns (Amount, Notes and Date).

<?xml version="1.0" encoding="UTF-8"?>
<worksheet>
    <Expenses>
        <Salaries>
            <Amount>50000</Amount>
            <Notes>People I work with</Notes>
            <Date>2016-11-30</Date>
        </Salaries>
        <Supplies>
            <Amount>30000</Amount>
            <Notes>Things I need</Notes>
            <Date>2016-11-30</Date>
        </Supplies>
        <Total>
            <Amount>8000</Amount>
            <Notes></Notes>
            <Date>2016-11-30</Date>
        </Total>
    </Expenses>
</worksheet>