Html Question posted in
Some basic documentation can be found here.

Html – FreeMarker/NetSuite PDF Template – Double sorting by date, with possible empty values

I need to sort a list of records and render them in a table, but they have to be sorted by a "From Date" column, and then by "To Date" at a second level. I checked the documentation for ?sort_by but it doesn’t mention any support for double sorting.

Thought these would work but it’s doing a different thing.

<#list invoices?sort_by(['from_date', 'to_date']) as inv>

<#list invoices?sort_by("from_date")?sort_by("to_date") as inv>

Also, some records have empty values for "From Date" or "To Date".

Unsorted table

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up doing something similar to this question, but had to add some parsing and render the records with empty dates at the bottom of the table.

    An adaptation of my Freemarker code:

    <table>

  <tr>
    <th>name</th>
    <th>quantity</th>
    <th>dateFrom</th>
    <th>dateTo</th>
  </tr>

  <#assign fromDates = "" />

  <!-- Sort records by from_date, then to_date -->
  <#list records?filter(x -> x.custcol_from_date?has_content && x.custcol_to_date?has_content)?sort_by("custcol_from_date") as record>
    <#assign currentFrom = record.custcol_from_date />

    <#if !(fromDates?has_content)>
      <#assign prevFrom = currentFrom />
      <#assign fromDates = currentFrom + "---" />
    </#if>
    <#if prevFrom != currentFrom>
      <#assign fromDates = fromDates + currentFrom + "---" />
    </#if>
    <#assign prevFrom = currentFrom />
  </#list>

  <#list fromDates?split("---") as fromDate>
    <#if fromDate?has_content>
      <#assign parsedDate = fromDate?date />
      <#list records?filter(x -> x.custcol_from_date?has_content && x.custcol_to_date?has_content && x.custcol_from_date = parsedDate)?sort_by("custcol_to_date") as record>
          <tr>
            <td>${record.name}</td>
            <td>${record.quantity}</td>
            <td>${record.custcol_from_date}</td>
            <td>${record.custcol_to_date}</td>
          </tr>
      </#list>
    </#if>
  </#list>

  <!-- Print entries without from/to date at the end of table -->
  <#list records?filter(x -> !x.custcol_from_date?has_content || !x.custcol_to_date?has_content) as record>
    <tr>
      <td>${record.name}</td>
      <td>${record.quantity}</td>
      <td>${record.custcol_from_date}</td>
      <td>${record.custcol_to_date}</td>
    </tr>
  </#list>

</table>

    Can be tested in https://try.freemarker.apache.org/ changing this line:

    <#assign parsedDate = fromDate?date />

    to

    <#assign parsedDate = fromDate />

    Data Model:

    records=[ { "name": "One", "quantity": "10", "custcol_from_date": "2024-05-31", "custcol_to_date": "2024-06-01" }, { "name": "Two", "quantity": "20", "custcol_from_date": "2024-05-31", "custcol_to_date": "2024-06-15" }, { "name": "Three", "quantity": "30", "custcol_from_date": "2024-05-31", "custcol_to_date": "2024-06-01" }, { "name": "Four", "quantity": "40", "custcol_from_date": "2024-05-31", "custcol_to_date": "2024-07-02" }, { "name": "Five", "quantity": "50", "custcol_from_date": "2024-04-01", "custcol_to_date": "2024-06-01" }, { "name": "Six", "quantity": "60", "custcol_from_date": "2024-04-02", "custcol_to_date": "2024-05-10" }, { "name": "Seven", "quantity": "70", "custcol_from_date": "2024-05-01", "custcol_to_date": "" }, { "name": "Eight", "quantity": "80", "custcol_from_date": "2024-04-01", "custcol_to_date": "2024-05-13" }, { "name": "Nine", "quantity": "90", "custcol_from_date": "", "custcol_to_date": "2024-06-01" }, { "name": "Ten", "quantity": "100", "custcol_from_date": "", "custcol_to_date": "" } ]

    Result:

    enter image description here


  2. 0

    Dealing with the empty values is another matter but Freemarker uses the sort from java.utils.Collection which is stable (i.e. multi-key sorts retain the original order for equal keys)

    So:

    <#list invoices?sort_by("to_date")?sort_by("from_date") as inv>

    Will get you what you are asking for.
    What this does is first sort by to_date. Then re-sort by from_date and any invoices where the from_date values are equal will retain their stable to_date sort order.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search