Smartstaff
281
edits
Changes
m
* To sum a value on a UTA Company 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(( [#(Custom Field ID 123456) for all companies with role '?object=company::criteria=rolename='Funding Agency'' AND a ) $?opc_123456.value stored on the level 1 (Custom Field ID $ + #] @#545454.value#@) and format it with no decimal places:,0))--></pre>
:{| border="1" cellpadding="5"|''Format''|''Description''|-|%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''|-|%y||will return the truncated year, e.g. ''12'' for 2012|-|%D||will return the month ordinal, e.g. ''30th''|-|%W||will return the day of the week, e.g. ''Sunday''|{MYSQLDATETIMEFORMATS}}
no edit summary
{{MySQL Syntax}}
Sscalculation is a [[Variables|variable]] syntax for:
* [[Web Page View Field|Web Page Views]]
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word;"><!--@sscalculation('@field@'*0.05)-->
</pre>
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:
* 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=company;activity::criteria=rolenametypeid='Funding Agency54321' and status.statusid not in ('10001')) $?opc_123456.value1755555$ + #] 0 )/@#545454level2.count(*):typeid='54321' and status.value#statusid not in ('10001')@),0))--></pre>
* To find the average of all values 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(Custom Field ID 1755555) on a particular Level 2 Type '@datetime(typeid=54321currentdate)@', excluding '@Last Contact Date@'))--></pre> * To add a particular status 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(DATE_ADD([[statusid]] is not 10001'@Anticipated Project End Date@', INTERVAL @system.Grants-FinalDisbursementDelay@ MONTH), rounded )--></pre> * To subtract a number of months to a date you use a negative number for the nearest whole numberINTERVAL (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(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==
* Use the following arguments to format the date/time information:
'''Note''': Any MYSQL syntax can be used within sscalculation. See [http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format here] for additional date/time formatting options. (Note: this is an external website.)
* 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==
