Upsert Statement Returning Error: Workflows (API data and webhooks)

💬 Workflows
, ,
1

Hello!

I am on retool self hosted, on v3.148.6

Goal
I am creating an automation workflow that fetches HubSpot API data (triggered by a webhook), cleans it (python script) and then upserts a DB table named 'hubspot'.

The api data is fetched and transformed well.

The issue arises in the upsert block, it returns an error that the column (probability) doesn't exist.

INSERT INTO hubspot (
  id,
  dealname, 
  company_name, 
  country,
  spv_name,
  country_of_spv,
  deal_stages, 
  createdate,
  hs_lastmodifieddate, 
  pipeline, 
  pv_wind_bess,
  deal_currency_code, 
  legal_entity, 
  type,
  amount__set_up_, 
  amount__recuring_,
  contract_term, 
  deal_reference, 
  contract_execution_date,
  job_code,
  probability
)
VALUES (
  {{ cleanDealsPy.data.id }},
  {{ cleanDealsPy.data.dealname }},
  {{ cleanDealsPy.data.company_name }},
  {{ cleanDealsPy.data.country }},
  {{ cleanDealsPy.data.spv_name }},
  {{ cleanDealsPy.data.country_of_spv }},
  {{ cleanDealsPy.data.deal_stages }},
  {{ cleanDealsPy.data.createdate }},
  {{ cleanDealsPy.data.hs_lastmodifieddate }},
  {{ cleanDealsPy.data.pipeline }},
  {{ cleanDealsPy.data.pv_wind_bess }},
  {{ cleanDealsPy.data.deal_currency_code }},
  {{ cleanDealsPy.data.legal_entity }},
  {{ cleanDealsPy.data.type }},
  {{ cleanDealsPy.data.amount__set_up_ }},
  {{ cleanDealsPy.data.amount__recuring_ }},
  {{ cleanDealsPy.data.contract_term }},
  {{ cleanDealsPy.data.deal_reference }},
  {{ cleanDealsPy.data.contract_execution_date }},
  {{ cleanDealsPy.data.job_code }},
  {{ cleanDealsPy.data.probability}}
)
ON CONFLICT (id) DO UPDATE SET
  dealname = EXCLUDED.dealname, 
  company_name = EXCLUDED.company_name, 
  country = EXCLUDED.country,
  spv_name = EXCLUDED.spv_name,
  country_of_spv = EXCLUDED.country_of_spv,
  deal_stages = EXCLUDED.deal_stages, 
  createdate = EXCLUDED.createdate,
  hs_lastmodifieddate = EXCLUDED.hs_lastmodifieddate, 
  pipeline = EXCLUDED.pipeline, 
  pv_wind_bess = EXCLUDED.pv_wind_bess,
  deal_currency_code = EXCLUDED.deal_currency_code, 
  legal_entity = EXCLUDED.legal_entity, 
  type = EXCLUDED.type,
  amount__set_up_ = EXCLUDED.amount__set_up_, 
  amount__recuring_ = EXCLUDED.amount__recuring_,
  contract_term = EXCLUDED.contract_term, 
  deal_reference  = EXCLUDED.deal_reference, 
  contract_execution_date = EXCLUDED.contract_execution_date,
  job_code = EXCLUDED.job_code,
  probability = EXCLUDED.probability
WHERE EXCLUDED.hs_lastmodifieddate > hubspot.hs_lastmodifieddate;

I get the following error, I can confirm that the probability column does exist indeed in the hubspot DB table, so that can't be the issue.

image
image965×217 18.6 KB

I tried:

  1. renaming the column in the DB

  2. adding a new block to update the id after the upsert block:

    image
    image482×336 14 KB

  3. deleting and re-adding the SQL block

  4. Running the below to confirm the col:

select column_name, data_type
FROM information_schema.columns
where table_name = 'hubspot'
and column_name = 'probability';
  1. check if {{ cleanDealsPy.data.probability }} exists => select {{ cleanDealsPy.data.probability }} AS probability and it does display the value
  2. Use both direct value and fall back {{... || 0 }}
  3. Cannot find 'refresh schema' button in resource settings

I'm somewhat at my wits end :sweat_smile:
Could someone point me towards to the right direction.

Thanks!

3

Hi @gia,
Have you looked at the data type to be sure they match? What happens if you cast as CAST({{ cleanDealsPy.data.probability }} AS numeric) ?

Can you add another column test_probability and try upserting to that? I would try first as a text data type, then to the desired data type.

4

hi @Shawn_Optipath
Thanks very much for the response. I tried both, same error.

I created a new workflow (without the webhook) and just used 1 hubspot id in the API call - all else remaining the same; same cols etc in the upsert block and it works fine there, just not on my webhook workflow...

Could it be a cache issue or something else?

Thanks!

5

Hmmm, I thought it might have been a conflict as I believe "probability" is a reserved name for deals.

Can you show screenshots of both the problematic workflow and the new one without the webhook? Also the data in both workflows.

6

@gia,
Can you just try to insert one record with hardcoded values from your webhook triggered workflow. Something like this:

INSERT INTO hubspot (
  id,
  dealname,
  probability
) VALUES (
  'test-id-123',
  'Test Deal',
  0.75
)
ON CONFLICT (id) DO UPDATE SET
  dealname = EXCLUDED.dealname,
  probability = EXCLUDED.probability;

If it is a caching issue, you can try to force a schema reload with changes, like adding a dummy column, refreshing, and then removing it. Or you could try to rename the table or make a duplicate table and run the query on IT?

Also, what if you run the second workflow you created with a webhook?

7

hi @Shawn_Optipath @lindakwoo

Thanks very much for your assistance! My apologies for the slight delay - I was off for a few days.

Some updates...

I also took the cache route -adding a dummy column (but this was last week) and it hadn't worked before.

I tried the hardcoded upsert and somehow today it worked... I didn't really do anything extraordinary - its a bit unsettling that Im still not sure what the root cause was :confused: but happy its fine now!

@Shawn_Optipath would you still like the screenshots?

Thanks again!

8

Hi @gia,
I'm glad you managed to get it working by hardcoding it. Do you still need help?

Wondering if you have tried preparing your data in a code block, then using the GUI instead of an SQL statement?

9

hi @Shawn_Optipath

No I'm good now, its worked!

I used a code block and then used a SQL Statement, i hardly use the GUI since my DB is normalised, so a few joins here and there.

Thanks for the assistance!

1 Like