TransWikia.com

Modified date of Records in Data extension using SSJS

Salesforce Asked by RKNauhwar on October 4, 2021

I want to fetch the date when a data extension record was modified . I want to exclude all the records modified in last 15 days or DE itself if any of its record has been modified in last 15 days. I know this can be achieved using WS proxy SSJS but i am unable to find anything prudctive in SF documentation . I have also gone through Data extension Object properties . Can anyone help me with that how to do it?

2 Answers

It seems you won't be able to retroactively extract this data even using WSProxy.

Let's assume you have a data extension called WSProxyDE with two columns:

  • TextID - your primary key
  • NumberValue - a numeric value you update

Insert data into this DE and modify one row (let's say the last of 3 rows).

Run the script on CloudPage:

<script runat="server">
Platform.Load("core","1.1.5");

var deName = "WSProxyDE"; //The name of your DE
var cols = ["TextId", "NumberValue"]; // columns you want to retrieve

var proxy = new Script.Util.WSProxy();
var object = "DataExtensionObject[dekey]".replace("dekey", deName);
var data = proxy.retrieve(object, cols);

Write(Stringify(data.Results));
</script>

You will get a response in the form of a stringifed array, but if you extract the third object representing your updated row, you will get this:

{
       "Name":null,
       "Keys":null,
       "Type":"DataExtensionObject",
       "Properties":[
          {
             "Name":"TextId",
             "Value":"ThirdContact"
          },
          {
             "Name":"NumberValue",
             "Value":"3"
          }
       ],
       "Client":null,
       "PartnerKey":null,
       "PartnerProperties":null,
       "CreatedDate":"0001-01-01T00:00:00.000",
       "ModifiedDate":null,
       "ID":0,
       "ObjectID":null,
       "CustomerKey":null,
       "Owner":null,
       "CorrelationID":null,
       "ObjectState":null,
       "IsPlatformObject":false
    }

The value you're seeking - ModifiedDate - is null (and the CreatedDateValue is also useless).

To achieve this you would need a column where you could store the date when a record was modified, but I see no way of extracting this data for that's already in your DE and was modified before you introduce a new column storing the modification date.

Correct answer by Rafał Wolsztyniak on October 4, 2021

The DataExtensionObject has a ModifiedDate and while the documentation states it is for an individual record, this seems not to be allowed to retrieve.

If you use describe you can see that ModifiedDate is no retrievable

<script runat="server">
    Platform.Load("core","1.1.1");
    var proxy = new Script.Util.WSProxy();
    var data = proxy.describe('DataExtensionObject');
    Write(Stringify(data));
</script>

Output

9:
    AttributeMaps: null
    Client: null
    CorrelationID: null
    CreatedDate: "0001-01-01T00:00:00.000"
    CustomerKey: null
    DataType: "DateTime"
    DefaultValue: null
    Description: null
    DisplayOrder: 0
    ID: 0
    IsAccountProperty: false
    IsContextSpecific: false
    IsCreatable: false
    IsEditable: false
    IsFilterable: false
    IsNillable: false
    IsPartnerProperty: false
    IsPlatformObject: false
    IsQueryable: false
    IsRequired: false
    IsRestrictedPicklist: false
    IsRetrievable: false
    IsSendTime: false
    IsUpdatable: true
    IsViewable: false
    Label: null
    Markups: null
    MaxLength: 0
    MaxValue: null
    MinLength: 0
    MinValue: null
    ModifiedDate: null
    Name: "ModifiedDate"
    ObjectID: null
    ObjectState: null
    Owner: null
    PartnerKey: null
    PartnerMap: null
    PartnerProperties: null
    PicklistItems: null
    Precision: 0
    PropertyType: "string"
    References: null
    RelationshipName: null
    Scale: 0
    Status: null
    ValueType: "xsdstring"

As the ModifiedDate of the DataExtensionObject is not retrievable, it seems you cannot get the ModifiedDate for an individual record.

Answered by shd.lux on October 4, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP