Finding relationships among Objects and associated records via foreign key

[SZaid5]

I have been working for a client who is a HubSpot Ent. customer. They are using Standard and Custom objects in their HubSpot account. They are also using another analytics tool which provides them the ability to have insights they need and it is connected to their dataware house (DWH).

They need HubSpot to push the data into their DWH in the required format and we are not able to figure out how to do that.

They are storing each different objects separately into their DWH e.g. Customers, Contacts, Tickets, Deals, Orders, Payments, Invoices and many more. Their admin defines the relationship among these different objects via a Primary and Foreign Key relationship in Objects.

For example, a JSON record from contact has a "CustomerID" column which contains the ID of the assoicated Customer to that contact. If the contact is associated with more than one customer the "CustomerID" column contains all those comma separated IDs. They are following this for all of their integrated platforms with DWH.

The problem is, I have looked at the HubSpot APIs and the data structure (JSON response) it returns does not have any foreign key column of the associated object e.g. Contact object does not have the Customer id column inside it.

Relationship APIs v3 and v4 both I have checked but did not find how to find the relationship among objects.

My objective is, if I look at the Contact json I should be able to find the associated Companies record(s) via a column value. Contact and Companies I have taken as example, but this is required for all the Standard and Custom objects into Hubspot.

[nikodev · Accepted solution]

Hey there @SZaid5,

As you've identified, with the exception of a few of the standard objects, (contact and companies as you pointed out) - you're not going to be able to glean the relationships based on property values on a given record. 

However, there is a fairly straightforward solution when dealing with the relationships between standard and custom objects. Most, if not all of the "read" endpoints for the standard objects have an optional "associations" parameter that you can include as a query string parameter. This should be passed as an array when using the node api client, and a comma separated list within the qs if going a more standard route. 

e.g. ["TICKET", "CONTACT"]

Unfortunately, unless things have been updated recently, this option is not available when batching. 

Another route you could take would involve using the search API. You can search using "associations.objectType" as a propertyName. But again, this is only available for standard objects as far as I'm aware.  

If none of these options work for your use case, your best bet is going to be to use the Associations API to retrieve your associations per record. If there are association labels being used in the portal, you will have to go with the v4 in order to retrieve those records that utilize a label within the relationship. 

If you wanted to, you could run a coded action within a workflow that would add the IDs of associated records to the record as property value. But this would be a more involved method. 

Feel free to reach out if you have any more questions. 

Good luck. 



 

[SZaid5]

Hi Nik,

Thanks for your response. I have not explored the coded action within a workflow, will read about that and give it a try.

Thanks,

Zaid

[Jaycee_Lewis]

Hi, @SZaid5 👋 Thanks for reaching out. Hey, @Teun @Mike_Eastwood @nikodev have you tackled anything similar for your client work?

Best,

Jaycee