Loops can be used to iterate through multiple records and fields to be merged into in your document.  Examples of this are displaying a list of contacts, or displaying a list of violations associated with an evaluation.  Loops may also be filtered to limit the merge to selected records.  An example of this is filtering a contact loop to only merge contacts that have a particular affiliation type/role code.

Loops

A foreach loop will be inserted automatically into the template if a group name (Bold field names) is double-clicked in the field selection list.

Merge fields set up for a loop can be displayed using a foreach statement such as the example.

Basic Loop Syntax

When editing a document template, double-clicking a bold field in the Insertable Fields list will insert a foreach loop into the document template. The example below inserts a permits loop into a document template:

<<foreach[permit in permits]>>[fields to be displayed here]<</foreach>>

Formatting Lists and Tables in a Document using Loops

To format a foreach loop, check the examples below:

Template Code

Output

Withing the opening <<foreach>> and closing <</foreach>> tags, the merge fields for that loop can be inserted. The following example displays the permitNumber within the loop:

<<foreach[permit in permits]

>>Permit Number: <<[

permit.permitNumber]>><</

Prefix Item1 Item2 Item3 Suffix

Prefix <<foreach [permit in permits]>><<[Item]>>
<</foreach>>Suffix

Prefix Item1

Item2

Item3

Suffix

Prefix
<<foreach [permit in permits]>><<[Item]>>
<</foreach>>Suffix

Prefix

Item1

Item2

Item3

Suffix

1. <<foreach [permit in permits]>><<[Item]>>
<</foreach>>

1. Item1

2. Item2

3. Item3

<<foreach [item in items]>><<[item.IndexOf() !=0 ? “, “: “”]>><<[item.itemName]>><</foreach>>.

Item1, Item2, Item3.

The last loop formatting in the examples above uses IndexOf() and Ternary "?:" to decide whether to place a comma "," or not to place anything after the first item, then the "." is placed after closing the foreach loop,  which translates into "is this true ? Yes : No". The code will result in adding a comma after each item except the last item in a list where a "." will be added.

Formatting Tables in Documents

A foreach statement within a table will loop within the table. In the example below, for each feature ID, a new row within the table will be created and the associated fields filled:

Feature ID

Description

Status

<<foreach [feature in features.OrderBy(e => e.featureText)]>><<[featureText]>> 

<<[featureDescription]>>

<<[status]>>

<</foreach>>

Extension Methods for Loops (Sorting, Filtering and More)

Foreach loop can be combined with extension methods to access data within the dataset such as .Where, .Take(), .OrderBy(), etc..

Using Lambda Expressions

In order to take advantage of many AceOffix capabilities including sorting and filtering loops, you will want to become familiar with lambda expressions. In the examples below. you will see that each repeating element is followed by a function name (e.g. Where, OrderBy, etc.) and then an expression in parenthesis. For example:

<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == "MNR_HAUL")]>>

The x => x.item notation is a so-called lambda expression. Any letter can be chosen to replace x. It's a shorthand way of saying "take x and return this property of x". For example, in the contact examples given above, "x" is used to represent the contact and "x => x.affiliationTypeCode" means "take the contact and return the affiliationTypeCode for that contact".

Filtered Loops (Where)

To filter a loop so that it only displays certain data (such as only displaying a contact with a specific affiliation type), add a ".Where" clause to the foreach syntax. For example, the following code only displays those contacts that have an affiliation type of manure hauler ("MNR_HAUL"):

<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == “MNR_HAUL”)]>>
   <<[contact.contactName]>>
<</foreach>>

To protect against multiple matching records being returned (such as 2 or more manure haulers in the example above), add .Take(1) to the markup:

<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == “MNR_HAUL”).Take(1)]>>
  <<[contact.contactName]>>
<</foreach>>

To return a different record when a specified record does not exist, syntax such as the following can be used:

<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == “ENGR”).Take(1)]>>
  <<[contact.contactLastName]>>
<</foreach>><<if[contacts.Where(x => x.affiliationTypeCode == “ENGR”).Count() == 0]>>
<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == “OWNR”).Take(1)]>>
  <<[contact.contactLastName]>>
<</foreach>><</if>>

