5

I'm currently using the POST and PUT methods to add and modify rows in custom data extensions (as documented here) but i don't see anywhere on the documentation nor the stackexchange a GET method to get rows for a specific ID ?

9

You'll have to use the SOAP API to retrieve DataExtensionObjects.

Here's a sample envelope that illustrates the structure of the call:

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Header>
      <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" soapenv:mustUnderstand="1">
         <wsse:UsernameToken xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="UsernameToken-24440876">
            <wsse:Username>XXXXX</wsse:Username>
            <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">XXXXX</wsse:Password>
         </wsse:UsernameToken>
      </wsse:Security>
   </soapenv:Header>
   <soapenv:Body>
      <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
         <RetrieveRequest>
            <ObjectType>DataExtensionObject[EXTERNALKEYOFDE]</ObjectType>
            <Properties>EMAIL_ADDRESS</Properties>
            <Properties>CUSTOMER_ID</Properties>
            <Properties>FIRST_NAME</Properties>
            <Properties>SITE_GROUP</Properties>
            <Filter xsi:type="SimpleFilterPart">
               <Property>EMAIL_ADDRESS</Property>
               <SimpleOperator>equals</SimpleOperator>
               <Value>test@example.com</Value>
            </Filter>
         </RetrieveRequest>
      </RetrieveRequestMsg>
   </soapenv:Body>
</soapenv:Envelope>

At this point in time, the SFMC SOAP API is significantly more complete/robust/whatever than the REST API. It's a good plan to follow the lead of the SFMC SDK, which utilizes both SOAP and REST.

2019-09-16 Update

This is floating around the community in some comments, but I thought I'd update my answer to include the undocumented REST route for retrieving a Data Extension row. The filter is specified in the URL.

GET /data/v1/customobjectdata/key/YOURDEEXTERNALKEY/rowset?$filter=emailAddress%20eq%20'test@example.com' HTTP/1.1
Host: YOURTENANT.rest.marketingcloudapis.com
Authorization: Bearer YOURAUTHTOKEN
Content-Type: application/json
User-Agent: PostmanRuntime/7.17.1
Accept: */*
Cache-Control: no-cache
Host: YOURTENANT.rest.marketingcloudapis.com
Accept-Encoding: gzip, deflate
Connection: keep-alive
cache-control: no-cache

Results

{
    "links": {
        "self": "/v1/customobjectdata/token/5f9c1ce3-8f0f-499c-a1dd-5a9bf6fbfa01/rowset?$page=1"
    },
    "requestToken": "71dac1af-88ed-48f1-bce2-259d2a1d5009",
    "tokenExpireDateUtc": "2019-09-17T18:48:03.03",
    "customObjectId": "9340eb5c-3a53-4490-a1f1-2d14c1b27c98",
    "customObjectKey": "YOURDEEXTERNALKEY",
    "pageSize": 2500,
    "page": 1,
    "count": 1,
    "items": [
        {
            "keys": {
                "emailAddress": "test@example.com"
            },
            "values": {
                "firstname": "Test",
                "lastname": "Person",
                "createddate": "7/18/2019 6:00:00 PM"
            }
        }
    ]
}
| improve this answer | |
4

If you're Data Extension is included in an Attribute Group in Contact Builder, then you can use the search REST API method to retrieve a value from one or more Data Extensions. For example, in this case I'm retrieving the First Name and Last name from a Contacts Data Extension based on a couple of criteria (from the Email address and a different Data Extension):

POST https://www.exacttargetapis.com/contacts/v1/attributes/search
Authorization: Bearer insertAccessTokenHere
Content-Type: application/json

{
    "request": {
        "attributes": [
            {
                "key": "Contacts.First Name"
            },
            {
                "key": "Contacts.Last Name"
            }]

    },
    "conditionSet": {
        "operator": "And",
        "conditionSets": [],
        "conditions": [{
            "attribute": {
                "key": "Email Addresses.Email Address"
            },
            "operator": "Equals",
            "value": {
                "items": ["sam@sample.com"]
            }
        },
        {
            "attribute": {
                "key": "Centre Subscriptions.valid"
            },
            "operator": "Equals",
            "value": {
                "items": [true]
            }
        }]
    }
}
| improve this answer | |
  • I don't think this works if a Contact has not been added for the matching row in the Data Extension. I've been testing this kind of retrieve and it didn't work for rows that had no Contact. – Adam Spriggs Oct 12 '17 at 19:51
  • Hmm, I didn't try that scenario. Good to know! – Eliot Harper Oct 12 '17 at 20:45
  • 1
    It's so crazy that you can add a row to DE with REST but can't retrieve one. Maybe I'm missing something in the documentation. – Adam Spriggs Oct 12 '17 at 21:11
  • 1
    Yes, that makes sense now I come to think of it. This is based on the Contact Model, so it will only work for contacts. If you're not using Contacts, then you will have to use SOAP. – Eliot Harper Oct 13 '17 at 0:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.