Fetching a content from Data Extension in an Email

Question

I am trying to fetch content from one DE(Let's say A) and send it to another DE(Let's say B), where there is no common field between these Data extensions. I used this code below to fetch the data but it just didn't pull anything.

Here is the code below,

%%[
var @p1, @p2, @p3, @p4, @p5, @today 
SET @today = Format(Now(), "M/dd/yy")
SET @rows = LookupRows("TestProducts", "Date", @today)

IF RowCount(@rows) > 0 THEN<

SET @row = Row(@rows,1)

SET @p1 = Field(@row,"Products")
SET @p2 = Field(@row,"Title")
SET @p3 = Field(@row,"Image")
SET @p4 = Field(@row,"Link")
SET @p5 = Field(@row,"Price")
ENDIF

]%%

%%=v(@p1)=%%
%%=v(@p2)=%%
%%=v(@p3)=%%
%%=v(@p4)=%%
%%=v(@p5)=%%

I have few doubts regarding this issue,

1. If I wanted to send an email to a Data extension A and pull content from a Data extension B, should this both DE's needs to be linked or synchronized to each other? (I saw this somewhere that both DE's needs to be synchronized)

2. I don't have any common field between these two DE's but I would like to pull content using the lookup, is this a right approach?

3. Small information about the above code:

Date is the field I added in my Data Extension ("TestProducts") with the data type date and with the current date being populated. And @today is a variable where we pull today's date using a function & assign to it.

But I was thinking that the content was not pulled in because the value in Date & @today didn't match.

Because in the DE the 'Date' field has time included in it. but in @today we only get the month, date & year Format(Now(), "M/dd/yy")

So do you think this could be the reason why it's not pulling any content?

Answer 1

Here are the answers to your questions:

Question 1: Technically, there is no need for the two data extensions to be linked if you are trying to pull data using AMPScript. However, if you plan to use these two data extenions in Data Filter or Journey Builder, you need to create a link between them.

Question 2: There is no problem with your approach as you are trying to pull data from TestProducts data extension based on the date field.

Question 3: All dates in a data extension in Marketing Cloud are stored in the 12-hour format of MM/DD/YYYY HH:MM:SS. Documentation

You have several options here.

Option 1 : If you don't need the time part of the date field in your TestProducts data extension, you can change your date's field data type to text. This way, you can store only the date part using the CONVERT function in your query (assuming that your DE is populated using an sql query).

Option 2: You can do an AMPScript lookup without using another field than date. That way, you'll pull all the data (limited to 2000 records, otherwise you need to use the API) and then do a loop to filter out the needed rows.

Your code will be something like:

%%[
var @p1, @p2, @p3, @p4, @p5, @today 
SET @today = Format(Now(), "M/dd/yy")
SET @rows = LookupRows("TestProducts", "OtherField", @otherField)

IF RowCount(@rows) > 0 THEN
    FOR @i TO ROWCOUNT(@Rows)
        SET @row = Row(@rows,@i)
        SET @date = Field(@row,"Date")
        SET @FormattedDate = FormatDate(@date, "M/dd/yy")
        IF @today == @FormattedDate THEN
            SET @p1 = Field(@row,"Products")
            SET @p2 = Field(@row,"Title")
            SET @p3 = Field(@row,"Image")
            SET @p4 = Field(@row,"Link")
            SET @p5 = Field(@row,"Price")
        ENDIF
    NEXT
ENDIF

]%%

PS: Lookups using AMPScript can not retrieve more than 2000 rows. If your data extension rows exceed 2000, you need to use the API. Documentation