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:

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}.’





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.


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. 


Return a value if the variable you are evaluating is NULL

Syntax: ISNULL({variable}, alternate value)

Example: ISNULL({AssetAttribute:SalvageValue},0)


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)


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


If/Then/Else logic

Syntax: IIF(A = B, ‘Result if True’, ‘Result if False’)

Example: 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 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 + ‘)’

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)

Table of Contents