[SalesForce] Unable to set datetime field in data extension to null; Marketing Cloud Rest API

The field is Nullable and I'm trying to hit

/data/v1/async/dataextensions/key:DE_NAME/rows

with attached items obj

const items = [
  {
    'Subscriber Key': SUBSCRIBER_KEY,
    'Date': null
  }
]

The same API hit with a non null date works.

const items = [
  {
    'Subscriber Key': SUBSCRIBER_KEY,
    'Date': new Date() // this is js
  }
]

API reference: https://developer.salesforce.com/docs/atlas.en-us.mc-apis.meta/mc-apis/insertDataExtensionIDAsync.htm

Best Answer

I didn't have the async endpoints you listed above turned on in the account I was testing this on, so I have not been able to verify this is true in those endpoints as well. By using the hub dataevents endpoint (also available as async, but requires primary keys in the DE) I was able to overwrite the date with null (well, technically I believe it is 'empty' not null, but for most use cases in SFMC this would be equivalent) by just using an empty string in the passed value.

For instance:

Endpoint: /hub/v1/dataevents/key:{{myDEKey}}/rowset

Async Endpoint: /hub/v1/dataeventsasync/key:{{myDEKey}}/rowset

Payload:

[
    {
  "keys": {
    "SubscriberKey": "gortonington@gortonington.com"
  },
  "values": {
    "Name": "John Smith",
    "Date": ""
  }
}
]

This would take the original value of:

enter image description here

and replace it with:

enter image description here

Be aware that the sync endpoint will return what was posted (e.g. the payload would be the response), but the async endpoint will return an empty string "" regardless of success or failure. I assume this is because it is undocumented.

Related Topic