R9 - Using CRM Query Language
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
(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:
(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 |
Set Data Object | |
accounts_receivable | Set Data Object |
activity | Set Data Object |
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 |
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 |
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 |
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 |
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 |
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 |
award_reward_transaction
Field | Type |
---|---|
number | String |
rewards_participant | Set Data Object |
submitted_date | Date |
amount | Number |
reward_offer | Single Data Object |
spend_reward_transaction
Field | Type |
---|---|
number | String |
rewards_participant | Set Data Object |
total_amount | Number |
submitted_date | Date |
submitted_by_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 |
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 |
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 is 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 |
Visit Understanding Segmentation for business examples related to CRM Query Language
Related Areas
-
Understanding Segmentation — Understand the usage of Segmentation within CRM.COM
-
Managing Segments — Learn to work with Segments
-
Using CRM Query Language — Learn how to use CRM Query Language
-
Configuring Segment Criteria Templates — Learn to configure Segment Criteria Templates
Popular Labels
- accounts-receivable
- accounts-receivable-admin
- accounts-receivable-advanced
- accounts-receivable-advanced-r7
- accounts-receivable-basics
- activities
- activities-admin
- activities-admin-r7
- activities-basics
- activities-basics-r7
- additive-discounts-admin
- additive-discounts-advanced
- additive-discounts-basics
- billing-application
- billing-engine
- communication-centre
- communications-advanced-r7
- conax-web-services-advanced
- conax-web-services-basics
- contact-information
- contact-information-admin-r7
- crm-application
- crm-application-r7
- customer-events-basics-r7
- dashboards-advanced
- finance-application
- financial-transactions-admin
- financial-transactions-admin-r7
- financial-transactions-advanced
- financial-transactions-advanced-r7
- foundation-application
- foundation-application-r7
- global
- inventory-management
- inventory-management-advanced
- inventory-management-basics
- inventory-management-basics-r7
- jobs
- jobs-admin
- jobs-admin-r7
- jobs-advanced
- jobs-basics
- jobs-basics-r7
- leads
- leads-admin
- leads-admin-r7
- network-management-basics
- network-management-basics-r7
- normal-billing-admin-r7
- normal-billing-r7
- normal-billing-run-admin
- normal-billing-run-basics
- notifications
- notifications-basics
- panaccess
- platform-advanced
- platform-basics
- prepaid-billing-basics
- prepaid-billing-r7
- price-plans
- products-admin-r7
- rated-billing-items-advanced
- rated-billing-items-basics
- reports-basics
- resource-scheduling-advanced
- rewards-admin-r7
- rewards-advanced
- rewards-advanced-r7
- rewards-basics
- rewards-basics-r7
- security-management
- security-management-advanced
- security-management-advanced-r7
- segmentation-basics
- service-requests-admin
- service-requests-admin-r7
- service-requests-advanced
- service-requests-advanced-r7
- subscription-actions-r7
- subscriptions
- subscriptions-advanced
- subscriptions-advanced-r7
- subscriptions-basics
- subscriptions-basics-r7
- udrs
- udrs-admin-r7
- usage-service-r7
- user-management
- user-management-basics
- vouchers
- vouchers-advanced
- vouchers-basics
- wallets
- wallets-admin
- wallets-advanced
- wallets-basics
- workflows-admin-r7
- workflows-advanced-r7
- workflows-r7
- zapier-basics-r7