AMPscript lookup based on multiple IDs on the same data field

Question

I am trying to create an abandoned cart journey, where on the bottom of the email, the items left in the cart by the person, would be listed below each other. There is no maximum limit of items one can have left in their basket. I have the abandoned cart data coming into Salesforce Sales Cloud, and the IDs of the products that are left in the basket are populated in a custom text field (Last_abandoned_cart_lineitem_Product_IDs), separated by commas (etc. 6569307013328,6569349873872,6645332050128).

In Marketing Cloud, I have a data extension named "EcommerceProductImageUrls", which contains the Product titles, IDs and image urls of all of the products we have in our online store. I am attempting to create AMPscript that would look up the product titles and image urls from the DE of the products left in the cart, based on the product IDs in the Salesforce field. I have managed to get the code to the point where if the field contains only one product id, the image and the title is shown in the email. But I can't figure out how to do it, if the field contains multiple product ids, separated with a comma? All of the product ids in the custom field should be looked up and stacked below each other in the email. I also don't know how I then get the images and titles to be listed below each other, when the number of products one can have in their basket is not fixed?

Here is the code I have managed to write so far (please excuse me, I am new to AMPscript):

%%[

var @ProductID, @ProductTitle, @ProductImageURL, @rows, @row, @rowCount

Set @ProductID = AttributeValue('Account:PersonContact:Last_abandoned_cart_lineitem_Product_IDs__c')
Set @rows = LookupRows("EcommerceProductImageUrls", "ProductID", @ProductID)
Set @rowCount = rowcount(@rows)

if @rowCount > 0 then

var @i
for @i = 1 to @rowcount do
set @row = Row(@rows,@i) 

]%%


      <center><img style="border: 0;display: block;height: auto;width: 150px;max-width: 1200px;" alt="" width="150" src="%%=Field(@row, "ProductImageURL")=%%"></center>

<p style="text-align: center; font-size: 16px">
  <span style="color: #FFFFFF">%%=Field(@row, "ProductTitle")=%%</span>
</p>
   

%%[ next @i ]%%

%%[ else ]%%

%%[ endif ]%%

Answer 1

Use BuildRowsetFromString to split your delimited string into Ids.

Try this :

%%[
    var @ProductID, @Product, @ProductRecord, @Ids
    Set @ProductID = AttributeValue('Account:PersonContact:Last_abandoned_cart_lineitem_Product_IDs__c')
    set @Ids = BuildRowsetFromString(@ProductID, ",")
    FOR @i = 1 to ROWCOUNT(@Ids) DO
        SET @ProductId = Field(ROW(@ids, @i),1)
        SET @Product = LookupRows("EcommerceProductImageUrls", "ProductID", @ProductID)
        SET @ProductRecord = row(@Product, 1)
]%%
    <center><img style="border: 0;display: block;height: auto;width: 150px;max-width: 1200px;" alt="" width="150" src="%%=Field(@ProductRecord, "ProductImageURL")=%%"></center>
    <p style="text-align: center; font-size: 16px"><span style="color: #FFFFFF">%%=Field(@ProductRecord, "ProductTitle")=%%</span></p>
%%[ next @i ]%%

See here and here for further details on how to split delimited strings.

Of course add the correct checks on max values and non empty strings.