Custom Formulas - Successforce Blog

Edit and Delete Records Directly from a Report with Custom Buttons

Today on the message boards gokubi posted a great best practice idea for initiating actions directly from a report.

The way Salesforce's reporting tool works is that you click through to view the record but it's one more click if you want to edit it or delete it. Gokubi created a custom formula field for edit, delete, and add to campaign which can be added as a column to your custom reports.

To see how he did it read the full post on his blog.

Edit and Delete Records Directly from a Report with Custom Buttons

Today on the message boards gokubi posted a great best practice idea for initiating actions directly from a report.

The way Salesforce's reporting tool works is that you click through to view the record but it's one more click if you want to edit it or delete it. Gokubi created a custom formula field for edit, delete, and add to campaign which can be added as a column to your custom reports.

To see how he did it read the full post on his blog.

Converting Date/Time function output into HH:MM:SS

Spazure Silicon blogged this tip on his/her blog with the caveat that "So, even though this formula won't ever be on the CRM Best Practices Custom Formula blog, I hope somebody finds it, and finds it useful." So, mainly to prove him/her wrong, but also because it's very useful, here it is - a function to convert Date/Time into HH:MM:SS.

This particular formula was designed for cases, calculating the elapsed time in HH:MM:SS between right now and the time the case was created. As you see, I had to use the TEXT() function to convert the numerically output Date/Time function results into text, which were then fed into the Concatenate function to make everything display nicely on one field. My original version of this formula actually used multiple fields, {!Hours} {!Seconds} {!Minutes}. This made the final formula look prettier, and helped with debugging, but ultimately was impractical for distribution.

So, even though this formula won't ever be on the CRM Best Practices Custom Formula blog, I hope somebody finds it, and finds it useful. If you use my formula, please comment -- not because I want credit, but because I really do like to know where my code ends up. :-)

Text(FLOOR((NOW() - {!CreatedDate}) * 24))

&":"&Text(FLOOR(((NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24))* 60))

&":"&Text(FLOOR((( (NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24 )) * 60 - FLOOR(((NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24 )) * 60 )) * 60 ))

Converting Date/Time function output into HH:MM:SS

Spazure Silicon blogged this tip on his/her blog with the caveat that "So, even though this formula won't ever be on the CRM Best Practices Custom Formula blog, I hope somebody finds it, and finds it useful." So, mainly to prove him/her wrong, but also because it's very useful, here it is - a function to convert Date/Time into HH:MM:SS.

This particular formula was designed for cases, calculating the elapsed time in HH:MM:SS between right now and the time the case was created. As you see, I had to use the TEXT() function to convert the numerically output Date/Time function results into text, which were then fed into the Concatenate function to make everything display nicely on one field. My original version of this formula actually used multiple fields, {!Hours} {!Seconds} {!Minutes}. This made the final formula look prettier, and helped with debugging, but ultimately was impractical for distribution.

So, even though this formula won't ever be on the CRM Best Practices Custom Formula blog, I hope somebody finds it, and finds it useful. If you use my formula, please comment -- not because I want credit, but because I really do like to know where my code ends up. :-)

Text(FLOOR((NOW() - {!CreatedDate}) * 24))

&":"&Text(FLOOR(((NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24))* 60))

&":"&Text(FLOOR((( (NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24 )) * 60 - FLOOR(((NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24 )) * 60 )) * 60 ))

Creating Fiscal Period Formula Fields in a Custom Fiscal Year Org

If your business’s fiscal year is not based on the Gregorian calendar but follows a different structure consisting of fiscal weeks, periods and quarters deviating from the regular calendar year, you can set up your fiscal year structure using the Custom Fiscal Year (CFY) feature in Salesforce.

For example, your company may run on a “4-4-5” structure and a 13-week quarter may be represented by three periods of four, four, and five weeks rather than by three calendar months. Or may be your fiscal years consist of 13 fiscal periods consisting of 4 weeks each.

As specified on the CFY enablement page in setup as well as in online help and release notes, a side effect of enabling the CFY feature for an organization is that you will not be able to use the fiscal period columns (Fiscal Year, Fiscal Quarter, and Fiscal Period) in opportunity, opportunity with product, opportunity with schedule reports or opportunity list views otherwise available in non-CFY organizations.

Here is the tip: Using the formula field capability in Salesforce, you can create custom fields on the opportunity object that can calculate the fiscal year, fiscal quarter, fiscal period or a combination of these for you. These custom fields can then be exposed in all opportunity reports and list views and you can also filter by values in those fields. If you were using the fiscal period columns in opportunity reports prior to enabling CFY, you can recreate the columns via this formula field approach to retain reporting functionality that you may have been used to.

Creating Fiscal Period Formula Fields in a Custom Fiscal Year Org

If your business’s fiscal year is not based on the Gregorian calendar but follows a different structure consisting of fiscal weeks, periods and quarters deviating from the regular calendar year, you can set up your fiscal year structure using the Custom Fiscal Year (CFY) feature in Salesforce.

For example, your company may run on a “4-4-5” structure and a 13-week quarter may be represented by three periods of four, four, and five weeks rather than by three calendar months. Or may be your fiscal years consist of 13 fiscal periods consisting of 4 weeks each.

As specified on the CFY enablement page in setup as well as in online help and release notes, a side effect of enabling the CFY feature for an organization is that you will not be able to use the fiscal period columns (Fiscal Year, Fiscal Quarter, and Fiscal Period) in opportunity, opportunity with product, opportunity with schedule reports or opportunity list views otherwise available in non-CFY organizations.

Here is the tip: Using the formula field capability in Salesforce, you can create custom fields on the opportunity object that can calculate the fiscal year, fiscal quarter, fiscal period or a combination of these for you. These custom fields can then be exposed in all opportunity reports and list views and you can also filter by values in those fields. If you were using the fiscal period columns in opportunity reports prior to enabling CFY, you can recreate the columns via this formula field approach to retain reporting functionality that you may have been used to.

Formula Tip: Using Picklist fields with the CASE() function

Case_function_1 Those of you experienced formula authors out there have probably groaned more than a few times when building advanced conditional logic based on a picklist field value. In the previous release of custom formulas, you had to build nested IF() functions with embedded ISPICKVAL() functions in order to accomplish this feat.

Well, there’s good news for you in the Winter ’06 release: You can now use picklist fields directly with the CASE() function. No ISPICKVAL() required. This has a number of big advantages:

The formula is easier to create and read
Using CASE can result in a significantly smaller compiled size, which is important for complex formulas that are approaching the 4K max compiled size limit.

Converting your existing picklist formulas to use the CASE function is easy. Here’s an example formula based on the Opportunity Stage picklist to use as a model:

Formula Tip: Using Picklist fields with the CASE() function

Case_function_1 Those of you experienced formula authors out there have probably groaned more than a few times when building advanced conditional logic based on a picklist field value. In the previous release of custom formulas, you had to build nested IF() functions with embedded ISPICKVAL() functions in order to accomplish this feat.

Well, there’s good news for you in the Winter ’06 release: You can now use picklist fields directly with the CASE() function. No ISPICKVAL() required. This has a number of big advantages:

The formula is easier to create and read
Using CASE can result in a significantly smaller compiled size, which is important for complex formulas that are approaching the 4K max compiled size limit.

Converting your existing picklist formulas to use the CASE function is easy. Here’s an example formula based on the Opportunity Stage picklist to use as a model:

Pre-Built Image Fields Available on the AppExchange

To help people experience the power of image fields, I created a very simple AppExchange component which allows you to quickly install nine working examples ranging from priority flags, to product photos, to stock charts.

This free component takes less than two minutes to install and it’s built on a stand-alone custom tab so you can add it to your account without affecting any of your users. One thing to note, when you install this component, you'll have to fill in the blanks (score, ticker, image url) for some of the images to appear.

The idea is that once you have these sample formulas in your account, you can reapply the them to any of your other objects. Give it a try!

Pre-Built Image Fields Available on the AppExchange

To help people experience the power of image fields, I created a very simple AppExchange component which allows you to quickly install nine working examples ranging from priority flags, to product photos, to stock charts.

This free component takes less than two minutes to install and it’s built on a stand-alone custom tab so you can add it to your account without affecting any of your users. One thing to note, when you install this component, you'll have to fill in the blanks (score, ticker, image url) for some of the images to appear.

The idea is that once you have these sample formulas in your account, you can reapply the them to any of your other objects. Give it a try!