Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It only takes a minute to sign up.
Sign up to join this community
We have a application that is using the Fuel SDK and we want the user to be able to search for a Data Extension.
It seems like FuelSDK only allows you to find DE based on CustomerKey or Name match. However the name match appears to be precise (i.e it only finds DE named exactly the same as your value).
Is there anyway of querying with a like% or returning all DE and parsing through them in our PHP app?
You'd need to retrieve the Data Extension object using a SimpleFilterPart - it supports the 'like' operator, something like this...
SOAP Example
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Header>
<Security xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
<UsernameToken>
<Username>{{Username}}</Username>
<Password>{{Password}}</Password>
</UsernameToken>
</Security>
</Header>
<Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
<RetrieveRequest>
<ObjectType>DataExtension</ObjectType>
<Properties>ObjectID</Properties>
<Properties>CustomerKey</Properties>
<Properties>Name</Properties>
<Properties>IsSendable</Properties>
<Properties>SendableSubscriberField.Name</Properties>
<Filter xsi:type="SimpleFilterPart">
<Property>Name</Property>
<SimpleOperator>like</SimpleOperator>
<Value>ctiv</Value>
</Filter>
</RetrieveRequest>
</RetrieveRequestMsg>
</Body>
</Envelope>
Returns...
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<soap:Header>
<wsa:Action>RetrieveResponse</wsa:Action>
<wsa:MessageID>urn:uuid:b1cb2645-c4c2-4dca-8a0a-1d004c1fe50a</wsa:MessageID>
<wsa:RelatesTo>urn:uuid:1be2f352-ce3f-4ba9-b24a-43b040a25a53</wsa:RelatesTo>
<wsa:To>http://schemas.xmlsoap.org/ws/2004/08/addressing/role/anonymous</wsa:To>
<wsse:Security>
<wsu:Timestamp wsu:Id="Timestamp-9e5b820f-32b0-4f1d-8b0b-99373eb56321">
<wsu:Created>2017-08-07T12:33:30Z</wsu:Created>
<wsu:Expires>2017-08-07T12:38:30Z</wsu:Expires>
</wsu:Timestamp>
</wsse:Security>
</soap:Header>
<soap:Body>
<RetrieveResponseMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
<OverallStatus>OK</OverallStatus>
<RequestID>b872a681-e291-4df8-b5cd-86178c4b915d</RequestID>
<Results xsi:type="DataExtension">
<PartnerKey xsi:nil="true" />
<ObjectID>fd32e4b5-00aa-e611-8c4c-00110a687a79</ObjectID>
<CustomerKey>PREDICTIVE_SCORES</CustomerKey>
<Name>PREDICTIVE_SCORES</Name>
<IsSendable>false</IsSendable>
</Results>
<Results xsi:type="DataExtension">
<PartnerKey xsi:nil="true" />
<ObjectID>0c6e94d9-427b-e711-80cb-1402ec723e3c</ObjectID>
<CustomerKey>ActivityInstance</CustomerKey>
<Name>ActivityInstance</Name>
<IsSendable>false</IsSendable>
</Results>
<Results xsi:type="DataExtension">
<PartnerKey xsi:nil="true" />
<ObjectID>136e94d9-427b-e711-80cb-1402ec723e3c</ObjectID>
<CustomerKey>Activity</CustomerKey>
<Name>Activity</Name>
<IsSendable>false</IsSendable>
</Results>
</RetrieveResponseMsg>
</soap:Body>
</soap:Envelope>
PHP Example
I never touch the SDKs, but PHP should look like this...
require('ET_Client.php');
$myclient = new ET_Client();
$dataextension = new ET_DataExtension();
$dataextension.filter = array('Property' => 'Name','SimpleOperator' => 'like','Value' => 'ctiv');
$dataextension->authStub = $myclient;
$response = $dataextension->get();
print_r($response);
Note: The above examples do the equivalent of a SQL "LIKE ('%ctiv%')" and not "LIKE ('ctiv%')". For testing just the ending of the Name of the Data Extension, you would need to retrieve all DEs and loop through them all evaluating each on the client-side, as @Eliot Harper states.
From my understanding and knowledge of the SDK Documentation it shows no way to do a LIKE type search to retrieve a data extension, it can only be specified by the exact name or customer key of a data extension.
PHP Example:
require('ET_Client.php');
$myclient = new ET_Client();
$dataextensionrow = new ET_DataExtension_Row();
$dataextensionrow->authStub = $myclient;
//Retrieve with Name
$dataextensionrow->Name = 'ExampleDEName';
//----------------OR------------------
//Retrieve with Customer Key
$dataextensionrow->CustomerKey = 'ExampleDECustomerKey';
$dataextensionrow->props = array('FirstName', 'LastName', 'AnotherColumnName');
$response = $dataextensionrow->get();
print_r($response);
You would need to retrieve all Data Extensions using the SOAP API then parse the response to search for the matching DE's. Here's a working SOAP request envelope (which you can test in Postman):
POST https://webservice.s7.exacttarget.com/Service.asmx // update endpoint based on your account
Content-Type: text/xml
<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<s:Header>
<a:Action s:mustUnderstand="1">Retrieve</a:Action>
<a:MessageID>urn:uuid:7e0cca04-57bd-4481-864c-6ea8039d2ea0</a:MessageID>
<a:ReplyTo>
<a:Address>http://schemas.xmlsoap.org/ws/2004/08/addressing/role/anonymous</a:Address>
</a:ReplyTo>
<a:To s:mustUnderstand="1">https://webservice.s7.exacttarget.com/Service.asmx</a:To>
<fueloauth xmlns="http://exacttarget.com">{{accessToken}}</fueloauth>
</s:Header>
<s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
<RetrieveRequest>
<ObjectType>DataExtension</ObjectType>
<Properties>CustomerKey</Properties>
<Properties>Name</Properties>
</RetrieveRequest>
</RetrieveRequestMsg>
</s:Body>
</s:Envelope>
This returns a complete list of all Data Extensions in the Business Unit:
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<env:Header xmlns:env="http://www.w3.org/2003/05/soap-envelope">
...
</env:Header>
<soap:Body>
<RetrieveResponseMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
<OverallStatus>OK</OverallStatus>
<RequestID>ec694a78-1a17-4ed6-b0aa-e54e32b40e04</RequestID>
<Results xsi:type="DataExtension">
<PartnerKey xsi:nil="true" />
<ObjectID xsi:nil="true" />
<CustomerKey>18B816A8-AA0F-45C2-8B29-A9F50177AC3E</CustomerKey>
<Name>Member_Reporting</Name>
</Results>
<Results xsi:type="DataExtension">
<PartnerKey xsi:nil="true" />
<ObjectID xsi:nil="true" />
<CustomerKey>04F14861-CEDF-47A4-88BD-0FEA06A346A4</CustomerKey>
<Name>Funding</Name>
</Results>
...
</RetrieveResponseMsg>
</soap:Body>
</soap:Envelope>
