Skip to main content
Cartegraph Campus

Filter Syntax Properties

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

All fields should be enclosed in [], and must use UIDs.

Yes/No Fields

Selected

Example: Give me all records where Inactive is checked (true).
Filter: ([Inactive] = true)

Not selected

Example: Give me all records where Inactive is unchecked (false).
Filter: ([Inactive] = false)

Common Operators

The following operators are common across all field types except for Yes/No:

  • =
  • <>
  • <
  • >
  • <=
  • >=
  • Is null
  • = null
  • Is not null
  • <> null

See specific field type for more information on how to use each operator for that type.

Date and Date/Time Fields

Date only note: The expression expects all Date values in the database to have 00:00:00 set as the Time part of the DateTime field; otherwise your returned results may not be as expected.

Is On (=)

  • Date field
    • Example: Give me all records that have an Actual Start Date on a specified date.
    • Date Parameter: 04/16/2014
    • Filter: ([StartDateActual] is on #2014-04-06#)
  • Date/Time field
    • Example: Give me all records that have an Entry Date on a specified date/time.
    • Date/Time Parameter: 04/16/2014 02:30:00 PM
    • Filter: ([EntryDate] is on #2014-04-06 02:30:00 PM#)

Is Not On (<>)

  • Date field
    • Example: Give me all records that have an Actual Start Date NOT on a specified date.
    • Date Parameter: 04/16/2014
    • Filter: ([StartDateActual] is not on #2014-04-16#)
    • This also returns any records where the date field is null.
  • Date/Time field
    • Example: Give me all records that have an Entry Date NOT on a specified date/time.
    • Date/Time Parameter: 04/16/2014 02:30:00 PM
    • Filter: ([EntryDate] is not on #2014-04-06 02:30:00 PM#)
    • This also returns any records where the date/time field is null.

Is Before (<)

  • Date field
    • Example: Give me all records that have an Actual Start Date before a specified date (not including that date).
    • Date Parameter: 04/16/2014
    • Filter: ([StartDateActual] is before #2014-04-06#)
    • This also returns any records where the date field is null.
  • Date/Time field
    • Example: Give me all records that have an Entry Date before a specified date/time (not including that date/time).
    • Date/Time Parameter: 04/16/2014 02:30:00 PM
    • Filter: ([EntryDate] is before #2014-04-06 02:30:00 PM#)
    • This also returns any records where the date/time field is null.

Is After (>)

  • Date field
    • Example: Give me all records that have an Actual Start Date after a specified date (not including that date).
    • Date Parameter: 04/16/2014
    • Filter: ([StartDateActual] is after #2014-04-06#)
  • Date/Time field
    • Example: Give me all records that have an Entry Date after a specified date/time (not including that date/time).
    • Date/Time Parameter: 04/16/2014 02:30:00 PM
    • Filter: ([EntryDate] is after #04/16/2014 02:30:00 PM#)

Is From (>=)

  • Date field
    • Example: Give me all records that have an Actual Start Date from a specified date and onward (including that date).
    • Date Parameter: 04/17/2014
    • Filter: ([StartDateActual] is from #04/17/2014#)
  • Date/Time field
    • Example: Give me all records that have an Entry Date from a specified date/time and onward (including that date/time).
    • Date/Time Parameter: 04/17/2014 02:30:00 PM
    • Filter: ([EntryDate] is after #2014-04-06 02:30:00 PM#)

Is Through (<=)

  • Date field
    • Example: Give me all records that have an Actual Start Date prior to and up through a specified date (including that date).
    • Date Parameter: 04/17/2014
    • Filter: ([EntryDate] is from #2014-04-17 02:30:00 PM#)
    • This also returns any records where the date field is null.
  • Date/Time field
    • Example: Give me all records that have an Entry Date prior to and up through a specified date/time (including that date/time).
    • Date/Time Parameter: 04/17/2014 02:30:00 PM
    • Filter: ([EntryDate] is through #2014-04-17 02:30:00 PM#)
    • This also returns any records where the date/time field is null.

Is Within Last [] Days

  • Example: Give me all records that have an Entry Date within the last 10 days.
  • Filter: ([EntryDate] is within last [] days 10)

Is Within Last [] Months

  • Example: Give me all records that have an Entry Date within the last 6 months.
  • Filter: ([EntryDate] is within last [] months 6)

Is Within Last [] Years

  • Example: Give me all records that have an Entry Date within the last 2 years.
  • Filter: ([EntryDate] is within last [] years 2)

Is Within Next [] Days

  • Example: Give me all records that have an Actual Start Date within the next 10 days.
  • Filter: ([StartDateActual] is within next [] days 10)

Is Within Next [] Months

  • Example: Give me all records that have an Actual Start Date within the next 6 months.
  • Filter: ([StartDateActual] is within next [] months 6)

Is Within Next [] Years

  • Example: Give me all records that have an Actual Start Date within the next 2 years.
  • Filter: ([StartDateActual] is within next [] years 2)

Is Not Within Last [] Days

  • Example: Give me all records that have an Entry Date NOT within the last 10 days.
  • Filter: ([EntryDate] is not within last [] days 10)
  • This also returns any records where the date or date/time field is null.

Is Not Within Last [] Months

  • Example: Give me all records that have an Entry Date NOT within the last 6 months.
  • Filter: ([EntryDate] is not within last [] months 6)
  • This also returns any records where the date or date/time field is null.

Is Not Within Last [] Years

  • Example: Give me all records that have an Entry Date NOT within the last 2 years.
  • Filter: ([EntryDate] is not within last [] years 2)
  • This also returns any records where the date or date/time field is null.

Is Not Within Next [] Days

  • Example: Give me all records that have an Actual Start Date NOT within the next 10 days.
  • Filter: ([StartDateActual] is not within next [] days 10)
  • This also returns any records where the date or date/time field is null.

Is Not Within Next [] Months

  • Example: Give me all records that have an Actual Start Date NOT within the next 6 months.
  • Filter: ([StartDateActual] is not within next [] months 6)
  • This also returns any records where the date or date/time field is null.

Is Not Within Next [] Years

  • Example: Give me all records that have an Actual Start Date NOT within the next 2 years.
  • Filter: ([StartDateActual] is not within next [] years 2)
  • This also returns any records where the date or date/time field is null.

Is Today

  • Example: Give me all records that have an Entry Date of today (the current date).
  • Filter: ([EntryDate] is today)

Is Not Today

  • Example: Give me all records that have an Entry Date that is NOT today.
  • Filter: ([EntryDate] is not today)
  • This also returns any records where the date or date/time field is null.

Is Before Today

  • Example: Give me all records that have an Entry Date before today.
  • Filter: ([EntryDate] is before today)
  • This also returns any records where the date or date/time field is null.

Is Before [] Days Ago

  • Example: Give me all records that have an Actual Start Date before 7 days ago (that is, any records prior to one week before the current date - not including the date from a week ago).
  • Filter: ([StartDateActual] is before [] days ago 7)

Is Before [] Days From Now

  • Example: Give me all records that have an Actual Stop Date before 7 days from now (that is, any records prior to one week after the current date - not including the date from a week ahead).
  • Filter: ([StopDateActual] is before [] days from now 7)

Is Before [] Months Ago

  • Example: Give me all records that have an Actual Start Date before 2 months ago (that is, any records prior to 2 months before the current date - not including the date from 2 months ago).
  • Filter: ([StartDateActual] is before [] months ago 2)

Is Before [] Months From Now

  • Example: Give me all records that have an Actual Stop Date before 2 months from now (that is, any records prior to 2 months after the current date - not including the date from 2 months ahead).
  • Filter: ([StopDateActual] is before [] months from now 2)

Is Before [] Years Ago

  • Example: Give me all records that have an Actual Start Date before 1 year ago (that is, any records prior to 1 year before the current date - not including the date from 1 year ago).
  • Filter: ([StartDateActual] is before [] years ago 1)

Is Before [] Years From Now

  • Example: Give me all records that have an Actual Stop Date before 1 year from now (that is, any records prior to 1 year after the current date - not including the date from a year ahead).
  • Filter: ([StopDateActual] is before [] years from now 1)

Is After Today

  • Example: Give me all records that have an Entry Date after today.
  • Filter: ([EntryDate] is after today)

Is After [] Days Ago

  • Example: Give me all records that have an Actual Start Date after 7 days ago (that is, any records after a week before the current date - not including the date from a week ago).
  • Filter: ([StartDateActual] is after [] days ago 7)

Is After [] Days From Now

  • Example: Give me all records that have an Estimated Start Date after 7 days from now (that is, any records after a week from the current date - not including the date from a week ahead).
  • Filter: ([StartDateEstimated] is after [] days from now 7)

Is After [] Months Ago

  • Example: Give me all records that have an Actual Start Date after 2 months ago (that is, any records after 2 months before the current date - not including the date from 2 months ago).
  • Filter: ([StartDateActual] is after [] months ago 2)

Is After [] Months From Now

  • Example: Give me all records that have an Estimated Start Date after 2 months from now (that is, any records after 2 months from the current date - not including the date from 2 months ahead).
  • Filter: ([StartDateEstimated] is after [] months from now 2)

Is After [] Years Ago

  • Example: Give me all records that have an Actual Start Date after 1 year ago (that is, any records after 1 year before the current date - not including the date from a year ago).
  • Filter: ([StartDateActual] is after [] years ago 1)

Is After [] Years From Now

  • Example: Give me all records that have an Estimated Start Date after 1 year from now (that is, any records after 1 year from the current date - not including the date from a year ahead).
  • Filter: ([StartDateEstimated] is after [] years from now 1)

Is From Today

  • Example: Give me all records that have an Actual Start Date from today onward (including today).
  • Filter: ([StartDateActual] is from today)

Is Through Today

  • Example: Give me all records that have an Entry Date that is through today (including today).
  • Filter: ([EntryDate] is through today) 
  • This also returns any records where the date or date/time field is null.

Is Null (= null)

  • Example: Give me all records that have an Entry Date that is null (that is, no value at all).
  • Filter: ([EntryDate] is null)
  • This operator is displayed in Cartegraph filtering field as 'Is Empty (Is Null)', but in syntax view 'is null' or '= null' should be used.

Is Not Null (<> null)

  • Example: Give me all records that have an Entry Date that is NOT null (that is, has some sort of value).
  • Filter: ([EntryDate] is not null)
  • This operator is displayed in Cartegraph filtering field as 'Is Not Empty (Is Not Null)', but in syntax view 'is not null' or '<> null' should be used.

Time Fields

The expression expects all Time values in the database to have 1/1/1990 set as the Date part of the DateTime field; otherwise your returned results may not be as expected.

is during (=)

  • Example: Give me all records that have a Time Started at noon.
  • Filter: ([TimeStarted] is during #12:00#)
  • This operator displays in Cartegraph filtering as Is A', but in syntax view is during or = should be used.

<>

  • Example: Give me all records that have a Time Started NOT at a specified time.
  • Filter: ([TimeStarted] <> #12:00#)
  • This also returns any records where the date field is null.

Is Before (<)

  • Example: Give me all records that have a Time Started before noon.
  • Filter: ([TimeStarted] is before #12:00#)

Is Before [] Hours Ago

  • Example: Give me all records that have a Time Started before 12 hours ago (that is, any records prior to 12 hours before the current time - not including the exact time 12 hours before). So, 12 hours before 2:35:12 PM on 4/21 would give back all records from 2:35:11 AM on 4/21 and before.
  • Filter: ([TimeStarted] is before [] hours ago 12) 

Is Before [] Hours From Now

  • Example: Give me all records that have a Time Started before 12 hours from now (that is, any records prior to 12 hours after the current time - not including the exact time 12 hours later). So, 12 hours from 2:35:12 PM on 4/21 would give back all records from 2:32:12 AM on 4/22 and before.
  • Filter: ([TimeStarted] is before [] hours from now 12)

Is After (>)

  • Example: Give me all records that have a Time Started after noon.
  • Filter: ([TimeStarted] is after #12:00#)

Is After [] Hours Ago

  • Example: Give me all records that have a Time Started after 12 hours ago (that is, any records after 12 hours before the current time - not including the exact time 12 hours before).
  • Filter: ([TimeStarted] is after [] hours ago 12)

Is After [] Hours From Now

  • Example: Give me all records that have a Time Started after 12 hours from now (that is, any records after 12 hours from the current time - not including the exact time 12 hours ahead).
  • Filter: ([TimeStarted] is after [] hours from now 12

Is From (>=)

  • Example: Give me all records that have a Time Started on or after noon.
  • Filter: ([TimeStarted] is from #12:00#)

Is Through (<=)

  • Example: Give me all records that have a Time Started prior to and up through a specified time (including that time).
  • Filter: ([TimeStarted] is through #12:00#)
  • This also returns any records where the time field is null.

Is Now

  • Example: Give me all records that have a Time Started that are now (this strips the minutes and seconds, so if the current time is 10:46:31 AM, it would compare against 10:00:00 AM).
  • Filter: ([TimeStarted] is now)

Is Not Now

  • Example: Give me all records that have a Time Started that are NOT now (this strips the minutes and seconds, so if the current time is 10:46:31 AM, it would compare against 10:00:00 AM).
  • Filter: ([TimeStarted] is not now)
  • This also returns any records where the time field is null.

Is Null (= null)

  • Example: Give me all records that have a Time Started that is null (that is, no value at all).
  • Filter: ([TimeStarted] is null)
  • This operator is displayed in Cartegraph filtering fields as 'Is Empty (Is Null)', but in syntax view 'is null' or '= null' should be used.

Is Not Null (<> null)

  • Example: Give me all records that have a Time Started that is NOT null (that is, has some sort of value).
  • Filter: ([TimeStarted] is not null)
  • This operator is displayed in Cartegraph filtering fields as 'Is Not Empty (Is Not Null)', but in syntax view 'is not null' or '<> null' should be used.

Text, Lookup, Quantity.unit and Attachment Fields

Is Equal To (=)

  • Example: Give me all records where Height.unit is equal to "ft".
  • Filter: ([Height.unit] is equal to "ft")
  • This operator is displayed in Cartegraph filtering fields as 'Is ', but in syntax view 'is equal to' or '=' should be used.

Is Not Equal To (<>)

  • Example: Give me all records where Height.unit is NOT equal to "ft".
  • Filter: ([Height.unit] is not equal to "ft")
  • This also returns any records where the text, lookup, quantity.unit, or attachment field is null.
  • This operator is displayed in Cartegraph filtering fields as 'Is Not ', but in syntax view 'is not equal to' or '<>' should be used.

Contains

  • Example: Give me all records that have a Street that contains "RD".
  • Filter: ([Street] contains "RD")

Does Not Contain

  • Example: Give me all records that have a Street that does NOT contain "RD".
  • Filter: ([Street] does not contain "RD")

Does Not End With

  • Example: Give me all records that have a Street that does NOT end with "AVE".
  • Filter: ([Street] does not end with "AVE")

Does Not Start With

  • Example: Give me all records that have a Street that does NOT start with "MAIN".
  • Filter: ([Street] does not start with "MAIN")

Ends With

  • Example: Give me all records that have a Street that ends with "AVE".
  • Filter: ([Street] ends with "AVE")

Is Greater Than (>)

  • Example: Give me all records that have an MUTCD Code value greater than "R1-1" (that is, records alphanumerically "R1-1" through "Z" - not including "R1-1").
  • Filter: ([MUTCDCode] > "R1-1")

Is Greater Than or Equal To (>=)

  • Example: Give me all records that have an MUTCD Code value greater than or equal to "R1-1" (that is, records alphanumerically "R1-1" through "Z" - including "R1-1").
  • Filter: ([MUTCDCode] >= "R1-1")

Is Less Than (<)

  • Ex: Give me all records that have an MUTCD Code value less than "R1-1" (that is, records alphanumerically "A" through "R1-1" - not including "R1-1").
  • Filter: ([MUTCDCode] < "R1-1")
  • This also returns any records where the text, lookup, quantity.unit, or attachment field is null.

Is Less Than or Equal To (<=)

  • Example: Give me all records that have an MUTCD Code value less than or equal to "R1-1" (that is, records alphanumerically "A" through "R1-1" - including "R1-1").
  • Filter: ([MUTCDCode] <= "R1-1")
  • This also returns any records where the text, lookup, quantity.unit, or attachment field is null.

Starts With

  • Example: Give me all records that have a Street that starts with "MAIN".
  • Filter: ([Street] starts with "MAIN")

Is Null (= null)

  • Example: Give me all records that have a Street that is null (that is, no value at all).
  • Filter: ([Street] is null)
  • This operator is displayed in Cartegraph filtering fields as 'Is Empty (Is Null)', but in syntax view 'is null' or '= null' should be used.

Is Not Null (<> null)

  • Example: Give me all records that have a Street that is NOT null (that is, has some sort of value).
  • Filter: ([Street] is not null)
  • This operator is displayed in Cartegraph filtering fields as 'Is Not Empty (Is Not Null)', but in syntax view 'is not null' or '<> null' should be used.

Currency, Number, Integer, and Quantity.amount fields

Numeric values can contain the decimal separator but should never include the currency symbol ($) or thousand separator (,).

=

  • Example: Give me all records that have a Total Cost equal to $5,000.
  • Filter: ([TotalCostActual] = 5000)
  • This operator is displayed in Cartegraph filtering fields as 'Is (=)', but in syntax view '=' should be used.

Is Not (<>)

  • Example: Give me all records that have a Total Cost NOT equal to $5,000.
  • Filter: ([TotalCostActual] <> 5000)
  • This also returns any records where the currency, number, integer, or quantity.amount field is null.
  • This operator is displayed in Cartegraph filtering fields as 'Is Not', but in syntax view '<>' should be used.

>

  • Example: Give me all records that have Labor Hours greater than 8.
  • Filter: ([LaborHoursActual] > 8)
  • This operator is displayed in Cartegraph filtering fields as 'Is Greater Than (>)', but in syntax view '>' should be used.

<

  • Example: Give me all records that have Labor Hours less than 8.
  • Filter: ([LaborHoursActual] < 8)
  • This also returns any records where the currency, number, integer, or quantity.amount field is null.
  • This operator is displayed in Cartegraph filtering fields as 'Is Less Than (<)', but in syntax view '<' should be used.

>=

  • Example: Give me all records that have Calls greater than or equal to 3.
  • Filter: ([Calls] >= 3)
  • This operator is displayed in Cartegraph filtering fields as 'Is Greater Than Or Equals (>=)', but in syntax view '>=' should be used.

<=

  • Example: Give me all records that have Calls less than or equal to 3.
  • Filter: ([Calls] <= 3)
  • This also returns any records where the currency, number, integer, or quantity.amount field is null.
  • This operator is displayed in Cartegraph filtering fields as 'Is Less Than Or Equals (<=)', but in syntax view '<=' should be used.

Is Null (= null)

  • Example: Give me all records that Units Completed.amount is null (that is, no value at all).
  • Filter: ([UnitsCompleted.amount] is null)
  • This operator is displayed in Cartegraph filtering fields as 'Is Empty (Is Null)', but in syntax view 'is null' or '= null' should be used.

Is Not Null (<> null)

  • Example: Give me all records that Units Completed.amount is NOT null (that is, has some sort of value).
  • Filter: ([UnitsCompleted.amount] is not null)
  • This operator is displayed in Cartegraph filtering fields as 'Is Not Empty (Is Not Null)', but in syntax view 'is not null' or '<> null' should be used.

Quantity Fields

ConvUnit([Length], "ft")

  • Example: Convert the Length value to "ft"
  • The left expression, [Length]) must have a valid unit and the 'right' unit, ft, but also be a valid unit.
  • Exception is thrown is either Units are invalid or the units can be converted to each other (i.e. can't convert 'hr' to 'ft'

Combining Statements with AND, OR, and NOT

Use the Syntax View, you are able to combine various statements together using the AND and OR keywords.

AND

  • Example: Give me all signs where MUTCD Code is R1-1 (stops signs) AND Sheeting Material is high intensity grade sheeting material.
  • Filter: ([MUTCDCode] is equal to "R1-1") AND ([SheetingMaterial] is equal to "High Intensity Grade")
  • Example: Give me all pavement records where Pavement Classification is Asphalt AND Estimated OCI is less than 50.
  • Filter: ([PavementClassification] is equal to "Asphalt") AND ([EstimatedOCI] < 50)
  • Example: Give me all picnic tables where ID starts with 2 AND Street is Black Walnut and Inactive is false.
  • Filter: ([ID] starts with "2") AND ([Street] is equal to "BLACK WALNUT") AND ([Inactive] = false)

OR

  • Example: Give me all signs where MUTCD Code is R1-1 (stop signs) OR MUTCD Code is W4-1 (merging traffic).
  • Filter: ([MUTCDCode] is equal to "R1-1") OR ([MUTCDCode] is equal to "W4-1")
  • Example: Give me all pavement records where Number of Lanes is 2 OR Number of Lanes is 4.
  • Filter: ([NumberofLanes] = 2) OR ([NumberofLanes] = 4)
  • Example: Give me all trash cans where Street is Main St. OR Street is 3rd St. OR Locator Street is Main St. OR Locator Street is 3rd St.
  • Filter: ([Street] is equal to "MAIN ST") OR ([Street] is equal to "03RD ST") OR ([LocatorStreet] is equal to "MAIN ST") OR ([LocatorStreet] is equal to "03RD St")

AND/OR

  • Example:  Give me all signs where City is Dubuque AND Backing Material is either Aluminum OR Backing Material is Embossed Aluminum
  • Filter: ([City] is equal to "Dubuque") AND ([BackingMaterial] is equal to "Aluminum") OR ([BackingMaterial] is equal to "Embossed Aluminum")
  • Statements inside of parentheses take a higher precedence (standard order of operations), otherwise ANDs and ORs are simply evaluated left to right

NOT

  • Previously undocumented
  • Using the Syntax View, the user is able to negate an entire statement using the NOT keyword
  • Example: Give me all signs where the MUTCD Code is not equal to R1-1 (stop signs)
  • Filter: NOT ([MUTCDCode] = "R1-1")

SQL Functions

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: tucker (name of windows workstation user). This function is often used to provide default values on a form.

  • User()
    • Returns the Cartegraph user name for the individual currently logged into Cartegraph.
    • User()
      • Results: Name of the Cartegraph user. This function is often used to provide default values and for archive records.
  • 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.
    • This is new for Cartegraph (previously undocumented)
  • Sum, Avg, WinUser, GetUnit, and GetAmount
    • No longer exist… Remove documentation
  • Math Functions (Add, Subtract, Multiply, Divide)
    • Now 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.
      • Ex: "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)
  • If Then Else and IIF
    • If Then Else was previously undocumented, but it works the same as IIF
      • 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 now support having no else case as well
        • (IF ([MUTCDCode] = "R1-1") THEN 75 END_IF < [EstimatedOCI])
        • (IIF(([MUTCDCode] = "R1-1"), 75) < [EstimatedOCI])
  • DateDiff
    • 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/l.../ms189794.aspx
      • For instance: DateDiff( "Day", #1/1/2014 23:59:59.999#, #1/2/2014 00:00:00.000# )
      • This will yield 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.
      • Hence, 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.
  • Suggest rewording the datepart units as follows:
    • 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
  • IN
    • Previously undocumented
    • Using the Syntax View, the user is able to verify that an expression is contained within a subset of values
    • No longer case sensitive
    • Ex: 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
    • Works the same way SQL LIKE does
    • % and _ characters are wildcards (same as SQL LIKE)
    • Ex: 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
    • Ex: 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)
    • Ex: Give me all records where there is NO Inspection record with an Inspected OCI greater than 75.
    • Filter: (Count([cgInspections\InspectedOCI] > 75) = 0)

Miscellaneous differences from Navigator functionality:

  • Field level validation will be run on every field, not just the ones you change.
    • Consider this rule on the City field: ([City] <> "Dyersville"), but I had an existing record with that city when I created the rule
      • In Navigator, you could still save the invalid record, as long as you did not dirty the city field.
      • In Cartegraph, you cannot save the record at all, because ALL field validation rules will fire, not just the ones for dirty fields.

 

  • Was this article helpful?