What does this section cover?

What is CRM Query Language

CRMQL stands for CRM.COM Query Language, which is business query language developed by CRM.COM in order to be used to query information within the CRM.COM system. CRMQL supports specific type of queries, which depend on the entity selected on the segment (or any other entity that might be using CRMQL Queries).

CRMQL Syntax

The structure of the CRMQL syntax is described below (using regular expressions). In general the CRMQL query consists of at least one set of criteria, operator and value. More than one sets can be defined or separated using AND or OR.

Criteria values, depend on the type of the specified criteria and the type of the specified operator.

Global functions can be used instead of the set of criteria, operator, value

CRMQL Syntax

(criteria_name operator criteria_value|global_function) ((AND|OR) (criteria_name operator criteria_value|global_function))*

Validations & Restrictions

N/A

CRMQL Functions

Global Functions

Function	Parameters	Result	Description
only_one(query)	CRMQL query	Boolean	It returns true if the specified query retrieves only one record, otherwise is returns false
more_than_one(query)	CRMQL query	Boolean	It returns true if the specified query retrieves more than one records, otherwise is returns false

Date Functions

Function	Parameters	Result	Description
week_start()	none	Date	It returns the start of the week
month_start()	none	Date	It returns the start of the month
year_start()	none	Date	It returns the start of the year
day_start()	none	Date	It returns the start of the day having the time set to 00:00:00
day_end()	none	Date	It returns the end of the day having the time set to 23:59:59
current_date()	none	Date	It returns the current date and time
week_end()	none	Date	It returns the end of the week
month_end()	none	Date	It returns the end of the month
year_end()	none	Date	It returns the end of the year
AVAILABLE FROM CRM.COM R9.0.0 - seconds_before(date,seconds)	Date, Number	Date	It returns the date which is before the specified number of seconds
AVAILABLE FROM CRM.COM R9.0.0 - minutes_before(date,minutes)	Date, Number	Date	It returns the date which is before the specified number of minutes
AVAILABLE FROM CRM.COM R9.0.0 - hours_before(date,hours)	Date, Number	Date	It returns the date which is before the specified number of hours
days_before(date,days)	Date, Number	Date	It returns the date which is before the specified number of days
weeks_before(date,weeks)	Date, Number	Date	It returns the date which is before the specified number of weeks
months_before(date,months)	Date, Number	Date	It returns the date which is before the specified number of months
years_before(date,years)	Date, Number	Date	It returns the date which is before the specified number of years
AVAILABLE FROM CRM.COM R9.0.0 - seconds_after(date,seconds)	Date, Number	Date	It returns the date which is after the specified number of seconds
AVAILABLE FROM CRM.COM R9.0.0 - minutes_after(date,minutes)	Date, Number	Date	It returns the date which is after the specified number of minutes
AVAILABLE FROM CRM.COM R9.0.0 - hours_after(date,hours)	Date, Number	Date	It returns the date which is after the specified number of hours
days_after(date,days)	Date, Number	Date	It returns the date which is after the specified number of days
weeks_after(date,weeks)	Date, Number	Date	It returns the date which is after the specified number of weeks
months_after(date,months)	Date, Number	Date	It returns the date which is after the specified number of months
years_after(date,years)	Date, Number	Date	It returns the date which is after the specified number of days
date_only(date)	Date	Date Only (time set to 00:00:00)	It returns the specified date having the time set to 00:00:00
current_day()	none	Integer	It returns the current day's value (1-31)
current_month()	none	Integer	It returns the current month's value (1-12)
current_year()	none	Integer	It returns the current year's value ()

CRMQL Operators & Possible Values

Global Operators

Global Operators are used to check if a value exist or not within a Set of values

Function	Parameters	Result	Description
exist(query)	CRMQL query	Boolean	It returns true if the specified query retrieves one or more records, otherwise is returns false
not exist(query)	CRMQL query	Boolean	It returns true if the specified query retrieves zero records, otherwise is returns false

Using Global Operator with Set Data Object

Global operators must be used when a Set of Data Objects is used:

Global operators - Set of Data Object

(criteria_name operator criteria_value|global_function) 
((AND EXIST|OR EXIST| AND NOT EXIST| OR NOT EXIST) (criteria_name operator criteria_value|global_function))*

String Operators

Operator	Possible Values
=	value \| string field \| empty
!=	value \| string field \| empty
<>	value \| string field \| empty
>	value \| string field
>=	value \| string field
<	value \| string field
<=	value \| string field
like	value
not like	value
in()	(value \| string field) ( , (value \| string field) )*
not in()	(value \| string field) ( , (value \| string field) )*

