I have a question regarding AMPScript. My Data Extensions are attached in the link at end of this question(For reference). I want to send personalised email adding product name.

var @productname
set @productname = lookup("Products","product_name","product_id",[product_id]) 

<h1> You just purchased : %%= v(@productname) =%%

Please can someone correct me ?

Also, should I link these tables in Data designer ??? [1]: https://i.stack.imgur.com/Fmq1p.png


First of all, you don't need to link the data extensions in Data Designer, in order to use Lookup function.

There are few issues in your code. I assume you get product_ID from your sendable data extension? If this is the case, I will start by declaring it using attributevalue. You will then be able to reference it in the lookup call. Also, you need to get rid of the spaces where you want to print your product name. This should work:

set @orderid = attributevalue("order_id")
set @orderrows = LookupRows("orders","order_id", @orderid)
set @orderRowCount = rowcount(@orderrows)
if @orderRowCount > 0 then
for @i = 1 to @orderRowCount do
set @row = row(@orderRowCount, @i)
set @productid = field(@row,"product_id")
set @productname = lookup("Products","product_name","product_id",@productid) 

<h1>You just purchased : %%=v(@productname)=%%</h1>

%%[ next @i ]%%

%%[ else ]%%
No order rows found
%%[ endif ]%%
  • Thank your for your answer. But what if my product_id is not in the Sendable data extension. How can I modify the code to display the product_name?
    – Guest17
    Jan 8 at 14:28
  • Well - it must be somewhere in the context of the send, @Guest17. How would we otherwise be able to know which product to lookup the name for? Jan 8 at 14:35
  • yes you are right, I got your point. I am a bit confused, I am referring to this table-i.stack.imgur.com/Fmq1p.png. Can I identify which product customer has bought by Order_id?
    – Guest17
    Jan 8 at 14:45
  • @Guest17 I digged more into your data model. You need to lookup the order rows for the recipient, and for each row lookup the product name. See my updated answer Jan 8 at 14:54
  • Thanks you very much for this answer. This is what I exactly wanted
    – Guest17
    Jan 8 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.