Chatter Hashtag report

On top of Chatter activity statistics and influence rank report in earlier blog (introduce in Winter 13 release). We also can run a report on usage of public hashtag (#) topics. Public hashtag topics are those used on profiles and in public groups. For privacy reasons, hashtag topic usage reports DO NOT include hashtags used on records or in private groups.

To create this report, you need to create new Custom Report and choose the Hashtag Definitions as primary object. You can select from the following fields:
• Created Date — Date the hashtag topic was created
• Hashtag Text — Text following the hashtag (#)
• Hashtag Count — Number of times the hashtag is used
• Normalized Hashtag Text — Text following the hashtag (#), without capitalization and punctuation

While for Chatter Activity report as in earlier blog, you can find following fields:
• Comment Count — Number of comments made by the parent
• Comment Received Count — Number of comments received by the parent
• Influence Raw Rank — Number indicating the parent’s influence rank, which is calculated based on the parent’s Chatteractivity statistics, relative to the other users in the organization
• Like Received Count — Number of likes received by the parent
• Parent — User name
• Post Count — Number of posts made by the parent

Chatter Usage and Activity Report

For organisation using Chatter, you may need and interested to know how Chatter usage in your company.

1. Chatter Usage Dashboards
This is free app from AppExchange, you can install from this link
From this dashboard you can see:
- top 10 most popular users
- top 10 most popular groups
- number of chatter groups (private vs public)
- total User feed & group feed
- top users post to group
- etc

2. Chatter Activity and Influence report
Create custom report type
Click on Setup - Create - Report Types - New Custom Report Type and choose "Chatter Activity" object. Give it a name "Chatter Activity and Influence" and store it in the folder of your choice. You can then go to the Reports Tab and find the report type you just created and click on Create.

Salesforce "Email Privileges Revoked" error message

When you write apex code in Salesforce to send email in Sandbox instance, you may find error "Email Privileges Revoked" error message.

You may never see this error earlier. What is the caused?

As per Spring'13, Salesforce have added a way for you to control the type of email your organization sends, with the new Access level setting in Setup > Administration Setup > Email Administration > Deliverability. 

While you typically won’t change this setting day-to-day, these options can be useful when you need to temporarily suspend outbound email or when you’re working with sandboxes, for example:

• No access: Prevents all outbound email to and from users.
• System email only: Allows only automatically generated emails, such as new user and password reset emails.
• All email: Allows all types of outbound email. Default for new, non-sandbox organizations.

Tip: The System email only setting is especially useful for controlling email sent from sandboxes so that testing and development work doesn’t send test emails to your users.

• Newly created sandboxes default to System email only.
• Sandboxes created before ’Spring 13 default to All email.

You may not be able to edit the Access level if salesforce.com has restricted your organization’s ability to change this setting.

Summary: by change access level to "All email", it should solve the issue in Sandbox.

Improve Salesforce report performance

Salesforce.com report is a simple, yet very powerful feature. Out-of-the-box, it come with multiple type of reports, and admin can create more custom report type as needed. With report builder introduce few years back, it even easier to use and more user friendly.

But, sometimes you may found that report take sometimes to load, or time-out (although this is very rare happened).

What is the caused?
1. Too many columns in report
Just add columns as needed. More columns to display will take more time to run report. You also can hide details, when it needed, just click "Show Details" button.

2. Too many records
Set report filter criteria correctly and efficiently, you can add multiple criterias and time frame.

3. Inefficient filters
Contain and Does Not Contain take longer time to load report.
Report using AND will load faster than OR.

If you have tune everything above and it still slow, you can schedule report for future run and email it to you daily or weekly.

For more information, watch this YouTube video.

Salesforce: Calculate age for weekdays and weekends

In Salesforce, it is easy to calculate the number of days between 2 dates. Just create a formula field, for example: End_Date__c - Start_Date__c for date fields, or  Datevalue(End_Date__c) - Datevalue(Start_Date__c) for datetime fields and DONE!!!

But, is it possible to find out the number of days - only weekdays and only weekends between 2 dates?
Hmmm.... most of us will think about Apex Trigger. Yes, it is the correct solution, apex trigger is able to calculate without issue, but if you are not a developer, you need a developer for this.

Wait a minute.... Can we 'just' use a formula field to calculate weekdays and weekends?

YES, it is possible with complex formulas. Here we go:

To calculate the number of  Weekday

IF ((CASE(MOD( Request_Date__c - DATE(1900,1,1),7),

0, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),

1, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),

2, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),

3, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),

4, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),

5, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),

6, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),

999)

+ (FLOOR(( Execution_Date__c - Request_Date__c )/7)*5)

- 1) < 0,

0,

CASE(MOD( Request_Date__c - DATE(1900,1,1),7),

0, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),

1, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),

2, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),

3, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),

4, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),

5, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),

6, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),

999)

+ (FLOOR(( Execution_Date__c - Request_Date__c )/7)*5)

- 1)

To calculate number of  Weekend

CASE(MOD( Request_Date__c - DATE(1900,1,1),7),

0, CASE( MOD( Execution_Date__c - Request_Date__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,0,3,1,2),
3, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,1,2),
4, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,1,2),
5, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,1,2),
6, CASE( MOD( Execution_Date__c - Request_Date__c, 7),6,2,1),
999)
+ (FLOOR(( Execution_Date__c - Request_Date__c )/7)*2)

Example:
Request Date = 1 Jan 2014
Execution Date = 10 Jan 2014
Total days on weekdays = 7
Total days on the weekend = 2

Request Date = 1 Mar 2014
Execution Date = 10 Mar 2014
Total days on weekdays = 5
Total days on the weekend = 4

If you see in the formula above, we have DATE(1900,1,1); this is referred to as 1-Jan-1900 is Monday. So, you can use any date which is Monday, for example, 1-Jan-2007

The rest is just the MOD() function, so have fun...


Another request is to calculate a date after X days from today (business day means weekday from Mon - Fri). Here is the formula; the result will be a formula in Date format, while Days__c is a number.

CASE( 
MOD(TODAY()- DATE(1900,1,7),7), 
0, (TODAY()) + Days__c+ FLOOR((Days__c-1)/5)*2, 
1, (TODAY()) + Days__c+ FLOOR((Days__c)/5)*2, 
2, (TODAY()) + Days__c+ FLOOR((Days__c+1)/5)*2, 
3, (TODAY()) + Days__c+ FLOOR((Days__c+2)/5)*2, 
4, (TODAY()) + Days__c+ FLOOR((Days__c+3)/5)*2, 
5, (TODAY()) + Days__c+ CEILING((Days__c)/5)*2, 
6, (TODAY()) - IF(Days__c > 0,1,0) + Days__c + CEILING((Days__c)/5)*2, NULL)

Result sample: today is 3 Oct 2016, and Days are 5, so the result would be 10 Oct 2016.

How to restrict sharing access in Salesforce?

We often heard that business would like to restrict x number of users to access Account, while open it to rest of users. How we can do that in Salesforce?

However in Salesforce, it is the other way round. You need to specify who can access an object, NOT about who cannot access an object.

If your company decide not to give access to anyone in company (to view or edit an object), Organization-Wide Defaults (OWD) should be set to Private, then add sharing rule for that object based on: Criterias, Public Groups, Roles or  'Roles and Subordinates'.

If OWD is set to Private or Public Read-Only. It will be shared in following rule:

• Role Hierarchy, any user in higher role hierarchy of record owner will be able to access the record
• Sharing Rule, any user and user in higher role hierarchy of users being shared in Sharing Rule will be able to access the record
• Manual Sharing, owner or user in higher role hierarchy of record owner, will be able to share records owned.

To investigate who can access a record, system admin can click on 'Sharing' button in record detail. Then, click 'Expand List' to get more details on user being shared and why they being shared.

Salesforce: Campaign field not show in Lead page layout

A user come to me and ask, "Why the Campaign field in Lead page disappear after create a new lead ?"

Hmmm... as system admin, what will you check?

• Make sure Campaign field is added in Lead page layout - checked
• Check Field-Level Security for particluar user profile - checked

User still do not see Campaign field in Lead after it saved or when user edit a Lead, but it is visible when user create new Lead. After some finding, it is designed by default from Salesforce.

Here is the explanation:
1. The Campaign lookup field on the lead page only available when user create new Lead.

2. It will disappears after a new lead is created, because the purpose of this field is to make it easier for the user when creating a new lead and to associate it to an existing campaign.

3. If a campaign is associated from this lookup field on Lead creation process, it will be shown in Campaign History related list. User can add more campaigns from Campaign History related list in Lead Page layout.

If we look to the data structure, Lead Id is a field in CampaignMember object, Setup | Customize | Campaigns | Campaign Members | Fields

When a Lead is converted, the most recently associated campaign is brought over to the Opportunity Campaign Source field upon conversion.


Reference: Why does the Campaign lookup field on the lead page disappear after a new lead is created?

Salesforce Global Variable

In previous blog, we talk about Custom Label, where System Administrator able to set values in Custom Label, it is accessible via formula field using $Label. $Label is one of Global Variable in Salesforce, you can use global variable to reference general information about the current user or your organization. Global variable can be easily identified with prefix $.

Components such as custom buttons, custom links, formulas, and Visualforce pages allow you to use global variables with special merge fields to reference the data in your organization. Please note, global variables accessible in formula field, validation rule and custom link / button are different. See screenshot below:

in formula field

in validation rule

in custom button / link

Here a few often used Global Variable merge field for validation rule or formula field:

$Profile -- A global merge field type to use when referencing information about the current user’s profile. Use profile merge fields to reference information about the user’s profile such as license type or name.

$User -- A global merge field type to use when referencing information about the current user. User merge fields can reference information about the user such as alias, title, and ID.

$RecordType -- A global merge field to use when referencing the record type of the current record.

Although this merge field is not visible in screenshot above, but it will work. You can use following merge fields: $RecordType.Id, $RecordType.Name

$Organization -- A global merge field type to use when referencing information about your company profile. Use organization merge fields to reference your organization’s city, fax, ID, or other details. If you use this merge field with $Organization.Id and reference to your production instance, when you create/refresh sandbox, Id value in the validation rule will change to new sandbox Org Id.

Some use cases using Global Variable:

1. To ensure that only the record owner or users with profile System Administrator can make changes to Opportunity Amount.

Create validation rule in Opportunity with following Error Condition Formula:

AND( ISCHANGED( Amount ), Owner <> $User.Id, $Profile.Name <> "System Administrator" ) 

2. Not allow users with profile System Administrator or users with first name = Steve to create or modify Account

Create validation rule in Account with following Error Condition Formula:

OR($User.FirstName = 'Steve', $Profile.Name = 'System Administrator')

3. To view all Account last modified by me (assume Account sharing Organization-Wide Defaults is Public)
Create a checkbox formula field "Last Modified by Me" in Account with formula:
$User.Id = LastModifiedById

"Last Modified by Me" value will be dynamic based on current login user. Then, create a List View with filter "Last Modified by Me" = True (the same can be applied for Report).

You can share this view to all users or certain groups of users without each user need to create their own view for the same purpose.

This kind of simple formula can be used for Created By Me or any user lookup custom field in any object.


Reference:

• Understanding Global Variables
• Global Variables

Salesforce Custom Label

Custom Label maybe not so popular among Salesforce administrator compare to other feature, such as: workflow, formula field and so on, even less popular compare to Custom Setting. Custom Label also enable admins or developers to show data/value in multi-language.

Custom Label may be more popular for developers to store a variable, but Custom Label actually also available for system administrator to be used in formula field, workflow rule, and validation rule. For a formula field that refer to custom label, value of the formula field will be depend on value from custom label referred, when display in page layout or report, it will be displayed based on language selected in user detail.

To create Custom Label, click Setup | Create | Custom Labels. You only can add translation language based on languages supported in your Translation Workbench.

Once created, you can find Custom Label in Global Variable start with $Label

Once added in formula field or other places, it will show with format $Label.Label_Name

Custom Label enable developers to create multilingual applications by automatically presenting information in a user's native language from Apex class or Visualforce page.

For Apex class, it would be just a simple as: String strMyLabel = Label.Hello;


Note: if you want to set Custom Label in multi-language, you need to enable Translation Workbench. To enable Translation Workbench from Setup | Translation Workbench | Translation Settings, then add and activate languages that you need.


Reference: Custom Labels

Delete Campaign in Salesforce

Background: a user come to me that she does not see Delete button, she would like to delete a campaign created by mistake.

As usual, I check user profile and page layout:
- User Profile have Delete permission on Campaign object - checked
- Page Layout have Delete button - checked
So, what is the caused ...?

Finding.... Campaign is not the same with other objects. You need to enable Marketing User permission selected in the user detail. Once enabled and both items above checked, the user should see 'Delete' button for Campaign, the same for 'New' button to create Campaign.

From Setup | Manage Users | User | look for user | Edit | enable Marketing User | Save

Marketing User permission in user detail allows a user to manage Campaigns, users with enabled Marketing User checkbox has the right to create and manage campaigns, including:

• Create a new campaign
• Edit an existing campaign
• Delete an existing campaign
• Update campaign statistics
• Import leads into a campaign*
• Mass update the status for members of a campaign*
• Configure advanced campaign setup to define member status values
• Change the campaign member type
• Run campaign reports across multiple campaigns
• Add Contact and Lead to Campaign from a report using Add to Campaign

* In order to use the campaign import wizards, the user must also have the Marketing User profile (or have "Import Leads" permission).

Create permission for Campaign object will enable:
- New button in Recent item and List Views
- Clone button in Campaign Page Layout

Edit permission for Campaign object will enable:
- Edit Campaign link in Recent item
- Edit button in Campaign Page Layout
- Manage Members (with: Add Members - Search & Edit Members - Search option) in Campaign Page Layout

Reference:

• Who can import campaign members?
• User Fields