Numeric Operators

Operator	Possible Values
=	value \| numeric field \| empty
!=	value \| numeric field \| empty
<>	value \| numeric field \| empty
>	value \| numeric field
>=	value \| numeric field
<	value \| numeric field
<=	value \| numeric field
between X and Y	value \| numeric field

Date Operators

Operator	Possible Values
=	value (dd/mm/yyyy) \| date field \| date function \| empty
!=	value (dd/mm/yyyy) \| date field \| date function \| empty
<>	value (dd/mm/yyyy) \| date field \| date function \| empty
>	value (dd/mm/yyyy) \| date field \| date function
>=	value (dd/mm/yyyy) \| date field \| date function
<	value (dd/mm/yyyy) \| date field \| date function
<=	value (dd/mm/yyyy) \| date field \| date function
between X and Y	value (dd/mm/yyyy) \| date field \| date function

Boolean Operators

Operator	Possible Values
=	true \| false
!=	true \| false
<>	true \| false

CRMQL Main Data Objects

contact_information

Field	Type
type	String
life_cycle_state	String
name	String
first_name	String
middle_name	String
last_name	String
category (name)	String
gender	String
day_of_birth	Number
month_of_birth	Number
year_of_birth	Number
name_day_day	Number
name_day_month	Number
industry (name)	String
industry_sector (name)	String
address	Set Data Object
phone	Set Data Object
email	Set Data Object
accounts_receivable	Set Data Object
activity	Set Data Object

contact_information reference data objects

address

Field	Type
type	String
postal_code	String
street_name	String
area	String
town	String
district	String
municipality	String
country	String

phone

Field	Type
type	String
area_code	String
full_number	String

email

Field	Type
type	String
email	String

subscription

Field	Type
type (name)	String
life_cycle_state	String
number	String
accounts_receivable	Single Data Object
billing_terms	Single Data Object
subscription_location	Single Data Object
billing_address	Single Data Object
service	Set Data Object
installed_item	Set Data Object

subscription reference data objects

billing_terms

Field	Type
unified_number	String
scheme_name	String
price_plan_name	String
billing_method	String
frequency	String
cycle_day	Number
binding_period	String
binding_start_date	Date
binding_end_date	Date
agreement_date	Date
life_cycle_state	String
effective_date	Date
expiration_date	Date

subscription_location

Field	Type
type	String
postal_code	String
street_name	String
area	String
town	String
district	String
municipality	String
country	String

billing_address

Field	Type
type	String
postal_code	String
street_name	String
area	String
town	String
district	String
municipality	String
country	String

service

Field	Type
product (code)	String
life_cycle_state	String
life_cycle_state_start_date	Date
date_added	Date
date_removed	Date

installed_item

Field	Type
serial_number	String
product (code)	String
date_added	Date
date_removed	Date

accounts_receivable

Field	Type
number	String
name	String
life_cycle_state	String
balance	Number
outstanding_amount	Number
contact_information	Single Data Object
subscription	Set Data Object
bill	Set Data Object
classification (name)	String
credit_limit_status	String
primary_account	Boolean
billing_address	Single Data Object
presentment_preference	Set Data Object

accounts_receivable reference data objects

contact_information

Field	Type
type	String
life_cycle_state	String
name	String
first_name	String
middle_name	String
last_name	String
category (name)	String
gender	String
day_of_birth	Number
month_of_birth	Number
year_of_birth	Number
name_day_day	Number
name_day_month	Number
industry (name)	String
industry_sector (name)	String
address	Set Data Object
phone	Set Data Object
email	Set Data Object

contact_information reference data objects

address

Field	Type
type	String
postal_code	String
street_name	String
area	String
town	String
district	String
municipality	String
country	String

phone

Field	Type
type	String
area_code	String
full_number	String

email

Field	Type
type	String
email	String

subscription

Field	Type
type (name)	String
life_cycle_state	String
number	String
billing_terms	Single Data Object
subscription_location	Single Data Object
service	Dynamic Data Object
installed_item	Dynamic Data Object

subscription reference data objects

billing_terms

Field	Type
unified_number	String
scheme_name	String
price_pan_name	String
billing_method	String
frequency	String
cycle_day	Number
binding_period	String
binding_start_date	Date
binding_end_date	Date
agreement_date	Date
effective_date	Date

subscription_location

Field	Type
type	String
postal_code	String
street_name	String
area	String
town	String
district	String
municipality	String
country	String

service

Field	Type
product (code)	String
life_cycle_state	String
life_cycle_state_start_date	Date
date_added	Date
date_removed	Date

installed_item

Field	Type
serial_number	String
product (code)	String
date_added	Date
date_removed	Date

bill

Field	Type
number	String
classification	String
life_cycle_state	String
from_date	Date
to_date	Date
due_date	Date
total_credit_amount	Number
total_debit_amount	Number
total_billed_amount	Number
total_amount_to_be_paid	Number
outstanding_amount	Number
unsettled_amount	Number
status	String

billing_address

Field	Type
type	String
postal_code	String
street_name	String
area	String
town	String
district	String
municipality	String
country	String

presentment_preference

Field	Type
presentment_method	String
active	Boolean

bill

Field	Type
number	String
accounts_receivable	Single Data Object
classification	String
life_cycle_state	String
from_date	Date
to_date	Date
due_date	Date
total_credit_amount	Number
total_debit_amount	Number
total_billed_amount	Number
total_amount_to_be_paid	Number
outstanding_amount	Number
unsettled_amount	Number
status	String
communication_queue	Set Data Object

bill reference data objects

accounts_receivable

Field	Type
number	String
name	String
life_cycle_state	String
balance	Number
outstanding_amount	Number
classification (name)	String
credit_limit_status	String
primary_account	Boolean

communication_queue

Field	Type
external_system	String
life_cycle_state	String
direction	String
media	String
category	String
created_date	Date
error_code	String

activity

Field	Type
number	String
type (name)	String
status (name)	String
category (name)	String
life_cycle_state	String
contact_information	Single Data Object
start_date	Date
estimated_completion_date	Date
actual_completion_date	Date
service	Set Data Object

activity reference data objects

contact_information

Field	Type
type	String
life_cycle_state	String
name	String
first_name	String
middle_name	String
last_name	String
category (name)	String
gender	String
day_of_birth	Number
month_of_birth	Number
year_of_birth	Number
name_day_day	Number
name_day_month	Number
industry (name)	String
industry_sector (name)	String

service

Field

Type

product

(code)

String

date_added

Date

job

Field	Type
number	String
type (name)	String
status (name)	String
category (name)	String
life_cycle_state	String
accounts_receivable	Single Data Object
start_date	Date
estimated_completion_date	Date
actual_completion_date	Date
billing_terms	Set Data Object
physical_good	Set Data Object
expense	Set Data Object

job reference data objects

accounts_receivable

Field	Type
number	String
name	String
life_cycle_state	String
balance	Number
outstanding_amount	Number
classification (name)	String
credit_limit_status	String
primary_account	Boolean

physical_good

Field

Type

product

(code)

String

date_added

Date

expense

Field

Type

serial_number

String

product

(code)

String

date_added

Date

billing_terms

Field	Type
unified_number	String
scheme_name	String
price_pan_name	String
agreement_date	Date
effective_date	Date
expiration_date	Date

rewards_participant

Field	Type
number	String
accounts_receivable	Single Data Object
sign_up_date	Date
sign_up_unit (name)	String
participating_reward_scheme	Set Data Object

rewards participant reference data objects

accounts_receivable

Field	Type
number	String
name	String
life_cycle_state	String
balance	Number
outstanding_amount	Number
classification (name)	String
credit_limit_status	String
primary_account	Boolean

participating_reward_scheme

Field	Type
name	String
alternative_code	String
sign_up_date	Date
sign_up_unit (name)	String

award_reward_transaction

Field	Type
number	String
rewards_participant	Set Data Object
submitted_date	Date
amount	Number
reward_offer	Single Data Object

award reward transaction reference data objects

rewards_participant

Field

Type

number

String

sign_up_date

Date

sign_up_unit

(name)

String

reward_offer

Field	Type
number	String
name	String
alternative_code	String
scheme (name)	String
type (name)	String

spend_reward_transaction

Field	Type
number	String
rewards_participant	Set Data Object
total_amount	Number
submitted_date	Date
submitted_by_unit (name)	String

spend reward transaction reference data objects

rewards_participant

Field

Type

number

String

sign_up_date

Date

sign_up_unit

(name)

String

service_request

