Changes

Custom Field Type: Special - Advanced Data Table

406 bytes removed, 16:27, 4 December 2018
no edit summary
=Overview=
 [[File:xmlSample.png|thumb|none|middle|500px|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.
{| class="wikitable"
| Summary
|
|-
| Commonly Used in Markets (Philanthropy, Research, Insurance)|Summary| All|
|-
| |Commonly Used By in Markets (User TypePhilanthropy, Research, Insurance)|| All
|-
| Requires Administrator Setup|Used By (User Type)| Yes|All
|-
| Configuration Complexity (Low, Medium, High)|Requires Administrator Setup| High|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:
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 settings area where you want to create a worksheet (e.g. the UTA level 1).
# Click the '''New Field''' (+) button.
# Use the '''Custom Field''' button to navigate back from the Section Builder to the Field settings to configure the display of tables.
== 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.
[[File:xml-sectionbuilder1.png|thumb|none|middle|500px|The section builder.]]
# While editing the custom field click on the '''Section Builder''' button (the four square icon) in the action bar.
# 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 ==[[File:xmlDynamic.png|thumb|none|500px|Example section set to dynamic rows.]]
[[File:xmlDynamic.png|thumb|none|middle|500px|Example In addition to defining a set number of columns and rows, you can also configure a section set to handle a dynamic number of rows. The user entering the data can add rowsas necessary at time of entry.]]This is done by entering '''0''' for the '''Number of Rows''', which in turn exposes the following configuration options:
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.
[[File:xml-xsl.png|thumb|none|middle|500px|The XSL template and configuration.]]
# While editing the custom field locate the '''XSL Template''' option and insert the '''Sample Template'''.
# 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''@
= Configuration - Advanced ===Creating formulas (calculations) in XML cells==[[File:xml-formula.png|500px|border|Adding a formula]]
== Creating formulas (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) in on this information found within your XML cells =(e.g. to sum the contents of column one, over two rows, enter '''=[1,1]+[File:xml-formula2,1]''').png|Adding a formula|500px|border]]
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) ==
# Navigate to your XML custom field.
# Click the '''Section Builder''' button in the action bar.
The Syntax for basic calculations within a section:
 
