Clay Icon

Troubleshooting HubSpot Table Enrichment Date Formula Issues

·
·

Hey there! I'm about done creating an evergreen table that will enrich companies whenever their last enrichment date was over 6 months ago. I want to map a last enrichment date into hubspot of when that specific company is added to the table and any of 5 enrichments were updated, but having trouble creating the formula. So far I've tried: moment().format('MM-DD-YYYY')

  • but i want it to only update if one of the 5 key enrichments im mapping into HS are updated

{{Operational Status - Enrichment}}?.response || {{Check if URL is Valid}}?.valid || {{Event Industry Classification - Enrichment}}?.response || {{Instagram Profile URL (FINAL)}} || {{Instagram Follower Count (FINAL)}} ? moment().format('MM-DD-YYYY') : ""

  • this doesnt seem to be returning accurate results - all of the rows are returning yesterdays date even though i just updated the table to add the last row a few minutes ago

here's the table: https://app.clay.com/workspaces/338374/workbooks/wb_FzjKYZcMw4cR/tables/t_jP49hWCHtsT7/views/gv_qq5SusMwJ7wA

  • Avatar of LuisArturo
    LuisArturo
    ·
    ·

    Hey there Eden thanks for reaching out, question for clarification here, should this data change for all rows that moment a single one of them is updated or the moment the table itself is updated? Asking as you are mentioning that the time should be todays date, however this is this because you added a new row or was it for anew column?

  • Avatar of Eden G.
    Eden G.
    ·
    ·

    Thanks for getting back to me LuisArturo! The date should be updated when a single row is updated. I added both a new row and column today

  • Avatar of Bo (.
    Bo (.
    ·
    ·

    Hey, thanks for reaching out - I've added a formula, once we re-run it we'll see tomorrow if it'll work. I'm suspicious but let's try. The reason why I'm suspicious is this typically would only work for one column so I've also gone ahead and added a free http api that prints the exact time that you're calling, it'll update automatically if any of those values are updated! :)

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·

    Hey there - just wanted to check in here to see if you needed anything else! Feel free to reply back here if you do.

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·

    We haven't heard back from you in a bit, so we're going to go ahead and close things out here - feel free to let us know if you still need something!

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·

    Hi Eden G.! This thread was recently closed by our Support team. If you have a moment, please share your feedback:

  • Avatar of Eden G.
    Eden G.
    ·
    ·

    Hi Bo (. thank you for adding that! I just ran the column (the one you added) for the remainder of the rows.. it returned the date and time of when i ran it (a few min ago). thats what we want, correct? because i guess as new rows get added in to the table it will run that date stamp when it happens There are a few rows that have errors - any idea why?

  • Avatar of Eden G.
    Eden G.
    ·
    ·

    Also just tested mapping this into HubSpot and running the update company enrichment and got an error because the date format doesnt match the one of my hubspot property. are we able to change the format of the timestamp its returning to be ONLY the date? (in the same date format (MM-DD-YYYY), just no time)

  • Avatar of LuisArturo
    LuisArturo
    ·
    ·

    Hey there Eden, thanks for reaching out jumping in for Bo here. The reason why you are getting this error is because HubSpot will on take in time entries in Unix format. The following video can show you how we can get a timestamp into this format so that HubSpot will take it. https://www.loom.com/share/5268f15e765e40b1817e8aee1ce51aa3?sid=58dad68a-799a-4d38-81a2-ef7edcb9bee8

  • Avatar of Eden G.
    Eden G.
    ·
    ·

    That works perfectly, thank you LuisArturo!

  • Avatar of Eden G.
    Eden G.
    ·
    ·

    One more question about this column - it's been working well over the last two days but about ~10% of the rows are getting an error for this enrichment, when I force run them again it helps, but would love to avoid any manual work as this is an evergreen table. Is there a way to fix this?

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·

    This thread was picked up by our in-app web widget and will no longer sync to Slack. If you are the original poster, you can continue this conversation by logging into https://app.clay.com and clicking "Support" in the sidebar. If you're not the original poster and require help from support, please post in 02 Support.