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!