Sscalculation
Revision as of 09:50, 9 July 2014 by Arthur Lathrop (talk | contribs)
Revision as of 09:50, 9 July 2014 by Arthur Lathrop (talk | contribs)
| This feature uses MariaDB syntax |
Sscalculation is a variable syntax for:
This works similar to sslogic but will do calculations based on the formula you provide. It works as follows:
<!--@sscalculation('@field@'*0.05)-->
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 views without having to use JavaScript. This resolves issues with creating PDF files from web page views that contain JavaScript.
The "sscalculation" method uses MySQL syntax. Any MySQL function such as concat(...), format(...), round(...), can be used.
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:
<!--@sscalculation( [#(?object=transaction)$add this$+#]0)-->
- Important: There must 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
<!--@sscalculation(date_format("@parent.fullstartdate@","%Y"))-->
- 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:
<!--@sscalculation(format(( [#(?object=company::criteria=rolename='Funding Agency') $?opc_123456.value$ + #] @#545454.value#@),0))-->
- 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:
<!--@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))-->
- To find the different in days between two different dates:
<!--@sscalculation(datediff('@datetime(currentdate)@','@Last Contact Date@'))-->
- To add a number of months to a date equal to the number selected in another field:
<!--@sscalculation(DATE_ADD('@Anticipated Project End Date@', INTERVAL @system.Grants-FinalDisbursementDelay@ MONTH))-->
- To calculate the difference in months between two date values:
<!--@sscalculation(PERIOD_DIFF(DATE_FORMAT('@Anticipated Project End Date@','%Y%m'),DATE_FORMAT('@Anticipated Project Start Date@','%Y%m')))-->
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:
<!--@sscalculation(date_format("@parent.fieldname@","%M %d, %Y"))-->
- Use the following arguments to format the date/time information:
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 %d will return the numeric day of month (01-31) %e will return the numeric day of month (1-31) %h will return hour (01-12) %i will return minutes (00-59) %j will return day of year (001-366) %k will return hour (0-23) %l will return hour (1-12) %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 in 12-hour format, e.g. 12:00:00 AM %s will return seconds (00-59) %u will return the week number (00-53) where Monday is the first day of the week %w day of the week (0=Sunday...6=Saturday) %y will return the truncated year, e.g. 12 for 2012 %D will return the month ordinal, e.g. 30th %H will return hour (00-23) %M will return the month name, e.g. September %T will return the full time in 24-hour format, e.g. 23:00:00 %U will return the week number (00-53) where Sunday is the first day of the week %W will return the day of the week, e.g. Sunday %Y will return the four-digit year, e.g. 2013
Note: Any MYSQL syntax can be used within sscalculation. See 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 page.
See Also
- MYSQL Date Format Note: this is an external website.