Custom Code to change Date/Time stamp to Date

[lana604]

I have a workflow rule where I grab a date/time field with custom code and update that field to a date field (MM/DD/YYYY) as an output.

Example: 
Date/time: 2024-06-21T00:29:23.243Z
Date: 06/21/2024 

I receive a "Success" message when testing the custom code. However, when I copy the output date and input it into my "signup_date" field I receive a message saying "Unable to update property because property value isn't valid." I've played around with the format of the output date to YYYY/MM/DD and DD/MM/YYYY as well as with "-" instead of "/" ... but no luck! 😞

How can I fix this?


Custom CodeSuccess Message from CodeWorkflowError Message

[Bortami · Accepted solution]

The date must best set to midnight in order to copy via native workflows. 

Here are my tests:

code used

output defined as datetimein milliseconds with time set to midnightstring set to midnight

[svamann · Accepted solution]

Ran into the same issue. With Python custom code. Got it to work by formatting the date as a timestamp in milliseconds AND ensuring it is an integer (rather than any kind of floating point number, I suppose):

Response type of the custom code is set to "Date":

Output now looks like this and is accepted by a "Copy property" action writing to a DatePicker field.

 

[Bortami · Accepted solution]

The date must best set to midnight in order to copy via native workflows. 

Here are my tests:

code used

output defined as datetimein milliseconds with time set to midnightstring set to midnight

[lana604]

Thank you Bortami, this did the trick! 

To summarize to copy the date time fields to a date field, the custom code must change the date/time field to set to UNIX milliseconds at midnight. 

[SteveHTM]

@lana604 - based on my own efforts on this topic, the only acceptable format for a date output from a custom code workflow is a timestamp value in milliseconds - not a formatted date string as you might expect. As in 1708905600000 rather than 2024-02-26.

Hope this helps!

Steve

[lana604]

Hi Steve,

I updated the output code to time stamp value in milliseconds, unfortunately, when I copy the property value to my date field, I get the same error where "property value is not valid...." 

I'm thinking this might be a HubSpot limitation. I have developers looking into this but so far no luck .

[LeanData-TonyJ]

@lana604 Did they ever get back to you? I am running into the same exact error. I've tried date fields, datetime fields, and nothing will accept the millisecond time stamp

[lana604]

Hi @LeanData-TonyJ.  You must set the custom code to update the date/time field and set it to UNIX milliseconds at midnight. 

[svamann]

Did you ensure the timestamp is returned as int? Because if it's a string in the background, subsequent actions won't accept it... see my solution above.

[LeanData-TonyJ]

I tried this as well. I have also tried almost every other output I could think of. However, I think the issue could be with the new consolidation of record updates on workflows. Since Clear, Copy, and Update are now consilidated into one action. Here is my code, any help would be very much appreciated.

exports.main = async (event, callback) => {
  try {
    const contactId = event.object.objectId;
    const accessToken = process.env.PRIVATE_APP_ACCESS_TOKEN; // Access your secret here

    // Fetch associated deals for the contact
    const associationsResponse = await fetch(`https://api.hubapi.com/crm/v3/objects/contacts/${contactId}/associations/deals`, {
      method: 'GET',
      headers: {
        Authorization: `Bearer ${accessToken}`,
        'Content-Type': 'application/json'
      }
    });

    const associationsData = await associationsResponse.json();
    console.log("Associations Data:", associationsData);

    if (!associationsData.results) {
      throw new Error("No associated deals found");
    }

    const dealIds = associationsData.results.map(deal => deal.id);

    // Fetch details for each associated deal
    const dealDetailsPromises = dealIds.map(dealId => 
      fetch(`https://api.hubapi.com/crm/v3/objects/deals/${dealId}`, {
        method: 'GET',
        headers: {
          Authorization: `Bearer ${accessToken}`,
          'Content-Type': 'application/json'
        },
        params: {
          properties: 'dealname,createdate'
        }
      })
    );

    const dealDetailsResponses = await Promise.all(dealDetailsPromises);
    const dealDetails = await Promise.all(dealDetailsResponses.map(res => res.json()));

    // Extract deal names and set create dates to midnight UTC
    const dealsInfo = dealDetails.map(deal => {
      const createDate = new Date(deal.properties.createdate);
      createDate.setUTCHours(0, 0, 0, 0); // Ensure midnight UTC
      return {
        name: deal.properties.dealname,
        createDate
      };
    });

    console.log("Deals Info:", dealsInfo);

    // Find the most recent deal create date
    const mostRecentDeal = dealsInfo.reduce((latest, current) => 
      current.createDate > latest.createDate ? current : latest
    );

    const timestamp = mostRecentDeal.createDate.getTime();
    const isoString = mostRecentDeal.createDate.toISOString();
    const formattedDate = `${(mostRecentDeal.createDate.getMonth() + 1).toString().padStart(2, '0')}/${mostRecentDeal.createDate.getDate().toString().padStart(2, '0')}/${mostRecentDeal.createDate.getFullYear()}`;
    const formattedDateV2 = `${mostRecentDeal.createDate.getFullYear()}-${(mostRecentDeal.createDate.getMonth() + 1).toString().padStart(2, '0')}-${mostRecentDeal.createDate.getDate().toString().padStart(2, '0')}`;

    console.log("Most Recent Deal:", mostRecentDeal);
    console.log("Timestamp:", timestamp);
    console.log("ISO String:", isoString);
    console.log("Formatted Date:", formattedDate);
    console.log("Formatted Date:", formattedDateV2);

    // Return the most recent deal name and create date in various formats
    callback({
      outputFields: {
        associatedDealNames: mostRecentDeal.name,
        mostRecentDealCreateDate: timestamp, // Use timestamp
        mostRecentDealCreateDateISO: isoString, // Use ISO string
        mostRecentDealCreateDateFormatted: formattedDateV2, // Use formatted date
        mostRecentDealCreateDateV2: parseInt(timestamp), // Use timestamp
        mostRecentDealCreateTimeStamp: Math.floor(timestamp),
        mostRecentDealDateNumber: parseInt(timestamp)
      }
    });
  } catch (error) {
    console.error("Error:", error.message);
    callback({
      outputFields: {
        associatedDealNames: "Error fetching deals",
        mostRecentDealCreateDate: "Error fetching date",
        mostRecentDealCreateDateISO: "Error fetching date",
        mostRecentDealCreateDateFormatted: "Error fetching date"
      }
    });
  }
};

[RaymondThomas]

What is the field type for that signup_date custom field?

[lana604]

It's a Date Picker field. My date format is the United States, so MM/DD/YYYY.

I do not have any validation rules associated to the field.

[RaymondThomas]

That and check for property validation rules