User Tools

Site Tools


acquire_query_building

Acquire - Query - Building

Configuring a Group

The only part of the group that can be configured is the operator, there are two values: OR, AND.

Configuring a Row

There are three components of configuring a row, they are:

  1. Field
  2. Operator
  3. Value

Field

When configuring a row, you must first select a field from the dropdown list on the left-hand side. There fields available here are based on the entity selected. This may change if operating from within a sub-query.

Operators

The next step is to configure the operator. Depending on the type of the field selected, the options for the operator may change.

The options for the operators are as follows:

  • EQUALS - must exactly match the input value.
  • DOES NOT EQUAL - must not exactly match the input value.
  • CONTAINS - must contain the input value.
  • DOES NOT CONTAIN - must not contain the input value.
  • Number and Date Operators
    • GREATER THAN - must be greater than input value.
    • GREATER THAN OR EQUAL TO - must be greater than or equal to input value.
    • LESS THAN - must be less than input value.
    • LESS THAN OR EQUAL TO - must be less than of equal to input value
    • BETWEEN - must be in range between two input values. For example, employees must be between 50 and 250, or incorporation date must be between 1980 and 1990.
    • NOT BETWEEN - must not be in range between two input values.
  • Multiselect Operators
    • IN - value is one of many values. For example, company country is in [UK, United States, China].
    • NOT IN - value is not one of many values.

Value

Depending on the type of field selected the options for the input value may change. For example, company name is a plain text input, however company country is a picklist/multiselect-picklist of values. Generally picklists are used when the options for the input value are limited.

Moving a Row

A row can be moved up and down using the move buttons at the end of the row.

If the next item after a row is a group, then the row will be moved into that group.

If the next item after a row is outside the current group, then the row will be moved up and out of that group into the higher group.

If the next item after a row is sub-query then the row will be moved past the sub-query.

Deleting a Row

A row can be deleted using the delete button at the end of the row.

Adding a New Row

A new row can be added by clicking the add button then selecting the row option.

A new row will then be added at the level of the add button used.

Adding a New Group

A new group can be added by clicking the add button then selecting the group option.

A new group will be added at the level of the add button.

Adding a Sub-Query

A sub-query is only usable when the main entity selected is the contact entity. The sub-query allows you to add company fields to a contact query.

A sub-query can be added by clicking the add button then selecting the company option.

A new sub-query will be added at the level of the add button.

Multiple sub-queries can be added to a query. A sub-query cannot be added within another sub-query.

Adding a Suppression List

A suppression list can be added to a query or sub-query at the top level to exclude records from a query or sub-query. A suppression list is added by clicking a top level add button and clicking the suppress list option.

A row is then added to the query for the suppression list. Initially there are no records suppressed so the count of suppressed records is 0.

Suppressed records are added by clicking the edit button on the suppress list row.

An interface for adding suppressed records will then appear.

To suppress record, click the add button.

A new row will then be added to the interface.

A suppressed record can be added by typing the ID of the record into the input.

Suppressed records can also be deleted by clicking the delete button.

After suppressed records have been added, the suppression list row will show how many records are being suppressed.

And if you hover your mouse over the display, you will get a tooltip showing the IDs of the suppressed records.

Exporting

You can export the suppression list in many formats for sharing or for re-use in other queries.

File

You can download the suppression list as a .dq4xls file by clicking the file option in the export button options.

The suppression list will then be downloaded to your downloads directory as a .dq4xls file.

Sheet

You can export the suppression list to a sheet by clicking the sheet option in the export button options.

The suppression list will be exported to the sheet in the following format:

CSV

You can download the suppression list as a .csv file by clicking the CSV option in the export button options.

The suppression list will then be downloaded to your downloads directory as a .csv file.

Importing

You can import a suppression list from a file, sheet or CSV for use in the query. Using the import tool will overwrite any record IDs currently in the interface.

File

You can import a suppression list as .dq4xls file by clicking the file option in the import options.

The record IDs will then be added to the interface.

Sheet

You can import a suppression list from the Excel worksheet by clicking the sheet option in the import options.

This will temporarily hide the query builder dialog. A taskpane will be opened on the right-hand side of Excel.

Click the select range button to select the range of cells containing the record IDs.

Then click the import button.

The query builder dialog will then be reopened with the record IDs in the suppression list interface.

CSV

A suppression list can be imported from a CSV by clicking the CSV option in the import button options.

A new interface will be opened for handling the CSV import. You can cancel this process by clicking cancel button, this will return you to the suppression list interface.

To start the CSV import process, select a file.

If there are headers in the CSV, then check the checkbox.

Then select the header of the column that contains the record IDs.

Then select import.

The record IDs will then be added to the suppression list interface.

Adding a Record Count

A record count can be added to the query to limit the number of records to retrieve, this can be used to reduce the cost of the query.

A record count is added by clicking the top level add button in the query and selecting the count option.

A new record count row is then added to the query.

A count must be specified in the count row. This can only be an integer value.

acquire_query_building.txt · Last modified: 2023/09/19 10:51 by qpww5

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki