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

I have a landing page that needs to display a list of all the records in a data extension in real time. In the past we have done this with a subset of the data using the LookupRows function. This works great, but from what I can see it only returns a subset not the entire data extension.

Is there a way in ampscript to return all the rows from a Data Extension?

Best Answer

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.

Related Solutions

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

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.

[SalesForce] Using AMPScript to retrieve multiple rows from a data extension

Welcome to SFSE, Antonio!

Props for posting your WIP code in your question.

Here's how I would code it:

%%[
var @rows, @row, @rowCount, @numRowsToReturn, @uid, @i

set @uid = AttributeValue("user_id")
set @numRowsToReturn = 0 /* all */
set @rows = LookupOrderedRows("DAILY SALES DE",@numRowsToReturn,"resource_name asc, store_purchases asc","User_ID", @uid)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    for @i = 1 to @rowCount do

        var @resource_name, @store_purchases
        set @row = row(@rows,@i) 
        set @resource_name = field(@row,"resource_name")
        set @store_purchases = field(@row,"store_purchases")

        if @i == 1 then
          outputline(concat("<ul>"))
        endif

        ]%%

           <li style="border-bottom: 1px solid #ebeced;">
                %%=v(@resource_name)=%%&nbsp;<span style="color:#5c656e;"><i><b>%%=v(@store_purchases)=%%</b></i></span>
           </li>

        %%[ 

        if @i == @rowcount then
          outputline(concat("</ul>"))
        endif


    next @i 

]%%

%%[ else ]%%

No rows found

%%[ endif ]%%

You won't want to comment out anything since the AMPScript needs to output HTML.

Reference:

Best Answer

Related Solutions

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

[SalesForce] Using AMPScript to retrieve multiple rows from a data extension

Related Topic