Before this release, date fields were not sortable as dates in Easy Form Report macros. They were only sortable as alphabetical strings. With this new option the dates can be saved in ISO-8601 format so that they are sortable in reports.
THIS CHANGE DOES NOT FIX SORT ORDERING FOR DATE FIELD SUBMISSION VALUES MADE PRIOR TO ENABLING THIS NEW OPTION ON A DATE FIELD (see details below).
There is a new field option in the form editor for the Easy Form macro: “Store date as ISO-8601 date. Recommended for proper sort order in reports”
When this option is enabled for a date field then this date field will be properly sortable as a date in columns in an Easy Form Report macro:
You can right-click → inspect to see the HTML markup for a date value in the Easy Form Report to see that there is a new “data-sort” attribute that uses the ISO-8601 date that is stored:
For a date field that does not have this new option enabled, the date is only sortable as an alphabetical string and we put a hint about that in the data-sort attribute:
The date picker for date fields will choose/set the date in the input as ISO-8601 but will also display the chosen value as a formatted date string to the right side of the input so that users can see a more easily understandable form of the chosen date:
Note that you can enable/disable this new option for an existing date field. Enabling/disabling it does not change the format of the date values that were saved prior to enable/disable of this option. In that scenario, you could have a mix of ISO-8601 and formatted date strings saved. In that situation you would inspect the “data-sort” attribute of a table cell to clarify which format has been saved for a given submission. If you have a mix of formats for the saved submission data then you will notice that during sorting, where the ISO-8601 dates will sort correctly as dates and the formatted date string values will be mixed in there, in alphabetical order instead of date order.
Easy Form Report macro export files (JSON, XML, XLS, CSV) will contain ISO-8601 date values for fields with this new option enabled. They will be like “2022-05-18”, they will not be formatted like “May 18, 2022”.
Notice that ISO-8601 format always is year, month, day. It is never year, day, month no matter what your date/time format is on your Confluence instance. ISO-8601 format puts the largest units of time to the left so that they are sortable.
Created and Updated Fields are sortable in Easy Form Reports
Note that in a prior release, 1.10.19, the “Created” and “Updated” fields in Easy Form Reports were fixed so that they are always sortable as dates. Those fields were already being stored as ISO-8601 dates so there was not need to change that, we only added the “data-sort” attribute to the table to make them properly sortable as dates: