Qlik HubSpot Integration

[abc19421296]

Hi I have been able to use the REST API to GET a set of default properties (mentioned on the Hubspot website) for all the deals in HubSpot.

/crm/v3/objects/deals/search

Deals

dealname, amount, closedate, pipeline,dealstage, createdate, hs_lastmodifieddate, hs_object_id

But the issue is I am only seeing these default properties and there are a lot more fields in Hubspot that I would like to capture information from for my deals to build a dashboard out of. How can I get these additional properties for my deal records?

[Niqander]

Working on this one at the moment. I found out that you can use the parameter "properties" to get additional values. Like this:

https://api.hubapi.com/crm/v3/objects/contacts/11111111111?properties=firstname,lastname,country

if I have more pieces of the puzzle i'll post more here.

[Niqander]

oh and here is the list of properties for the contact:
https://api.hubapi.com/properties/v1/contacts/properties

[Ola_Mayer]

@abc19421296 can you please clarify what functionality are you using? Is this Analytics application or Data Integration pipeline?

[Niqander]

analytics application. I use this script to pick up some data with specific standard and custom fields from the deals table:

/**************************************************************************/

SET vNext_page = 101;

TRACE TRACE vNext_page = $(vNext_page);

//loop over the pages without knowing where the end is hence the 9999999
Do While '$(vNext_page)' < 999999999999

E_Deals:
SQL SELECT
"__KEY_root",
(SELECT
"id",
"createdAt",
"updatedAt",
"archived",
"__KEY_results",
"__FK_results",
(SELECT
"amount",
"closedate",
"createdate",
"dealname",
"dealstage",
"hs_lastmodifieddate",
"hs_object_id",
"pipeline",
"bel_team_verantwoordelijke",
"type_deal",
"hs_latest_approval_status",
"hs_date_entered_closedwon",
"deal_source",
"__FK_properties"
FROM "properties" FK "__FK_properties")
FROM "results" PK "__KEY_results" FK "__FK_results"),
(SELECT
"__KEY_paging",
"__FK_paging",
(SELECT
"after",
"link",
"__FK_next"
FROM "next" FK "__FK_next")
FROM "paging" PK "__KEY_paging" FK "__FK_paging")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "$(vBaseURL)/Deals"
,HTTPHEADER "Authorization" "$(vBearerToken)"
,QUERY "limit" "100"
,QUERY "after" "$(vNext_page)" // pak het max id op van de contact
,QUERY "properties" "amount,closedate,createdate,dealname,dealstage,hs_lastmodifieddate,hs_object_id,pipeline,bel_team_verantwoordelijke,type_deal,hs_latest_approval_status,hs_date_entered_closedwon,deal_source"
);

// check next page number
Paging: Load Max(after) as after Resident E_Deals Where after > 0 Order By after ASC;

// Last page doesnt have a next page. stop the script
LET vNext_page_check = Peek('after', 0, 'Paging');
IF vNext_page = vNext_page_check THEN
STORE E_Deals INTO [lib://******:DataFiles/E_Deals.qvd](qvd);
Drop Table E_Deals;
EXIT;
ENDIF

// set the new-next page and drop the pagind table
LET vNext_page = Peek('after', 0, 'Paging');
Drop Table Paging;

Loop

[Niqander]

to find all your default and custom fields available in the deals table you can use this:

/**************************************************************************/

Deal_Properties:

SQL SELECT 

"name",

"label" AS "label_u0",

"description" AS "description_u0",

"groupName",

"type",

"fieldType",

"fieldLevelPermission",

"hidden" AS "hidden_u0",

"dateDisplayHint",

"optionsAreMutable",

"searchTextAnalysisMode",

"optionSortStrategy",

"createdUserId",

"searchableInGlobalSearch",

"hasUniqueValue",

"externalOptionsReferenceType",

"createdAt",

"dataSensitivity",

"referencedObjectType",

"isCustomizedDefault",

"numberDisplayHint",

"textDisplayHint",

"formField",

"displayOrder" AS "displayOrder_u0",

"readOnlyValue",

"readOnlyDefinition",

"mutableDefinitionNotDeletable",

"favorited",

"favoritedOrder",

"calculated",

"externalOptions",

"displayMode",

"showCurrencySymbol",

"hubspotDefined",

"updatedUserId",

"currencyPropertyName",

"deleted",

"updatedAt",

"__KEY_root",

(SELECT 

"@Value",

"__FK_sensitiveDataCategories"

FROM "sensitiveDataCategories" FK "__FK_sensitiveDataCategories" ArrayValueAlias "@Value"),

(SELECT 

"value",

"readOnly",

"hidden",

"description",

"doubleData",

"displayOrder",

"label",

"__FK_options"

FROM "options" FK "__FK_options")

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION (

  URL "$(vBaseURLProperties)/deals/properties"

  ,HTTPHEADER "Authorization" "$(vBearerToken)"

);

STORE Deal_Properties INTO [lib://********:DataFiles/Deal_Properties.qvd](qvd);

Drop Table Deal_Properties;