{| class="wikitable"
|'''Operation'''
|'''Symbol'''
|'''Example'''
|-
||'''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
== Validate XML values when saving the worksheet ==
==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.
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).
* Total Amount greater than 0, for currency formatted column totals
 
<pre>ssParseNum([this])>0</pre>
Message: Total must be greater than $0.00
 
* Date is at least 30 days in the future
 
<pre>datediff(ConvertDateStr([this],'@dateformat@'),'@date(currentdate)@','d') > 30</pre>
Message: Please select a date that is at least thirty 30 days from today.
 
* Cell is not empty
 
<pre>[this].length>0</pre>
Message: Please provide a value.
 
* Check the user selected something other than the default Combo Box value
 
<pre>[this]!="Default Combo Box Value"</pre>
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.)
 
<pre>parseInt([this])>0</pre>
Message: Please provide a numeric value greater than 0.
== 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).
 === 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.
<pre>if("<ssEscape>@level1.xml.fieldname.sectionnodename.rownodename.columnnodename.nodevalue@</ssEscape>"=="")
{result.isPassed=false;
result.addMsg('xml_@fieldname.id@','YourAlertMessage'); }</pre>
=== 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.
<pre>"<ssEscape>@level1.xml.fieldname.sectionnodename.rownodename.columnnodename.nodevalue@</ssEscape>"!=""</pre>
<pre>"@level1.xml.fieldname.sectionnodename.rownodename.columnnodename.nodevalue@"!=""</pre>
=== 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.
 
<pre>@xml.customfieldname.sectionnodename.rownodename.columnnodename@</pre>
The following function allows you to check for an already selected value from an existing row when using dynamic rows:
 <pre>// REPLACE '7172_dsItem_Population' AND '7172_dsItem_Population_' WITH SPECIFIC XML FIELD ID
var saveFunc=saveXML;
== Managing Level 2 / 3 Activities with an XML Section == 
System administrators can configure XML fields to add, modify or delete {{l2}} records on a {{l1}} entity or {{l3}} records on a {{l2}} entity.
In the [[XML Section Builder]], when the ''Number of Rows'' for a Section is set to '0', the ''Enable Mapping'' option will be shown.
[[Image:XMLEnableMapping2017.png|link=|800px|border|800px]]
When ''Enable Mapping'' is selected, you can choose one type of activities to be referenced by the XML field.
The ''Activity Status Filter'' setting allows you to filter the activities to be referenced by status.
The ''Additional Filter'' setting allows you to select custom fields and associated values. These are used to filter the existing activities that will be listed within the XML. The custom fields used have to be those with pre-defined options (e.g. [[Custom Field Type: Select One – Combo Box|Combo box]], [[Custom Field Type: Select Many – Check Boxes|Check Boxes]], [[Custom Field Type: Dynamic Data – Dynamic Control Field|Dynamic Control]], Ajax Lookup).
[[Image:XMLEnableMappingFilter2017.png|link=|650px|border|650px]]
When building your section, you will need to map the fields on the {{l2}}s that will be referenced by the XML section.
For each Section column, enter the field name from the Level 2 type that you have selected from the ''Activity Type'' dropdown in the ''Map Field'' section:
[[Image:MappingL2Fields2017.png|link=|800px|border|800px]]
The syntax for the ''Map Field'' section is:
 
* for [[custom fields]], simply the field name (i.e., ''Fund Name'');
* for [[standard field]]s, use the prefix ''sf_'' and then the system name for the standard field (i.e., ''sf_status'').
:''see [[Standard Level 2 Field List]] article for a list of system names for {{l2}} standard fields.
: ''see [[Standard Level 2 Field List]] article for a list of system names for {{l2}} standard fields.
If the Standard or Custom field has a limited number of pre-defined options then these need to be defined in the Content section of the cell properties.
You may need to reference some standard field options using both the displayed name and stored ID values.
[[Image:MappingL2Fields2018.png|link=|650px|border|650px]]
When this section is added to the XSL Template of your XML field, the list of {{l2}} activities will be displayed when the XML is opened. Level 2s can be added, deleted and modified directly from the {{l1}} XML field.
= Displaying XML Data===Displaying XML Data on an object==[[File:xml-readonly.png|500px|border|The XML displayed in a read only]]
== Displaying 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 an your object ==[[File:xml-readonly, 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.png|The XML displayed same syntax can be used elsewhere in a read only|500px|border]]the system (e.g. include Display Only - Web Page View, Special MS Word Merge, visibility conditions, etc.) when referencing the xml values.
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.
 == 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'''.
 
<pre>@xml.fieldname.sectionnodename.rownodename.columnnodename@</pre>
* Extract the value from a specific individual cell using the '''field id'''.
 
<pre>@xml.#fieldid#.sectionnodename.rownodename.columnnodename@</pre>
* Extract numeric value and return in '''comma format''' (i.e. 1000 will display as 1,000).
 
<pre>@xml.fieldname.sectionnodename.rownodename[# ~comma(columnnodename.nodevalue)~ #]@</pre>
* Extract a numeric value and return in '''currency format'''.
 
<pre>@xml.fieldname.sectionnodename.rownodename[# ~currency(columnodename.nodevalue)~ #]@</pre>
* Extract a numeric value and return in a '''specific currency format'''.
 
<pre>@xml.fieldname.sectionnodename.rownodename[# ~currency_eur(columnodename.nodevalue)~ #]@</pre>
Further currency formatting options below
 
{| border="1"
|-
|| 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.
 
<pre>@xml.fieldname.sectionnodename.rownodename[# ~currency_eur(columnodename.nodevalue,2)~ #]@</pre>
* Extract a value and return the number of characters in the field. 
<pre>@xml.fieldname.sectionnodename.rownodename.columnodename.nodevaluelength@</pre>
* Extract a numeric value and return in '''currency format with two decimal places''' for value pulled from a '''static''' XML section. 
<pre><!--@sscalculation(format("@xml.fieldname.sectionnodename.rownodename.columnodename.nodevalue@",2))--></pre>
* Extract a percentage value and '''format with two decimal places'''. If pulled from a '''dynamic''' XML section then you will see multiple values.
 
<pre>@xml.fieldname.sectionnodename.rownodename[# ~percentdisplay(columnodename.nodevalue,2)~ #]@</pre>
* Extract date values and return with specific formatting using the following syntaxReturn the date in the user's date format
 
<pre>@xml.fieldname.sectionnodename.rownodename.formatdate(columnnodename.nodevalue)@</pre>
* Return the year.
 
<pre>@xml.fieldname.sectionnodename.year(columnnodename.nodevalue)@</pre>
* Return the month.
 
<pre>@xml.fieldname.sectionnodename.month(columnnodename.nodevalue)@</pre>
* Return the month name.
 
<pre>@xml.fieldname.sectionnodename.monthname(columnnodename.nodevalue)@</pre>
* Return the day.
 
<pre>@xml.fieldname.sectionnodename.day(columnnodename.nodevalue)@</pre>
* Return the day of the week.
 
<pre>@xml.fieldname.sectionnodename.dayweek(columnnodename.nodevalue)@</pre>
* Count the number of rows the user created in a dynamic XML section.
 
<pre>@xml.fieldname.sectionnodename.rownodename.nodecount@</pre>
* Return the XML feed (code) from the field.
 
<pre>@fieldname.xmlvalue@</pre>
* Return multiple rows for XML with dynamic sections.
 
<pre>@xml.fieldname.sectionnodename.rownodename[# ~columnnodename.nodevalue~ #]@</pre>
* Return Filtered row values for XML with dynamic sections.
 
<pre>@xml.fieldname.sectionodename.rownodename[#(?criteria="~colunnnodename.nodevalue~" == "matching criteria")~columnnodename.nodevalue~ #]@</pre>
* Return multiple rows for XML with dynamic sections in a custom html table.
<pre><table><tbody>@xml.fieldname.sectionnodename.rownodename[##]@<tr><td>{||-||~columnodename.nodevalue~</td><td>||~columnodename.nodevalue~</td><td>||~columnodename.nodevalue~</td></tr>#]@</tbody></table></pre>|}
* Return a single value in the total row of a Dynamic XML.
== Styling XML data for web and print (PDF) ==[[File:xml-readonly-styled.png|500px|border|The XML styled at 700 pixels wide in a read only|500px|border]]
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.
 === 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.
<pre><div class="xml700"><div class="xmlTitleBar">Title Bar Name</div><div class="xml3col">@xml.fieldname.sectionnodename.html@</div></div></pre>
 Now create a CSS file. e.g.
<pre>@charset "utf-8";
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.
 
If you want to make one cell larger than the others, add a custom class to that cell in the section builder e.g.
<pre>.xml3col .xmlCell.note {width:150px; min-width:150px; max-width:150px; font-size:12px}</pre>
=== 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.
== 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.
 
<pre>ExtractValue([this], '/worksheet/sectionnodename/rownodename/columnnodename')</pre>
= Appendix =Field Options==All common options:<br />[[Custom Fields - General Information #General_Settings|General Settings]]<br />== [[Custom Fields - General Information #Role Field options Permissions and settings ==Status Field Permissions|Permissions & Availability]]<br />[[Custom Fields - General Information #Classic Options|Classic Options]]<br />===For further information, please refer to [[Custom Fields – General Settings===Information]].<br /><br />Options listed below are specific to this particular field type.
{{XSL Template}}
{{Custom Field Ids}}
=== Display =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).
{{Display Order}}<pre><!--?xml version="1.0" encoding="UTF-8"?-->
{{Tab Name}} {{Caption}} {{Caption Location}} {{Instructions}} {{Button Label}} {{Tool Tip}} {{On New Record}}  === Options ===  {{Appear Mandatory}} {{Track Changes}} {{Enable Builder}} {{EnableKML}} {{Disable Field from Global Search}}  === Value Storage === {{Value Storage}} == 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). <pre><?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></pre>
{{CustomFieldPageFooter}}
[[Category:XML]]
Smartstaff, administrator
421
edits