In this example, the foreach loop will filter based on an affiliation type of "ENGR". The syntax Take(1) is used to return the first value, in cases in which multiple contacts with the same affiliation type are returned. In this case, the Contact Last Name is returned. An if statement is used to check if the count of affiliation type "ENGR" is 0 (using .Count() == 0) and, if so, to return the contact with the "OWNR" affiliation type.

To filter the loop using multiple criteria, add additional expressions within the Where clause, separated by appropriate boolean operators (such as && for "and" or || for "or"). For example, the following snippet returns the phone number of the contact only if the affiliation type is "MNR_HAUL" and the Phone Type Description is "Office":

<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == “MNR_HAUL” && x.contactPhoneTypeDescription == "Office")]>>
  <<[contact.contactPhone]>>
<</foreach>>

Sorting Loops (OrderBy)

To add sorting to a loop so that it displays the data in a certain order, add an ".OrderBy" or "OrderByDescending" clause to the foreach syntax.

<<foreach [contact in contacts.OrderBy(c => c.contactLastName)]>>
  <<[contactFirstName]>>, <<[contactLastName]>>
<</foreach>>

In the above example the code will sort contacts based on the contact's Last Name and will display Contact First Name, Contact Last Name in a list.

If trying to sort an ordered list in a tag value (1. 2. 3. etc..) then you'll need to have the .OrderBy only look at those specific values; otherwise it will sort incorrectly. One way to do that is to use the .Substring() function, in combination with the .IndexOf() function and the Convert.ToDouble() function, as shown below. 

<<foreach [ listquestionsItem in listquestions.OrderBy(c => Convert.ToDouble(c.questions.Substring(0, c.questions.IndexOf("."))))]>>
   <<[questions]>> 
<</foreach>>

Explanation of the above string: 

• .OrderBy() must be in between the [] of the foreach statement, attached to the last tag name.

• Convert.ToDouble() is used to enclose the specific text you are trying to sort by (i.e. c => c.questions) c.questions here is what you are trying to have OrderBy sort off of.   **Please note ToInt() has not worked in this situation, so ToDouble() is best. 

• .Substring(start, end) is used to pick out a certain part of the tag value. In this example, the user wants to pick out whatever is before the period since those will be the numbers in the list of questions.  So the user has the Substring start at 0 (the beginning of the tag value) and then end at the .IndexOf(".") the period. 

• .IndexOf() will give the exact placement of the period in the string so that when it looks at the substring of (0, c.questions.IndexOf(".")) it will only be looking at "1" as the first question. If the numbers were proceeded by a "-" (dash), then  c.questions.IndexOf("-") would be used instead. You can look for any unique value this way. If it's not a unique value, only the first occurrence of that value will be found.  

Nested Loops

The following example demonstrates a loop for each of a site’s permits and a sub-loop listing each permit’s features in a table:

Image Removed

1. <<foreach>> syntax example. The foreach tag is used to display the same fields for repeating records. For example, in this figure, the fields between the <<foreach>> and <</foreach>> tags are displayed for all permits associated with this site, regardless of how many permits there are.  (Note that the "Site Permits" caption does not repeat for each caption, as it is not included within the <<foreach>> tags. ). Also note the OrderBy syntax. OrderBy should always be used to sort looped data. In this case, the repeated permits are sorted by permit number (permitNum). 

2. <<foreach>> syntax example in a table. In cases in which the foreach tags are used within a table, the template engine is smart enough to know that there should be a row for each looped item.

3. Date formatting example. This example shows how to format a date in year-month-day order, e.g., 2019.04.25.

4. Logical if example. This example demonstrates a way of only rendering output for a particular field if data is present in that field. See "Address with check for missing data" below for another example.

Combining Methods

The above methods may be combined together. 

The following example shows a loop that combines  Where( ), Order By( ), and Take(1) methods.

<<foreach [workflowTask in workflowTasks.Where(w => w.TaskStatusDescription == "Unstarted").OrderBy(w => w.taskSequence).Take(1)]>><<[workflowTask.taskName]>><</foreach>>

Other Useful Methods to use with Loops

A full list of supported methods can be found on Microsoft's Enumerable Class support page.

Other useful methods are listed below

