3

I have a filtered data extension where some contacts have multiple rows, containing unique product, i.e.

Contact ID | ProductName

Contact 1  | Product A

Contact 1  | Product B

Contact 2  | Product A

Contact 3  | Product A

Contact 4  | Product A

Contact 4  | Product C

I would like to display the data in the body of an email text as:

Contact 1: Product A and Product B

Contact 2: Product B

Contact 3: Product A

Contact 4: Product A and Product C

This doesn't seem like it should be hard to do but I haven't been able to find the right ampscript / template to do so (I am very new to this and figuring things out as I go).

3
  • Does this need to be done in email at send time, or can this be prepared prior to send via SQL? Jun 21, 2023 at 20:50
  • Ideally I'd like to do it at send time
    – Laura T.
    Jun 21, 2023 at 21:00
  • I found ampsript that works for a table, but I'd really prefer the values inline. I also found this ampscript to do that, but can't seem to translate it to my dataset, stackoverflow.com/questions/25691654/…
    – Laura T.
    Jun 21, 2023 at 21:04

2 Answers 2

1

There are 2 scenarios:

  • A contact ID only has a single product. In this case, you just need to populate its values when you are sure it's a single one.
  • A contact ID has multiple products. This one is a complicated one. You can use a loop and then add contact's products into a staging variable and hold the product values until it hits the row number of that contact and lastly you populate the information.

First, you can add an addition column with a default value of 1. Let's call it "Remark".

enter image description here

From there, you can use LookupOrderedRows function to get all rows and sort them in order.

%%[
    var @contact_w_multi_rows, @products, @cnt
    set @contact_w_multi_rows = ""
    set @products = ""


    set @data = LookupOrderedRows("Your DE name", 0, "Contact ID Asc", "Remark", "1")
    set @rowcnt = RowCount(@data)
    if @rowcnt > 0 then
        for @i = 1 to @rowcnt do
            set @row = Row(@data, @i)
            set @contactID = Field(@row, "Contact ID")
            set @ProductName = Field(@row, "ProductName")

            /* Check if the contact has a single product or multiple products */
            set @contact_rows = LookupRows("Your DE name", "Contact ID", @contactID)
            set @num = RowCount(@contact_rows)
            if @num > 1 then
            
                if @contactID != @contact_w_multi_rows then
                    set @contact_w_multi_rows = @contactID
                    set @products = @ProductName
                    /* reset counter when a new contact ID shows up*/
                    set @cnt = 1
                else
                    set @products = Concat(@products, ' and ', @ProductName)
                    set @cnt = Add(@cnt, 1)
                    /* counter hits contact rows, display the result */
                    if @cnt == @num then

]%%

%%=v(@contact_w_multi_rows)=%%: %%=v(@products)=%%<br><br>

%%[

                    endif
            
                endif
                
]%%

%%[
                
            else
]%%

%%=v(@contactID)=%%: %%=v(@ProductName)=%%<br><br>

%%[
            endif

        next @i
    endif
]%%

In the code above, I use the LookupRows function to get the row number of a contact, then compare it with the counter to decide if the current iteration is for the last row of a contact, so I can use it to show it in the email body.

1
  • thank you for the feedback!
    – Laura T.
    Jun 23, 2023 at 12:27
1

I would try the following to output your needs.

In order to use this, you will need to add a field to your source data extension. This field should be:

name: LU
dataType: Text
maxLength: 1
default: 1

You then want to make sure that this field is filled with a value of 1 in each record. This is added to make sure you can utilize the AMPscript lookup capability to grab all records from this DE.

In order to ensure not just that the Contact IDs are correctly ordered, but also the Products are ordered alphabetically, I added additional sorting in the LookupOrderedRows.

I also added in the ability that if there is more than 2 products per Contact, that it will change to comma on previous and only the final one will have the ' and '.

If your source Data Extension return contains more than 2500 records (which would be crazy, but possible I guess), you would replace the 0 in the LookupOrderedRows to instead be DataExtensionRowCount("myDEexample") to get full Data Extension. (Just do be aware that the larger the volume, the slower the email will process)

Here is example of my dataset:

enter image description here

I then run the below code:

%%[
SET @myRS = LookupOrderedRows("myDEexample",0,"Contact ID ASC, ProductName ASC", "LU",1) 
SET @RC = RowCount(@myRS)

FOR @i = 1 TO ADD(@RC,1) DO

  IF @i <= @RC THEN
    SET @Row = Row(@myRS,@i)
    SET @contactID = Field(@Row,"Contact ID")
    SET @ProductName = Field(@Row,"ProductName")

    IF @prevID == @contactID THEN
      SET @productString = Replace(@productString, " and ", ", ") /* Replace previous ' and ' to comma if more than 2 records */
      SET @productString = CONCAT(@productString, " and ", @ProductName)
      SET @productOutput = @productString
    ELSE
      SET @productString = @ProductName
    ENDIF
  ELSE
    SET @override = 1
  ENDIF

  IF (@prevID != @contactID AND NOT EMPTY(@prevID)) OR @override == 1 THEN]%%

    <p>%%=v(@prevID)=%%: %%=v(@productOutput)=%%<p>
       
    %%[  
      SET @productOutput = @productString 
  ENDIF

SET @prevID = @contactID

NEXT @i
]%%

Which then will output:

enter image description here

Hopefully this helps give you what you need.

2
  • Thank you for this - I will try it out!
    – Laura T.
    Jun 23, 2023 at 12:27
  • 1
    It took me a while to understand why you make the loop run to ADD(@RC,1). Good idea, Greg
    – Duc Le
    Jun 27, 2023 at 10:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .