AnsweredAssumed Answered

The date format of one date column in Accounts ListView is incorrect, and it's causing the date to not be searchable by range.

Question asked by Scott Olipra on Aug 28, 2014
Latest reply on Sep 1, 2015 by Darren Thomas
The date format of one date column in Accounts ListView is incorrect (does not match the others), and it's causing the date to not be searchable by range.

The date expected by Sugar is mm/dd/yyyy
The date showing in the ListView column is yyyy-mm-dd

This is version 6.4.1 CE
MSSQL (SQL Server)

I'd like to correct the way it's being returned from the database; because it's evidently not being processed by Sugar's formatter; neither for presentation in the list view, nor in the search form query process (searching for records within this date range).
The vardef for the field is correctly set to be a date field.... BUT... this field in the Accounts listView comes from a related module; a custom module, let's call it st_custom.
So here's what we find in the listviewdefs:


One more layer:
 This is not the core list view; it's a custom listview, invoked by the user using an action button from the normal Sugar listview.

I've looked in the database; the values are stored the same as any other date.
What's baffling here is that the Sugar date formatter doesn't seem to be run on this field after being queried... because it's presented wrong, and processed wrong for the search by date range.  This is probably because the date field belongs to a related (secondary) module.


APPROACH 1: Add customcode to the listviewdefs, and reformat the date using Smarty
I tried putting a customCode line in custom/modules/Accounts/metadata/listviewdefs.php.  It works when I just enter 'customCode' => '{$DATEFIELD_NAME}'. (It just returns the same date, in the wrong format.)  
So I try to use smarty's date coverter function; like ${DATEFIELD_NAME|date_format:"%D"}.
But that returns null
This makes sense when I read the Smarty documentation, because Smarty expects the time to be either a unix timestamp or a mysql timestamp.  Neither of those are populating here, so I'm stuck trying to format the data after it's queried, not upon presentation.  (And I would have to do likewise in the search form processing anyway... when the user is looking for a date range of results).


APPROACH 2: Override the Module's Class such that the create_new_list_query() function includes a date converter... just for my special case related module date.
See http://hirenprajapati.wordpress.com/2013/04/19/change-listview-query-in-sugar-crm/
I successfully extended the classes, and the class:AccountListView -> with function: create_new_list_query() does get loaded and parsed, but it is not successfully overriding that function from data/SugarBean.php.  
(so I'm stuck at a dead-end here)


APPROACH 3: Change the search query itself, before it's requested from the database, such that the format will be produced as Sugar would... since Sugar doesn't seem to be formatting it properly.
See http://sugarcrm-customization-ash.blogspot.com/2013/05/how-to-edit-search-query-in-sugarcrm.html


APPROACH 4: Go looking within Sugar for where the date re-formatting is done, and find a way to extend or compose a custom function in custom/include or in my custom ListView that does this... when the date field from a related module is to be handled in ListView.
(i currently have no idea where that date processing is done)


My Questions:
 - Which Approach seems the fastest to resolve this?  
 - Am I missing a better, easier approach?
 - Does this problem happen even if this date field WASN'T in the custom, alternative ListView?
 - What URLs should I visit that might help me here?

Outcomes