Using Workflows to Periodically Update a Postgres Table Using Data From HubSpot Tickets API

Hi everyone,

I'm currently facing a challenge with a workflow that I'm trying to create using Retool's features and integrations with HubSpot and Postgres. I'm relatively new to workflows and APIs, so I'm hoping someone with more experience can offer some guidance.

The objective of the workflow is to perform the following steps:

STEP 1) HUBSPOT - Every hour, retrieve the "attire_po" (custom property) and "hubspot_owner_id" for HubSpot tickets that have the properties "confetti_order" set to "Yes" and "hs_pipeline" set to 0.

STEP 2) POSTGRES - Select the "poid" (referred to as "attire_po" in HubSpot) and "hubspotownerid" (referred to as "hubspot_owner_id" in HubSpot) columns from our Postgres table, which contains Purchase Order information. We want to retrieve this information for orders where the "userid" (referred to as "confetti_order" in HubSpot) is set to "Confetti".

STEP 3) UPDATE POSTGRES TABLE - For each row retrieved in STEP 2, update the "hubspotownerid" column with the corresponding value from HubSpot's "hubspot_owner_id" property. This update should be based on the link between "attire_po" from HubSpot and "poid" from the Postgres table.

To provide an example for STEP 3, let's say we have a HubSpot ticket with "attire_po" set to "12345" and "hubspot_owner_id" set to "23423432". In the Postgres table, we have a row with "poid" set to "12345", which matches the "po" reference. However, the "hubspotownerid" column in Postgres may have a different value or no value at all. We want to update this column with the value "23423432".

I've attached a screenshot of the progress I've made so far with my workflow, in case it helps to visualize the setup.

I truly appreciate any assistance that can be provided!

Thank you,


Update - I've got a little further with this. I think if I can replace the static ['0'] index in query5 with the actual index # in the query 4 loop, I'd get the required result.

Does anyone know how I can do this?

I managed to figure it out, so for anyone who finds themselves in a similar situation, this is how I did it:

Of course, if anyone can think of a better or more elegant solution, or perhaps can see issues with the workflow I've created, I'd love to hear!

1 Like