LookupRows by ID, with multiple Ids

Question

Within Salesforce Marketing Cloud, I have an email template. Every day the email will go out. The email recommends up to 3 jobs posted on our site, to each Contact in the data extension.

This data extension is created with complicated SQL Activities and SSJS Activities Automation Studio.

The data extension looks similar to this:

ContactId   FirstName   Job_Id_1    Job_Id_2    Job_Id_3

For each Contact, there might not be 3 jobs to send. So Job_Id_1 and Job_Id_2 might be filled, but Job_Id_3 might be empty.

I'd want to look up the data for each job by it's id, and then put some info in the email.

I'm trying to find a clean way of looping over the results, parsing the fields, and printing the same HTML structure with dynamic data.

This is what I'm trying to do, but it gives an error, because I use "Id" more than once.

SET @rows = LookupRows("JobsDataExtension", "Id", Job_Id_1, "Id", Job_Id_2, "Id", Job_Id_3)
SET @rowCount = RowCount(@rows) // One row for each Job that actually had an ID set

I could look up each one at a time:

SET @jobOneRows = LookupRows("JobsDataExtension", "Id", Job_Id_1)
SET @jobTwoRows = LookupRows("JobsDataExtension", "Id", Job_Id_2)
SET @jobThreeRows = LookupRows("JobsDataExtension", "Id", Job_Id_3)
SET @jobOneRowCount = RowCount(@jobOneRows)
SET @jobTwoRowCount = RowCount(@jobTwoRows )
SET @jobThreeRowCount = RowCount(@jobThreeRows )

But then I'd end up having to copy and paste a lot of code:

%%[if @jobOneRowCount > 0 THEN]%%
...Lots of HTML with dynamic data...
%%[endif]%%

%%[if @jobTwoRowCount > 0 THEN]%%
...Lots of Copied HTML with dynamic data...
%%[endif]%%

%%[if @jobThreeRowCount > 0 THEN]%%
...Lots of Copied HTML with dynamic data...
%%[endif]%%

Please tell me there is a better way than this...

Thanks

Answer 1

If your field names contain numbers at the end like in example, you can create a loop that iterates set number of times and build lookup value dynamically:

FOR @i = 1 TO 3 DO 
    SET @row = LookupRows('JobsDataExtension', 'Id', AttributeValue(Concat('Job_Id_', @i))
    IF RowCount(@row) > 0 THEN
        /* do something here*/
    ENDIF
NEXT @i