search

hougaard / Simple-Object-Designer

Issue tracking for the Simple Object Designer for Business Central
7 stars 0 forks source link

Format Date field on report #357

Closed ScottB-Ronco closed 1 month ago

ScottB-Ronco commented 1 month ago

I created a date field on our Purchase Order screen and made it a field type of DATE. I also added this field to our Purchase Order report for printing the PO.

However when it prints the date value on the report it shows as date and time like this 5/28/2024 12:00:00 AM.

How can I format the date field on the report so that it shows as May 28, 2024?

hougaard commented 1 month ago

Easiest way is to create a calculated field where you format the value with the Format() command...

ScottB-Ronco commented 1 month ago

Do you mean adding the already created date field as a calculated field using the Report Calculated Fields option?

If so how do I use the format option to accomplish this?

hougaard commented 1 month ago

create a calculated Text field with this expression: format(dataitem."your fieldname",0,7)

The 7 comes from here: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-format-property

So if the dataitem is called "header" and your field is "myfield":

format(header.myfield,0,7)

ScottB-Ronco commented 1 month ago

I don't understand what you mean by if dataitem is called header?

Do you mean this is where the date field I created exists? Meaning I created and added it to the purchase header?

hougaard commented 1 month ago

Here is a calculated field added to the header dataitem of an invoice report:

image

And here is the expression formatting the posting date field with a different date format: image

ScottB-Ronco commented 1 month ago

So I followed your example and created the report calculated field as follows. I created the field on a custom word template report.

image

However after I published the changes I went into custom reports in BC and did update report layout and then exported the report, but I cannot see the field added to the report dataset in the XML Mapping pane.

Also per the link you provided for formatting I used date format 4 instead of 7.

hougaard commented 1 month ago

Make sure restart your browser session before doing the "update layout" .... there's some weirdness going on otherwise.

ScottB-Ronco commented 1 month ago

OK so when I came back to work today, my sessions timed out so I had to login again.

After that I did a report update and this time the report calculated field I created showed up on the report.

Thank you for the help