Overview

CRMQL stands for CRM.COM Query Language and was developed by CRM.COM for querying information within the system. CRMQL supports specific types of queries which depend on the entity selected in each segment.

CRMQL Syntax

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

The values of criteria depend on the type of criteria and operator.

Global functions can be used instead of the set of criteria, operators and values.

CRMQL Syntax

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

CRMQL Functions

Global Functions

Function	Parameters	Result	Description
only_one(query)	CRMQL query	Boolean	Returns true if the specified query retrieves only one record; otherwise returns false.
more_than_one(query)	CRMQL query	Boolean	Returns true if the specified query retrieves more than one record; otherwise returns false.

Date Functions

Function	Parameters	Result	Description
week_start()	none	Date	Returns the start of the week.
month_start()	none	Date	Returns the start of the month.
year_start()	none	Date	Returns the start of the year.
day_start()	none	Date	Returns the start of the day having the time set to 00:00:00.
day_end()	none	Date	Returns the end of the day having the time set to 23:59:59.
current_date()	none	Date	Returns the current date and time.
week_end()	none	Date	Returns the end of the week.
month_end()	none	Date	Returns the end of the month.
year_end()	none	Date	Returns the end of the year.
seconds_before(date,seconds)	Date, Number	Date	Returns the date which is before the specified number of seconds.
minutes_before(date,minutes)	Date, Number	Date	Returns the date which is before the specified number of minutes.
hours_before(date,hours)	Date, Number	Date	Returns the date which is before the specified number of hours.
days_before(date,days)	Date, Number	Date	Returns the date which is before the specified number of days.
weeks_before(date,weeks)	Date, Number	Date	Returns the date which is before the specified number of weeks.
months_before(date,months)	Date, Number	Date	Returns the date which is before the specified number of months.
years_before(date,years)	Date, Number	Date	Returns the date which is before the specified number of years.
seconds_after(date,seconds)	Date, Number	Date	Returns the date which is after the specified number of seconds.
minutes_after(date,minutes)	Date, Number	Date	Returns the date which is after the specified number of minutes.
hours_after(date,hours)	Date, Number	Date	Returns the date which is after the specified number of hours.
days_after(date,days)	Date, Number	Date	Returns the date which is after the specified number of days.
weeks_after(date,weeks)	Date, Number	Date	Returns the date which is after the specified number of weeks.
months_after(date,months)	Date, Number	Date	Returns the date which is after the specified number of months.
years_after(date,years)	Date, Number	Date	Returns the date which is after the specified number of years.
date_only(date)	Date	Date Only (time set to 00:00:00)	Returns the specified date having the time set to 00:00:00.
current_day()	none	Integer	Returns the current day's value (1-31).
current_month()	none	Integer	Returns the current month's value (1-12).
current_year()	none	Integer	Returns the current year's value ().
new_date(year,month,day,hour,minutes,seconds)	Number, Number, Number, Number, Number, Number	Date	Returns a date based on the parameters passed to the function. Any null values are replaced with the current date or time value

CRMQL Operators & Possible Values

Global Operators

Global operators are used to check whether a specific value is present within a set of values.

Function	Parameters	Result	Description
exist(query)	CRMQL query	Boolean	Returns true if the specified query retrieves one or more records; otherwise returns false.
not exist(query)	CRMQL query	Boolean	Returns true if the specified query retrieves zero records, otherwise 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 Autocomplete Enable Fields

Specific fields of objects (type, status, life cycle state) provide users the ability to select values from a pre-populated list. The list is populated after the operator is provided next to an <object.field> expression.

Type

Object	Field	Entity to be Populated
contact_information	type	Type name
subscription	type	Type name
activity	type	Type name
job	type	Type name
service_request	type	Type name
payment_gateway_card	type	Type name
payment	type	Type name

Status

Object	Field	Entity to be Populated
bill	status	Status name
activity	status	Status name
job	status	Status name
service_request	status	Status name

Life Cycle State

