How to properly group records when executing a <#list>

Question

New guy here. I have been building an advanced form in NetSuite (uses Freemarker) to display invoice data. Everything looks and works great, however, I want to group the invoice line items by location. I am using a simple <#list> loop to pull the line item records. I currently display the location on each line item.

Code (formats/styles removed for simplicity):

<table>
  <#list record.item as item>
     <tr>
        <td> ${item.location} </td>
        <td> ${item.description} </td>
        <td> ${item.quantity} </td>
        <td> ${item.rate} </td>
        <td> ${item.amount} </td>
    </tr>
  </#list>
</table>

Current Output example:

Location A     Des 1              1        $100     $100
Location B     Des 1              1        $100     $100
Location C     Des 1              1        $100     $100
Location A     Des 2              1        $100     $100
Location B     Des 2              1        $100     $100
Location C     Des 2              1        $100     $100
Location A     Des 3              1        $100     $100
Location C     Des 3              1        $100     $100

Desired Output Example:

Location A
Des 1              1        $100     $100
Des 2              1        $100     $100
Des 3              1        $100     $100
Location B
Des 1              1        $100     $100
Des 2              1        $100     $100
Location C
Des 1              1        $100     $100
Des 2              1        $100     $100
Des 3              1        $100     $100

I have tried to nest a second <#list> but it did not work correctly. Any suggestions or pointers would be helpful to push me in the correct direction.

Thank you!

Answer 1

FreeMarker expects such grouping to be done by whatever sets up the variables, which is in this case NetSuite. (However, I think this could be seen as purely a presentation concern, and so maybe FreeMarker should handle this in the future.) If NetSuite indeed won't group the data for you, then you have to do it in FreeMarker, which will be a bit awkward, as it's not a real programming language... but here it is.

Define a macro like this:

<#macro listGroups items groupField>
  <#if items?size == 0><#return></#if>
  <#local sortedItems = items?sort_by(groupField)>
  <#local groupStart = 0>
  <#list sortedItems as item>
    <#if !item?is_first && item[groupField] != lastItem[groupField]>
      <#local groupEnd = item?index>
      <#nested lastItem[groupField], sortedItems[groupStart ..< groupEnd]>
      <#local groupStart = groupEnd>
    </#if>
    <#local lastItem = item>
  </#list>
  <#local groupEnd = sortedItems?size>
  <#nested lastItem[groupField], sortedItems[groupStart ..< groupEnd]>
</#macro>

You can use this macro later like this:

<@listGroups record.item "location"; groupName, groupItems>
  <p>${groupName}</p>
  <table>
    <#list groupItems as groupItem>
       <tr>
          <td>${groupItem.location}</td>
          <td>${groupItem.description}</td>
          <td>${groupItem.quantity}</td>
          <td>${groupItem.rate}</td>
          <td>${groupItem.amount}</td>
      </tr>
    </#list>
  </table>
</@listGroups>

Note that groupName and groupItems in <@listGroups ...> are just arbitrary loop variable names that you specify, and they need not match the variable names used inside the #macro definition.

Update:

If you need to group by a composite key, you can nest calls of the above. Let's say you have these records coming from the data-model (here defined in FreeMarker, so it's easy to try):

<#assign records = [
  {"a": 1, "b": "c1", "c": 1, "d": 11},
  {"a": 1, "b": "c1", "c": 2, "d": 12},
  {"a": 1, "b": "c2", "c": 3, "d": 13},
  {"a": 1, "b": "c3", "c": 4, "d": 14},
  {"a": 2, "b": "c3", "c": 5, "d": 15},
  {"a": 2, "b": "c3", "c": 5, "d": 15}
]>

You need to group by a and b, like 1, c1 is a group, and 1, c2 is another. Then you can do this:

<@listGroups records "a"; a, level1GroupItems>
  <@listGroups level1GroupItems "b"; b, groupItems>
    <p>${a}, ${b}:</p>
    <table>
      <#list groupItems as groupItem>
         <tr>
            <td>${groupItem.c}</td>
            <td>${groupItem.d}</td>
        </tr>
      </#list>
    </table>
  </@listGroups>
</@listGroups>

Another use-case is when your records are already sorted by the composite group key. In that case there's a slightly more efficient solution:

<#macro listGroupsOfSorted items groupFields...>
  <#if items?size == 0><#return></#if>
  <#local groupStart = 0>
  <#list items as item>
    <#if !item?is_first && !subvariablesEqual(item, lastItem, groupFields)>
      <#local groupEnd = item?index>
      <#nested items[groupStart ..< groupEnd]>
      <#local groupStart = groupEnd>
    </#if>
    <#local lastItem = item>
  </#list>
  <#local groupEnd = items?size>
  <#nested items[groupStart ..< groupEnd]>
</#macro>

<#function subvariablesEqual(obj1, obj2, subVars)>
  <#list subVars as subVar>
    <#if obj1[subVar] != obj2[subVar]>
      <#return false>
    </#if>
  </#list>
  <#return true>
</#function>

and then in your normal template:

<#-- Note: records must be already sorted! -->
<@listGroupsOfSorted records "a" "b"; groupItems>
  <p>${groupItems[0].a}, ${groupItems[0].b}:</p>
  <table>
    <#list groupItems as groupItem>
       <tr>
          <td>${groupItem.c}</td>
          <td>${groupItem.d}</td>
      </tr>
    </#list>
  </table>
</@listGroupsOfSorted>