Solved

Why does custom DateTime calculated from database field display incorrect time?

  • 25 February 2022
  • 17 replies
  • 912 views

Badge +10

I have a custom field on Cases which pulls the ‘Start date’ of the last created Appointment activity attached to the Case. It works splendidly, except that the time is wrong. It’s five hours different:

 

The field definition is:

namespace CaseWorkflow
{
public class CaseWorkflow_CRCaseExt : PXCacheExtension<CRCase>
{
#region UsrAppointmentDate
[PXDateAndTime(InputMask = "g", DisplayMask = "g")]
[PXFormula(typeof(TimeZoneNow))]
[PXDBScalar(typeof(SearchFor<CRActivity.startDate>.
In<
SelectFrom<CRActivity>.
Where<CRActivity.refNoteID.IsEqual<CRCase.noteID>.
And<CRActivity.type.IsEqual<appointmentType>>>.
OrderBy<CRActivity.createdDateTime.Desc>>))]
[PXUIField(DisplayName = "Appointment Date", Enabled = false)]
public virtual DateTime? UsrAppointmentDate { get; set; }
public abstract class usrAppointmentDate : PX.Data.BQL.BqlDateTime.Field<usrAppointmentDate> { }
#endregion
}

public class appointmentType : PX.Data.BQL.BqlString.Constant<appointmentType>
{
public appointmentType() : base(ActivityTypes.Appointment) { }
}
}

 

It seems like a time zone or formatting issue, but I can’t find any documentation on what could be wrong.

icon

Best answer by Nayan Mansinha 25 February 2022, 23:57

View original

17 replies

Userlevel 7
Badge +5

Normally the date in the DB is stored in UTC and is converted to local timezone when displayed. It seems that having the field like that breaks this.

Badge +10

Normally the date in the DB is stored in UTC and is converted to local timezone when displayed. It seems that having the field like that breaks this.

So like, bug broken? Would there be a way to work around this? All our users are in the same time zone, so I would be fine with a hacky solution temporarily.

Userlevel 4
Badge +2

Question 1)

Your implementation uses an unbound field to calculate the Appointment Date each time Case records are returned by Calculation utilizing PXDBScalar to look up CRActivity Records of type Appointment.

Does PXDBScalar return the UTC date without PXFormula being implemented?

Questions 2/3)

Are you using PXFormula to aggregate the StartDate from your Activities in case of multiple records?

OR

Are you using PXFormula to set the AppointmentDate = Now if no records are found from PXDBScalar?

[PXFormula(typeof(PX.Objects.CS.TimeZoneNow))] and [PXFormula(typeof(TimeZoneNow))]

Is ref to a sealed class that fetches the Time in the Profiled/Site Pref set time zone

And is implemented on CRActivity.StartDate to capture the current time

CRActivity.StartDate implements the EPStartDateAttribute to lookup the timezone and format the field after the database returns the UTC value.

 

My recommendation would be to remove PXFormula from your unbound field and decorate it with EPStartDate and/or create an attribute of your own to display the Date in the format you need for your implementation

 

Badge +10

@tvars71 

I was going to remove [PXFormula] from the code block, but I must not have saved it. That was not supposed to be in this post. That was something I tried as it was unclear to me what it did. I have tried it without that, and it makes no difference. [PXDBScalar] executes fine without it and does return UTC time.

 

I attempted to use [EPStartDate] but receive the error ‘Invalid cache type. CRActivity is expected.

 

I’m afraid defining an attribute from scratch is slightly outside of my current skillset.

Userlevel 4
Badge +2

@Deetz  try this [PXDateAndTime(UseTimeZone = true)] in place of

 [PXDateAndTime(InputMask = "g", DisplayMask = "g")]

and see if corrects the display

Badge +10

@tvars71 - I already tried that. Unfortunately, it does not help.

Userlevel 4
Badge +2

Did several experiments  with different tricks and this is definitely returning UTC time. 