Object	Field	Entity to be Populated
contact_information	life_cycle_state	Life cycle state name
subscription	life_cycle_state	Life cycle state name
accounts_receivable	life_cycle_state	Life cycle state name
bill	life_cycle_state	Life cycle state name
activity	life_cycle_state	Life cycle state name
job	life_cycle_state	Life cycle state name
rewards_participant	life_cycle_state	Life cycle state name
service_request	life_cycle_state	Life cycle state name
payment_gateway_request	life_cycle_state	Life cycle state name
buy_in_advance_request	life_cycle_state	Life cycle state name

CRMQL Main Data Objects

contact_information

Field	Type
type	String
life_cycle_state	String
title	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
birthday_date	Date The birthday date of the contact information as defined by the combination of the day of birth, month of birth and year of birth, for example if the birthday is set on 24/04/1985 then if this tag is used within 2018, it should retrieve 24/04/1985
birthday_current_year_date	Date The birthday date within the current year, for example if the birthday is set 24/04/1985 then if this tag is used within 2018, it should retrieve 24/04/2018
name_day_date	Date The name day date within the current year, for example, if the name day is 24/04 then if this tag is used within 2018, it should retrieve 24/04/2018
name_day_day	Number
name_day_month	Number
industry (name)	String
industry_sector (name)	String
udf_string_1 - udf_string_16	String
udf_float_1 - udf_float_8	Float
udf_date_1 - udf_date_8	Date
address	Set Data Object
phone	Set Data Object
email	Set Data Object
accounts_receivable	Set Data Object
activity	Set Data Object
consent_state	String The state of the Contact's Consent. Applicable values are: PENDING ACCEPTED REJECTED WITHDRAWN
consent_updated_date	Date
created_date	Date
updated_date	Date
owned_by_group	Set Data Object
send_direct_ marketing_material	Boolean
send_affiliate_ marketing_material	Boolean
kyc_profile	String The KYC profile name
kyc_profile_expiration_date	Date
kyc_profile_state	String The KYC profile's life cycle state. Available values are: PENDING_VERIFICATION, VERIFIED, REFUTE, EXPIRED

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
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

phone

Field	Type
type	String
area_code	String
full_number	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

email

Field	Type
type	String
email	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

activity

Field	Type
number	String
type (name)	String
status (name)	String
category (name)	String
life_cycle_state	String
start_date	Date
estimated_completion_date	Date
actual_completion_date	Date
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

owned_by_group

Field	Type
number	String
name	String
alternative_code	String

subscription

Field	Type
type (name)	String
type_classification	String
life_cycle_state	String
number	String
is_free	Boolean
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
owned_by_group	Set Data Object
closest_service_rated_up_to_date	Date
date_created	Date
applied_process	Set Data Object
udf_string_1 - udf_string_8	String
udf_float_1 - udf_float_8	Float
udf_date_1 - udf_date_8	Date

subscription 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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

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
effective_date	Date
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

subscription_location

Field	Type
type	String
postal_code	String
street_name	String
area	String
town	String
district	String
municipality	String
country	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

billing_address

Field	Type
type	String
postal_code	String
street_name	String
area	String
town	String
district	String
municipality	String
country	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

owned_by_group

Field	Type
number	String
name	String
alternative_code	String

service

Field	Type
product (code)	Single Data Object
life_cycle_state	String
life_cycle_state_start_date	Date
date_added	Date
date_removed	Date
rated_up_to_date	Date
is_free	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

service reference data objects

product

Field	Type
type	String

installed_item

Field	Type
serial_number	String
product (code)	Single Data Object
date_added	Date
date_removed	Date
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

installed_item reference data objects

product

Field	Type
type	String

applied_process

Field	Type
name	String
applied_date	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
created_date	Date
updated_date	Date
billing_address	Single Data Object
payment_preference	Set Data Object
wallet	Single Data Object Note: Only refers to 'Effective' wallets
parent_account	Single Data Object
presentment_preference	Set Data Object
assets	Set Data Object
owned_by_group	Set Data Object
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_8	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

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
name_day_date	Date The name day date within the current year, for example, if the name day is 24/04 then if this tag is used within 2018, it should retrieve 24/04/2018
industry (name)	String
industry_sector (name)	String
address	Set Data Object
phone	Set Data Object
email	Set Data Object
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

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
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

