Premium Service Lookup for Hubspot

The HubSpot toolkit offers a Premium Service Lookup component which provides lookup capabilities by searching record in HubSpot instance. The Premium Service Lookup (also short for "PSL" below) component was first introduced in our v21.1 release, any prior versions will not have the component. The Premium Service Lookup component is a shared component that is shipped by a few other Integration Toolkits including SSIS Productivity Pack, Dynamics 365, HubSpot, HubSpot toolkit since the v21.1 release.

In order to use the Premium Service Lookup component to look data from a HubSpot instance, you will need to have the HubSpot Toolkit installed. Once installed, you may continue with the configuration of the component using the following four different pages.

General

The General page can be used to configure the general configuration settings for the Premium Service Lookup component.

Premium Service Lookup component - Hubspot - General


Connection Manager

Connection Manager is required for the Premium Service Lookup component to connect to. A list of available connection managers will populate the drop-down.


Target Object

The Target Object can be selected from the drop down list. This would be based on the Connection manager. The target object would be used as the lookup table for the input data.


Cache Mode

You can select a cache mode from the below options.

    • Full Cache: When chosen, the component will populate a full cache of all records from the target object. This is the preferred option when the number of records in target object is small.
    • Partial Cache: When chosen, the component will gradually build up lookup cache as the data load progresses. This is the preferred option when the number of records in target object is significantly large.
    • No cache: No cache is built when this is chosen.

Note: The cache mode would be available based on the Target object chosen. Some objects and queries do not support one or more cache modes, which would be reflected in the component.


Advanced Text Matching Options

The Advanced Text Matching options are as follows

  • Ignore case: When chosen, the Lookup will perform a case-insensitive lookup. For instance, "ABC Company" will be treated the same as "abc company".
  • Ignore Leading Whitespace: When chosen, the leading whitespaces will be ignored.
  • Ignore Symbols: When chosen, any symbols would be ignored.
  • Ignore Trailing whitespaces: When enabled, any trailing whitespaces would be ignored.
  • Ignore Diacritics/Accents: When chosen, the Lookup will not take any diacritics within a string into consideration.

Note: The Advanced Text Matching Options only apply to Full Cache mode.


Use HubSpot Search API

Enable this option to use HubSpot Search API for performing the lookup.


Batch Size

The Batch Size option allows you to specify how many records you want to retrieve each time. The default value is 100. The maximum allowed value is 1000.

Lookup Conditions

The Lookup Conditions page can be used to configure the lookup settings for the Premium Service Lookup component.

Condition builder Mode

The Condition builder mode provides a grid for creating a query that can be used as the lookup.

PSL - HubSpot - Condition Builder Mode

The Query Editor supports adding filter conditions.

  • Add condition (+): Adds a condition to be used in query.
  • Remove condition (-): Removes the condition that was added.
  • Arrows: Use arrows to group the lookup conditions.
  • AND/OR: Specify AND or OR to create logical expressions of your lookup conditions.
  • Lookup Column: Select the lookup column from the drop-down list which displays available columns for the specified Lookup Table.
  • Operator: Use query operator to specify how each input value in a clause must relate to the corresponding value in a lookup table.
  • Input Value: The Input Value for the lookup condition. Available options are:
  • Input Column: Enables user to perform Lookup match by selecting a field from the Lookup Table Column

    Variable: Enables user to perform Lookup match based on a System or User SSIS variable

    Static Value: Enables user to perform Lookup match based on a static value


Custom Query Mode

Switching the query designer to Custom Query Mode will allow you to freely edit the query text. This can help in some cases where certain advanced queries may not be supported in the condition builder.

PSL- Hubspot - Lookup conditions - Custom Query Mode

Output Columns page

The Output Columns page allows you to add columns from the lookup entity to be returned as output columns for the lookup component.

CRM Source Editor

In this page, you can easily add any columns that should be returned. Note that at the bottom of the page, you can include two special lookup result fields.

  • _MatchFound - this will indicate whether the lookup has succeeded. It should return a true when there is at least a match found in the target system. Otherwise it should return a false.
  • _HasMoreThanOneMatch - this will be set to true when there are more than one matching records in the target system. 

Error Handling page

The Error Handling page allows you to specify how errors should be handled when they happen.

CRM Source Editor

There are three options available that you can choose for the component's error handling.

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error