Smartstaff
281
edits
Changes
m
<!--@sscalculation(This will return the computed value of '@field@'*0.05)-->and replaces the sscalculation tag.
</pre>This way you can do calculations right on the [[Web Page View Field|web page views]] without having to use JavaScript. This resolves issues with creating PDF files from [[Web Page View Field|web page views]] that contain JavaScript.
This will return the computed value of @field@*0.05 and replaces the sscalculation tag.
This way you can do calculations right on the [[Web Page View Field|web page views]] without having to use javascript which solves the problem on PDF and javascript. This <font size=3>'''The "sscalculation" method uses MySQL syntax, so you can do any . Any MySQL functions like function such as concat(...), format(...), round(...), etccan be used.'''<br/font>
* To sum a value on a UTA Company field (Custom Field ID 123456) for all companies with role ''Funding Agency'' AND a value stored on the level 1 (Custom Field ID 545454) and format it with no decimal places:
:{| border="1" cellpadding="5"|-|%M %d, %Y||will return date in format of ''September 30, 2012''|-|%a||will return the truncated day of the week, e.g. ''Sun.'' for Sunday|-|%b||will return the truncated name of the month, e.g. ''Sep'' for September|-|%c||will return the month number in short format, e.g. ''9'' for September|-|%h||will return hour, e.g. ''12''|-|%i||will return minutes, e.g. ''00''|-|%m||will return the month number, e.g. ''09'' for September|-|%p||will return AM/PM information, e.g. ''AM''|-|%r||will return the full time format, e.g. ''12:00:00 AM''|-{MYSQLDATETIMEFORMATS}}
|%y||will return the truncated year, e.g. ''12'Note''' for 2012|: Any MYSQL syntax can be used within sscalculation. See [http://dev.mysql.com/doc/refman/5.5/en/date-and-time-|%D||will return the month ordinal, efunctions.g. ''30th''|html#function_date-|%W||will return the day of the week, eformat here] for additional date/time formatting options.g(Note: this is an external website. ''Sunday''|})
<!-- NOT SURE ABOUT THE FOLLOWING TWO ENTRIES, %u and %v (Arthur Lathrop): |%u||will return * It is not necessary to use sscalculation if the custom field being referenced is on the same level as the week numberfield referencing it. For example, eif you are referencing a level 2 custom field in a web page view field on level 2.g. ''39'' for September 30In this case, 2012; |%v||will return the week number treating Sunday as you can use the first day of syntax outlined on the week, e[[Web_Page_View_Field_Variables#Custom_Fields_Date.g2FTime|Web Page View Field Variables]] page. ''40'' for September 30, 2012 -->
* It is not necessary to use sscalculation if the custom field being referenced is on the same level as In the field referencing it. For following example, if you are referencing the variable ''@Select Many Checkbox Fields@'' returns a level 2 custom field in a web page view semicolon-separated list of selected values from the field on level : <pre style="white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;">Option 1;Option 2. ;Option 3</pre> In this caseorder to replace the semicolons with a comma, followed by a space, you can use the following syntax outlined on the : <pre style="white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;"><!--@sscalculation(REPLACE("@Select Many Checkbox Field@",";",", "))--></pre> ...which will return: <pre style="white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;">Option 1, Option 2, Option 3</pre> ==See Also==* [[Web_Page_View_Field_Variableshttp://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#Custom_Fields_Date.2FTime|Web Page View Field Variablesfunction_date-format MYSQL Date Format]] pageNote: this is an external website.
no edit summary
{{MySQL Syntax}}
Sscalculation is a [[Variables|variable]] syntax for:
* [[Web Page View Field|Web Page Views]]
This works similar to [[sslogic]] but will do calculations based on the formula you provide. It works as follows:
<pre style="white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;"><!--@sscalculation('@field@'*0.05)--></pre>
==Using sscalculation in a List==
To obtain a Sum (for example) of the values in a [[Custom Field]] for all transactions associated with a Company/Contact using [[sscalculation]]:
<prestyle="white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;"><!--@sscalculation( [#(?object=transaction;)$add this$+#]0)-->
</pre>
* '''Important:''' There <u>must</u> be a space prior to the opening square bracket.
* Also: if you are finding the sum don't forget the zero at the end of the statement. For example, if there are transactions with values of 10, 15 and 20 the statement between the square brackets would be translated as follows:
::''[#(?object=transaction;)$add this$+#]'' becomes ''10+15+20+''::so the 0 at the end ensures that the statement evaluated doesn't end with a plus sign (which will fail).
* If you need to pull information from the level 1 when you are at level 2 within the sscalculation tag, you can use the ''parent '' keyword
<prestyle="white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;"><!--@sscalculation(date_format("@year(parent.fullstartdate@","%Y")@)-->
</pre>
'''Note:''' When using date_format with a standard date field you must refer to the field using either @datetime(parent.startdate)@ or @parent.fullstartdate@
* To sum a value on a [[Company Role]] field (Custom Field ID 123456) for all companies with the role of ''Funding Agency'' AND a value stored on the level 1 (Custom Field ID 545454) and format it with no decimal places:
<pre style="white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word;"><!--@sscalculation(format(( [#(?object=company::criteria=rolename='Funding Agency') $?opc_123456.value$ + #] @#545454.value#@),0))--></pre>
* To find the average of all values (Custom Field ID 1755555) on a particular Level 2 Type (typeid=54321), excluding a particular status ([[statusid]] is not 10001), rounded to the nearest whole number:
<pre style="white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word;"><!--@sscalculation(format( ( [#(?object=activity::criteria=typeid='54321' and status.statusid not in ('10001')) $1755555$ + #] 0 )/@level2.count(*):typeid='54321' and status.statusid not in ('10001')@,0))--> </pre>
* To find the different in days between two different dates:<pre style="white-space: pre-wrap; <white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;"><!--@sscalculation(formatdatediff('@datetime( [#(?object=company;criteria=rolename=currentdate)@'Funding Agency,') $?opc_123456.value$ + #] @#545454.value#Last Contact Date@),0'))--></pre>
* To find the average add a number of all values (Custom Field ID 1755555) on a particular Level 2 Type (typeid=54321), excluding months to a particular status ([[statusid]] is not 10001), rounded date equal to the nearest whole numberselected in another field:<pre style="white-space: pre-wrap; <white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;"><!--@sscalculation(format( ( [#DATE_ADD(?object=activity;criteria=typeid='54321@Anticipated Project End Date@' and status, INTERVAL @system.statusid not in ('10001'Grants-FinalDisbursementDelay@ MONTH)) $1755555$ + #] 0 )--></@level2.countpre> * To subtract a number of months to a date you use a negative number for the INTERVAL (*for example -6 months below):typeid<pre style='54321' and status.statusid not in "white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;"><!--@sscalculation(DATE_ADD('10001@Anticipated Project End Date@')@,0INTERVAL -6 MONTH))--> </pre>
* To calculate the difference in months between two date values:
<pre style="white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word;"><!--@sscalculation(PERIOD_DIFF(DATE_FORMAT('@Anticipated Project End Date@','%Y%m'),DATE_FORMAT('@Anticipated Project Start Date@','%Y%m')))--></pre>
or
<pre style="white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word;"><!--@sscalculation(TIMESTAMPDIFF( MONTH,'@Start Date.value@','@End Date.value@') )--></pre>
==Using sscalculation to Change Date Formats of Data in Custom Fields==
* To parse out date/time information from a custom field and display it in a different format, you can use the following syntax on level 2, referencing the field ''fieldname'' on level 1:
<prestyle="white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;"><!--@sscalculation(date_format("@parent.fieldname@","%M %d, %Y"))--></pre>
* Use the following arguments to format the date/time information:
==Using sscalculation to replace text==
[[Category:Variables]][[Category:Date Formats]]
