Calculated Fields

You are here:

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:
GREATEST({AssetAttribute:VolumeA}, {AssetAttribute:VolumeB})

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):
https://www.w3schools.com/sql/func_sqlserver_convert.asp

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:
LEAST(ISNULL({AssetAttribute:NextInspectionDateA}, ‘2999-12-31’), ISNULL({AssetAttribute:NextInspectionDateB}, ‘2999-12-31’))

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, or converting dates to strings.

 

Please refer to the following link for the “styles” used for converting dates to strings: https://www.w3schools.com/sql/func_sqlserver_convert.asp

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))

Syntax for converting dates to strings: TRY_CONVERY(varchar, date, style) 

Example: TRY_CONVERT(varchar, {AssetAttribute:Date}, 107)

Table of Contents