Method

Description

Where(x => x.affiliationTypeCode == “MNR_HAUL”)

Filters a sequence of values based on a predicate.

Take(int)

Returns a specified number (int) of contiguous elements from the start of a sequence.

Equivalent to TOP X in a SQL query. Ideally used in conjunction with OrderBy.

OrderBy()

Sorts the elements of a sequence in ascending order according to a key.

OrderByDescending()

Sorts the elements of a sequence in descending order according to a key.

ThenBy()

Required when sorting by two or more fields
Example: <<foreach [feature in features.OrderBy(e => e.Sort1).ThenBy(e => e.Sort2)]>>

Count()

Returns the number of elements in a sequence.
Example: <<[coHierarchy.contct.Count()]>>

Sum()

Returns the sum of item values in a sequence. You will need to cast to a numeric type in most cases.

Example: <<[acresTable.Sum(item => Convert.ToDecimal(item.acresDisturbed))]

Contact Loops - Finding Available Affiliation Types

Navigate to Admin > Lookups > Affiliation Types (Roles), this will display existing affiliation types. The Lookup Code is what should be used when filtering contacts based on Affiliation Types. 

Affiliation Types related to one or more functional areas (e.g. Applicant, Permittee) can only be assigned in the corresponding functional area(s).  Roles which do not have a functional area specified are available in all functional areas.

Tips and Tricks

Returning Distinct Results Using foreach and a Variable

Consider a document template that needs to show the distinct list of receiving waters across multiple permitted features. Specifying distinct results is important here because in almost all cases, the receiving water for all features will be the same.

Unfortunately, the Distinct() method does not appear to work in the current version of the document template editor. The following is a workaround that returns the desired results:

1. Set a local variable to a default value (e.g., "None").

2. Setup a foreach loop to iterate through all of the features.

3. Within the loop, compare the receiving water to the local variable. If different:

   1. Set the local variable = the receiving water;

   2. Display the receiving water.

4. If the receiving water is the same as the local variable, then ignore and move on.

A syntax example is shown below. (Note that carriage returns have been added here for visibility only, using the code as written this way will cause many unwanted carriage returns).

Distinct in a foreach

<<var [v_recvWtr = “None”]>>
<<foreach [featrItem in coMuni.featr.OrderBy(p => p.recvWtr)]>>
<<if[!string.IsNullOrEmpty(featrItem.recvWtr)]>>
<<if[featrItem.recvWtr != v_recvWtr]>>
<<if[v_recvWtr!=“None”]>>, <</if>>
<<var [v_recvWtr = featrItem.recvWtr]>>
<<[v_recvWtr]>><</if>><</if>><<foreach>>

Loop Variable Naming

Note that permit in the example above is a variable name than can be chosen by the template developer. For a more compact display, this variable name can be substituted with a different value if desired. Shorter variable names can improve readability and layout of a template since the code takes up less space in the document. In the example below, the variable p is substituted for permit in the previous example:

<<foreach[p in permits]>>Permit Number: <<[p.permitNumber]>><</foreach>>

Nested Loops

Loops can be nested within one another when supported by the selected datasource(s). For example an form with a repeating section that contains an advanced table will require two nested loops to access the advanced table data. The example below shows an example of a nested loop using a hypothetical datasource:

<<foreach[p in permits]>><<foreach[pc in permitContacts]>>
  Contact Name: <<[pc.contactName]>>
<</foreach>><</foreach>>

Merge Field Scope within a Loop

Within a loop, you have access to all the variables at levels above the current loop. In the example below, the p.permitNumber is accessed from within the permitContacts child loop:

<<foreach[p in permits]>><<foreach[pc in permitContacts]>>
  Permit Number: <<[p.permitNumber]>>
  Contact Name: <<[pc.contactName]>>
<</foreach>><</foreach>>

When a Loop is Not a Loop!

The beginning of this section mentions a bold entry in the Insertable Fields list represents a loop. This is not always true. More specifically, bold entries represent an object that is a container for additional elements. That object may or may not be a list. For example, the ”currentUser” and “environmentSettings”entries in the Insertable Fields list can be inserted using a foreach loop, however because there is only ever one record returned for both of these entries, you can also reference the object’s properties directly without using a foreach loop. For example, both syntax examples will work and will produce exactly the same output:

