Hubspot integration API issue with Date Formatting

[chadcrowell]

Describe the bug

I have a form setup with Hubspot integration. Everything works fine except under one circumstance. Hubspot has a Date Picker field that requires the data be formatted a specific way when sent to it through the API. When I map my field in Formie to the appropriate date picker field in Hubspot, I get an error in the queue logs for the integration processing. From Hubspot:

Date properties will only store the date, and must be set to midnight UTC for the date you want. For example, May 1 2015 would be 1430438400000 (01 May 2015 00:00:00 UTC). If you try to set a value that is not midnight UTC, you will receive an error. In HubSpot, date properties always display the specific date they are set to, regardless of the time zone setting of the account or user.

Due to the need for this formatting, I am using a plain text Formie field that is hidden in the form (I have also tried a hidden field). The template that shows the form has this:

{% set form = block.form.one() ?? null %}

{% set dateUtc = now|date('U') %}
{% set dateMidnight = dateUtc|date_modify('today 00:00')|date('U') %}

{% do form.setFieldSettings('formSubmissionDate', {
    defaultValue: dateMidnight~'000',
}) %}

This leaves me with a default value in the field that is formatted correctly down to milliseconds. i.e. 1709596800000

The form submits, the value is stored correctly, the email notification goes out, showing the field and the value formatted correctly.

However, the Hubspot integration fails. Here is the error:

_2024-03-05 15:29:54 [ERROR] Hubspot Account: API error: “{"validationResults":[{"isValid":false,"message":"56144-12-18 was not a valid long","error":"INVALID_LONG","name":"form_submissiondate"}],"status":"error","message":"Property values were not valid","correlationId":"a0f088f8-971f-4824-9a54-c3b0723eaee3"}” /srv/users/virtu-investments/apps/virtu-investments/vendor/guzzlehttp/guzzle/src/Exception/RequestException.php:113

Specifically, I wonder how a stored date of 1709596800000 is getting sent in the payload to Hubspot like this: 56144-12-18

Is that string somehow getting converted to something else during the pre-processing for the HubSpot API transmission?

Steps to reproduce

1. Setup form
2. Setup Hubspot integration
3. Try to send a properly formatted milliseconds date string through the API
4. See error in queue processing

Form settings

• Multi-page form: No
• Submission Method: Page Reload
• Client-side Validation: Yes
• Custom Form Templates: No

Craft CMS version

4.6.1

Plugin version

2.1.2

Multi-site?

No

Additional context

No response

[engram-design]

Looks like an issue on our end with lack of formatting a Date field properly. You shouldn't need that other hidden field just to wrangle that value.

Fixed for the next release. To get this early, run composer require verbb/formie:"dev-craft-4 as 2.1.2".

[chadcrowell]

Thank Josh for the update. I installed it, and indeed the API transaction is going through. However, a date stored in Craft as 1709683200000 (yesterday, midnight UTC) is showing up in Hubspot as 9/14/2007. Can you verify that the unix timestamp is indeed being sent as a string, unaltered, to the API?

[engram-design]

Strange, that's showing correctly on my end. From the front end:

To the submission (note my locale is Australia, so day/month/year):

In HubSpot:

But that's exactly what we're doing https://github.com/verbb/formie/blob/68f41a415e0dfe84b98cf69a11453985e791c336/src/integrations/crm/HubSpot.php#L103-L112

[chadcrowell]

The difference is that, on the Formie end, we're not using a date picker field, or even a date field, it is a hidden text field. We don't have a need on the form for the user to interact with the date. It is just the current date, formatted as discussed.

So I wonder, if you change your form field to a text or hidden field with the date string as the value, and send that through the integration, if it formats correctly?

Or, do you recommend that I place a date field that is hidden from the form into the form layout, with a default set to "today", in order to take advantage of your code above?

[engram-design]

Oh, gotcha! Yes, I might change that implementation to format that date if the destination field is a HubSpot date field, not just if the source field was a Formie Date field. The tricky part is that we then can't guarantee that the date provided in whatever fashion (hidden field, single-line text, etc) is actually a valid date.

I've just pushed some fixes for that to address this properly. Now, if you're passing in a raw value, that's used and it's not automatically tried to parse as a date.

[chadcrowell]

Thanks you Josh, this is now working as needed!