Is it possible to expose all rows in a Marketing Cloud data extension?

Question

I'm looking to display a list of items in an email dynamically- was wondering if this was possible to achieve with AmpScript?

For example, let's say I have an email like this:

Items now available near you: Apples Banana Orange

Where the DE contains the list of food items and the items are updated via API. I would like to get to a point where I don't have to manually change the content of the emails, and have the emails be updated as new items are added.

In addition, am I able to limit the number of rows shown? (IE Top 3). I have use cases for both exposing all rows as well as only showing the top 3 rows.

Answer 1

What you are looking for is the AMPscript function: LookUpOrderedRows().

To gather ALL the records from the DE, first you are going to need to add a field to the DE that is the same for each. I usually include a field 'Secret' and give it a default of '1' so I can retrieve all of the items inside the DE easily in an AMPscript lookup. You are also going to need to utilize another function as well: DataExtensionRowCount() if your DE will go above 2000 (default max row count).

Making something like:

%%[
  SET @DEname = "yourDEName"

  SET @DERowCount = DataExtensionRowCount(@DEname)

  SET @rows = LookupOrderedRows(@DEname,@DERowCount,"yourSortField Desc","Secret","1")
]%%

This will pull the rowset. From there you just loop through it using a FOR loop to gather and display the individual rows.

%%[ 
    FOR @i=1 TO Rowcount(@rows) DO

      SET @Row = Row(@rows, @i)

      SET @field = Field(@Row, "Fieldname")

    NEXT @i
]%%

You can also achieve this via SSJS Retrieve, but this comes with its own restrictions (2500 row max) and much slower processing.

And then with slight adjustments, you can limit and sort utilizing the same LookUpOrderedRows() function.

See example (top 10 fruit, sorted by subclass):

%%[

    SET @DEname = "food_for_sale"

    SET @rows = LookupOrderedRows(@DEname, 10, "subclass ASC", "Type", "Fruit")

]%%

This can then be iterated through with a FOR loop same as described above.