I currently have 2 Data Extensions that are being imported via FTP into Marketing Cloud.

DE1 contains user information with a primary key Vehicle_Model_ID. DE1 is used to collect the profile attributes of people submitting their requests for a test drive, so it also contains their email addresses. This is a people-focused Data Extension.

DE2 contains vehicle information with a primary key Vehicle_Model_ID. DE2 contains information about the vehicle like it's make, model AND it contains the information about the 3 recommended vehicles that "match up" with the vehicle. This is a data extension containing vehicle information.

What I was going to do was create a data relationship between DE1 and DE2 - so I could create a data filter out of the combined fields across both these tables, run the filter every day and dump the results in DE3. Now if this can work, how can I extend the code that we already wrote up for a single lookup to automatically pick up the Model_ID? That's the ONLY missing piece I guess.

We can figure out the rest of the code to fetch data about the Recommended Vehicle 1, Recommended Vehicle 2 and Recommended Vehicle 3 - and create the content block around each of them. Please tell me that I won't have to write a line of code that contains 480 items in it - as we have a list of 480 Vehicle_Model_IDs.

  • I hope I'm understanding this correctly - but it sounds like you just need to call the DE column Vehicle_Model_ID. That is, if your target data filter contains the column Vehicle_Model_ID as a result of the JOIN, for you to display it in the email, you'll need to type %%Vehicle_Model_ID%% for it to display. You can also assign the value to a variable to then use for looping through the recommendations if needed. Ex: Set @modelID = [Vehicle_Model_ID] – Eduardo Arias Apr 4 '17 at 20:01

I'd forget the Data Relationship and just do a simple lookup in your email to retrieve the vehicle information. Something like this:

var @rows, @row, @rowCount, @numRowsToReturn, @model_id, @i

set @model_id = AttributeValue("Vehicle_Model_Id") /* from sending Data Extension */
set @numRowsToReturn = 0 /* 0 means all */
set @rows = LookupOrderedRows("VehicleInformation",@numRowsToReturn,"Make desc, Model asc","Model_Id", @model_id)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    for @i = 1 to @rowCount do

        var @Make, @Model
        set @row = row(@rows,@i) /*get row based on loop counter */
        set @Make = field(@row,"Make")
        set @Model = field(@row,"Model")


        <!-- Vehicle HTML goes here --> 
        Row %%=v(@i)=%%, Make: %%=v(@Make)=%%, Model: %%=v(@Model)=%%


    next @i ]%%

%%[ else ]%%

No vehicles found

%%[ endif ]%%


| improve this answer | |

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.