Seems that even though PXDateAndTimeAttribute inherits from PXDateAttribute its not running the PXDBScalar result thru the TimeZone conversion. Acumatica will have to take a look at this.

As a workaround, You can catch this in an event and try to reformat the UTC time into the Local format or add a standard DateTimeOffset since everyone is in the same time zone.

 

Badge +10

@tvars71 

I got it to return the correct result after modifying it in a field selecting event, but this does not correct it for a GI. Is there a way I can achieve both?

Userlevel 4
Badge +2

Use attribute called PXDBDatetimeScalar for this purpose.  It basically derived from PXDBScalar with SetValue() override which does the appropriate time conversion.

 

See example below:

 

     #region UsrAppointmentDate
        [PXDateAndTime(DisplayMask = "g")]
        [PXDBDatetimeScalar(typeof(Search<CRActivity.startDate,
            Where<CRActivity.refNoteID, Equal<CRCase.noteID>>>), PreserveTime = true, UseTimeZone = true)]
        [PXUIField(DisplayName = "Appointment Date", Enabled = false)]
        public virtual DateTime? UsrAppointmentDate { get; set; }
        public abstract class usrAppointmentDate : PX.Data.BQL.BqlDateTime.Field<usrAppointmentDate> { }
        #endregion

 

Badge +10

Use attribute called PXDBDatetimeScalar for this purpose.  It basically derived from PXDBScalar with SetValue() override which does the appropriate time conversion.

     #region UsrAppointmentDate
        [PXDateAndTime(DisplayMask = "g")]
        [PXDBDatetimeScalar(typeof(Search<CRActivity.startDate,
            Where<CRActivity.refNoteID, Equal<CRCase.noteID>>>), PreserveTime = true, UseTimeZone = true)]
        [PXUIField(DisplayName = "Appointment Date", Enabled = false)]
        public virtual DateTime? UsrAppointmentDate { get; set; }
        public abstract class usrAppointmentDate : PX.Data.BQL.BqlDateTime.Field<usrAppointmentDate> { }
        #endregion

 

You sir, are fantastic. Thank you!

Userlevel 3
Badge

Use attribute called PXDBDatetimeScalar for this purpose.  It basically derived from PXDBScalar with SetValue() override which does the appropriate time conversion.

     #region UsrAppointmentDate
        [PXDateAndTime(DisplayMask = "g")]
        [PXDBDatetimeScalar(typeof(Search<CRActivity.startDate,
            Where<CRActivity.refNoteID, Equal<CRCase.noteID>>>), PreserveTime = true, UseTimeZone = true)]
        [PXUIField(DisplayName = "Appointment Date", Enabled = false)]
        public virtual DateTime? UsrAppointmentDate { get; set; }
        public abstract class usrAppointmentDate : PX.Data.BQL.BqlDateTime.Field<usrAppointmentDate> { }
        #endregion

 

You sir, are fantastic. Thank you!

Is there any way to do this in a GI field (convert to local user timezone from UTC) without a customization?  If so, can you show an example of the formula that would do this?  I know it is supposed to be automatic using the standard datetime field selection in a GI (such as to show the created and modified times for a record along with the date), however it doesn’t show the time (only the date) unless you enter it as a field formula (such as in the Sales Orders GI here).

 

Userlevel 4
Badge +2

Is there any way to do this in a GI field (convert to local user timezone from UTC) without a customization?  

 

I doubt unless there is already a field in the system which can be used as Schema Field for the column in GI.

Userlevel 3
Badge

Is there any way to do this in a GI field (convert to local user timezone from UTC) without a customization?  

 

I doubt unless there is already a field in the system which can be used as Schema Field for the column in GI.

So the million dollar question: Is there another field to use as a schema field?  

 

Seems really odd that this isn’t built in functionality for datetime fields to show the time in the local time.  Otherwise why ask each user what timezone they’re in for their profile?

Userlevel 4
Badge +2

 

So the million dollar question: Is there another field to use as a schema field?  

 

Seems really odd that this isn’t built in functionality for datetime fields to show the time in the local time.  Otherwise why ask each user what timezone they’re in for their profile?

Good question.  The reason, let me explain - audit fields viz. CreatedDateTime and LastModifiedDateTime are stored in UTC using system attributes PXDBCreatedDateTime and PXDBLastModifiedDateTime respectively.  These attributes default display format is d - short date, that is why we see only the date portion.  If we modify this to say g or G - general date/time format, we then can see date and time. See example below for CreatedDateTime field that sets InputMask property to G.  Standard formats can be found here: Standard date and time format strings | Microsoft Docs)  

    public class SOOrderExt : PXCacheExtension<PX.Objects.SO.SOOrder>
{
#region CreatedDateTime
[PXDBCreatedDateTime(InputMask = "G")]
[PXUIField(DisplayName = PXDBLastModifiedByIDAttribute.DisplayFieldNames.CreatedDateTime, Enabled = false, IsReadOnly = true)]
public DateTime? CreatedDateTime { get; set; }
#endregion
}

With this information, now we can search in Source Code screen for InputMask = “g” or “G” to find the field and use that field as Schema Field in GI.  I did a quick search and found many fields, see below.  

 

Userlevel 3
Badge

 

So the million dollar question: Is there another field to use as a schema field?  

 

Seems really odd that this isn’t built in functionality for datetime fields to show the time in the local time.  Otherwise why ask each user what timezone they’re in for their profile?

Good question.  The reason, let me explain - audit fields viz. CreatedDateTime and LastModifiedDateTime are stored in UTC using system attributes PXDBCreatedDateTime and PXDBLastModifiedDateTime respectively.  These attributes default display format is d - short date, that is why we see only the date portion.  If we modify this to say g or G - general date/time format, we then can see date and time. See example below for CreatedDateTime field that sets InputMask property to G.  Standard formats can be found here: Standard date and time format strings | Microsoft Docs)  

    public class SOOrderExt : PXCacheExtension<PX.Objects.SO.SOOrder>
{
#region CreatedDateTime
[PXDBCreatedDateTime(InputMask = "G")]
[PXUIField(DisplayName = PXDBLastModifiedByIDAttribute.DisplayFieldNames.CreatedDateTime, Enabled = false, IsReadOnly = true)]
public DateTime? CreatedDateTime { get; set; }
#endregion
}

With this information, now we can search in Source Code screen for InputMask = “g” or “G” to find the field and use that field as Schema Field in GI.  I did a quick search and found many fields, see below.  

 

Appreciate the response, but I must not be understanding.  When I use that field as the schema field it doesn’t seem to change it from UTC to local.  Do I not just put [PXDBCreatedDateTime(InputMask = "g")] in the schema field, as in the GI example below?

Thanks for the guidance. New to Acumatica this year and still getting some of the nuances.

Userlevel 4
Badge +2

Appreciate the response, but I must not be understanding.  When I use that field as the schema field it doesn’t seem to change it from UTC to local.  Do I not just put [PXDBCreatedDateTime(InputMask = "g")] in the schema field, as in the GI example below?

Thanks for the guidance. New to Acumatica this year and still getting some of the nuances.

 

As the name suggest, Schema Field only takes field name whose characteristics (format) needs to be applied.  So no - you can’t do that.

In my previous reply, I mentioned about searching field with the required format in Source Code screen.  The resulting search contains fields you can use in the Schema Field column.  You do that by simply adding the DAC in the Tables, no need to define any relations.  See screenshots below:

 

Userlevel 3
Badge

THANK YOU SO MUCH!!!!  IT WORKS!  Our long national nightmare is finally over!   Really appreciate the patient and detailed explanation on this.  I’ve fixed several GIs already that were driving our users crazy!

 

Jeff

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved