1. Displaying Record Created Date/Time - Summit 2019 Session Examples
This post shows you how to create one of the examples from my presentation at Acumatica Summit 2019 in Houston, entitled C103: Getting Actionable Information with Self-Service Reports & Dashboards (click here for all of the examples).
In this Example #1, let's say you want to look at a list of Purchase Receipts to see both the Date and Time that they were created.
When you look at the regular Purchase Receipts (PO3020PL) inquiry screen in Acumatica, you can see the Created On Date, but not the Time.
What if you want to add the Time to the Created On field? There is a trick.
1. Go to CUSTOMIZATION -> Edit Generic Inquiry in the upper right-hand corner to edit the Generic Inquiry.
2. Add PX.Objects.CR.CRCase to the TABLES tab. The reason for this is that the POReceipt.CreatedDateTime Data Access Class in Acumatica is set to only display the Date, but the CRCase.CreatedDateTime Data Access Class is set to display both the Date and the Time.
3. Set the Schema Field column for the Created On field to CRCase.CreatedDateTime. The Schema Field column is only used for formatting/display purposes. We still want to show the POReceipt.CreatedDateTime value, but use the Data Access Class display format of CRCase.CreatedDateTime.
4. Now when we look at the Purchase Receipts (PO3020PL) inquiry screen in Acumatica we can see both the Date and Time displayed in the Created On field:
Want to join the conversation? Feel free to add a Post below to this Topic to continue the conversation.
This was great except for a result that I didn't expect and don't understand.
I am displaying FSAppointment.ScheduledDateTimeBegin and it uses the default Caption "Scheduled Date"
When I put CRCase.CreatedDateTime, it changes the Caption to "Date Reported"
I did manually put in a Caption "Scheduled Date" to fix it, I'd just like to understand the logic behind the change.
I wouldn't worry too much about how it defaults Captions. In most of my GIs, I manually assign all Captions and column widths to have a nicely formatted output.
True! I'm guessing its the Caption for CRCase.CreatedDateTIme but I don't see why they would want the Caption to match the Schema rather than the Field
Captions are inconsistently defined, hence my previous comment.
I'm working on a GI that returns the date and time of a SOLine created. We need to look at the time based on the branch/warehouse location; which means that it might be ET or CT. Just realized that SOLine.DateTimeCreated returns the time in my time zone.
However, the Field Audit shows the ET.
What would you recommend to use for the time conversion, let say for the GI results to always return Eastern Time?
I struggle with the time zones personally. I'm still not 100% clear how they get used throughout Acumatica.
There is the Login Time Zone on the Site Preferences (SM200505) screen. Then there is the Time Zone field on the PERSONAL SETTINGS tab of the Users (SM201010) screen. But it seems like they both get used to display dates/times depending on where you are in the system.
I also wonder if all Create Date and Last Modified Date values get stored in UTC format in the database, but I haven't confirmed.
Sorry, wish I could be more helpful.
@timrodman Thank you, Tim!
Here is what Acumatica Support says:
"The issue is related to the inability of Acumatica to display the time stamp in a user time zone in versions earlier that 2019R2 because the system stores audit date/times in the time zone of Sql server. Upgrade to 2019R2 should resolve the issue"
For the GI that we created in 2019 R1 they recommended us to use the SQL Server time for the formulas.
Ah, interesting. Hopefully upgrading to Acumatica 2019 R2 solves the problem for you.