[SalesForce] Multiple rows using Loop in Ampscript

I want to print some rows in a table of the HTML email based on the count of rows (rowcount) that match the criteria.

According with the number of rows, it have to show the number of informations. For example: 1 row = 1 info, 2 rows = 2 infos.

I'm using 'FOR' to do a loop and populate the table rows with the records, but it's not working as expected. It shows only one row even when it must show more than one.

Is there a way to resolve this?

I am using the code below:

%%[

set @localjob = AttributeValue('Questionario__c:Local_de_Avaliacao__c')
set @nomejob = AttributeValue('Questionario__c:Nome_Job_simplificado__c')
set @id = AttributeValue('Questionario__c:Pesquisador__c')

set @Questionario = retrievesalesforceobjects('Questionario__c','Pesquisador__c,Status_anterior_do_questionario__c,Nome_Job_simplificado__c,Local_de_Avaliacao__c,Status_do_Questionario__c','Status_anterior_do_questionario__c','=','Em Andamento','Status_do_Questionario__c','=','Expirado','Pesquisador__c','=',@id)

set @rowcount = rowcount(@Questionario)


set @styletd = "-ms-text-size-adjust: 100%; -webkit-text-size-adjust: 100%; border: 1px solid #606060; border-collapse: collapse !important; color: #606060; font-family: 'Ubuntu', 'Candara', 'Arial', sans-serif; font-size: 10px; line-height: 120%; padding: 8px 0;"


for @contador = 1 to @rowcount do

set @rowcelula = row(@Questionario,@contador)

set @fieldcelula1 = field(@rowcelula,'Nome_Job_simplificado__c')

set @fieldcelula2 = field(@rowcelula,'Local_de_Avaliacao__c')


set @linha = CONCAT('<tr><td align="center" style="',@styletd,'">',@fieldcelula1,'</td>','<td align="center" style="',@styletd,'">',@fieldcelula2,'</td></tr>')


next @contador

]%%

Best Answer

You need to output the concatenated variable using the V() AMPScript function, you can set this inside your for loop so on each iteration, this can be achieved with the refined code below, I have not tested this code but it should work.

I added an opening and closing <table> tag in case it is required otherwise you can remove that.

%%[

set @localjob = AttributeValue('Questionario__c:Local_de_Avaliacao__c') set @nomejob = AttributeValue('Questionario__c:Nome_Job_simplificado__c') set @id = AttributeValue('Questionario__c:Pesquisador__c')

set @Questionario = retrievesalesforceobjects('Questionario__c','Pesquisador__c,Status_anterior_do_questionario__c,Nome_Job_simplificado__c,Local_de_Avaliacao__c,Status_do_Questionario__c','Status_anterior_do_questionario__c','=','Em Andamento','Status_do_Questionario__c','=','Expirado','Pesquisador__c','=',@id)

set @rowcount = rowcount(@Questionario)


set @styletd = "-ms-text-size-adjust: 100%; -webkit-text-size-adjust: 100%; border: 1px solid #606060; border-collapse: collapse !important; color: #606060; font-family: 'Ubuntu', 'Candara', 'Arial', sans-serif; font-size: 10px; line-height: 120%; padding: 8px 0;"

]%%

<table>

%%[ for @contador = 1 to @rowcount do

set @rowcelula = row(@Questionario,@contador)

set @fieldcelula1 = field(@rowcelula,'Nome_Job_simplificado__c')

set @fieldcelula2 = field(@rowcelula,'Local_de_Avaliacao__c')

set @linha = CONCAT('<tr><td align="center" style="',@styletd,'">',@fieldcelula1,'</td>','<td align="center" style="',@styletd,'">',@fieldcelula2,'</td></tr>')

v(@linha)

next @contador ]%%

</table>

Reference

V AMPScript Function

Related Solutions

[SalesForce] Using AMPScript to retrieve multiple rows from a data extension

Welcome to SFSE, Antonio!

Props for posting your WIP code in your question.

Here's how I would code it:

%%[
var @rows, @row, @rowCount, @numRowsToReturn, @uid, @i

set @uid = AttributeValue("user_id")
set @numRowsToReturn = 0 /* all */
set @rows = LookupOrderedRows("DAILY SALES DE",@numRowsToReturn,"resource_name asc, store_purchases asc","User_ID", @uid)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    for @i = 1 to @rowCount do

        var @resource_name, @store_purchases
        set @row = row(@rows,@i) 
        set @resource_name = field(@row,"resource_name")
        set @store_purchases = field(@row,"store_purchases")

        if @i == 1 then
          outputline(concat("<ul>"))
        endif

        ]%%

           <li style="border-bottom: 1px solid #ebeced;">
                %%=v(@resource_name)=%%&nbsp;<span style="color:#5c656e;"><i><b>%%=v(@store_purchases)=%%</b></i></span>
           </li>

        %%[ 

        if @i == @rowcount then
          outputline(concat("</ul>"))
        endif


    next @i 

]%%

%%[ else ]%%

No rows found

%%[ endif ]%%

You won't want to comment out anything since the AMPScript needs to output HTML.

Reference:

[SalesForce] Loop Support in AMPscript

I have put some time into this and in the resources it only lists FOR/TO(DOWNTO) as a viable looping option.

I have attempted FOR/IN and received a definitive syntax error that FOR requires to be followed by TO or DOWNTO - which means the functionality is definitely not there.

I then investigated using WHILE, which has no documentation at all and provided no errors when used - BUT it did not actually do what it was supposed to do. After looking through the code and using AMPScript editors to check syntax, etc. I have found out that both DO and WHILE are not recognized as commands. This means that there is no inherit capability to achieve these.

Basically what it comes down to is that the only available inherit looping function in AMPScript is FOR/TO(DOWNTO) and without tremendous 'hacking' of the inherit functionality (not even sure THAT is possible), of which I have not yet found a good use case, it is not possible to use the other loop types directly in AMPScript. You would need to use SSJS to achieve these.

SFMC support (helpful as always) directed me to the documentation resource listed above and told me those are the only available commands/functions.

Best Answer

Reference

Related Solutions

[SalesForce] Using AMPScript to retrieve multiple rows from a data extension

[SalesForce] Loop Support in AMPscript

Related Topic