Calculated Fields
The table below includes a selection of commonly used SQL Server functions. For a full list of SQL Server functions, please see this helpful online resource: https://www.w3schools.com/sql/sql_ref_sqlserver.asp
To enter text into a calculated field, simply place your text inside single quotes: ‘ ‘. Standard Assetas variables may be placed within text. For example: ‘Created on {Today}.’
SQL server functions are used in Reports and Document Templates. For Form Types, Task Types, and Workflows, please use the {Evaluate} function.
Function | Use | Example |
DateAdd | Add days, months, or years to a date. | Syntax: DateAdd(day, number, Date) Example: DateAdd(day, 1, {EndDate}) Note: you may modify the unit ‘day’ with ‘month’, ‘year’, ‘hour’, etc. |
DateDiff | Subtract two dates (Date A – Date B). Please note the ordering of dates within the Syntax. | Syntax: DateDiff(day, ‘Date B’, ‘Date A’) Example: DateDiff(day, Asset.InServiceDate, {Today}) Note: you may return the difference in a unit other than days by replacing ‘day’ with ‘month’, ‘year’, ‘hour’, etc. |
GetDate() | Return date and time on the server in a ‘YYYY-MM-DD hh:mm:ss.mmm’ format | Syntax: GetDate() Note: do not enter anything between the parenthesis. This returns the date and time on the server which may not necessarily be in your timezone. For the date and time in your timezone, use the {Now} variable, and for just the date, use the {Today} variable. |
GREATEST | Returns the largest of two or more items. | Syntax: GREATEST(value 1, value 2, value 3, ….) Example: |
IIF | If/Then/Else logic | Syntax: IIF(A = B, ‘Result if True’, ‘Result if False’) Example: IIF(Asset.TagList=’No RFID’, ‘No’, ‘Yes’)
IIF({ContactAttribute:BestPractices}= ‘True’, ‘Signed’, ‘Incomplete’)
Example: IIF(DateDiff(day, {Today}, {ContactAttribute:NextFitnessTest})<0,’Overdue’,”)
Example – calculating the days since the last time a form was submitted: DATEDIFF(day, max(Form.Date), {Today})
Example: IIF(Contact.Company=1, ‘Company’, ‘Person’) |
Insert Date as Text | Converts a date into a text string. Please refer to the following link for various style options (use the ‘With century’ value): | Syntax: CONVERT(VARCHAR,{variable}, style)
Example: CONVERT(VARCHAR,{FormAnswer:Date}, 111) + ‘ – ‘ + {FormAnswer:LastName} |
Insert Text | Displays text alongside logic and/or variables. Please note that any data elements included in a Calculated Field must also be explicitly included in either the Report Filters or in the Sorting (for example, sort by Asset Type.Name). When working with Contacts in a Calculated Field, please note that the correct syntax for the full name is Contact.DisplayName, not Contact.ContactName. When working with Models in a Calculated Field, please note that the correct syntax for the Model table is MModel.Name, MModel.ModelID, etc. | Syntax: ‘ ‘ (single quotes) Example: ‘Created on {Today}.’ Example: {FormAnswer:FirstName} + ‘ ‘ + {FormAnswer:LastName}
Example: Asset.Name + ‘ (‘ + AssetType.Name + ‘)’
Example: Contact.DisplayName + ‘ (‘ + ContactType.Name + ‘)’ |
ISNULL | Return a value if the variable you are evaluating is NULL | Syntax: ISNULL({variable}, alternate value) Example: ISNULL({AssetAttribute:SalvageValue},0) ISNULL({AssetAttribute:RetiredDate}, ‘2999-01-01’) |
LEAST | Returns the smallest of two or more items. | Syntax: LEAST(value 1, value 2, value 3, ….) Example: |
ROUND | Rounds a number to a specific number of decimal places (but does not truncate the decimal digits) | Syntax: ROUND(value, number of decimal places) Example: ROUND({AssetAttribute:OriginalCost},1) |
Truncate Date/Time (CAST function) | Removes the timestamp from a date/time value and retains only the date portion. If the same variable is used across multiple record types, be sure to specify the type of record within the variable. | Syntax: CAST({variable} As Date) or CAST({variable, EID of type} As Date)
Example: CAST({ContactAttribute:EarliestDate} As Date)
Example: CAST({AssetAttribute:StartDate, Engine} As Date) |
TRY_CONVERT | Converts a value to a specific data type, useful in truncating decimals and often combined with the ROUND function | Syntax: TRY_CONVERT(data_type [(length)], value) Syntax: TRY_CONVERT(decimal(total number of digits to the left and right of the decimal, number of digits to the right of the decimal), value) Example: TRY_CONVERT(decimal(28,1), ROUND({AssetAttribute:OriginalCost},1)) |
