Views:

Scenario Overview

In this business scenario the requirement is for the Opportunity form to have a field translating the Est. Close Date field into the Pacific time equivalent. 

It is required to do this automatically upon creating or saving an Opportunity record. 

For this sample, two custom fields Proposal Complete Date and Pacific Time are created and added to the Opportunity Form.  Their logical names are new_proposalcompletedate and new_pacifictime. 

new_proposalcompletedate is of type 'Date and Time' and new_pacifictime is of type 'Single Line of Text.'

    

N52 Formula Manager Solution

The Formula Manager solution works like this:

  • On save of the Opportunity record, the Proposal Complete Date is checked for a new or updated value
  • If the value has changed the formula is triggered and the LocalTimeFromUTCTime function is used to convert the date/time value to Pacific Time (the integer '4' specifies the timezone, for a full list of timezone codes see https://msdn.microsoft.com/en-us/library/ms912391(v=winembedded.11).aspx
  • The value is formatted using the ToString function and the 'u' parameter (the 'u' specifies the format e.g. 2008-06-15 21:15:07Z, to view other format options see https://msdn.microsoft.com/en-us/library/zdtaw1bw(v=vs.110).aspx)
  • Finally, the formatted value is saved to the Pacific Time field


N52 Formula Manager Steps  

The following set of steps outline how to create this Formula:

  • Create a new formula of type 'Save - Perform Action'
  • Set the Mode to 'Server Side'
  • Set the Event to 'Create & Update'
  • Set the Source Entity to 'Opportunity'
  • Set the Source Property to 'Proposal Complete Date' *
  • Go to the Formula editor
  • Copy and paste the formula below
  • Click Save
  • It is now ready for testing

* Note that this is a custom field and will need to be created beforehand.

Formula

UpdateRecord('opportunity', 
        [opportunity.opportunityid], 
        SetAttribute('new_pacifictime', 
                      ToString( localtimefromutctime([opportunity.new_proposalcompletedate], 4) , 'u')
               )
      )

Wizard - LocalTimeFromUtcTime

Please see below the wizard you can use to create the LocalTimeFromUtcTime() function call used in this formula. 

Note you will need to create the custom field beforehand.