phone

Field	Type
type	String
area_code	String
full_number	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

email

Field	Type
type	String
email	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

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
applied_process	Set Data Object
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

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
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

subscription_location

Field	Type
type	String
postal_code	String
street_name	String
area	String
town	String
district	String
municipality	String
country	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

service

Field	Type
product (code)	Single Data Object
life_cycle_state	String
life_cycle_state_start_date	Date
date_added	Date
date_removed	Date
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

service reference data objects

product

Field	Type
type	String

installed_item

Field	Type
serial_number	String
product (code)	Single Data Object
date_added	Date
date_removed	Date
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

installed_item reference data objects

product

Field	Type
type	String

applied_process

Field	Type
name	String
applied_before	Date
applied_after	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
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

billing_address

Field	Type
type	String
postal_code	String
street_name	String
area	String
town	String
district	String
municipality	String
country	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

presentment_preference

Field	Type
presentment_method	String
active	Boolean

payment_preference

Field	Type
payment_method	String
used_for_recurring_payments	Boolean
opt_in_for_subscriptions	Boolean
opt_in_for_purchases	Boolean

wallet

This information only refers to 'Effective' wallets.

Field	Type
number	Number
balance	Number
estimated_consumption_days	Number
estimated_consumption_date	Number
last_transaction_date	Date The date that the last transaction was created
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

assets

Field

Type

product

(code)

Single Data Object

assets reference data objects

product

Field	Type
type	String

owned_by_group

Field	Type
number	String
name	String
alternative_code	String

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
udf_string_1 - udf_string_8	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

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
owned_by_group	Set Data Object
udf_string_1 - udf_string_16	String
udf_float_1 - udf_float_10	Float
udf_date_1 - udf_date_10	Date

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
name_day_date	Date The name day date within the current year, for example, if the name day is 24/04 then if this tag is used within 2018, it should retrieve 24/04/2018
industry (name)	String
industry_sector (name)	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

service

Field

Type

product

(code)

String

date_added

Date

owned_by_group

Field	Type
number	String
name	String
alternative_code	String

job

Field	Type
number	String
type (name)	String
status (name)	String
category (name)	String
life_cycle_state	String
accounts_receivable	Single Data Object
subscription	Single Data Object
start_date	Date
estimated_completion_date	Date
actual_completion_date	Date
expiration_date	Date
physical_good	Set Data Object
expense	Set Data Object
subscription_physical_good	Set Data Object
subscription_service	Set Data Object
subscription_expense	Set Data Object
billing_terms	Set Data Object
owned_by_group	Set Data Object
udf_string_1 - udf_string_16	String
udf_float_1 - udf_float_8	Float
udf_date_1 - udf_date_8	Date

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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

physical_good

Field

Type

product

(code)

Single Data Object

date_added

Date

physical_good reference data objects

product

Field	Type
type	String

expense

Field

Type

serial_number

String

product

(code)

String

date_added

Date

subscription_physical_good

Field

Type

product

(code)

Single Data Object

date_added

Date

subscription_physical_good reference data objects

product

Field	Type
type	String

subscription_service

Field

Type

product

(code)

String

date_added

Date

subscription_expense

Field

Type

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

owned_by_group

Field	Type
number	String
name	String
alternative_code	String

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
owned_by_group	Set Data Object
access_token	Set Data Object
life_cycle_state	String
udf_string_1 - udf_string_8	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

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
contact_information	Single Data Object
is_parent_account	Boolean
is_partner_account	Boolean
wallet	Single Data Object Note: Only refers to 'Effective' wallets
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

accounts receivable reference data objects

wallet

This information only refers to 'Effective' wallets.

Field	Type
number	Number
balance	Number
alternative_balance	Number
estimated_consumption_days	Number
estimated_consumption_date	Number
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

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
name_day_date	Date The name day date within the current year, for example, if the name day is 24/04 then if this tag is used within 2018 then this tag should retrieve 24/04/2018
industry (name)	String
industry_sector (name)	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

participating_reward_scheme

Field	Type
name	String
alternative_code	String
sign_up_date	Date
sign_up_unit (name)	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

