Smartstaff
281
edits
Changes
m
no edit summary
{{MySQL Syntax}}
Sscalculation is a [[Variables|variable]] syntax for:
* [[Web Page View Field|Web Page Views]]
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).
white-space: -o-pre-wrap;
word-wrap: break-word;">
<!--@sscalculation(date_format("@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:
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>
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>
white-space: -o-pre-wrap;
word-wrap: break-word;"><!--@sscalculation(datediff('@datetime(currentdate)@','@Last Contact Date@'))--></pre>
* To add a number of months to a date equal to the number selected in another field:
white-space: -o-pre-wrap;
word-wrap: break-word;"><!--@sscalculation(DATE_ADD('@Anticipated Project End Date@', INTERVAL @system.Grants-FinalDisbursementDelay@ MONTH))--></pre>
* To subtract a number of months to a date you use a negative number for the INTERVAL (for example -6 months below):
<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(DATE_ADD('@Anticipated Project End Date@', INTERVAL -6 MONTH))--></pre>
* To calculate the difference in months between two date values:
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==
* It is not necessary to use sscalculation if the custom field being referenced is on the same level as the field referencing it. For example, if you are referencing a level 2 custom field in a web page view field on level 2. In this case, you can use the syntax outlined on the [[Web_Page_View_Field_Variables#Custom_Fields_Date.2FTime|Web Page View Field Variables]] page.
==Using sscalculation to replace text==
In the following example, the variable ''@Select Many Checkbox Fields@'' returns a semicolon-separated list of selected values from the 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;">Option 1;Option 2;Option 3</pre>
In order to replace the semicolons with a comma, followed by a space, you can use the following syntax:
<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==
