[SalesForce] Returning the complete results from a Data Extension including field names

First question on here and new user of Ampscript – please be gentle!

Primarily for debug purposes I would like to use the LookupRows function to return a subset of rows from a number of Data Extensions, but rather than then returning a specific field I would like to return all the fields from all the rows including the header row.

As I will be repeating this process on Data Extensions of all shapes and sizes, I was looking for a way to do with programatically, and thus dynamically deal with Data Extensions with different field and row counts.

I did look through the documentation but couldn't find an example, or even a function that would return a field header.

Best Answer

Have you tried server side javascript for this? You can either get all the fields returned as a JSON object:

http://help.exacttarget.com/zh-HK/documentation/exacttarget/content/server_side_javascript/server_side_javascript_syntax_guide/core_library_server_side_javascript_functions/data_extension_server_side_javascript_functions/#section_11

or even get all the rows with all fields as a JSON object: http://help.exacttarget.com/zh-HK/documentation/exacttarget/content/server_side_javascript/server_side_javascript_syntax_guide/core_library_server_side_javascript_functions/data_extension_server_side_javascript_functions/#section_25

Note that the retrieve is limited to 2500 rows.

You can then access any of this data in ampscript by passing values between them. There are further articles on how to achieve this.

Related Solutions

[SalesForce] Exacttarget data extension – How to retrieve all rows in AMPScript

The only way I know of to do this is to create a field in the data extension that has a default value, since this value will be the same for every record you use this field/value pair as your lookup criteria.

So for example add a field called "return", with a default value of "1". Then use this code:

LookupRows("YOUR DE NAME","return","1")

This isn't recommended for large data sets.

[SalesForce] How to use ampscript in order to pull user data from a second data extension

The "Lookup()" function should give you the results needed. This will allow you to send to any list or data extension and pull back records from a completely different data extension. In order for this to work, your Unique Subscriber ID on the Lookup DE would need to relate back to the subscriber you're sending to. For example, if your Specific ID matches up with Subscriber Key on your account, you can setup something similar to the below.

More info can be found here as well: Data Extension Ampscript Functions

The below creates 3 variables. "SubscriberAnswer" does your lookup to pull the value from your other data extension. This says, pull the answer from the row of data where the Column "UniqueID" matches the value of "_SubscriberKey" for the subscriber being sent to. I've added the variable "Survey" to generate a standard response when their answer is not null. Then I perform an IF/Then statement in the variable "Answer" to say, If the variable "SubscriberAnswer" is null, pull back "Please fill out Survey", otherwise just pull back the value for "SubscriberAnswer". Hope that is clear and useful. Thanks!

%%[
Var @SubscriberAnswer, @Survey, @Answer
Set @SubscriberAnswer = Lookup("Survey","Answer","UniqueID",_subscriberkey)
Set @Survey = Concat("Thank You.  Your results can be found here:", "  ", @SubscriberAnswer)
Set @Answer = IIF(Empty(@SubscriberAnswer), "Please fill out Survey", @Survey)
]%%
<p>
%%=v(@Answer)=%%
</p>

Best Answer

Related Solutions

[SalesForce] Exacttarget data extension – How to retrieve all rows in AMPScript

[SalesForce] How to use ampscript in order to pull user data from a second data extension

Related Topic