APIs & Integrations

BBarany
Participant

How to map associations to columns - API

Hi,

 

I am doing an API call from Azure Data Factory, the goal is to retrieve the deals with specific properties, and associations.

 

I can see that it works fine, the APi call retrieves the associations, but it is all labelled with "id" and "type", so the mapping simply doesn't understand that there are multiple of them, see example below:

"associations": {
          "companies": {
            "results": [
              {
                "id": [id],
                "type": "deal_to_company"
              },
              {
                "id": [id],
                "type": "deal_to_company_unlabeled"
              },
              {
                "id": [id],
                "type": "direct_company"
              },
              {
                "id": [id],
                "type": "billing_company"

I tried everything I can think of to try to map these into rows or columns, but it is not working. Anyone who had similar issues? How to resolve this?

My platform is ADF, so that might also cause some limitations, but nevertheless, if you have any ideas, let me know please.

 

(PS: I am not interested in any kind of premade and paid connectors, please do not post commercials for them on my question, thanks)

0 Upvotes
5 Replies 5
AHernández60
Contributor | Diamond Partner
Contributor | Diamond Partner

How to map associations to columns - API

Hi @BBarany 

 

Im not very familiar with ADF, but I usually work with the HS API endpoints.

 

What endpoint are you using to retrieve the deal info? You can you loop in the associations array and create a new request for each one of the objects to obtain the extra data propierties.

 

If I can help you with anything else or if I have misinterpreted your problem, please tell me. 

 

Regards 

0 Upvotes
BBarany
Participant

How to map associations to columns - API

I am using the

/crm/v3/objects/deals?limit=100&properties=dealname&associations=companies&archived=false

 

To include just a test property, and also associations so I see it. Is there any other endpoint that brings all associations per label and deal in?

 

0 Upvotes
AHernández60
Contributor | Diamond Partner
Contributor | Diamond Partner

How to map associations to columns - API

Hi @BBarany 

The current endpoints cannot return the complete information of the related objects. Unfortunately, you will have to iterate the results of associations by making new API requests for each object if you need the properties of each one ("/crm/v3/objects/companies/{companyId}" for companies, "/crm/v3/objects/contacts/{contactId}" for contacts, ...).

 

I hope I have been able to help you. If you need anything else, please do not hesitate to contact me.

 

Regards

0 Upvotes
BBarany
Participant

How to map associations to columns - API

That is not only cumbersome to do but also very counterproductive. I need to get all the deals, not individually, because the Hubspot-ADF connector is not able to do so properly, it only works up till a certain properties, without any flexibility, and then simply stops working. We need to be able to receive information from hubspot, and be able to use that information in datasets - I think these are very basic requests for any company working with multiple systems (so, probably all of them).

 

So unfortunately, it will not work for me to do that API per object. Honestly, it seems easier to download an excel export, and just load that to an SQL server, which you can understand is ridiculous. Sorry for being harsh, but it is a very well known painpoint, I have read a lot of community posts on similar problems, some dating back 4 years, yet still no tangible solution to any of them. It is frustrating.

 

0 Upvotes
AHernández60
Contributor | Diamond Partner
Contributor | Diamond Partner

How to map associations to columns - API

Hi @BBarany 

 

I guess you already know this but to get the complete listing of an object type you normally use the List action (GET - /crm/v3/objects/contacts). In the "properties" parameter you can define all the properties you want to get, separated by commas. You can also get the associations as a list of Ids (I understand that this list is the one giving you problems). The limit of objects per call is 100, but you can make new paged calls using the "after" parameter.

I assume you are creating a custom integration between Azure Data Factory and HubSpot. Unfortunately Im not familiar with the Azure Data Factory connector, but we have done different solutions for other environments (like powerBI for example). In these cases, what we usually do is to retrieve the different objects (let's say as tables, for example) and use the associations endpoint for the relationships between objects. In some other integration cases, we obtain the fields of the associated elements in the integration logic itself, although it is rare for a related object to have a 1=1 schema.


I hope I have been able to help you. This my meeting link if you want to clarify something or more specific help.

Regards.

0 Upvotes