Loop through a list of items

If you have an advanced integration setup that is sending data in an array or JSON object, you can setup loops in your document to print that information. Examples include line items on an invoice or a list of items for a report.

From a technical standpoint, your data will look like this for HTTP POST (name/value pairs)

items[0][Name]=Shirt&items[0][Quantity]=2&items[0][Price]=19.95&items[1][Name]=Jeans&items[1][Quantity]=3&items[1][Price]=89.99

Or JSON:

{"items": [{"Name":"Shirt", "Quantity":"2", "Price":"19.95", "Total":"39.90"},{"Name":"Jeans", "Quantity":"3", "Price":"89.99", "Total":"269.97"}]}

Create a Simple List

If you have a list of items that you would just like to print in your document, you can use the following {foreach} code

{foreach from=$items item=_row}
{$_row.Quantity} x {$_row.Name} = ${$_row.Price|number_format:2}
{/foreach}

Create a Table

If you're generating an invoice, you're probably looking to create a table that loops through each of the line items and prints them on a new row in the table.  No problem!  We've added a new "tablerow" tag that you can use and it tells our system to treat the table row as a loop.  The tag works exactly like a "foreach" loop, so all you need to do is use "tablerow" instead of "foreach" and we'll handle the rest.

Here's an example:

Name Quantity Price Total
{tablerow from=$products item=_product}{$_product.Name} {$_product.Quantity} {$_product.Price} {$_product.Total}{/tablerow}

The data that you send through to a table row loop needs to be an array (ie and array of products) that can be easily looped through.  The part after the dot in the variable name is the individual property for that element in the array.

If you'd like to skip items in the list, you can use an if statement in your table like this:

Name Quantity Price Total
{tablerow from=$products item=_product}{tableif $_product.Type == 'shirt'}{$_product.Name} {$_product.Quantity} {$_product.Price} {$_product.Total}{/tableif}{/tablerow}

 

Create a Bulleted/Numbered List

You can also use special "listrow" code to loop through items in a list to create a bulleted list.  Like this:

  • {listrow from=$products item=_product}{$_product}{/listrow}

 

Sort Your List

If you would like to sort your items based on a certain "key" you can do so using the "sort" modifier in this format "|sort:[KEY]:[asc or desc]" (asc is default). For example:

{foreach from=$items|sort:"Name" item=_row}
{$_row.Quantity} x {$_row.Name} = ${$_row.Price|number_format:2}
{/foreach}

To sort by price in descending order:

{foreach from=$items|sort:"Price":"desc" item=_row}
{$_row.Quantity} x {$_row.Name} = ${$_row.Price|number_format:2}
{/foreach}

 

Sort By Multiple Fields

If you would like to sort your items based on multiple keys, you can do so using the "multisort" modifier in this format "|multisort:[KEY1]:[asc or desc]:[KEY2]:[asc or desc]" (asc is default). For example:

{foreach from=$items|multisort:"Name":"asc":"Price":"desc" item=_row}
{$_row.Quantity} x {$_row.Name} = ${$_row.Price|number_format:2}
{/foreach}
Have more questions? Submit a request

115 Comments

  • Avatar
    Sergio Travieso Teniente

    Hi! is it possible to get data for looping items from Excel or Sharepoint? I've created a Microsoft flow, when a JotForm is completed, searchs for a item in a Sharepoint and I want to create a PDF with a table... is it possible?

  • Avatar
    Jeremy Clarke

    Hi Sergio,

    Yes, it's possible, but you need to program your Flow to send over a JSON object with the rows of data (as an array in the JSON). Unfortunately, we don't have any good documentation on how to do that in Microsoft Flow, but their support team can hopefully point you in the right direction!

    Thanks

  • Avatar
    Sergio Travieso Teniente

    Hi Jeremy,
    I've almost solved it: I have achieved a JSON objet in Flow; I test it on webmerge, and works ok.
    But I don't get it done in the Flow: where should I place the array? In the $products or in the _product? Shouldn't there be a JSON box as in the Test mode?

    Thanks for your help.

    Edited by Sergio Travieso Teniente
  • Avatar
    Jeremy Clarke

    Hi Sergio,

    You'll want to put the JSON in the "products" field in Flow and you'll only want to put the specific piece for the products. IE [{product 1 fields},{product 2 fields}]

    One other thing you'll probably have to do is use the Field Map (https://support.webmerge.me/hc/en-us/articles/206526086-Field-Map) to convert the JSON into an array like this:

    products:
    {if !is_array($products)}{$products}{/if}

    Our system will detect the JSON in {$products} and convert it to an array.

  • Avatar
    Sergio Travieso Teniente

    Hi Jeremy,

    I got it!

    Thank you very much for your help!!

  • Avatar
    Rebecca Newton

    Hi Jeremy,
    I'm trying to use the {tableif} tag within a {tablerow} and followed your example above but it isn't pulling in any data that meets the criteria. If I remove that tag, the table fills out with all the data as expected.
    I have a 6 column table. My first column in my table starts shows this:
    {tablerow from=$PartnerRoles item= _Partner_Role__c}{tableif $_Partner_role__c.Partner_Role__c == “Employee”}{$_Partner_Role__c.Contact__c.Name}

    The last column closes the table row tag with {/tablerow} (there is no closing of the {tableif} tag shown in example so assume that isn't needed. Partner_Role__c is the name of the Custom object and there is also a field in that object Partner_Role__c that is the picklist field, thus why Partner_Role__c is repeated in the TableIF tag. Thanks for any insight!
    Rebecca

  • Avatar
    Jeremy Clarke

    Hi Rebecca,

    You don't "need" the {/tableif} but we recommend it and we've updated the help article :)

    The issue is more than likely due to "Partner_Role__c" not being the correct subfield to use for the logic. I suggest turning on Debug Mode (https://support.webmerge.me/hc/en-us/articles/206526126-Debug-Mode) to double check the merge data we're receiving and make sure you have the correct field and value in your logic.

    I've created a support ticket for you and we can continue the thread there :)

    Thanks!

  • Avatar
    Robby Johnson

    can i use a tableif and an if in the same table but different cols

    i have a table with 8 cols. the first col needs to be a 1 or a 0 based on the salesforce product category. The second col needs to be GENERALMAIN or GENERAL based on the same product category from the first col

    so if SF product category is machine - col 1 and 2 should be
    1,GENERALMAIN
    if SF product category is anything but machine - then col 1 and 2 should be
    0,GENERAL

    I have a tableif in my first col and an if in my second col - but I can't get it to work. no matter what i put in all cols are 1,GENERALMAIN for all lines

    thanks

  • Avatar
    Robby Johnson

    I finally go it. i just needed to use an if /else in each cell. I guess the tableif is if you want to exclude and entire row in a table? thanks again

  • Avatar
    Jeremy Clarke

    Hi Robby,

    Correct, the {tableif} is used if your want to include/exclude an entire row.

  • Avatar
    Property Wheel

    These examples seem to be only for JSON or HTML query string inputs.
    For Salesforce integration, how can I loop through a list of custom objects related to a Opportunity object with a lookup relationship?

  • Avatar
    Jeremy Clarke

    Hello,

    Please see this help article for Salesforce Child Relationships: https://support.webmerge.me/hc/en-us/articles/206527196-Working-with-Child-Relationships

  • Avatar
    Jane Isaac

    What modifier do I need to use in the table so that it sorts by the earliest date? Each entry in the table has a date field and I want the rows sorted by that field, with the earliest date record as the top row in the table.

  • Avatar
    Jeremy Clarke

    Hi Jane,

    Our system sees a date as a "string" so it sorts it alphabetically instead of by the actual date. So instead, we need to convert the date to a number (timestamp) so it will sort it correctly. This is easy to do and you'll want to put this code before your table:

    {foreach from=$products item=_p key=_k}{$products[$_k].sort = strtotime($_p.DateField__c)}{/foreach}

    Then in your table you'll do something like this:

    {tablerow from=$products|sort:"sort" item=_product} .....

    If you have any additional questions, please create a support ticket and we'll help!

    Thanks,

  • Avatar
    Aaron Rubin

    I'm using the following code using the Document Builder and then testing using the JSON data above, but its returning just the header info. What am I doing wrong? 

    Name Qty Price Total
    {tablerow from=$products item=_product}{$_product.Name} {$_product.Quantity} {$_product.Price} {$_product.Total}{/tablerow}
    Edited by Aaron Rubin
  • Avatar
    Jeremy Clarke

    Hi Aaron,

    If you're entering the JSON in the Test tab in WebMerge, you just need to enter the JSON for the line items like this:

    [{"Name":"Shirt", "Quantity":"2", "Price":"19.95", "Total":"39.90"},{"Name":"Jeans", "Quantity":"3", "Price":"89.99", "Total":"269.97"}]

  • Avatar
    Aaron Rubin

    Thats exactly what I entered in the JSON box. Output was the same. Headers but no row data.

  • Avatar
    Jeremy Clarke

    Hi Aaron,

    Sorry, my instructions were for entering the JSON in the box under the "products" label. If you're entering the JSON into the JSON box (using JSON for all fields in your merge) then you need to do this:

    {"products":[{"Name":"Shirt", "Quantity":"2", "Price":"19.95", "Total":"39.90"},{"Name":"Jeans", "Quantity":"3", "Price":"89.99", "Total":"269.97"}]}

  • Avatar
    Jérôme AUDINEAU

    Hi Jeremy,

    it's possible to use stristr, or !stristr with tableif ?

    another question :
    I have a test on the firstnames, without accent is ok, with accent, the test does not work anymore, how to do?

    {tablerow from=$ACTIVITES item=_row}{tableif $_row.Owner.Name != "Jerome"} -->OK

    {tablerow from=$ACTIVITES item=_row}{tableif $_row.Owner.Name != "Jérôme"} -->KO

    I have a test on the subject email, without quote is ok, with quote, the test does not work anymore, how to do?

    subject = "Information" -->detected OK

    subject = "Votre demande d'information" -->detected KO

     

     

    Edited by Jérôme AUDINEAU
  • Avatar
    Jeremy Clarke

    Hi Jerome,

    Yes, you can use stristr() in your tableif. The accent causes problems, so let's try this instead:

    {tableif html_entity_decode($_row.Owner.Name) != "Jérôme"}

  • Avatar
    Lambert Davel

    Hi Jeremy
    I'm retrieving the following array from Salesforce via a SOQL query:
    Branch A Prod A 10 2 20
    Branch A Prod B 20 1 20
    Branch B Prod C 30 1 30
    Branch B Prod B 20 1 20

    And I would like to transform it into a table like this via Webmerge:

    Branch Product Price Qty Total
    Branch A Prod A 10 2 20
    Prod B 20 1 20
    Sub 40

    Branch B Prod C 30 1 30
    Prod B 20 1 20
    Sub 50

    Total 90
    Can this be done via the tablerow function or is it best to use a foreach function? Also, can I calculate the sub total and grand total within the webmerge loop or is it best to also obtain that via the SOQL?

  • Avatar
    Jeremy Clarke

    Hi Lambert,

    You're going to use a combination of a foreach loop and a tablerow loop. We're going to use a foreach loop to "group" the rows by branch, then we're going to use the tablerow loop to build the table.

    So this foreach loop would go before the table:
    {foreach from=$rows item=_row}{$_branches[$_row.BranchName__c][] = $_row}{/foreach}

    Then you will use this nested tablerow loop in the table:

    {tablerow from=$_branches item=_rows key=_name}
    {$_name}{$_sum = 0}
    {tablerow from=$_rows item=_row}{$_row.ProductName__c} {$_row.Price}{$_sum = $_sum + $_row.Quantity}{/tablerow}
    {$_sum}
    {/tablerow}

  • Avatar
    Lambert Davel

    Thanks!

  • Avatar
    Mark Balsam

    Will loops work for a fillable PDF template or only for word doc templates? If so, how might we set it up.

  • Avatar
    Jeremy Clarke

    Hi Mark,

    The loops don't work in a fillable PDF (since the contact is static). But you can use the Field Map (https://support.webmerge.me/hc/en-us/articles/206526086-Field-Map) to set the value of each field using the static merge field for the value in the loop like this:

    {$products.0.price} or {$products.1.name}

Please sign in to leave a comment.
Powered by Zendesk