Fixing SalesLogix Web Client Ticket Journal Date Display

As you probably know, SalesLogix displays most date times in the database in UTC format.  Normally the SalesLogix provider handles converting the UTC formatted dates to the local client date times.  There are exceptions to this automatic adjustment.  One such case is the Journal tab within the Ticket area of the SalesLogix web client.  In this tab the SalesLogix entity model records those fields marked as audited into the TICKETHISTORY table.  This table then displays in the Journal table.  The audit records which field has changed and what the old value was and the new value is.


Audit Trail showing UTC


The issue here arises because the old value and new values can represent anything, based upon what the field that is changing is.  For instance the values might display a user, a status code, a date, etc.  The SalesLogix provider only applies automatic UTC conversion to fields marked as date fields.  Since the Old and New Value fields are a collection of various data types, all stored as strings, the SalesLogix web client does not apply any UTC conversion to date values stored in the Old and New value columns.


The result as shown in the image above is that the Date Changed column (since it is a Date Time data type) Automatically has UTC to local time conversion performed on it, while the Old Value and New Values show the unconverted UTC date time values. I.E., the line showing a Changed Date of 7/1/2009 3:17 PM shows a new Value of 7/1/2009 8:17:19PM.  The New Value is in fact the same as the Date Changed, SalesLogix is simply just not converting the database level value to the correct user display format.


The Fix


So how can we fix this behavior?  Well if you look at the Ticket History quick form you will see the data grid as one of the form elements.  If you look at the properties of the grid, you will see it contains a “Columns” property.


Audit Trail Data Grid Properties


Clicking on the ellipse of the Columns Property will launch the Columns dialog.  Here you can see the column definitions for both the Old and New Values.  If you click on either of those columns on the left side of the screen you will see properties for the column.  Specifically a property called Format Code.


Audit Trail Column Properties


Clicking on the Ellipse of the Format Code will open the code editor for the column Formatting code.  Here we can see SalesLogix is already doing some interpretation based on the field being changed.  For instance, if the field is StatusCode it is getting the picklist text of the Status, rather than just displaying the PICKLISTID, which is actually what is stored in the database.  We can add our own code to handle converting the dates to this same area.



protected string FormatOldValue(object fieldChanged, object oldValue)
{
    string result = String.Empty;
    if (fieldChanged != null && oldValue != null)
    {
    result = oldValue.ToString();
    switch (fieldChanged.ToString().ToUpper())
        {  
            case “STATUSCODE”:
                Sage.SalesLogix.PickLists.PickList picklist = Sage.SalesLogix.PickLists.PickList.GetPickListByItemId(result);
                if (picklist != null)
                {
                    result = picklist.Text;
                }
                break;
            case “ASSIGNEDTOID”: case “RECEIVEDBYID”: case “COMPLETEDBYID”:
                Sage.Entity.Interfaces.IOwner assignedTo = Sage.Platform.EntityFactory.GetRepository<Sage.Entity.Interfaces.IOwner>().Get(result);
                if (assignedTo != null)
                {
                    result = assignedTo.ToString();
                }
                break;
            case “ACCOUNTID”:
                Sage.Entity.Interfaces.IAccount account = Sage.Platform.EntityFactory.GetRepository<Sage.Entity.Interfaces.IAccount>().Get(result);
                if (account != null)
                {
                    result = account.AccountName;
                }
                break;
            case “CONTACTID”:
                Sage.Entity.Interfaces.IContact contact = Sage.Platform.EntityFactory.GetRepository<Sage.Entity.Interfaces.IContact>().Get(result);
                if (contact != null)
                {
                    result = contact.ToString();
                }
                break;
    }
    return result;
}


Here is the new code with a conversion of the dates if the Assigned Date was changed.  (The new code is shown in Red):



protected string FormatOldValue(object fieldChanged, object oldValue)
{
    string result = String.Empty;
    if (fieldChanged != null && oldValue != null)
    {
    result = oldValue.ToString();
    switch (fieldChanged.ToString().ToUpper())
        {  
            case “STATUSCODE”:
                Sage.SalesLogix.PickLists.PickList picklist = Sage.SalesLogix.PickLists.PickList.GetPickListByItemId(result);
                if (picklist != null)
                {
                    result = picklist.Text;
                }
                break;
            case “ASSIGNEDTOID”: case “RECEIVEDBYID”: case “COMPLETEDBYID”:
                Sage.Entity.Interfaces.IOwner assignedTo = Sage.Platform.EntityFactory.GetRepository<Sage.Entity.Interfaces.IOwner>().Get(result);
                if (assignedTo != null)
                {
                    result = assignedTo.ToString();
                }
                break;
            case “ACCOUNTID”:
                Sage.Entity.Interfaces.IAccount account = Sage.Platform.EntityFactory.GetRepository<Sage.Entity.Interfaces.IAccount>().Get(result);
                if (account != null)
                {
                    result = account.AccountName;
                }
                break;
            case “CONTACTID”:
                Sage.Entity.Interfaces.IContact contact = Sage.Platform.EntityFactory.GetRepository<Sage.Entity.Interfaces.IContact>().Get(result);
                if (contact != null)
                {
                    result = contact.ToString();
                }
                break;
        case “ASSIGNEDDATE”:
                TimeSpan ts = System.DateTime.UtcNow – System.DateTime.Now;
                result = System.Convert.ToDateTime(oldValue).AddHours(-1 * ts.Hours).ToString(); //System.DateTimeOffset.Now.ToString();
                break;

        }
    }
    return result;
}


The code shown above is the Format Code for the Old Value column.  You can do the same type of change to the New Value column’s format code, you would just need to change the reference of oldValue to newValue like so:

        case “ASSIGNEDDATE”:
                TimeSpan ts = System.DateTime.UtcNow – System.DateTime.Now;
                result = System.Convert.ToDateTime(newValue).AddHours(-1 * ts.Hours).ToString(); //System.DateTimeOffset.Now.ToString();
                break;

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!