Skip to main content
Inspiring
July 31, 2024
Question

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

  • July 31, 2024
  • 2 replies
  • 561 views

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.

 

 

 

This topic has been closed for replies.

2 replies

Raymond Camden
Community Manager
Community Manager
July 31, 2024

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

 

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

Inspiring
July 31, 2024

with the :eval(details):

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

 

 

Thanks

 

Raymond Camden
Community Manager
Community Manager
July 31, 2024

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.

Inspiring
July 31, 2024

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