owned_by_group

Field	Type
number	String
name	String
alternative_code	String

access_token

Field

Type

classification

(name)

String

life_cycle_state

String

award_reward_transaction

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

award reward transaction reference data objects

rewards_participant

Field	Type
number	String
sign_up_date	Date
sign_up_unit (name)	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

reward_offer

Field	Type
number	String
name	String
alternative_code	String
scheme (name)	String
type (name)	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

initiated_currency

Field	Type
code	String

spend_reward_transaction

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

spend reward transaction reference data objects

rewards_participant

Field	Type
number	String
sign_up_date	Date
sign_up_unit (name)	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

initiated_currency

Field	Type
code	String

leads

Field	Type
start_date	Date
expected_completion_date	Date
actual_completion_date	Date
owned_by_group	Set Data Object
accounts_receivable_name	Single Data Object
accounts_receivable_number	Single Data Object
number	String
type (name)	String
status (name)	String
category (name)	String
life_cycle_state	String
importance_level	String
priority_level	String
source_type (name)	String
source_name	String
product_of_interest.code (code)	String
product_type_of_interest.name (name)	String
product_family_of_interest.name (name)	String
product_brand_of_interest.name (name)	String
subscription_package_name (name)	String
team.username	String
team.email	String
competitor.name (name)	String
udf_date_1 - udf_date_8	Date
udf_float_1 - udf_float_8	Float
udf_string_1 - udf_string_26	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	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	Data Object
services	Data Object
owned_by_group	Data Object
udf_string_1 - udf_string_16	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_12	Date

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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

physical_good

Field	Type
serial_number	String
product (code)	String
date_added	Date
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

services

Field	Type
product (code)	String
date_added	Date
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

owned_by_group

Field	Type
number	String
name	String
alternative_code	String

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
used_for_recurring_payments	Boolean
opt_in_for_subscriptions	Boolean
opt_in_for_purchases	Boolean

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
execution_date	Date
updated_date	Date
type	String
process_name	String The options should be EXECUTE_NORMAL_BILLING_RUN, MANUAL

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
udf_string_1 - udf_string_8	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

voucher

Field	Type
type (name)	String
lot (number)	String
value	Number
life_cycle_state	String
classification	String
udf_string_1 - udf_string_8	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

communication

Field	Type
direction	String
media	String
category	String
life_cycle_state	String
created_date	Date
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

Only 'Posted' payments are retrieved.

buy_in_advance_request

Field	Type
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 Supported values are RATED, NOT_RATED
life_cycle_state	String
subscription	Set Data Object
udf_string_1 - udf_string_8	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

buy_in_advance_request reference data objects

subscription

Field	Type
type (name)	String
life_cycle_state	String
number	String
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

wallet

Field	Type
number	String
estimated_consumption_days	Number
estimated_consumption_date	Date
estimated_consumption_as_of_date	Date
balance	Number
alternative_balance	Number
opening_balance	Number
opening_alterntive_balance	Number
opening_balance_date	Date
life_cycle_state	String
last_transaction_date	Date (The date that the last transaction was created)
udf_string_1	User Defined Field 1 of type String
udf_string_2	User Defined Field 2 of type String
udf_string_3	User Defined Field 3 of type String
udf_string_4	User Defined Field 4 of type String
udf_string_5	User Defined Field 5 of type String
udf_string_6	User Defined Field 6 of type String
udf_string_7	User Defined Field 7 of type String
udf_string_8	User Defined Field 8 of type String
udf_float_1	User Defined Field 1 of type Float
udf_float_2	User Defined Field 2 of type Float
udf_float_3	User Defined Field 3 of type Float
udf_float_4	User Defined Field 4 of type Float
udf_date_1	User Defined Field 1 of type Date
udf_date_2	User Defined Field 2 of type Date
udf_date_3	User Defined Field 3 of type Date
udf_date_4	User Defined Field 4 of type Date

wallet 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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

wallet_transaction

