0

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
  • Do you have a „last modified date” field in your DE?
    – zuzannamj
    Jul 21, 2020 at 11:34
  • No , I want to fetch it from DataExtensionObject
    – RKNauhwar
    Jul 22, 2020 at 8:30

2 Answers 2

1

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.

2
  • I have gone through SF Documentation on Dataextension object .The DataExtensionObject object represents a row within a data extension. And has column ModifiedDate Nullable` which Indicates the last time object information was modified. I want to fetch this field by passing customer key in filter in retrieve function. Doc Link : developer.salesforce.com/docs/…
    – RKNauhwar
    Jul 22, 2020 at 8:56
  • I know the field is mentioned in the documentation and is returned by the API call I posted above, but the value is null even if the row was modified. Try my solution and see for yourself - the code will retrieve rows from a data extension, but the ModifiedDate value will not be populated. Jul 22, 2020 at 11:16
1

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.

You must log in to answer this question.

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