Smartstaff
281
edits
Changes
m
* 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:
no edit summary
{{MySQL Syntax}}
Sscalculation is a [[Variables|variable]] syntax for:
* [[Web Page View Field|Web Page Views]]
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 JavaScript. This resolves issues with creating PDF and javascriptfiles from [[Web Page View Field|web page views]] that contain JavaScript.
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
<pre style="white-space: pre-wrap;
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:
<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(datediff('@datetime(currentdate)@','@Last Contact Date@'))--></pre>
* To add a number of months to a date equal to the number selected 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(formatDATE_ADD(( [#(?object=company;criteria=rolename='Funding Agency@Anticipated Project End Date@') $?opc_123456.value$ + #] , INTERVAL @#545454system.value#Grants-FinalDisbursementDelay@),0MONTH))--></pre>
* To find the average subtract a number of all values (Custom Field ID 1755555) on months to a particular Level 2 Type (typeid=54321), excluding date you use a particular status negative number for the INTERVAL ([[statusid]] is not 10001for example -6 months below), 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(formatDATE_ADD( ( [#(?object=activity;criteria=typeid='54321' and status.statusid not in ('10001')) $1755555$ + #] 0 )/@level2.count(*):typeid='54321' and status.statusid not in (Anticipated Project End Date@'10001')@,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==
* 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==
