Skip to main content
Cartegraph Campus

Functions in Syntax View

Syntax functions can be used in Cartegraph for filtering, reporting, validation rules, and in setting default values for certain field types (Date, Date and Time, Time and Text field types).

Recordset fields in the database should display in the correct time zone on a report. Exceptions include date and time variables such as <Current Time> or <Today's Date>, and date functions such as <Now>, which are calculated and displayed in database time.

For basic rules on how to create your expressions, see Basic SQL Syntax Rules.

Functions are divided according to function type:

Date

Date functions calculate date values.

AddDays(date,number)

Adds a specified number of days to a date.

AddDays([StartDateActual], 10)

Results: Adds ten days to the start date (of a work order, for example).

AddMonths(date, number)

Adds a specified number of months to a date.

AddMonth([StartDateActual], 2)

Results: Adds two months to the start date (of a work order, for example).

AddYears(date, number)

Adds a specified number of years to the date.

AddYears([StartDateActual], 2)

Results: Adds two years to the start date (of an inspection work order, for example).

DateDiff("datepart", startdate, enddate)

Determines the difference between the startdate value and the enddate value and expresses the difference in "datepart" units.

Valid "datepart" units are:

  • Year = year, yy, yyyy
  • Quarter = quarter, qq, q
  • Month = month, mm, m
  • Day = day, dd, d
  • Week = week, wk, ww
  • Hour = hour, hh
  • Minute = minute, mi, n
  • Second = second, ss, s