Field	Type
type (name)	String
number	String
amount	Number
alternative_amount	Number
extra_added_amount	Number
extra_added_alternative_amount	Number
life_cycle_state	String
caused_by_entity	String
wallet	Data Object
initiated_currency	Data Object
udf_string_1 - udf_string_20	String
udf_float_1 - udf_float_12	Float
udf_date_1 - udf_date_12	Date

wallet_transaction reference data objects

wallet

Field	Type
number	String

initiated_currency

Field	Type
code	String

write_off

Field	Type
type (name)	String
category (name)	String
number	String
life_cycle_state	String
issued_on	Date
posted_on	Date
total_amount	Number
accounts_receivable	Data Object

write_off 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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

credit_note

Field	Type
type (name)	String
category (name)	String
number	String
life_cycle_state	String
discount_amount	Number
vat_amount	Number
tax_amount	Number
net_amount	Number
total_amount	Number
issued_on	Date
posted_on	Date
accounts_receivable	Data Object
udf_string_1 - udf_string_8	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

credit_note 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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

invoice

Field	Type
type (name)	String
category (name)	String
number	String
life_cycle_state	String
discount_amount	Number
vat_amount	Number
tax_amount	Number
net_amount	Number
total_amount	Number
outstanding_amount	Number
unsettled_amount	Number
issued_on	Date
posted_on	Date
due_on	Date
accounts_receivable	Date Object
udf_string_1 - udf_string_8	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

invoice 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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

invoice_cancellation

Field	Type
type (name)	String
category (name)	String
number	String
life_cycle_state	String
issued_on	Date
posted_on	Date
total_amount	Number
accounts_receivable	Data Object

invoice_cancellation 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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

payment_cancellation

Field	Type
type (name)	String
category (name)	String
number	String
life_cycle_state	String
issued_on	Date
posted_on	Date
payment_amount	Number
accounts_receivable	Data Object

payment_cancellation 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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

refund

Field	Type
type (name)	String
category (name)	String
refund_method (name)	String
number	String
life_cycle_state	String
refund_amount	Number
issued_on	Date
posted_on	Date
payment_gateway_reference_number	String
accounts_receivable	Data Object

refund 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
is_parent_account	Boolean
is_partner_account	Boolean
udf_string_1 - udf_string_4	String
udf_float_1 - udf_float_4	Float
udf_date_1 - udf_date_4	Date

subscription_action

Field	Type
number	String
subscription_number	String
type	String
sub_type	String
behavior_code	String
classification_code	String
executed_date	Date
scheduled_date	Date
udf_string_1	User Defined Field 1 of type String
udf_string_2	User Defined Field 2 of type String
udf_string_3	User Defined Field 3 of type String
udf_string_4	User Defined Field 4 of type String
udf_string_5	User Defined Field 5 of type String
udf_string_6	User Defined Field 6 of type String
udf_string_7	User Defined Field 7 of type String
udf_string_8	User Defined Field 8 of type String
udf_float_1	User Defined Field 1 of type Float
udf_float_2	User Defined Field 2 of type Float
udf_float_3	User Defined Field 3 of type Float
udf_float_4	User Defined Field 4 of type Float
udf_date_1	User Defined Field 1 of type Date
udf_date_2	User Defined Field 2 of type Date
udf_date_3	User Defined Field 3 of type Date
udf_date_4	User Defined Field 4 of type Date

On this page

Release news

Check out a full list of CRM.COM features available per release.

Features

Check out upgrade notes to find out what needs to be done to upgrade from your current release to the latest release of CRM.COM.

Upgrade Notes

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

CRM Query Language - R15

Overview

CRMQL Syntax

CRMQL Functions

Global Functions

Date Functions

CRMQL Operators & Possible Values

Global Operators

String Operators

Numeric Operators

Date Operators

Boolean Operators

CRMQL Autocomplete Enable Fields

Status

Life Cycle State

CRMQL Main Data Objects

contact_information

subscription

accounts_receivable

bill

activity

job

rewards_participant

award_reward_transaction

spend_reward_transaction

leads

service_request

payment_gateway_card

payment_gateway_request

payment

buy_in_advance_request

wallet

wallet_transaction

write_off

credit_note

invoice

invoice_cancellation

payment_cancellation

refund

subscription_action

Release news