Cumulus Query Format

A Cumulus Search Query consists of a sequence of conditions or subqueries combined using boolean operators.
A condition consists of a field descriptor, a comparison operator and (depending on the comparison) a value to compare to.

General Rules

 

Matching Names

All names are matched ignoring the case.
For example you can type AND to combine two conditions.

 

Special Characters in Names

You can escape any character that might have a special meaning for the query parser by preceding it with a backslash (\).
For example you can type Record\ Name as a field descriptor instead of using quotes.

Language Independent Queries

If you want to create queries that are independent of the language you can create it using the following rules:

 

  • Use field unique identifiers in field descriptors.
  • Use symbols for the comparison and combining operators.
  • Use string list value IDs for the comparison value for string list fields.
  • Use the language independent date and time format when searching for fields of type "Date."
  • Query Format

    The query has the following formal format definition:
    Query:
    	Condition
    	( Query )
    	Query Combine Query
    
    Condition:
    	FieldDescriptor UnaryComparison
    	FieldDescriptor BinaryComparison Value
    	
    Combine:
    	and
    	&&
    	or
    	||
    
    FieldDescriptor:
    	FieldUID
    	StringValue
    	"[FieldDescriptor/]FieldUID:String"
    	"[FieldDescriptor/]FieldUID"
    	"[FieldDescriptor/]String"
    	'[FieldDescriptor/]FieldUID:String'
    	'[FieldDescriptor/]FieldUID'
    	'[FieldDescriptor/]String'
    	[FieldDescriptor/]FieldUID:String_without_Spaces_and_Parentheses
    
    FieldUID:
    	{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}
    
    UnaryComparison:
    	"has value"
    	*
    	"has no value"
    	!*
    	
    BinaryComparison:
    	is
    	==
    	"is not"
    	!=
    	contains
    	?
    	"doesn't cont."
    	!?
    	"starts with"
    	^
    	"does not start with"
    	!^
    	prior to
    	under
    	<
    	up to
    	<=
    	from
    	>=
    	over
    	after
    	>
    	similar to
    	~
    	not similar to
    	!~
    	
    Value:
    	StringValue
    	Number
    	DateValue
    	DateOnlyValue
    	TimeOnlyValue
    	BooleanValue
    	StringListValue
    	ThumbnailValue
    	
    StringValue:
    	"String"
    	'String'
    	String_without_Spaces_and_Parentheses
    
    DateValue:
    	Date
    	"Date Time"
    	'Date Time'
    	
    DateOnlyValue:
    	Number-Number-Number
    	Date
    
    TimeOnlyValue:
    	Number:Number:Number.Number
    	Time
    
    BooleanValue:
    	true
    	false
    
    StringListValue:
    	ID
    	"ID:String"
    	'ID:String'
    	ID:String_without_Spaces_and_Parentheses
    	StringValue
    
    CategoriesValue:
    	StringValue
    	":CategoriesOptions:String"
    
    CategoriesOptions:
    	CategoriesOption
    	CategoriesOption CategoriesOptions
    
    CategoriesOption:
    	A
    	B
    	R
    	CategoryID
    
    ThumbnailValue:
    	StringValue
    	"String:Number:Number"
    	'String:Number:Number'
    	"String_without_Spaces_and_Parentheses:Number:Number"
    
    

    Query

    A query is a sequence of simple conditions or sub-queries surrounded by parentheses.
    The sequence is evaluated left to right combining each condition result with the result of the previous conditions using the combining operator.

      For example a sequence of

    	A and B or C and D
    
    is evaluated as
    	(((A and B) or C) and D)
    

      If you want to enforce a certain precedence you can group parts of the sequence using parentheses, e.g.

    	(A and B) or (C and D)
    

      Here are some examples of valid queries:

     
     "Record Name" contains "JPG" and Status is Final
     Categories is Job-0012 and ("File Format" is "TIFF Image" or "File Format" is "JPEG Image")

    Combining Boolean Operator

    You can combine different conditions or sub-queries using either and or or. The boolean operator can be specified either in any of the supported languages or by using the language independent symbol.

     
    Combining Binary Operator Symbol
    and &&
    or ||

    Condition

    A condition consists a field descriptor, a comparison operator and an optional value depending on the comparison. If either of these contain spaces or parentheses you need to escape these characters or surround the item using single or double quotes.

      The comparison operator that can be used depends on the type and the indexing settings of the field.
    The comparison operators has value and has no value do not need any value to compare to. All other comparisons need a value to compare to.

      Here are some examples of valid conditions:

     
     "Record Name" is "DCS0001.JPG"Search for specific record by name
     Color\ Mode is "Grayscale"Find all grayscale images. This one uses space escaping and quotes.
     Status "has no value"Return all record that have no Status assigned.

    Field Descriptor

    The field descriptor is either a field unique ID (e.g. {af4b2e00-5f6a-11d2-8f20-0000c0e166dc} for the field "Record Name") or a field name in any of the languages available (e.g. Datensatzname for the field "Record Name") or a combination of a unique ID and a name separated by a colon (:). If you want to search using a sub-table field you specify the field starting with the table name of the sub-table separated by "/". The query can use the built in field name "ID" to search for a specific record or category by its ID. If the field descriptor contains any white space or parenthesis you need to surround it with single quotes or double quotes. When parsing te query the unique ID is checked first and the name is only evaluated if the catalog does not have a field of the given ID. When matching the name the preferred language is checked first.

      Here are some examples of valid field descriptors for the field "Record Name":

     
     "Record Name"Given by English Name. Quotes used because it contains a space.
     Record\ NameGiven by English Name escaping the space.
     {af4b2e00-5f6a-11d2-8f20-0000c0e166dc}Given by unique ID.
     {af4b2e00-5f6a-11d2-8f20-0000c0e166dc}:DatensatznameGiven by unique ID and German Name.
     "{af4b2e00-5f6a-11d2-8f20-0000c0e166dc}:Record Name"Given by unique ID and English Name. Quotes used because it contains a space.
     IDThe built-in ID of the record or category.
     "Asset Usage History/Date"Given by table field name and name.
     "{a89b908c-e98e-413b-ac4d-50668b2fafbc}:Asset Usage History/{8118abe0-ab62-11d6-b68e-0050baeba6c7}:Date"Given by unique ID and name for table field and and the field itself.

    Comparison Operator

    The comparison operator is the way the field value is compared to the value in the query. You can use the same comparison operator strings you see in the find window in Cumulus in any language.

    Instead of the language dependent operators you can also use the following symbols:

    Comparison Operator Symbol String Integer Real Date Date Only Time Only Boolean String List Categories Thumbnail
    has value *  
    has no value !*  
    starts with ^                  
    does not start with !^                  
    is ==  
    is not !=  
    contains ? 1)             2)  
    doesn't cont. !? 1)             2)  
    under <            
    prior to <                  
    up to <=          
    from >=          
    over >            
    after >                  
    similar to ~                  
    not similar to !~                  
    1)Available for fields that are marked to be indexed for content or word searching.
    If the value is surrounded by single quotes and the field is indexed for word searching Cumulus will search by full words.

    2)Available for fields that are set to multiple values.


    Here are some examples of valid comparison operators:

     "has value"Given by English name. Quotes used because it contains a space
     !=Given by language independent symbol (same as "is not")
     istGiven by German name.

    Comparison Value

    Following the comparison operator except has value and has no value is the value to compare to.

    If the value contains white space, quotes or parentheses you need to surround the value string with single or double quotes.

    String Values

    A comparison value for a field of type "String" is either a literal string or an expression following the syntax known from the Cumulus formula fields surrounded with "${" and "}".
    You can use any function in this expression except for the ones accessing fields of an asset record. The function to access a user context field value (e.g. "userFieldValue" function) are available.

    Here are some examples of valid comparison values:

     "image"The literal string image
     "${userFieldValue('Login Name')}"The login name of the current user
     "${userFieldValue('User Unique ID')}"The unique ID of the current user as it is stored in the "Asset Usage History" field "User"
     "${'${mystring}'}"The literal string "${mystring}" specified as a string expression to escape a literal string that matches the expression syntax

    Date Values

    A comparison value for a field of type "Date" is either a date string or a date string followed by a time string separated by space.
    If only a date string is specified Cumulus matches the whole day (time values between 00:00:00 and 23:59:59).

    Cumulus first tries to read the date and time strings using the following language-independent format:

    	YYYY-MM-DD hh:mm:ss
    
    The hours are given in a 24 hour cycle.

    If the date value can not be converted using the format above Cumulus tries to convert it using operating system functions with the current locale setting.

    Cumulus 8.5.1 introduced a very flexible way to specify a comparison value using the formula syntax and functions known for Cumulus formula fields.
    In this case the date comparison value is a string that surrounds the expression with "${" and "}", e.g. "${startofday() - days(7)}"

    Cumulus 7 introduces two placeholders for date searches. Each placeholder is optionally followed by an offset specifier that allows to modify the date. The offset specifier immediately follows the placeholder name and starts with a '+' or '-' followed by a series of numbers with units. If no unit is specified each placeholder assumes a default unit. Possible units are
     dThe number is given in days (86,400 seconds).
     hThe number is given in hours (3,600 seconds).
     mThe number is given in minutes (60 seconds).
     sThe number is given in seconds.

     $todayThe current day. This placeholder is replaced with the current day without any given time which will match all date values between 00:00:00 and 23:59:59.
    The default unit id d (days)
     $nowThe current time. This placeholder is replaced with the current day and time.
    The default unit id s (seconds)

    You can concatenate numbers with different units to form an offset.

    Here are some examples of valid comparison values:

     2003-12-24December, 24th, 2003
     "2003-12-24 15:30:00"December, 24th, 2003, 3:30 PM
     "10/31/99 8:23 AM"October, 31st, 1999
     "${startofyear() - years(1)}"January, 1st, 00:00:00 of last year (the one before the current year)
     "${userFieldValue('Password Modification Date')}"The date and time the current user's password has been changed
     $todayAll date values of today between 00:00:00 and 23:59:59
     $today-1All date values of yesterday between 00:00:00 and 23:59:59 (same as $today-1d)
     $now+1hOne hour after the current date and time
     $now-1d12hOne day and 12 hours (36 hours) before the current date and time

    Date Only Values

    A comparison value for a field of type "Date Only" can specify a certain day, month or year. Cumulus first tries to evaluate the value using the following language independent format:

     YYYY-MM-DDA specific day
     YYYY-MM-0A whole month
     YYYY-0-0A whole year

    If the day is zero the value specifies a month, if both month and day are zero it specifies the given year.

    If The value string does not match the above format Cumulus uses a format that preserves the order of the current locale settings but always uses '/' as the separator. Here the day and month are omitted to specify a whole month or year.

    Here are some examples of valid values:

     2004-01-01January, 1st, 2004
     1999-06-0June 1999
     2000-0-0The whole year of 2000
     12/2000December 2000
     10/12/2000December, 10th, 2000 for the French locale
     10/12/2000October, 12th, 2000 for the US locale

     

    Time Only Values

    A comparison value for a field of type "Time Only" can specify a certain number of milliseconds. The format of this value is:

     
     hh:mm:ssGiven as hours, minutes and seconds
     hh:mm:ss.sGiven as hours, minutes, seconds, and milliseconds

      Here are some examples of valid values:

     
     0:0:1717 seconds
     2:15:002 hours and 15 minutes
     0:00:05.3005 seconds and 300 milliseconds

    String List Values

    A comparison value for a field of type "String List" is either consists of the value ID, the value name in any language or a combination of a value ID and a value name separated by colon (:).

      Here are some examples of valid values for the "Color Mode" field:
     :ID:2Given by id 2
     GrayscaleGiven by English name
     1Given by name or by ID
     1:GraubildGiven name or by ID and German name
     "1:Niveaux de gris"Given by name or by ID and French name. Quotes used because it contains spaces

    Categories

    A comparison value for the "Categories" field is typically the category name. You may specify additional options (including a query on the Categories table instead of using a category name) using the following format:
    	:Options:Category
    	:Options:[Query[Categories Query]]
    
    Where the Category is either a category name or full path and Options is a set of the following optional strings:

     
     UUse the user's preference settings for category searches.
     AFind also records assigned to the categories above the one specified.
     BFind also records assigned to the categories below the one specified.
     RWhen searching below: resolve related categories and also find records assigned to the original.
     OWhen searching for multiple categories find records that are assigned to either of the given categories. If this flag is missing only records being assigned to all of the categories are found.
     Within the options you can also specify one or more category IDs that the records should match when you do not search for "contains." If your query is executed on multiple catalogs the category need to be preceded by a catalog ID. A catalog ID is specified with a leading C character. All category IDs following the catalog ID are assumed to be of that given catalog.
     The category name or path is ignored when you specify a category id.
     If the category name part starts with [Query[ and ends with ]] everything in between is treated as a query string on the Categories table.
    This enables one to write a query which dynamically builds a category list which is then used to find assigned categories.
     The category name or path is ignored when you specify a category id.

    Here are some examples of valid values:
     ImagesFind all records assigned to the category named "Images". Cumulus takes the first category of that name
     "Top Level:Images"Find all records assigned to category "Images" of the top-level category "Top Level".
     :100:Find all records assigned to category ID 100.
     ":B 200:"Find all records assigned to category ID 200 or one of the sub-categories of it.
     :ABR:ImagesFind all records assigned to category "Images" or one of the sub-categories or super-categories.
     :O C1 200 300 C7 400:Find all records assigned to category ID 200 or 300 of catalog with ID 1 or assigned to category ID 400 of the catalog with ID 7.
     :BO:$Categories:*:ExcelFinds all records which are assigned to at least one category named "Excel" (or one of its child categories) which itself is a sub-category of the $Categories category. The :*: is a placeholder for multiple categories between the category tree path before the :*: element and the wanted category name behind it. Therefore the given example would find all of these categories: $Categories:Excel, $Categories:SomeSubCategory:Excel, $Categories:SomeSubCategory:SomeAdditionalSubCategory:Excel
     :BO:[Query[(\"{4feba972-1695-11d4-9053-0080ad80c556}:Job ID\" == \"JobID 4711\" || \"{c02adb31-5c2c-4014-b86a-a53cf83f7e6c}:Internal ID\" == 144)]]Finds all records which are assigned to at least one of the categories (or one of the child categories) found by the given query. Please note the escaped double quotes within the query as the entire options block needs to be surrounded by double quotes as well. The example shows the usage of two fields called "Job ID" and "Internal ID" which must exist on the Categories table.