How to update my Retool database from a HubSpot query?

[Marwan_Dajani]

End Goal
I am building an app to help manage the company's tasks from different apps. One of those apps is HubSpot. I want to pull all the Tasks that ever existed in HubSpot and add them to a Retool database that I created. I think the solution to this is simpler than I think, but I have no idea how to get around it.

Thinking process and what I have done so far

• I was able to get all the tasks from HubSpot by creating a query called getAllTasks, and I am able to display them on a table. NOTE: HubSpot's API doesn't allow for filtering, so you have to just query every task you have ever created.
• I created a database in Retool called hubspot_tasks that has fields like "hs_recordid, hs_duedate, hs_tasktype.. etc"
• HubSpot tasks are created automatically, no one create tasks manually, so I want to create an integration in HubSpot that automatically inserts all new tasks to this Retool database (problem for another day)
• Right now, I just want to be able to add all the existing tasks into the database
• There must be a way to insert all those tasks into the database. I added a resource query to the PostgreSQL in Retool, and I am using the GUI mode to bulk insert. But how do I match the the fields from the HubSpot API response to the fields of the database?

Am I thinking about this properly?

[ScottR]

Yes and you can use a WITH clause... Check out this post:

Retool DB - adding lookup fields from a linked record from a different table

The two fields folderId and name must already be existing columns in the logs table for you to insert them into the logs table. YOu can then use a with clause statement: WITH selected_scenarios AS ( SELECT name, folderId FROM scenarios ) INSERT INTO logs (name, folderId) SELECT name, folderId FROM selected_scenarios;

[Tess]

This post might be helpful as well!