Data formatting for Fields in a table do not work (Jsonata)

[Steven C. Wong]

Refering to this original post about formatting dates in a JSON object using toMillis and fromMillis:

Date formatting / examples of using JSONata expres... - Adobe Community - 13261898

This apporach works with dates in the JSON object that are not in an array. If you try to do this using the old table format or new table format, they result in an unexpected result.

Consider this data:

{
   "noArrayDate": "2024-12-30T05:00:00Z",
   "details": [
    {
      "unitId": 0,
      "endDate": "2031-01-01T05:00:00Z"
    },
    {
      "unitId": 1,
      "endDate": "2025-01-01T05:00:00Z"
    },
    {
      "unitId": 2,
      "endDate": "2025-01-01T05:00:00Z"
    }
  ]
}

Using the following Word Template, include either the "New Table Format" or the "Old Table Format" and generate the document to see the result.

with the new table format, you get this (unsupported expression):

New Table Format

Unit	Start date	Start date Formatted
{% table-start details %} {{unitId}}	{{endDate}}	{{ $fromMillis($toMillis(endDate),'[D]/[M]/[Y]') }} {% table-end %}

with the old table format you get this (format as text - ignored):

Old Table Format

Unit	Start date	Start date Formatted
{{details.unitId}}	{{details.endDate}}	{{ $fromMillis($toMillis(details.endDate),'[D]/[M]/[Y]’) }}

Note that formatting dates NOT in an array works fine:

Dates outside of an Array

{{ $fromMillis($toMillis(noArrayDate), '[D]/[M]/[Y]') }}

{{ $fromMillis($toMillis(noArrayDate), '[FNn], [D1o] [MNn] [Y]') }}

Is there a proper way to reference the date field in an array?

We need to use the same JSON object to support different templates which require different date formats for the same field. 

Thanks for any pointers.

[Steven C. Wong]

@Michael MQ thought i'd tag you in case this applies to you too. Thanks.

[Raymond Camden]

I can't test this second, but what if, using the new format, maybe try: 

$fromMillis($toMillis(endDate:eval(details)),'[D]/[M]/[Y]')

[Steven C. Wong]

with the :eval(details):

Error: "Please provide the valid/supported input document template"

Thanks

[Raymond Camden]

Yeah shoot. To be fair, that was the solution for - I'm looping inside a loop and need to provide a way to reference the right loop. 

Unfortunately, you may be out of luck. Of course, you can always just format your numbers _before_ you call DocGen. That's a solution you can do rather quickly. 

I'll make a small reproducable case for this and log a bug.

[Raymond Camden]

It looks like I may have already reported it a few years ago. 😕 

https://community.adobe.com/t5/acrobat-services-api-discussions/date-formatting/td-p/12173140

I'll check. 

[Raymond Camden]

Ok, so the example of fromMillis and toMillis *does* work on $now, but not an array value, as I beliieve you saw - it's just an issue with the arrays. I filed a new bug.

As I said though, you can format locally. If you are using Node.js and need an example, let me know. Intl makes it super easy.

[Steven C. Wong]

Thanks @Raymond Camden , i appreciate you following up on the bug. 

for our purposes, we will need to format the date differently based on the type of report created with the data. Some reports need March 2024, some reports need 03-31-2024, etc.

We would have to add a field to the array for each format type a user could possibly need in their report multipiled by each date field in the array.

I'll keep an eye out for future updates. Cheers.