Field	Type
number	String
type (name)	String
status (name)	String
category (name)	String
classification (name)	String
life_cycle_state	String
accounts_receivable	Single Data Object
urgency_level	String
impact_level	String
priority_level	String
start_date	Date
estimated_completion_date	Date
actual_completion_date	Date
response_date	Date
response_category	String
temporary_resolution_category	String
temporary_resolved_by	String
temporary_resolution_date	Date
temporary_resolution_accepted_by	String
final_resolution_category	String
final_resolution_resolved_by	String
final_resolution_accepted_by	String
final_resolution_date	Date
physical_good	Set Data Object
services	Set Data Object

service_request reference data objects

accounts_receivable

Field	Type
number	String
name	String
life_cycle_state	String
balance	Number
outstanding_amount	Number
classification (name)	String
credit_limit_status	String
primary_account	Boolean

physical_good

Field

Type

serial_number

String

product

(code)

String

date_added

Date

services

Field

Type

product

(code)

String

date_added

Date

payment_gateway_card

Field	Type
payment_gateway_provider (name)	String
expiration_month	Number
expiration_date	Number
brand	String
type	String
address_line_1_check_result	String
address_zip_check_result	String
security_code_check_result	String
created_date	Date
updated_date	Date
default	Boolean

Note that only the cards which are related with an accounts receivable payment preference which is used for recurring payments are retrieved.

payment_gateway_request

Field	Type
payment_gateway_provider (name)	String
life_cycle_state	String
process_run (number)	String
created_date	Date

payment

Field	Type
number	String
reference_number	String
type (name)	String
payment_method (name)	String
category (name)	String
posted_date	Date
amount	Number
accounts_receivable	Single Data Object
voucher	Single Data Object
communication	Set Data Object

payment reference data objects

accounts_receivable

Field	Type
number	String
name	String
life_cycle_state	String
balance	Number
outstanding_amount	Number
classification (name)	String
credit_limit_status	String
primary_account	Boolean

voucher

Field	Type
type (name)	String
lot (number)	String
value	Number
life_cycle_state	String

communication

Field	Type
direction	String
media	String
category	String
life_cycle_state	String
created_date	Date

Note that only posted payments are retrieved

buy_in_advance_request

Field	Type	Additional information
number	String
duration	Number
duration_unit_of_time	String Supported values are DAYS, WEEKS, MONTHS, YEARS
created_date	Date
billing_effective_date	Date
expiration_date	Date
billing_state	String	As from CRM.COM R9.0.0 billing_state is replaced by rating_state however it remains available for backward compatibility. The logic however i updated and works based on rating_state logic
rating_state AVAILABLE FROM CRM.COM R9.0.0	String	Replaces binding_state
life_cycle_state	String
subscription	Set Data Object

buy_in_advance_request reference data objects

subscription

Field

Type

type

(name)

String

life_cycle_state

String

number

String

Visit Understanding Segmentation for business examples related to CRM Query Language

Related Areas

Page:

Understanding Segmentation — Understand the usage of Segmentation within CRM.COM
Page:

Managing Segments — Learn to work with Segments
Page:

Using CRM Query Language — Learn how to use CRM Query Language
Page:

Configuring Segment Criteria Templates — Learn to configure Segment Criteria Templates

Operator	Possible Values
=	value \| numeric field \| empty
!=	value \| numeric field \| empty
<>	value \| numeric field \| empty
>	value \| numeric field
>=	value \| numeric field
<	value \| numeric field
<=	value \| numeric field
between X and Y	value \| numeric field

Operator	Possible Values
=	value (dd/mm/yyyy) \| date field \| date function \| empty
!=	value (dd/mm/yyyy) \| date field \| date function \| empty
<>	value (dd/mm/yyyy) \| date field \| date function \| empty
>	value (dd/mm/yyyy) \| date field \| date function
>=	value (dd/mm/yyyy) \| date field \| date function
<	value (dd/mm/yyyy) \| date field \| date function
<=	value (dd/mm/yyyy) \| date field \| date function
between X and Y	value (dd/mm/yyyy) \| date field \| date function

R9 - Using CRM Query Language

What does this section cover?

What is CRM Query Language

CRMQL Syntax

Validations & Restrictions

CRMQL Functions

Global Functions

Date Functions

CRMQL Operators & Possible Values

Global Operators

String Operators

Numeric Operators

Date Operators

Boolean Operators

CRMQL Main Data Objects

contact_information

subscription

accounts_receivable

bill

activity

job

rewards_participant

award_reward_transaction

spend_reward_transaction

service_request

payment_gateway_card

payment_gateway_request

payment

buy_in_advance_request

Related Areas

Popular Labels