Using CRM Query Language
- Former user (Deleted)
- p_demou (Deactivated)
- Persia Xenopoulou (Deactivated)
- Former user (Deleted)
Owned by Former user (Deleted)
On this page
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 days. |
| 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 (). |
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 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 |
| 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 |
| 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 |
| 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 |
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 |
| wallet | Single Data Object Note: Only refers to 'Effective' wallets |
| parent_account | Single Data Object |
| presentment_preference | Set Data Object |
assets | 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
| 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 |
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 |
| udf_string_1 - udf_string_16 | String |
| udf_float_1 - udf_float_10 | Float |
| udf_date_1 - udf_date_10 | Date |
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 |
| udf_string_1 - udf_string_16 | String |
| udf_float_1 - udf_float_8 | Float |
| udf_date_1 - udf_date_8 | 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 |
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 |
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 |
| udf_string_1 - udf_string_16 | String |
| udf_float_1 - udf_float_4 | Float |
| udf_date_1 - udf_date_12 | Date |
payment_gateway_card
| Field | Type |
|---|---|
payment_gateway_provider (name) | String |
| expiration_month | Number |
| expiration_year | 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 |
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 |
| udf_string_1 - udf_string_8 | 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 | 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 but remains available for backwards compatibility. The logic is updated to work based on rating_state logic. |
rating_state | String | Replaces binding_state |
| 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 |
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 |