We do not attempt to calculate the approximate number in between.

  • See SQL DATEDIFF documentation: "Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
  • "http://technet.microsoft.com/en-us/library/ms189794.aspx
  • For example, DateDiff( "Day", #1/1/2014 23:59:59.999#, #1/2/2014 00:00:00.000# )
  • This yields a 1 day difference, even though there is truly only 1 millisecond in between the two date time values.
  • This applies to all of the different date parts
  • For weeks, Sunday is considered the first day of the week. Saturday and the following Sunday are considered 1 week apart. Sunday and the following Saturday are considered in the same week. We removed support for Day of Year, as it has the same net effect as Day.

DateDiff("m",[StopDateActual], Now())

Results: Shows the difference (in months) between today's date and a work order stop date.

This function is not applicable when defining default values in Structure Manager for Date or Date and Time field types.

Now()

Provides the current system date and time.

Now()

Results: Returns today's date. This function is often used as part of a more complex expression or function as shown above, or for a default field value.

SubtractDays(date, number)

Subtracts a specified number of days from the date.

SubtractDays([StartDateActual],6)

Results: Subtracts six days from the start date(of a work order, for example).

SubtractMonths(date, number)

Subtracts a specified number of months from the date.

SubtractMonths([StartDateActual],6)

Results: Subtracts six months from the start date(of a work order, for example).

SubtractYears(date, number)

Subtracts a specified number of years from the date.

SubtractYears([StartDateActual],6)

Results: Subtracts six years from the start date(of a work order, for example).

DatePart('datepart", date)

Returns an integer that represents the specified datepart of the specified date. For additional information, see SQL DATEPART documentation: https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql

Valid datepart units are:

  • Year = year, yy, yyyy
  • Quarter = quarter, qq, q
  • Month = month, mm, m
  • Day of Year = dayofyear, dy, y
  • Day of Week = weekday, dw
  • Day = day, dd, d
  • Week = week, wk, ww
  • Hour = hour, hh
  • Minute = minute, mi, n
  • Second = second, ss, s
DatePart( "Day",  #1/27/2014 23:59:59.999# )

Results: 27, as this is the day of the month for the given date value.

DatePart( "mm",  [EntryDate])

Results: Returns the month portion of the Entry Date field.

General

General Functions calculate quantity (amount and unit) fields and create logical condition statements.

ConvUnit(expression1, expression2)

Converts the value of expression1 to the units in expression2.

ConvUnit([Length],"inch")

Results: Converts the contents of length(which may be stored in meters or feet) to inches.

GetUnit(expression)

Returns the unit portion of the value stored in quantity field.

GetUnit([Length])

Results: Returns the unit portion of the value stored in the field "Length" —, inches, etc.

GetAmount(expression)

Returns the amount portion of the value stored in quantity field.

GetAmount([Length])

Results: Returns the amount portion of the value stored in "Length".

If Then Else and IIF(condition, expression1, expression2)

If the condition is met, the action in expression1 is performed. If the condition is not met, the action in expression2 is performed.

IIF([StartDateEstimated] is on #12/25/2003#,
AddDays([StartDateEstimated],5),[StartDateEstimated])

Both examples check if the MUTCD Code  is equal to "R1-1", then verify that the Estimated OCI is greater than 75, otherwise they verify that the Estimated OCI is greater than 50

  • (IF ([MUTCDCode] = "R1-1") THEN 75 ELSE 50 END_IF < [EstimatedOCI])
  • (IIF(([MUTCDCode] = "R1-1"), 75, 50) < [EstimatedOCI])

Both functions support having no else case as well

  • (IF ([MUTCDCode] = "R1-1") THEN 75 END_IF < [EstimatedOCI])
  • (IIF(([MUTCDCode] = "R1-1"), 75) < [EstimatedOCI])

Group

Group Functions calculate sums and averages for numeric fields.

Sum(expression)

For numeric fields only. Sum the fields listed in the expression.

Sum(Add([Log\Overtime1Cost],[Log\Overtime2Cost]))

Results: Calculates the sum of the Overtime 1 and Overtime 2 costs.

Avg(expression)

For numeric fields only. Calculate the average of the contents of expression.

Avg([Log\Overtime1Cost],[Log\Overtime2Cost])

Results: Calculates the average value of Overtime 1 and Overtime 2 costs.

Math

Math Functions calculate numeric fields.

Enforce that the left and right are numeric types (number, integer, or currency).

  • Addition (+) also allows text types (text, lookup, attachment, etc), and will just function as concatenation in those cases.
    • Example: "12" + "34" will evaluate to: "1234"
  • Math operators can be used in place of the functions.  The following examples are equivalent filters
    • [AddressNumber] > (1 + 2)
    • [AddressNumber] > Add (1, 2)

Add(number1, number2)

Adds the specified numeric values.

Add ([cgLaborLog\Overtime1Cost], [cgLaborLog\Overtime2Cost])

Results: Adds the values of Overtime 1 and Overtime 2.

Divide(number1, number2))

Divides the specified numeric values.

Divide ([CurrentRateID\StandardRate.amount],1.042)

Results: Divides the current rate by 1.042.

Multiply(number1, number2)

Multiplies the specified numeric values.

Multiply ([CurrentRateID\Standard Rate.amount],1.042)

Results: Multiplies the current rate by 1.042.

Subtract(number1, number)

Subtracts the specified numeric values.

Subtract([TotalCostActual],[TotalCostEstimated])

Results: Subtracts the estimated cost from the actual cost.

Sqrt(number)

Takes the square root of a given number, currency, integer, or quantity field.

Sqrt([TotalCostActual])

Results: Calculates the square root of the actual cost.

Abs(number)

Takes the absolute value of a given number, currency, integer, or quantity field.

Abs([CurrentRateID\Standard Rate.amount])

Results: Calculates the absolute value of the current rate.

Power(number, power)

Raises a given number, integer, currency, or quantity to a specified power. Both values can be negative and/or nonintegers.

Power([Length.Amount], 3)

Results: Calculates the length to the third power (length cubed).

Text

Text Functions are used for text fields or to convert numeric fields to text.

AddText("string1", "string2")

Adds(concatenates) text strings.

AddText("Work Performed - ", [Activity])

Results: Adds "Work Performed- " to the contents of the Activity field such as "Work Performed - Installed".

AsString(expression)

Converts the numeric field to a text value.

AsString(Add([cgLaborLog\Overtime1Cost],[cgLaborLog\Overtime2Cost]))

Results: Adds the values of Overtime 1 and Overtime 2 and converts it to a text string.

User()

Returns the database user name for the individual currently logged into the database.

User()

Results: Name of database user. This function is often used to provide default values and for archive records.

WinUser()

Returns the Windows user name for the individual currently logged into the database.

WinUser()

Results: clark (name of windows workstation user). This function is often used to provide default values on a form.

Role()

Returns the Cartegraph role name for the individual currently logged into Cartegraph.

Role()

Results: Name of the Cartegraph user's role. This function is often used to provide default values and for archive records.

Time

Time Functions calculate time fields.

AddHours(time, number)

Adds specified number of hours to the time value.

AddHours([Breaks\WhenReported],8)

Results: Adds 8 hours to the time stored in the Breaks\When Reported field.

AddMinutes(time, number)

Adds specified number of minutes to the time value.

AddMinutes([Breaks\WhenReported],120)

Results: Adds 120 minutes to the time stored in the Breaks\When Reported field.

DateDiff("timepart", starttime, endtime)

Determines the difference between the starttime value and the endtime value and expresses the difference in "timepart" units.

Valid "timepart" units are:

  • Year = year, yy, yyyy
  • Quarter = quarter, qq, q
  • Month = month, mm, m
  • Day = day, dd, d
  • Week = week, wk, ww
  • Hour = hour, hh
  • Minute = minute, mi, n
  • Second = second, ss, s
DateDiff( "h", [Breaks\WhenReported],[Breaks\WhenFixed])

Results: Computes the difference between the time a break was reported and when the break was fixed and reports it as the number of hours.

This function is not applicable when defining default values in Structure Manager for Time field types.

Now()

Returns the current system time.

Now()

Results: Returns current time. This function is often used as part of a more complex expression or function, or for a default field value.

SubtractHours(time, number)

Subtracts specified number of hours from the time value.

SubtractHours([Breaks\WhenFixed],8)

Results: Subtracts 8 hours from the time stored in the Breaks\When Fixed field.

SubtractMinutes(time, number)

Subtracts specified number of minutes from the time value.

SubtractMinutes([Breaks\WhenFixed],120)

Results: Subtracts 120 minutes from the time stored in the Breaks\When Fixed field.

IN

  • Using the Syntax View, the user is able to verify that an expression is contained within a subset of values
  • Is not case sensitive
  • Example: Give me all records that have an ID of either 'abC', 'dEf', 'Ghi' or any variation of casing in the previous values
  • Filter: ([ID] IN ("abC", "dEf", "Ghi"))
    • Matches: abc, Abc, ABc, ABC, AbC, aBC, abC, etc...
    • Not Matches: abcd, abcdef, etc…

LIKE

  • Using the Syntax View, the user is able to perform a SQL LIKE operation using the LIKE keyword
  • % and _ characters are wildcards (same as SQL LIKE)
  • Example: Give me all signs where the Location Description ends with an 'h', followed by any character, followed by 'llo'
  • Filter: ([LocationDescription] like "%h_llo")
    • Matches: Alskjfhallo, Hello, Hollo, Asdfasdfasdfasdfasdfhwllo, etc...
    • Not Matches: Heello, Helloo, etc...

Count

  • Example: Give me all records where there is at least 1 Inspection record with an Inspected OCI greater than 75.
  • Filter: (Count([cgInspections\InspectedOCI] > 75) > 0)
  • Example: Give me all records where there is NO Inspection record with an Inspected OCI greater than 75.
  • Filter: (Count([cgInspections\InspectedOCI] > 75) = 0)

Round

Round functions return desired decimal places for a given number, currency or quantity field.

Round(numeric expression, length)

Returns a numeric value, rounded to the specified length or precision

Total Area = ROUND(([Length.Amount] * [Width.Amount]), 2)
ROUND(123.555, 2) = 123.56
ROUND(123.555, 1) = 123.6
ROUND(123.555, 0) = 124
ROUND(123.555, -1) = 120
ROUND(123.555, -2) = 100

The maximum number of decimal places to round to is 15.

 

  • Was this article helpful?