Excerpt

hidden	true

Learn how to use CRM Query Language

What does this section cover?

Table of Contents

minLevel	2

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

Panel

name	blue
title	CRMQL Syntax

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

Validations & Restrictions

N/A

CRMQL Functions

Anchor
global
global
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

Status

colour	Red
title	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

Status

colour	Red
title	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

Status

colour	Red
title	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

Status

colour	Red
title	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

Status

colour	Red
title	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

Status

colour	Red
title	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

Info

title	Using Global Operator with Set Data Object

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

Code Block

language	sql
title	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

Expand

title	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

Expand

title	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

Expand

title	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

Expand

title	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

Expand

title	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

Expand

title	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

Expand

title	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

Expand

title	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

Expand

title	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

Expand

title	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

Expand

title	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

Expand

title	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

Info
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

Expand

title	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

Info
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

Status

colour	Red
title	Available from CRM.COM R9.0.0

String

Replaces binding_state

life_cycle_state

String

subscription

Set Data Object

Expand

title	buy_in_advance_request reference data objects

subscription

Field

Type

type

(name)

String

life_cycle_state

String

number

String

Note
Visit Understanding Segmentation for business examples related to CRM Query Language

Panel

name	grey

Related Areas

Filter by label (Content by label)

showLabels	false
spaces	V4Manual
showSpace	false
excerpt	true
labels	segmentation-basics-r7,segmentation-advanced-r7,segmentation-admin-r7

Panel

name	grey

Popular Labels

spaceKey	V4Manual
style	heatmap

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

Versions Compared

Old Version 1

New Version 2

Key

What does this section cover?

What is CRM Query Language

CRMQL Syntax

Validations & Restrictions

CRMQL Functions

Anchor
global
global
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

Page Comparison

Versions Compared

Old Version 1

New Version 2

Key

What does this section cover?

What is CRM Query Language

CRMQL Syntax

Validations & Restrictions

CRMQL Functions

AnchorglobalglobalGlobal 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

Anchor
global
global
Global Functions