Update DateTime Field in Data Extension to Current DateTime Via REST API

Question

Is there any way to have SFMC Rest API update the existing records DateTime to the current DateTime? (The Data Extension field type has it set to use the current date as the default.)

Data Extension Setup

• SubscriberKey: Text, 64, Primary Key
• Name: Text, 64, Required
• UpdatedAt: Date, Required, Default to Current

Upsert Endpoints

• Asynchronously Upsert Data Extension
• Insert Data Extension Rows by Key

Code Samples

1. Asynchronously Upsert Data Extension

PUT {{sfmc.rest.url}}/data/v1/async/dataextensions/key:{de.key}/rows

Payload:
{
   "items": [
       {
            "SubscriberKey": "1234",
            "Name": "John",
            "UpdatedAt": ""
        }
    ]
}

2. Insert Data Extension Rows by Key

POST {{sfmc.rest.url}}/hub/v1/dataevents/key:{de.key}/rowset

Payload:
[
    {
        "keys": {
            "SubscriberKey": "1234"
        },
        "values": {
            "Name": "John",
            "UpdatedAt": ""
        }
    }
]

When you go through the FTP, if you have no value for the UpdatedAt field in the CSV; then it will automatically grab the default value current DateTime for both INSERTS and UPSERTS. However, the REST API does not seem to respect the same logic any longer; errors are thrown if:

• the UpdatedAt is not included in the payload.
• the UpdatedAt is set to "" (empty string) in the payload.
• the UpdatedAt is set to "Now()" in the payload.

Answer 1

After very long back and forths with SFMC; there is NO way of persisting this functionality from the FTP process to the API process. You can either:

1. Pass a timestamp via the API request or
2. Use a staging data extension to generate a default value
   • API insert to a staging data extension; it will automatically generate the timestamp for you.
   • API to copy the record from the staging data extension to your target data extension.
   • API to delete the record from the staging data extension.
   • Ensure that no API call is made between all of this which will impact the same record since it will have a race condition.

Closing thoughts: The API implementation is disappointing; it doesn't support basic features and moreso lacks proper response messages. This would be fine if they would at least let you query for statuses of your API requests but they don't. I've seen startup software be less cumbersome than this.