Spring 18′ Release: 9 new functions that got forgotten in the release notes

SHORT STORY OF A QUEST FOR A MISSING FORMULA DOCUMENTATION

One of the things I find really impressive about Salesforce is Documentation. I was always taken aback by the quality and the level of detail that the teams put into it. And up till the last week, I was convinced that if something is not documented = it doesn’t exist. But this conviction turned into an unsettled one. Now the story.

The leaning junky that I am, I couldn’t wait to get the test drive of the new features. As soon as the Spring 18′ Release Preview hit my sandbox I went on discovering all the latest swag that product teams have delivered.

It happened so that at the same time I was working on the project for a client and one of the features to deliver was an automatic calculation of the contract renewal date based on the Signature date and the contract duration. The good student as I am, I first worked on my process builder in the Sandbox. And oh happy discovery I found a function I have never heard about that seemed to be designed just for that use case – ADDMONTHS.

First thing I did – google search. And first time in my experience as Salesforce consultant I had 0 relevant results. Next stop was the help.salesforce.com search – and the same outcome. One more try – in page search on the “Formula Operators and Functions A–Z” section – again nothing.

At this point, I started to doubt myself (you can’t doubt Salesforce documentation), but I did try one last assumption – maybe it is a part of the latest Release. Thus, next up – Spring 18′ Release Notes – ctrl+f – drum roll – nothing here either.

The further reflexion was – maybe this sandbox has some kind of a package or custom dev that resulted in adding this function? To check this assumption I decided to check in one last place – Functions list in the Validation Rules interface. the thinking was – if the function is here it’s a part of the standard setup.

And I finally had it, the only place I was able to find any kind of documentation about this function (it was 3 lines only but still). And another thing I found – the 8 more functions that have been likewise forgotten by the documentation team.

I still gave it a benefit of doubt and waited till the official release date for production orgs before publishing this article. But here we are past 9 February.

And this was a long introduction to the more applicable information below (I guess tech consultants also have creative writing impulses at times 🙂

DISCLAIMER:

as there is no official documentation on those functions yet, below information is based on empirical date, aka me testing it our in the formulas in my dev org. Thus, any updates and corrections are welcome

[Note 23.02.2018] – the time-related functions has been, in fact, released in Winter 18′ together with the Time (Beta) field, but it the list of undocumented funcrions stays valid except for this minor corrections.

FUNCTION 1 – ADDMONTHS (Spring 18′)

FUNCTION DESCRIPTION:
ADDMONTHS – Add the num months to the date, using the last date of the month if a date is the last day of the month or adding num months has fewer days.

SYNTAX:
ADDMONTHS(date,num);  date – the date field that you will use as a base for your calculation; num – a number of months you need to add.

EXAMPLES:
USE CASE: user needs to populate automatically the END DATE field based on the known Contract duration

1. You can add a fixed number to a given date by specifying a number

End Date =
ADDMONTHS( Start_contract_date__c ,  4)

2. You can add a field value of a number type  to a given date by specifying the field

End Date =
ADDMONTHS( Start_contract_date__c ,  Contract_duration_number__c )

3. You can add a field value of a text type  to a given date by using a VALUE function

End Date =
ADDMONTHS( Start_contract_date__c ,  VALUE( Contract_duration_text__c )

 

Then add the currencies in the Currency Management page

Amount in GPB =
TEXT( MCEILING(CURRENCYRATE(“GPB”)) * Amount) & ” £”

Amount in JPY =
TEXT( MCEILING(CURRENCYRATE(“JPY”)) * Amount) & ” ¥”

 

 

FUNCTION 3 – MCEILING (Spring 18′)

FUNCTION DESCRIPTION:
MCEILING – Rounds a number up to the nearest integer, towards zero if negative. As you can see from the name this function is similar to CEILING function, except MCEILING is Integers only when CEILING works with negative numbers

SYNTAX:
MCEILING(number); number – a numeric value or an expression that you want to round up.

EXAMPLES:

USE CASE: Round up the result of calculation of a price to display the reference Opportunity Amount in 3 different currencies on the Opportunity Detail page (I have used the same use case as for the function above – if you want more details, read the #2).

We are calculating the Account field in a different currency. The result of the calculation is returned as a number, with no limit of decimals. We want to as a Text so that we can add currency signs next to it. This is where MCEILING function comes in handy – it rounds the result to the nearest integer

 Amount in USD (text) =
TEXT( MCEILING(CURRENCYRATE(“USD”)) * Amount) & ” $”

 

FUNCTION 4 – MFLOOR (Spring 18′)

FUNCTION DESCRIPTION:
MFLOOR- Rounds a number down to the nearest integer, away from zero if negative. Same principle as for the CEILING-MCEILING couple. MFLOOR is Integers only when FLOOR works with negative numbers

SYNTAX:
MFLOOR(number); number -a numeric value or an expression that you want to round down.

EXAMPLES:

USE CASE: Round down the result of calculation of a price to display the reference Opportunity Amount in 3 different currencies on the Opportunity Detail page (I have used the same use case as for the function above – MCEILING. The results of the 2 functions will differ by 1, FLOOR being 1 smaller. It’s up to you which function to use, depending on your purpose)

1. We are calculating the Account field in a different currency. The result of the calculation is returned as a number, with no limit of decimals. We want to as a Text so that we can add currency signs next to it. This is where MCEILING function comes in handy – it rounds down the result to the nearest integer.

 Amount in USD (text) =
TEXT( MFLOOR(CURRENCYRATE(“USD”)) * Amount) & ” $”

 

 

The next 5 functions are closely related to new Field Type – “TIME” that was introduced in the latest release. All of them a pretty straightforward.  Most of the use cases I thought of are the one where you need to do smth X time before the Time stored in a given field. For example the event management, or promotion.

Let’s get a little creative and imagine that for all the use cases below we will be working for a company managing the internal certification for their employees through Salesforce.

 

 

FUNCTION 5 – TIMEVALUE (Winter 18′)

FUNCTION DESCRIPTION:
TIMEVALUE(expression) – Returns a time

SYNTAX:
TIMEVALUE(expression); expression – date/time field type or text representation of the time.

EXAMPLES:
In the most use cases, it will serve as an intermediate function for other time formulas. You can convert the date/time or text representation of time using this function. If you want to get creative we can use the following use case

USE CASE: Once participants are registered they need to receive the confirmation email that would indicate the date and time of the session. When configuring Sessions, admin wanted to optimize the number of fields used, thus he chose a Date Time field type. Now the session manager would the confirmation email to have a more appealing design and separate the Date and Time in the text.  And Time Value comes in handy just for this: let’s isolate the Time part to display in the text when sending the email reminders to participants.

Session time =
TIMEVALUE (session_scheduled_date_time_c)

FUNCTION 6 – TIMENOW (Winter 18′)

FUNCTION DESCRIPTION:
TIMENOW() – Returns a time representing the current moment

SYNTAX:
TIMENOW(); () – you leave this empty, it’s a part of the formula – similar to TODAY() and NOW().

EXAMPLES:
In the most use cases, it will serve as an intermediate function for other time formulas. In particular when you want to calculate how much time is let until a certain moment. If you want to get creative we can use the following example

USE CASE: Now we everyone is registered and reminders are sent, we need to make sure that on D-day session manager have all the necessary tools to run the exam. Admin has created a custom Exam Session object for the supervisor to use during the session. One of many elements is the field that shows the time left until the end of the exam and sends an alert at T-10 min and T-5 min calculate the time left for a test session, to make sure that he gets back in time.

Time till session start =
TIMENOW – TIMEVALUE (session_scheduled_date_time_c)

 

 

 

FUNCTION 7 – MILLISECOND (Winter 18′)

FUNCTION DESCRIPTION:
MILLISECOND – Returns millisecond of day.
I doubt you will be calculating something in milliseconds often but I imagine it can be useful for the calculation of script execution time.

SYNTAX:
MILLISECOND(expression); expression – a time type value. Date/time value is not accepted. But you can convert the date/time or text representation of time with TIMEVALUE(expression) function.

EXAMPLES:

USE CASE: Each session for a given certification has several challenges and those are managed by scripts. It’s important to track the performance of those scripts. When scripts get tested, the Execution Start Time and Execution End Time populate the custom time fields of the object. What needs to be done now is calculate the script execution time in milliseconds, to display it on the Script Detail page.

NOTE: Well, this was a use case I have imagined as this function might be useful for. But (a big disappointment) it didn’t work out. After several tests, I have found out that only values it takes as (expression) are the fields of Time format (which is a new Beta field type), but those only display HH:MM, and thus the Millisecond function return 0…

The only non 0 result if managed to produce is with the expression below.

Field =
MILLISECOND(TIMENOW())

But I have trouble finding a use case for it… Thought? Ideas?

 

 

 

 

FUNCTION 8 – MINUTE (Winter 18′)

FUNCTION DESCRIPTION:
MINUTE(expression) – Returns minute of day.

SYNTAX:
MINUTE(expression); expression –expression – a time type value. Date/time value is not accepted. But you can convert the date/time or text representation of time with TIMEVALUE(expression) function.

EXAMPLES:
USE CASE: Now when scripts have been tested and session are up an running. We need to manage the participants’ registration. A session manager would like to calculate the minutes spend for each task in the test. When participant opens the question he clicks “Answer” and the new answer record is created. When a participant submits the answer the Submission time is saved in a field.

Time Per Task =
MINUTE( Submit_time__c) – MINUTE( TIMEVALUE( CreatedDate ) )

 

 

 

FUNCTION 9 – WEEKDAY (Spring 18′)

FUNCTION DESCRIPTION:
WEEKDAY(date) – Return the day of the week for the given date, using 1 for Sunday, 2 for Monday, through 7 for Saturday.
A common use case where you have to skip the weekends for your automation was a bit of a headache to write a formula for. Salesforce has made everyone’s life easier by adding this function.

NOTE: the formula behind the WEEKEND function is, in fact, the following

CASE(MOD( date – DATE( 1900, 1, 7 ), 7 ), 7, “7”, 1, “1”, 2, “2”, 3, “3”, 4, “4”, 5, “5”, “6”)

 

SYNTAX:
WEEKDAY(date); date – any field of the date format, or a specific date.

EXAMPLES:

USE CASE: once the results are ready, the Session Manager wants to communicate those to users by mail. The results are automatically generated 3 days after the session. The manager wants to make sure that participants aren’t disturbed on the weekends. If the results date falls on Saturday or Sunday send the send sate should be the next Monday.

Results sent date =
IF( (WEEKDAY( CloseDate ) + 3) == 6 , (TODAY() + 6),
IF( (WEEKDAY( CloseDate ) + 3 )== 7 , (TODAY() + 5), (TODAY() + 5)))

 

This would be the end of this short tour. As I said in the introduction, this article is a result of my empirical tests and shouldn’t be taken as official documentation. But I believe it can be helpful in the meantime. Don’t hesitate to contribute if you run your own tests or come across some more information from Salesforce.