Cumulus Query Format

General Rules

Matching Names

Special Characters in Names

Language Independent Queries

• 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

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
	VocabularyValue
	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
	O
	U
	CategoryID

VocabularyValue:
	ID
	StringValue

ThumbnailValue:
	StringValue
	"String:Number:Number"
	'String:Number:Number'
	"String_without_Spaces_and_Parentheses:Number:Number"

Query

For example a sequence of

	A and B or C and D

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

Combining Binary Operator	Symbol
and	&&
or	||

Condition

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

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\ Name	Given by English Name escaping the space.
	{af4b2e00-5f6a-11d2-8f20-0000c0e166dc}	Given by unique ID.
	{af4b2e00-5f6a-11d2-8f20-0000c0e166dc}:Datensatzname	Given 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.
	ID	The 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

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")
	ist	Given by German name.

Comparison Value

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

String Values

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

Cumulus first tries to read the date and time strings using the full ISO8601 format:

	yyyy-MM-dd'T'HH:mm:ssZ

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

	d	The number is given in days (86,400 seconds).
	h	The number is given in hours (3,600 seconds).
	m	The number is given in minutes (60 seconds).
	s	The number is given in seconds.

	$today	The 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)
	$now	The 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:

	2015-12-24	December, 24th, 2015
	2015-12-24T15:30:00+0100	December, 24th, 2003, 3:30 PM in the time zone that is 1 hour ahead of GMT.
	"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
	$today	All date values of today between 00:00:00 and 23:59:59
	$today-1	All date values of yesterday between 00:00:00 and 23:59:59 (same as $today-1d)
	$now+1h	One hour after the current date and time
	$now-1d12h	One day and 12 hours (36 hours) before the current date and time

Date Only Values

	YYYY-MM-DD	A specific day
	YYYY-MM-0	A whole month
	YYYY-0-0	A 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-01	January, 1st, 2004
	1999-06-0	June 1999
	2000-0-0	The whole year of 2000
	12/2000	December 2000
	10/12/2000	December, 10th, 2000 for the French locale
	10/12/2000	October, 12th, 2000 for the US locale

Time Only Values

	hh:mm:ss	Given as hours, minutes and seconds
	hh:mm:ss.s	Given as hours, minutes, seconds, and milliseconds

Here are some examples of valid values:

	0:0:17	17 seconds
	2:15:00	2 hours and 15 minutes
	0:00:05.300	5 seconds and 300 milliseconds

String List Values

Here are some examples of valid values for the "Color Mode" field:

	:ID:2	Given by id 2
	Grayscale	Given by English name
	1	Given by name or by ID
	1:Graubild	Given 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
	"${userFieldValue('User Type')}"	Compares string list ID of the user type of the current user
	"${userFieldValue('User Type'), true}"	Compares the string instead of string list ID of the user type of the current user

Categories

	:Options:Category
	:Options:[Query[Categories Query]]

	U	Use the user's preference settings for category searches.
	A	Find also records assigned to the categories above the one specified.
	B	Find also records assigned to the categories below the one specified.
	R	When searching below: resolve related categories and also find records assigned to the original.
	O	When 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:

	Images	Find 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:Images	Find 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:*:Excel	Finds 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.

Vocabulary Values

  Here are some examples of valid values:

	:ID:2	Given by id 2
	Germany	Given by English name

Related master Assets and Related Sub Assets

• "{af4b2e71-5f6a-11d2-8f20-0000c0e166dc}:Related Sub Assets" !? {7c41d1b1-4650-11d2-8f00-0000c0e166dc}

  • The above finds all asset records which do not reference any other asset.

• "{af4b2e71-5f6a-11d2-8f20-0000c0e166dc}:Related Sub Assets" !*

  • The above finds all asset records which does not reference any other sub-asset.

• {af4b2e72-5f6a-11d2-8f20-0000c0e166dc} !? {7c41d1b0-4650-11d2-8f00-0000c0e166dc} &&
  {af4b2e72-5f6a-11d2-8f20-0000c0e166dc} !? {9ed0887f-40e8-4091-a91c-de356c869251} &&
  {af4b2e72-5f6a-11d2-8f20-0000c0e166dc} !? {6fe3b22c-390e-44b3-99e5-df25de94893b} ||
  {af4b2e72-5f6a-11d2-8f20-0000c0e166dc} !*

  • The above finds all asset records that are assumed "master assets" (at least if no custom relations beyond the predefined ones are defined).
    This means assets that are not contained in any other asset, that are not alternates or variants of another asset,
    or assets that have no reference to any other master asset