This:
   <<foreach [user in currentUser]>><<[user.displayName]>><</foreach>>
Produces the same output as this:
   <<[currentUser.displayName]>>

How is this possible? AceOffix evaluates each object in the merge field data at the time the document is being rendered. It determines in real time whether an object is a list (an array in technical terms) or just a named collection of individual data elements (“scalar” in technical terms). If it is scalar, it will also be treated as a list with just one row, allowing for either syntax to be used.

This can be helpful when using form submission datasources in document templates. The merge field representing the form is always scalar (only one logical row) and therefore, the properties can be reference using the simpler syntax above without the foreach loop.

Formatting Lists and Tables in a Document using Loops

Loops are most often used in bulleted/numbered lists or in tables in a Word document.

Formatting Lists

To format a foreach loop, check the examples below:

Prefix<<foreach [permit in permits]>> <<[Item]>><</foreach>>Suffix

Prefix <<foreach [permit in permits]>><<[Item]>>
<</foreach>>Suffix

Prefix
<<foreach [permit in permits]>><<[Item]>>
<</foreach>>Suffix

1. <<foreach [permit in permits]>><<[Item]>>
<</foreach>>

<<foreach [item in items]>><<[item.IndexOf() !=0 ? “, “: “”]>><<[item.itemName]>><</foreach>>.

The last loop formatting in the examples above uses IndexOf() and Ternary "?:" to decide whether to place a comma "," or not to place anything after the first item, then the "." is placed after closing the foreach loop,  which translates into "is this true ? Yes : No". The code will result in adding a comma after each item except the last item in a list where a "." will be added.

Formatting Tables

A foreach statement within a table will create new table rows for each item in the list. In the example below, for each feature ID, a new row within the table will be created and the associated fields filled:

Extension Methods for Loops (Sorting, Filtering and More)

Foreach loop can be combined with extension methods to access data within the dataset such as .Where, .Take(), .OrderBy(), etc..

Using Lambda Expressions

In order to take advantage of many AceOffix capabilities including sorting and filtering loops, you will want to become familiar with lambda expressions. In the examples below. you will see that each repeating element is followed by a function name (e.g. Where, OrderBy, etc.) and then an expression in parenthesis. For example:

<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == "MNR_HAUL")]>>

The x => x.item notation is a so-called lambda expression. Any letter can be chosen to replace x. It's a shorthand way of saying "take x and return this property of x". For example, in the contact examples given above, "x" is used to represent the contact and "x => x.affiliationTypeCode" means "take the contact and return the affiliationTypeCode for that contact".

Filtered Loops (Where)

To filter a loop so that it only displays certain data (such as only displaying a contact with a specific affiliation type), add a Where clause to the foreach syntax. For example, the following code only displays those contacts that have an affiliation type of manure hauler ("MNR_HAUL"):

<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == “MNR_HAUL”)]>>
   <<[contact.contactName]>>
<</foreach>>

To filter the loop using multiple criteria, add additional expressions within the Where clause, separated by appropriate boolean operators (such as && for "and" or || for "or"). For example, the following snippet returns the phone number of the contact only if the affiliation type is "MNR_HAUL" and the Phone Type Description is "Office":

<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == “MNR_HAUL” && x.contactPhoneTypeDescription == "Office")]>>
  <<[contact.contactPhone]>>
<</foreach>>

To protect against multiple matching records being returned (such as 2 or more manure haulers in the example above), add .Take(1) to the markup:

<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == “MNR_HAUL”).Take(1)]>>
  <<[contact.contactName]>>
<</foreach>>

To return a different record when a specified record does not exist, syntax such as the following can be used:

<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == “ENGR”).Take(1)]>>
  <<[contact.contactLastName]>>
<</foreach>><<if[contacts.Where(x => x.affiliationTypeCode == “ENGR”).Count() == 0]>>
<<foreach [contact in contacts.Where(x => x.affiliationTypeCode == “OWNR”).Take(1)]>>
  <<[contact.contactLastName]>>
<</foreach>><</if>>

