Filtering Data in Skyvia Query Add-on

Skyvia Query Add-on allows you to configure filters in Query Builder in order to filter queried data.

Adding Filter Condition

To add a condition, perform the following steps:

  1. In the Filters section, click Plus icon Add Filter Condition.
  2. In the first drop-down list, you need to select the object name to filter data by its field. By default, the current object, which is selected in the Object list, is used. You can also filter data by fields of its related objects.

  3. In the second drop-down list, select the name of the field to filter the data by.

    It is not necessary to have this field selected in the Columns list, and thus, present in the returned data. You can filter data by any field of the object selected in the previous list.

  4. In the third drop-down list, select the kind of the filter to use: value, parameter, relative, or list.

    • Value filters are the simplest ones. They simply compare the selected field with some value (or values) or check whether the field is null.

      For example, the following filter checks if a string field “Type” begins with the word “Customer”:

      Value filters

    • Parameter filters compare the field with a parameter instead of the constant value. You can later specify parameter values before running the query without modifying the query itself. See Query Parameters in Google Sheets Add-On for more information.

      For example, the following filter checks if values of a datetime field “CreatedDate” lays within the interval specified by two parameters:

      Parameter filters

    • Relative filters are available only for fields with date or datetime data. They check if the field value belongs to a time period that is relative to the current date. For example, whether the date belongs to current, next, or previous week, month, etc.

      For example, the following filter checks if values of a datetime field “CreatedDate” belong to the previous year:

      Relative filters

    • List filters are available only for fields with textual data. They allow you to specify a list of strings and to check if the field value belongs to this list.

      For example, the following filter checks if a string field “Type” is equal to one of the list values:

      List filters

      To enter a list of values, after each value press Enter.

  5. In the third drop-down list select the comparison operator to use — equal to, not equal to, greater than, is null, etc. Note that different comparison operators are available for different field types.

  6. Depending on the selected kind of the filter, specify a value or the name of a parameter to compare with, enter a list of values, or select a relative time period.

You can add as many filter conditions as you need.

To remove a condition, click the Remove Filter cross button in the line of the corresponding condition, on the right.

AND and OR Filter Groups

By default, all the filter conditions are united with the AND logical operator. This means that a row must meet every filter condition to be retrieved to Google Sheets.

If you have several filter conditions and you want to query rows that match some of the query conditions, you may use OR operator instead. For this, click the drop-down list with And and select Or.

Adding or Removing Filter Groups

For more complex cases, conditions can be united into subgroups with their own AND or OR logical operators. Nested filter groups can contain their own nested filter groups and so on.

To add a condition group, click the Add Filter Group button Add Filter Group button to the right of the parent group logical operator. After this you can add conditions and condition groups to the new condition group.

Note that logical operator of a group is not displayed if it has only one filter condition or subgroup or no conditions and groups at all.

To remove a group from the filter, click the Remove Group cross button in the line of the corresponding group, on the right.

Filter Example

Let’s show how to configure filters on a specific example. Suppose we need to get Salesforce accounts with Customer-Direct or Customer-Channel types, added in the previous month.

We can do it in two ways — with usual value filters or with list and relative filters. Using relative and list filters in this case is more convenient, however we show both ways in order to demonstrate using value filters and filter groups.

Example with Relative and List Filters

  1. If you are not logged in to Skyvia, log in. For this, on the Add-ons menu point to Skyvia Query and then click Log In.
  2. Open the query editor - on the Add-ons menu point to Skyvia Query and then click Query.
  3. In the topmost box of the query editor, select the connection to Salesforce.
  4. In the Object box, select Account.
  5. In the list below, select the Account fields to query.
  6. Click Plus icon Add Filter Condition.
  7. In the first drop-down list of the condition, Account is already selected. In the second drop-down list, select CreatedDate.
  8. In the third drop-down list, select relative.
  9. In the fourth drop-down list, keep is in. In the rightmost drop-down list select Previous Month.
  10. Click Plus icon Add Filter Condition.
  11. In the first drop-down list of the condition, Account is already selected. In the second drop-down list select Type.
  12. In the third drop-down list, select list.
  13. In the fourth drop-down list, keep is in list. In the rightmost drop-down list select Previous Month.
  14. Then click Previous month in the Details pane.
  15. Enter Customer-Direct in the rightmost box and press Enter.
  16. Enter Customer-Channel in the rightmost box and press Enter.

That’s all, our query is ready. It looks like the following in the editor:

Relative filters

Example with Value Filters

  1. If you are not logged in to Skyvia, log in. For this, on the Add-ons menu point to Skyvia Query and then click Log In.
  2. Open the query editor — on the Add-ons menu point to Skyvia Query and then click Query.
  3. In the topmost box of the query editor, select the connection to Salesforce.
  4. In the Object box, select Account.
  5. In the list below, select the Account fields to query.
  6. Click Plus icon Add Filter Condition.
  7. In the first drop-down list of the condition, Account is already selected. In the second drop-down list select CreatedDate.
  8. In the third drop-down list, value is already selected. In the fourth drop-down list select between.

  9. In the rightmost boxes, select the start and end date of the previous month.

    Value filters

  10. Now we need to specify the filter conditions for selecting records with Customer-Direct or Customer-Channel types. Note that we select records with any of these values, so we need to create a filter subgroup, which is satisfied if any of its conditions is met. Click the Add filter group Add Filter Group button.
  11. In the new group, click Plus icon Add Filter Condition (the upper one).
  12. In the first drop-down list of the condition, Account is already selected. In the second drop-down list select Type.
  13. In the third drop-down list, value is already selected. In the fourth drop-down list equal to is already selected. In the rightmost box, type Customer-Direct.
  14. Click Plus icon Add Filter Condition in this group again.
  15. In the first drop-down list of the condition, Account is already selected. In the second drop-down list select Type.
  16. In the third drop-down list, value is already selected. In the fourth drop-down list equal to is already selected. In the rightmost box, type Customer-Channel.
  17. In the drop-down list with the logical operator of this group, select Or.

After this our query is ready. It looks like the following in the editor:

Value filters