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
    Jeremy Clarke

    Hi Markwood,

    Yes, but you'll need to use our Field Map (https://support.webmerge.me/hc/en-us/articles/206526086-Field-Map) to process the field. When you use the Field Map, we look for JSON and convert it to an array automatically. If your field is called "Products" simply add {$Products} to the box under that field in the Field Map :)

    Thanks!

  • Avatar
    Alex Melzer

    Hi Jeremy,

    I can't seem to get the sorting feature working for me in a word document. My code is: {tablerow from=$Lineitems|sort:”POS.” item=_lineitem}{$_lineitem.position__c} and my first column is called "POS.". When I merge the docx and have it also output as docx I can open it and then have the list sort by the POS. column with no problem.

    Any idea how I can get your server to do this for me? Anything wrong with my code?

    Thank you for your help,
    Gregor

  • Avatar
    Jeremy Clarke

    Hi Gregor,

    The issue is that you have a "dot" in your field name "POS.". Only letters, numbers, and underscores are allowed in your field names. Can you please use "POS" instead?

  • Avatar
    Alex Melzer

    Thanks Jeremy,

    I have now changed the code to {tablerow from=$Lineitems|sort:”POS” item=_lineitem}{$_lineitem.position__c} and changed the name of the column to "POS" and it still won't sort it for me. Do you have any other ideas?

  • Avatar
    Jeremy Clarke

    Hi Gregor,

    I'm 99% sure that the POS field is not coming through correctly (or the capitalization is off). Can you turn on Debug Mode so can see the data we are receiving?

    Also, go ahead and create a support request and we can help you in more detail.

    Thanks!

  • Avatar
    Markwood Fields

    Hey Jeremy,
    Finally at a point in my project where I can test this out! (TrackVia -> Zapier -> WebMerge)

    Getting an error (maybe something wrong with field maps?):
    "There is an error in your document: Syntax Error in template "string:" on line 10 "{foreach from=$array item=array}" item variable 'array' may not be the same variable as at 'from'

    The field name I'm using is called "array". First column in my table looks like:

    {tablerow from=$array item=array}{$array.firstname}

    The field map for this just looks like (with first line being what Webmerge shows, second being the input field):

    array
    {$array}

    Was a little confused on what to put in field map. Any thoughts here?

  • Avatar
    Jeremy Clarke

    Hi Markwood,

    As the error message states, you can't use the same for "item" and "from" in your tablerow loop. So you need to use something like:

    {tablerow from=$array item=row}{$row.firstname}

    That should do it!

  • Avatar
    Markwood Fields

    Awesoooooome Thanks Jeremy, working now. You're the best!

  • Avatar
    Mac McCraw

    Is there a way to do something the first time a value comes up or last.

    This is what I was trying, but am getting a warning of syntax errors.

    {if $_row.TailNo@first}
    -----
    {/if}

  • Avatar
    Jeremy Clarke

    Hi Mac,

    The @first and @last are used for determining if the current iteration of the loop is the first item or the last item. Is that what you're trying to do? If so, the code would be this:

    {if $_row@first} ....... {/if}

    If you're trying to find the first instance of a value, you need to do something like this:

    {if empty($_tails[$_row.TailNo])}
    {$_tails[$_row.TailNo] = $_row.TailNo}
    ....
    {/if}

    The code above creates a new merge field ($_tails) and looks to see if that value has been seen yet. If not, it saves the value and then processes the inside of the if statement.

  • Avatar
    Mac McCraw

    Thank you so much for the fast response and you were exactly right i was trying to find the first instance of a value.

    Your approach makes sense and works for me. On that very same note how can i determine the last instance of value?

    I see i can use @last if looking for iteration which is helpful for sure, but not sure how to handle for a value.

    Really appreciate the help.

  • Avatar
    Jeremy Clarke

    Hi Mac,

    Getting the last item is going to be a little more difficult. You'll have to use 2 loops - the first to determine how many of each TailNo there are, then the 2nd is your actual loop that prints in the doc. Like this:

    {foreach from=$items item=_row}{$_totals[$_row.TailNo] = $_totals[$_row.TailNo] + 1}{/foreach}
    {foreach from=$items item=_row}
    {if $_totals[$_row.TailNo] == 1}
    .....
    {/if}

    {$_totals[$_row.TailNo] = $_totals[$_row.TailNo] - 1}
    {/foreach}

    If you have any more questions, go ahead and create a support ticket and we'll help you from there!

  • Avatar
    Mac McCraw

    ah! this works perfectly. Thank you so much for the help.

  • Avatar
    Ivan Kljaić

    Is there somewhere a sample on how to create the document for item looping. All I can see in the editor is how to create a static table.

  • Avatar
    Jeremy Clarke

    Hi Ivan,

    I'm not sure what you mean. You just put that code in your template. Please create a support ticket and we'll help you directly.

    Thanks!

  • Avatar
    David Ellis

    Hi Jeremy,

    I am looking to create an attendance sheet from a campaign in Salesforce based on campaign members. Is this possible with this technique? Can you point me to any specific doco?

    Thanks Jeremy, 

    I worked it out from what you posted

    Edited by David Ellis
  • Avatar
    Jeremy Clarke

    Hi David,

    Yes, you can do that. Setup your table in your doc (using the examples above) then you can turn on Debug Mode (https://support.webmerge.me/hc/en-us/articles/206526126-Debug-Mode) so you can see the data coming over from Salesforce and see what subfields to use inside the table. When you match up the loop field in Salesforce, you'll match it up to the Child Relationship for your attendees. Here's an example using line items for an invoice (process is the same): https://www.webmerge.me/blog/generate-an-invoice-from-an-opportunity-won-in-salesforce

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

    Thanks!

  • Avatar
    Carlos Useche

    Hi Jeremy, I read that you support the {tablerow} code in Excel templates. Is it possible to loop through an array and create one excel sheet per element?

  • Avatar
    Jeremy Clarke

    Hi Carlos,

    Unfortunately, we don't have a way to dynamically add sheets. We'll add this to our feature request list and let you know when it hits the roadmap!

    Thanks :)

  • Avatar
    User_jacobs User_jacobs

    does the tableif work in excel? mytesting in excel doesn't work. the example above has no close if like i would expect. i'm using this in my 1st cell in the row.

    {tablerow from=$Inspections item=Inspection}{tableif $Inspection.record.floor_no == '1/F'}{$Inspection.record.test_location_no}

  • Avatar
    Jeremy Clarke

    Yes, the {tableif} code works in Excel too. Can you turn on Debug Mode and make sure you're using the correct field in your if statement? If you still have troubles, please create a support ticket and we'll help you directly.

    Thanks!

  • Avatar
    Olivia Gantner

    Hi Jeremy,

    We sometimes have alternate bids so I used Tableif to show the line items that are alternates in different tables. Is there a way to sum up the line items that are within the table? We don't currently have fields in SF that break out the general bid line items and the alternates so I wanted to see if we could just use a formula on the template to get the totals within each chart.

    Thank you

  • Avatar
    Jeremy Clarke

    Hi Olivia,

    Yes, inside the table row, you can have code like this to add up the totals: {$total = $total + $_row.Total}

    Then after your table you can print the total like normal: {$total|number_format:2}

  • Avatar
    Olivia Gantner

    Hello Jeremy,

    Can you give me the code to add up table rows using the Salesforce field that I am trying to add up? The field is "_qli.Line_Item_Total_WF__c"? I was trying {$total = $_qli.Line_Item_Total_WF__c + $_row._qli.Line_Item_Total_WF__c} but that did not work. Are you using $total as a sort of variable?

    Thank you,

  • Avatar
    Jeremy Clarke

    Hi Olivia,

    Yes, $total is a variable that we're using to add things up. Try this: {$total = $total + $_qli.Line_Item_Total_WF__c}

  • Avatar
    User_jacobs User_jacobs

    question: is it supported to have nested arrays within your json. Eg: my json has an array that stores the photos.
    i can output elements in the root element with image.element. But it doesn't seem to output anything when I try image.elementarray.element to get to the my data next level down.

  • Avatar
    Jeremy Clarke

    Hi Jacobs,

    Yes, we support nested arrays (you can go as many levels deep as you want). Please turn on Debug Mode (https://support.webmerge.me/hc/en-us/articles/206526126-Debug-Mode) so you can see what merge fields to use. If you need more help, please create a support ticket.

    Thanks!

  • Avatar
    John Williams

    Is there a way to loop through rows coming from Smartsheet? I am assuming not since the integration is via Zapier which only retrieves one row at a time. Or if it does, that the Zapier transaction count will cost prohibitive.
    I'm trying to build a simple invoicing system where the user select one or several hundred items from a list and then WebMerge is used to get all of the items onto a single invoice.
    "Going horizontally" in Smartsheet is not an option due to the limitation of 200 columns.
    It appears the only real option will be to create the JSON array using Smartsheet's API and bypassing the integration to Zapier altogether.

    Craig Williams

  • Avatar
    Jeremy Clarke

    Hi John,

    Unfortunately, Zapier is only able to pull 1 line at a time, so the best route is to build a JSON array using the Smartsheet API and send the data directly to WebMerge.

    Thanks!

  • Avatar
    John Williams

    I suspected as much, thanks for the prompt response.

    Is it possible to build a workflow in WebMerge such that I can populate section 1 from one sheet/source, section 2 from a different sheet/source, etc..?

Please sign in to leave a comment.
Powered by Zendesk