Getting a date data type from a datetime

I am following the info here:

http://support.sugarcrm.com/Knowledge_Base/Studio_and_Module_Builder/Sugar_Logic/Calculated_Field__Converting_Datetime_t… 

I am using the formula as specified by casting a date time to a string, then taking a substring of that and using as a parameter to cast to a date.  I am doing this in the formula builder to populate a field of datatype date, marked as a calculated value.

Since this wasn't working, I created a new filed, of the text field data type, and tried entering a formula that is simply toString($date_entered) where $date_entered is the datetime field I am trying to work with.  This also fails, and neither the new date field nor the new text field are populated.  What am I doing wrong?  And can someone point me toward the documentation for all data types and functions?

  • Hi Missy Brooks,

    Instead of entering a formula with functions in the formula builder, what do you get in the fields if you just enter: $date_entered

    Does that output serve your need effectively?

    Edit:

    To confirm the documentation was still accurate, I followed the document linked above in an 8.1.0 Enterprise hosted instance.

    In the Textfield type, I entered this formula: 

    In the Date type, I entered this formula:

    I then added these two fields to the module layout then Created a new record.

    These were the results:

    In the Edit, before saving the new record, the fields show as blank. This is consistent with the formula because the Date Entered is not populated until after the initial save. Since this edit screen precedes that initial save, there is no data to copy.

    After saving the new record, this is shown in the RecordView:

    My user profile at the time of the above screenshot is dd.mm.yyyy

    I change my user profile date format to mm/dd/yyyy, then return to the record and click Edit to find this:

    The Date type field has changed automatically because the user profile setting impacts the translation of al Date and DateTime type fields.

    The textfield type still shows the old format because this was the content of the string the last time the formula ran.

    Note the above shot is in Edit, where we might expect the formula to dynamically update. It does not update, though, because the source field, $date_created, is not being changed (it never changes). Therefore, the output of the formula is not going to update until I click Save.

    After clicking save, this is what I see:

  • Thank you Patrick.  I started over from the beginning, as your results exactly mirrored what I expected.  I am also working from an Enterprise 8.1.0 system, although it is a UAT environment.

    What I had seen before was after adding the fields to the module layout and entering the formulas for the new fields (exactly as you have above), those fields would never be populated and new records could no longer be saved if I left them as calculated fields (I'm assuming some sort of error was being thrown in the background).  I removed the fields and started over from the beginning.

    I'm not sure what changed, but it works exactly as expected now.  I can validate that the steps you took above returned the expected results in the UAT system.  Thanks for your help!