In this example, the foreach loop will filter based on an affiliation type of "ENGR". The syntax Take(1) is used to return the first value, in cases in which multiple contacts with the same affiliation type are returned. In this case, the Contact Last Name is returned. An if statement is used to check if the count of affiliation type "ENGR" is 0 (using .Count() == 0) and, if so, to return the contact with the "OWNR" affiliation type.

Sorting Loops (OrderBy)

To add sorting to a loop so that it displays the data in a certain order, add an ".OrderBy" or "OrderByDescending" clause to the foreach syntax.

<<foreach [contact in contacts.OrderBy(c => c.contactLastName)]>>
  <<[contactFirstName]>>, <<[contactLastName]>>
<</foreach>>

In the above example the code will sort contacts based on the contact's Last Name and will display Contact First Name, Contact Last Name in a list.

For more examples, see the Tips and Tricks section below

Other Useful Methods (Sum, Count, Take…)

A full list of supported methods can be found on Microsoft's Enumerable Class support page.

Other useful methods are listed below

Combining Methods

The methods above can be combined together. 

The following example shows a loop that combines  Where(), Order By(), and Take() methods.

<<foreach [workflowTask in workflowTasks
  .Where(w => w.TaskStatusDescription == "Unstarted")
  .OrderBy(w => w.taskSequence)
  .Take(1)]>>
  <<[workflowTask.taskName]>>
<</foreach>>

Loop Tips and Tricks

Returning Distinct Results Using foreach and a Variable

Consider a document template that needs to show the distinct list of receiving waters across multiple permitted features. Specifying distinct results is important here because in almost all cases, the receiving water for all features will be the same.

Unfortunately, the Distinct() method does not appear to work in the current version of the document template editor. The following is a workaround that returns the desired results:

1. Set a local variable to a default value (e.g., "None").

2. Setup a foreach loop to iterate through all of the features.

3. Within the loop, compare the receiving water to the local variable. If different:

   1. Set the local variable = the receiving water;

   2. Display the receiving water.

4. If the receiving water is the same as the local variable, then ignore and move on.

A syntax example is shown below. (Note that carriage returns have been added here for visibility only, using the code as written this way will cause many unwanted carriage returns).

<<var [v_recvWtr = “None”]>>
<<foreach [featrItem in coMuni.featr.OrderBy(p => p.recvWtr)]>>
<<if[!string.IsNullOrEmpty(featrItem.recvWtr)]>>
<<if[featrItem.recvWtr != v_recvWtr]>>
<<if[v_recvWtr!=“None”]>>, <</if>>
<<var [v_recvWtr = featrItem.recvWtr]>>
<<[v_recvWtr]>><</if>><</if>><</foreach>>

Breaking Out of a Loop

Sometimes it's useful or necessary to break out of a loop when a particular condition has been met or value has been found. This can be achieved by doing the following:

1. Create a variable and set it to "true". 

2. At the beginning of the loop, check to see that the variable is equal to "true".

3. Somewhere in the loop, perhaps when a specific condition has been met, set the value of the variable to "false".

The following code illustrates this. (The code is indented here for legibility but doesn't need to be indented in the document template.)

<<var [keepGoing = “true”]>>
<<if[violations!=null]>>
  <<foreach [violation in violations]>>
    <<if [keepGoing==”true”]>>
      <<if [regulationReference !=null]>>
        <<if [regulationReference.StartsWith(“W50-51”)]>>
          W50-51 Failure to meet the applicable requirements of the Windsor Solutions Standards for Wastewater Facility Construction requirements.
          <<var [keepGoing = “false”]>>
        <</if>>
      <</if>>
    <</if>>
  <</foreach>>
<</if>>

]>>
        <<if [regulationReference.StartsWith(“W50-51”)]>>
          W50-51 Failure to meet the applicable requirements of the Windsor Solutions Standards for Wastewater Facility Construction requirements.
          <<var [keepGoing = “false”]>>
        <</if>>
      <</if>>
    <</if>>
  <</foreach>>
<</if>>

<<foreach [ listquestionsItem in listquestions.OrderBy(c => Convert.ToDouble(c.questions.Substring(0, c.questions.IndexOf("."))))]>>
   <<[questions]>> 
<</foreach>>