0

I have a data extension in Marketing Cloud with data about tasks per account per contact. A contact can be related to more than one account and can have multiple tasks in each account.

I need to send one email to a person with information about tasks in each account. For example contact c1 should receive an email like this:

Tasks in a1:

  • task1
  • task2
  • task3

Tasks in a2:

  • task1

I attempted to achieve this in AMPscript via retrieving and a couple of loops, but I would end up outputting duplicate AccountNames

a1

  • task 1
  • task 2
  • task 3

a1

  • task 1
  • task 2
  • task 3

If I could use JavaScript I would iterate the records and put them in a map, but as far as I know Map is not supported by SSJS? I can have a script to prep data first (write to a field), but I'm not sure how to get rid of the duplicates.

Here's my ampscript:

%%[
SET @tasksDE = 'TasksDE'
SET @activities = LOOKUPROWS(@tasksDE, 'ContactId', Id)
SET @rowCount = rowCount(@activities)

IF @rowCount > 0 THEN
    FOR @i = 1 TO @rowCount DO
        SET @row = row(@activities, @i) /*get row*/
        SET @accountName = field(@row, "AccountName")
        SET @accountId = field(@row, "AccountId")
]%%
<p>Shop "%%=v(@accountName)=%%":</p>
%%[
        SET @activitiesPerAccount = LOOKUPROWS(@tasksDE, 'AccountId', @accountId)
        SET @rowCount1 = rowCount(@activitiesPerAccount)

        IF @rowCount1 > 0 THEN
            FOR @j = 1 TO @rowCount1 DO
                SET @row1 = row(@activitiesPerAccount, @j) /*get row*/ 
                SET @taskName = field(@row1, "TaskName")
]%%
<p>%%=v(@taskName)=%%</p>
%%[
            NEXT @j
        ENDIF
    NEXT @i
ENDIF
]%%

I am new to development on Marketing Cloud. Any help is appreciated.

1
  • 2
    Are you simultaneously using Output within the loop's block and v inline? Without seeing your code, everyone will just be guessing. So if you can, please edit your question to include code snippets of what you've tried so far.
    – Mark G
    Commented Jul 22, 2023 at 15:35

1 Answer 1

1

Based on what you provided, I have added some changes so you can get sorted data and use the nested loop for this case.

%%[ 
  SET @deName= "TasksDE"
  SET @Id = AttributeValue("_subscriberkey") 
  SET @initAccountId = ""

  SET @contactRows= LookupOrderedRows(@deName, 0 , "AccountId ASC, TaskName ASC", "ContactId",@Id)
  SET @rowCount = RowCount(@contactRows) 
    IF @rowCount > 0 THEN 
      FOR @i=1 TO @rowCount DO 
        SET @row = row(@contactRows, @i ) /*get row*/ 
        SET @accountName = field(@row,"AccountName") 
        SET @accountId = field(@row,"AccountId")
        IF @initAccountId != @accountId THEN
            SET @initAccountId = @accountId
]%%

<p>Shop "%%=v(@accountName)=%%":</p>

%%[
       SET @tasks= LookupRows(@deName,"ContactId", @Id, "AccountId",@initAccountId) 
       SET @rowCount1= rowCount(@tasks) 
        IF @rowCount1 > 0 THEN 
          FOR @j= 1 TO @rowCount1 DO 
            SET @row1 = row(@tasks,@j) /*get row*/ 
            SET @taskName= field(@row1,"TaskName") 
]%% 

<p>%%=v(@taskName)=%%</p>

%%[NEXT @j ENDIF]%% 
%%[ENDIF]%%
%%[NEXT @i ENDIF]%%

You can try it yourself here: https://mcsnippets.herokuapp.com/s/Gv5necBi

Note: This will work if your "TasksDE" has smaller 2000 records, otherwise you need to enhance it.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .