1

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

3
  • Have you tried just excluding the field in your payload when you want it to be null? Commented Sep 6, 2019 at 20:40
  • @Gortonington Yes, if I exclude the field the value does not change from what it was before.
    – ehacinom
    Commented Sep 6, 2019 at 20:49
  • ah so you are looking to overwrite the existing value, not put in a brand new record. Commented Sep 8, 2019 at 14:39

3 Answers 3

3

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": "[email protected]"
  },
  "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.

3
  • 1
    that's what I ended up doing too. This is a good explanation. Salesforce should still take null values in the future though.
    – ehacinom
    Commented Sep 9, 2019 at 17:36
  • @Gortonington Any way to ensure SFMC date type fields which have default values set will update to the current timestamp on upserts?
    – user80446
    Commented Aug 29, 2022 at 18:34
  • @Jordan - Do you mean that if on an upsert they are updated, not added as a new record, that the timestamp would then be the most recent date? If so, then no that is not a default capability, you would need to have that be either on the import data that was pushed in or utilze a query or script to adjust it. Commented Aug 30, 2022 at 17:39
3


At this time there is no way to overwrite an existing Date value with a NULL using the REST API. I've been told the SOAP API does allow Updating to NULL - however I haven't tested it yet.

A work around could be to set the date to 1/1/1900, and run SSJS or SQL activities over the DataExtension to replace the fake date with a NULL.

0

Here is the solution to this issue.

I have used Endpoint as: /data/v1/async/dataextensions/key:DE_NAME/rows

Whenever we are passing an empty value it's passing as ["Date":null] in the payload here passing null is creating an issue so instead of that, I have tried passing the blank space so working fine. Here is the payload after the change ["Date":""].

Write the logic for the attribute where you have Null as a value it should replace with a blank space.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .