Logo

How to get the MAX or Most Recent Date of several date fields in Salesforce Formula

Goal: Return the MAX or Most Recent Date of several dates

Recently I needed to get the most recent date of 4 separate date fields.  I created a new formula field and was shocked to find that MAX(custom_date_1,custom_date_2,custom_date_3,custom_date_4) did not work.  MYSQL allows MAX() to function for date fields and I was surprised it didn’t work here!

Workaround: MAX(DATE) Formula

The full formula example of the workaround is below.  An important aspect of the formula is to handle if any of the dates that are being compared are null.  Also remember to add DATEVALUE() for any fields that are of type DATETIME.

IF(
    AND(
        ISBLANK(custom_date_1),
        ISBLANK(custom_date_2),
        ISBLANK(custom_date_3),
        ISBLANK(custom_date_4)
    ),
    NULL,
    DATE(1900, 01, 01) +
    MAX(
        BLANKVALUE(
            custom_date_1, 
            DATE(1900, 01, 01)
        ) - DATE(1900, 01, 01),
        BLANKVALUE(
            custom_date_2,
            DATE(1900, 01, 01)
        ) - DATE(1900, 01, 01),
        BLANKVALUE(
            custom_date_3,
            DATE(1900, 01, 01)
        ) - DATE(1900, 01, 01),
        BLANKVALUE(
            custom_date_4,
            DATE(1900, 01, 01)
        ) - DATE(1900, 01, 01)
    )
)

I hope you find this helpful!

Salesforce – let’s have a date…and time! Date Fields Missing Time Workaround

System Date Fields Missing Time

You can’t go on a date without first setting a time, right?  Well, Salesforce has found a way to have system date fields missing time information!  Have you ever wanted to know the created date & time of a record within Salesforce reports?  How about sorting a list view in ascending or descending order based on the creation or last modified date?  Well unfortunately, even though these date/time fields capture both the date and the time, only the date is displayed in reports and views.  Thankfully there is a quick and easy workaround for this issue as outlined below.

Date Fields Missing Time – The Problem

Date Fields Missing Time Without Time In Salesforce List Views

Where has the time gone? Definitely not found in Salesforce list views and reports!

Date Fields Missing Time Without Time In Salesforce Reports

Date Fields Missing Time – The Workaround

Create a formula field and be sure to select Date/Time as the type:

Create Custom Formula for Date Fields Missing Time Without Time

For the formula result, select the System field you would like to retrieve:

Set Created Date as Formula for Date Fields Missing Time Without Time

In this case, we are wanting to pull in the Created Date & Time info.

Set Created Date As Formula Result for Date Fields Missing Time Without Time

Add the new formula fields to your list views & reports and enjoy!

Dates With Times Now In Salesforce List Views

There’s the time!  Now you can easily sort in ascending or descending order!

Dates With Times Now In Salesforce Reports

 

What other tricks do you have related to date/time fields?  Let me know in the comments!

Multiple tabs are your Salesforce friends!

I’m sure you leverage multiple tabs in your everyday web browsing life, but have you utilized them in Salesforce.com?  No, I’m not talking about (my 3-year-old son says, “but but but that’s not what I was talking about!) the objects you add to apps to display in the tab bar at the top of every page within Salesforce.  I’m talking about using multiple browser tabs.  I’ve found them to be instrumental in day-to-day administration and configuration of the platform.

For example, I’ve opened multiple browser tabs to quickly compare 5 or 10 page layouts and quickly used the keyboard to toggle between them rapidly to ensure all layouts include/exclude particular fields with the correct access (read-only) and placement.  Sure, I could have used a single tab and used the Salesforce drop-down to select the next layout and repeated 10x, but opening them all up quickly and then cycling through is much faster.

Multiple Tabs – Profile – Object Permissions

While editing profile permissions, I’ve opened multiple a tab of each profile in question and quickly toggled between them to compare and contrast.  Sure, I do know that the enhanced profile interface would let me add a new view with columns for each CRED on specific objects and view those for whichever profiles I want.  But this is honestly a pain.  I can only have 10 columns and each CRED attribute (Create, Read, Edit, Delete, View All, Modify All, etc.) take up a column.  I can maybe get two objects within a view and that takes time to build if you are leveraging many standard and custom objects including managed packages.  Not to mention you can’t add page layout or record type assignments to these views.

Below is a quick example of this.  I was not able to use my keyboard shortcuts while creating the GIF, so I just cycled through with the mouse.  The key here is to learn the keyboard shortcut so you can toggle through much faster, and focus all of your attention on the text.  Any differences will quickly jump out at you!  Once you are done cycling through the current objects on the page, hit space bar on each tab as you cycle through.  It will scroll the window down by the same amount on each tab so that they all line up perfectly for you to continue your review.  In this case the only difference is the Tab for Leads is “Default Off” for the Support Profile.

Use Multiple Tabs to Check Profile Permissions

Multiple Tabs – CRED – Field Security – Page Layouts & Record Types

From here I can open up multiple tabs for various objects to edit/review or open the same object but for various profiles (example below).  Here I can quickly see CRED permissions, field security, and page layout & record type assignments and make the necessary changes.

Use Multiple Tabs to Check Assigned Page Layouts Record Types And Object Perms

Multiple Tabs – Available Picklist Values for Record Types

This also comes in handy when quickly reviewing the available picklist values for different record types.

Use Multiple Tabs to Check Record Type Available Picklist Values

Admittedly, these examples are simple and I wasn’t able to cycle through as fast as you can with the keyboard.  It may seem that you could just as easily accomplish a simple task with a single browser tab/window.  But what if you had 10 different picklist fields to compare for 10 different record types?  Using multiple tabs can help you quickly and efficiently review settings across the platform and make changes if necessary.

How do you leverage multiple tabs in your Salesforce Administration?  Would love to hear your comments!

Lead Users Through a Business Process with Record Types

AnimLeadRecordTypeConvertButton

Record Types Create Salesforce Magic!

Do you understand the basics of custom fields, page layouts, and workflows and want to reach the next level?

Maybe you have a well administered salesforce org with validation rules, but encounter the need for “walking” your users through a specific business process specific to your industry.

Look no further than record types! They are not as confusing as they may first appear.

Record types are just a clean way of associating a different page layout, stage/status, and customizing the available values on picklist fields.

Record Types Determine:

  • Page Layout – Display different layouts depending on the record type.  For example, a lead that was generated from a google ad could display very different fields and buttons compared to one manually entered from an inbound phone call.
  • Process – These are specific to the object, but some examples include opportunity stages, lead statuses.  The google ad lead above may not include a “working” status.  You can simply remove that option from the picklist.
  • Picklist Values – Just like you can control the availability of the stage/status picklist values above you can also do this for any other picklist field on the object!

LeadProcess

Lead Process Selection

Once you master these available options, add workflow to the mix for some real process magic.

Let’s say you don’t want to allow users to convert a lead until they have entered a specific set of information. You can set it up so that all incoming leads will be a specific record type with a page layout that does not include the “convert” button.  Then, when certain fields have been filled out, a workflow can fire to update the record type of the lead. The new record type will have different page layout for the specific user’s profile that will reveal the “convert” button.  See the animation below for this use case in action!

 

UpdateFieldRecordType
Setting Up The Field Update to Update Lead Record Type

AnimLeadRecordTypeConvertButton

Animation – Convert Button Not Present Until Specific Fields Entered