Salesforce: Change Account Owner

Do you realize when account owner is changed using Salesforce.com UI, the new owner will also gain ownership of:

• All Notes and open Activities for the Account (completed Activities will not be transferred)
• All Contacts tagged to the Account owned by old owner
• All open Opportunities tagged to the Account, including closed Opportunities if you select the Transfer closed opportunities, and Opportunities owned by other owner if you select Transfer open opportunities not owned by the existing account owner, see screenshot below:

Above scenario will also correct for mass transfer from Setup | Data Management | Mass Transfer Records | Transfer Accounts.

But, if  you change Account owner through API, or API tool such as: Data Loader, only the Account Owner will be change, it will not change owner of Contact, Opportunity and other objects related.


Reference:

• How do I change Account Ownership using the Data Loader?
• How to Change the Record owner using the Data Loader?
• Mass Transfer Records

Last Update: 17 May 2016

Salesforce: Formula field best practice

Formula field in Salesforce is great feature for user, it simplify complex filter conditions for SOQL queries, list views, and reports.

Let's say we have CloseDateAge formula field, we can use it in report filter CloseDateAge range = Current FY.

Or in SOQL
SELECT Id, Name
  FROM Opportunity
 WHERE CloseDateAge__c >  14
   AND CloseDateAge__c <= 21

Everyone is happy because they can get the data they need with very little effort, right? Wait a minute.

Indexes
By default, formula fields don’t have indexes. So when you create and use a formula field such as CloseDateAge with very large objects (say, objects that have more than one million of records), the SOQL queries and reports using the formula field as a filter might perform slower because your queries and reports have to perform full scans to find target records.

You can workaround by create trigger or workflow to update a field. It's work, but it requires overhead and is not intuitive.

Since the Winter ’13 release, you have been able to contact salesforce.com Customer Support to create a custom index on a formula field, provided that the function that defines the formula field is deterministic. But, Salesforce cannot index formula fields that:

• Reference other entities (i.e., fields accessible through lookup fields)
• Include other formula fields that span over other entities
• Use dynamic date and time functions (e.g., TODAY, NOW)

A formula is also considered non-deterministic when it includes:

• Owner, autonumber, divisions, or audit fields (except for CreatedDate and CreatedByID fields)
• References to fields that Force.com cannot index:

• Multi-select picklists

• Currency fields in a multicurrency organization

• Long text area fields

• Binary fields (blob, file, or encrypted text)

• Standard fields with special functionalities:

• Opportunity: Amount, TotalOpportunity, Quantity, ExpectedRevenue, IsClosed, IsWon

• Case: ClosedDate, IsClosed

• Product: Product Family, IsActive, IsArchived

• Solution: Status

•    Lead: Status

•    Activity: Subject, TaskStatus, TaskPriority

The CloseDateAge field above is a perfect example of a non-deterministic formula field that Force.com cannot index. So what can you do when you can’t create an indexed formula field to hide complex filter logic? Go back to square one: Put your filter logic in your filters!

So, in the SOQL above, change it to
SELECT Id, Name
  FROM Opportunity
 WHERE ((CloseDate = LAST_N_DAYS:21)
   AND  (CloseDate < LAST_N_DAYS:14))

Tips on Analytic Snapshots

click image for higher resolution image

Salesforce: Activities Archived

One day, a user comes to me, "Why I do not see some of my activities in the report, although they are visible in the page layout related list from an object"?

This is because Salesforce archive activities (tasks and events) that are over a year old, for activity with criteria:

• Events with an ActivityDateTime or ActivityDate value greater than or equal to 365 days old
• Tasks with an IsClosed value of true and an ActivityDate value greater than or equal to 365 days old
• Tasks with an IsClosed value of true, a blank ActivityDate field, and created date is greater than or equal to 365 days ago

If you are using Data Loader, you need to select Export All instead of Export, and if using other API tools, make sure it calls queryAll() instead of query() function.

You also can filter on the isArchived field to find only the archived records. You can update or delete archived records, though you cannot update the isArchived field. If you use the API to insert activities that meet the criteria listed below, the activities will be archived during the next run of the archival background process.

Even the activities are archived, users will still see the activities in the Lightning page (under Activities component), Salesforce does not delete the records, so you can open the activity if you have the Activity Id. 

The activities archived will no longer show in the Salesforce report and standard API call.

If you are using Einstein Analytics dataflow, archived records will not be retrieved from the sfdcDigest node, feel free to vote this idea.

Last updated: 7-Jan-2021

Reference:

• Work with Archived Activities
• Archived Activities
• Increasing Archive Days for your Organization
• Export or delete archived activities through API

Salesforce: Account Team Member and Account Share

Account Team Member and Account Share only available in Enterprise and Unlimited edition (included Developer edition). An account team is a team of users that work together on an account. You can add a user into Account Team manually from Account Team related list in the Account Page Layout.

After click Add button, you can specify Account Access, Contact Access, Opportunity Access, Case Access, and Team Role. If your default sharing in OWD is Private for that object, you will see Private, Read-Only, and Read/Write options in the picklist. You can add up to 5 users every time you add team members manually. 

But, the manual process is not fun when you need to add users into hundreds of account team members. So, here is the solution, use Data Loader to mass create AccountTeamMember records and AccountShare records.

1. Account Team Member

The API name is AccountTeamMember, you need to populate the following fields when mass inserts into AccountTeamMember: 

- AccountId

- UserId

- TeamMemberRole

Note: creating this record will not allow users added into AccountTeamMember to access Contact, Opportunity, Case if the sharing setting is Private. TeamMemberRole value can be different from values defined in Team Role. 

Purpose: it will show user added to Account Team related list.

2. Account Share

The API name is AccountShare, you need to populate following fields when mass insert into AccountShare: 

- AccountId

- UserOrGroupId

- AccountAccessLevel

- CaseAccessLevel [optional]

- ContactAccessLevel [optional]

- OpportunityAccessLevel [optional]

You can set Private, Read-Only, and Read/Write in the above fields optional if default sharing in OWD is set to Private.

Note: user added into Account Share will access Case, Contact, and Opportunity records with  Read-Only or Read/Write permission, even if it is not shown in Account Team related list.

Here is more information on mass create manual sharing rules with the Data Loader, click here.

Mass upload/insert non-English characters to Salesforce

We can mass upload / insert data to Salesforce easily using Data Loader or other API tool. But, if we get the data in Excel file with non-English characters, non-English characters will be come ??, This  is happened if we save the CSV file directly from Ms Excel.

To upload in non-English characters correctly, the file should be in UTF-8. Here step by step needs to be done, to avoid Ms Excel limitation in process non-English characters.

[ MS Excel ]
1. Open the *.xlsx file from MS Excel
2. Click File | Save As
3. Give a file name and choose "Unicode Text (*.txt)" for "Save as type :" and click Save

[ MS Notepad ]
4. Open the unicode text file using Microsoft Notepad. Some characters can appear as a box. This is because Notepad cannot display some unicode characters, but you can ignore it at this point.
5. It must be the tab delimitered file. Replace tabs with comma (,)
- Select tab character by dragging the character between two column headers and press Ctrl+C
- Replace all tab characters with comma using Replace function (Ctrl+H)
6. In Notepad, click Save As
7. Give a file name and change Encoding: to "UTF-8"
8. Click Save.

[ Windows File Explorer]
9. Change the file extension from "*.txt" to "*.csv". The file icon should be changed to MS Excel now. If it is not changed, check Windows Folder options. (Windows Start menu > Control Panel > Folder Options > View tab > uncheck "Hide extensions for known file types"). This will show the file extensions. Try changing the file extension again.

[ MS Excel ]
10. Open this CSV file from Excel and verify if the data is okay.
*** If you find any problem in this step, you should NOT save the file from Excel. It will cause the encoding problem!! Just check the data only in Excel. If you see a data problem at this stage, fix the data from the original spreadsheet and repeat the steps again. Or, you can change it from Notepad but should ensure that the file is saved as UTF-8 format.

You can download sample csv file with non-English characters saved in UTF-8 encoding here.

Salesforce Time-Dependent Workflow and Entry Criteria

Time-Dependent Workflow is a very nice feature out-of-the-box from Salesforce. You can easily configure it to do action such as: create task, send email alert, field update and send outbound message. The actions will only trigger when before or after certain date or hour as configured.

Actions for Time-Dependent Workflow is exactly the same with Immediate Workflow, and you can share the same actions between Time-Dependent Workflow with Immediate Workflow, depend on your business needs, even to combine Time-Dependent and Immediate workflow in the same workflow rule.

One thing you need to note when create Time-Dependent Workflow is the Evaluation Criteria, make sure it is set to created or created, and any time it’s edited to subsequently meet criteria only. Created, and every time it’s edited will NOT allow you to have Time-Dependent Actions.

I get a request from my user to change a running Time-Dependent Workflow from 7 days before Birth date to 14 days before Birth date. Here is the step:

1. Take a screenshot of existing Time-Dependent Workflow with the actions (for backup purpose)
2. Deactivate the workflow
3. Delete all Time-Based Workflow queue related to the workflow, from Setup - Monitor - Time-Based Workflow, you can take screenshots as backup purpose.
4. Edit Time-Dependent Workflow, once step 3 done, you will see Edit link next to the time trigger. For this sample, let's modify the day to 14 days.
5. Activate the workflow rule

Notes:
1. Deactivate workflow rule (step 2 above)
This activity will NOT delete time-dependent actions are placed in the workflow queue.

2. All existing data will NOT trigger again for the Workflow
Because it already met the entry criteria earlier. So, if your Entry Criteria is 'Evaluate the rule when a record is created, and any time it’s edited to subsequently meet criteria', you need to make the record to 'meet' the criteria again:

• Update the record NOT to meet the criteria, then update it back to meet the criteria.
• Example: if we are using Rule Criteria "Birth date NOT EQUAL TO null", we need to make it to null (make sure to backup), then populate the original Birth date back, so it will meet the criteria again.

3. Monitoring
You can monitor record in queue to be triggered for Time-Based Workflow from Setup - Monitor - Time-Based Workflow. You can select some criteria to monitor the queue or just leave all to --None-- and hit Search button.


Limitation and Idea:
The time based workflow queue is not queryable via Apex, or the API or run a report.
Vote for this idea to have ability to export Queue of Time Based Workflow.

How to report Chatter records following by users?

As of the Salesforce Spring '13 release, the maximum number of people and records a user can follow in Chatter is 500. Once a user hits 500, the user is unable to follow any user or record; if you need more than 500 limits, you can log a case to Salesforce support to get this number increased to 2000.

Question, how do you check the number of people + records that have been followed? Easy answer, click on the user profile, and it will show in the right panel of count and items "Following". Then, you can click "Show All (number)" to see all records and people that user are following.

But, is there a way to list all the following items for all users? We cannot find this in the Chatter report. If you are familiar with SOQL or Data Loader, this is possible...

The object that stores all 'following' is EntitySubscription.
EntitySubscription represents a subscription for a user following a record or another user. A user can subscribe to a record or to another user. EntitySubscription is available in API version 18.0 and later.

Here is the simple SOQL:
Select e.Id, e.SubscriberId, e.ParentId From EntitySubscription e order by e.SubscriberId
SubscriberId here is UserId, and ParentId is any object or user following.

If you do not want to auto-follow the records you created (so you do not easily hit max 500), you can configure it from Setup - Personal Setup - My Chatter Settings - My Feeds. Uncheck Automatically follow records that I create.

There are also applications in AppExchange you can install to auto-unfollow Chatter items based on a set of rules; this app was created by Salesforce Lab:
- Cloud Swarm 3 - Chatter Auto Follow - Unmanaged - EE / UE / DE
- Chatter Unfollow Rules - Managed


Reference:

• Increase limit on the number of records or Users that you can 'Follow' in Chatter

Salesforce Report for SAML Federation Id for SSO

For organization implementing SSO for Salesforce.com login authentication, if you get a ticket from user query that they cannot login to Salesforce via SSO. Here steps for you need to check:

1. Make sure you are in office network (including VPN) and try to login yourself. If not working, check with your peers of SSO expert, maybe server down or some other issue.
2. If you can login with SSO, check the user detail and make sure Federation Id is enter correctly.

It is a good practice for admin to run a report to list down all Active users with their Federation Id information. But.... we cannot find Federation Id field in available field using standard Users report type.

So how? You can create custom report type, here we go:

1. Create Custom Report Type
From Setup - Build - Create - Report Types

• Click New Custom Report Type button
• Select Users as Primary Object
• Enter Label, Description and Store in Category (preferred Administrative Reports)
• Select deployment status
• Click Next button and Save button

To confirm, click "Edit Layout" button in Fields Available for Reports, you should see "SAML Federation Id" is there.

2. Create Report
Create normal report and select report type created in step 1 from correct report folders.
You should be able to see SAML Federation Id in available field fro report.

Cool..... ?

How to create report in Salesforce with double quote in filter

A user come to me today asking how to create a report on Account with Account Name contain " (double quote) ?
Hmmm... it seems easily, just type " in report filter criteria using CONTAIN. But, it will not work, since " is consider as string function.

Checking to Salesforce community? @SteveMo response me with excellent answer. Type ' + " + ' (read: without + sign), instead of using " or \" (escape sequence in SOQL).

But I realize, after the report save and open the report again, the value for filter '"' is gone.

Not sure if this Salesforce bug? But, I believe this is something Salesforce need to look into.

Query Chatter comment

In my previous blog, I give overview on Chatter Feed, how to query it, and how it is structured within Salesforce.

In this blog, I would like to give overview on Chatter Comment. Using any API tool, we can query and extract all feed comment into csv file. The API object name is FeedComment (record prefix with 0D7).

Sample query
Select f.Id, f.FeedItemId, f.ParentId, f.CommentType, f.CommentBody, f.RelatedRecordId, f.CreatedDate From FeedComment f

FeedItemId is Chatter feed that comment tag to, and ParentId show the comment is tag to what object, it can go to Opportunity, User, Chatter Group and etc.

As of API version 23.0 and beyond, if you have “View All Data” permission you can query FeedComment records directly without an ID filter. Using API version before 23, you will get this error:

More information on chatter comment here.

Pretty simple...?

Query Chatter Feed Item & How the structure in Salesforce object

Salesforce admin can enable Chatter feed tracking on Salesforce objects, including custom objects.
Go to Setup and search for Feed Tracking in the Quick Find search box. Select the object to track and tick "Enable Feed Tracking", you can select up to 20 fields.

In the background, once an object is enabled for feed tracking, Salesforce will create a new object with the suffix Feed (for Standard object) or _Feed (for Custom object). This is available in API version 18.0 and later.

Sample query:

SELECT Id, ParentId, Type, RelatedRecordId, Title, Body, CreatedById, CreatedDate, CommentCount, LikeCount FROM AccountFeed ORDER BY CreatedDate DESC

This query will return all feed tagged to Account (see ParentId field)

Another object storing all feeds is FeedItem, this object is available in API version 21.0 and later, sample query
SELECT Id, ParentId, Type, RelatedRecordId, Title, Body, CommentCount, LikeCount FROM FeedItem ORDER BY ParentId

This query will return all feed tags to any objects.

See the architecture below (right-click the image to enlarge):

Notice that objectFeed object shared the same prefix with FeedItem: 0D5

If you need the screenshot in Visio file, click here

Salesforce: Trending with Reporting Snapshot

Can we have a report of data trending in Salesforce? Hmmmm, Salesforce only report current data? But, is there a way to store historical data so we can use it for reporting?

Yes, if you are using Salesforce Enterprise or Unlimited edition, you can configure Reporting Snapshot to store historical data, but do not expect old historical data will be available before configured reporting snapshot. Only historical data for specific reporting snapshot will be available after configured and run properly.

Users with "Manage Reporting Snapshots" permission able to create and configure reporting snapshot. Each edition have a limited number of reporting snapshot can be created, so used it wisely.

Here is the summary to create and configured reporting snapshot:

1. Create a source report, this report type should be only tabular or summary.
Please note, the number of records will be created in target object will be the same with how many records in report result. For a tabular report, it will be one to one, and for a summary report, it will be by group summary or grand summary. If you choose grand summary, it will always create only 1 record in a target object.

2. Create a target object, this would be a custom object in Salesforce. Make sure the target object must not be included in a WORKFLOW.

3. Create fields in the target object, by default reporting snapshot will give us 3 information that we can capture into fields in target object: Reporting Snapshot Name, Reporting Snapshot Running User, Execution Time. Try to create field name similar or represent data will be created from source report.

4. Create the reporting snapshot, from Setup - Data Management - Reporting Snapshots. You need to enter: Reporting Snapshot Name (Job name), Running User, Source Report, Target Object, and Description (optional).

5. Map Fields from Source Report to Target Object, in this step, if your source report type is summary, you need to select grouping level. Applicable source report field will be available for each target object, it is based on the field type.

6. Create Schedule, you can set to email reporting snapshot to yourself, other user or to a group, and select frequency on daily, weekly and monthly basis. Available start time will be shown on this screen.

After the reporting snapshot has been run for a few times, you can build a report to show the trend.


Monitoring
Lastly, you can monitor ALL Reporting Snapshot in your Salesforce instance from Setup - Monitor - Scheduled Jobs. Create a new View and filter with Type = Reporting Snapshots, this will show the Job Name, Submitted By, Started, and Next Scheduled Run. If the Next Scheduled Run is blank, this means that 'Reporting Snapshot' is no longer active.

In the past Reporting Snapshot is known as Analytics Snapshot.

Blog last updated: 2 Dec 2018


Reference: Prepare Reporting Snapshots

How to list all Dynamic Dashboards in Salesforce?

Dynamic dashboard is great to enable each user to see the data they only have access to or their team have access to, without the need to create separate dashboards using a fixed user "viewing as".

But, each organization only has up to 5 dynamic dashboards for Enterprise Edition and 10 dynamic dashboards for Unlimited Edition, additional dynamic dashboards available for purchase separately.

So, it important for the system admin to know all dashboards configured as a dynamic dashboard. Let us go through how to list down all dashboards configured as a dynamic dashboard.

Here we go:
1. Create a custom report type with the primary object = Dashboards
2. Create a new report using the report type created in Step 1
3. Add filter of "Dashboard Running User" NOT EQUALS TO "Run as specified user" (optional)
4. Run the report. You will see it will only show Dashboard Running User :

• Let authorized users change running user for dashboard with Let authorized users change running user selected, and
• Run as logged-in user

Have fun...



Reference: 

• Find the Dynamic Dashboards in my Organization

Salesforce Sharing Button

Manual sharing record in Salesforce is easy, user just click Sharing button to share a record to Public Groups, Roles, Roles and Subordinates and Users, only when 'Organization-Wide Defaults' sharing is set to Private or Public Read Only for the object.

By clicking Sharing button, you’ll see who can access that record and why (sharing rule, group, role hierarchy, manual share etc). The Sharing button allows users to extend the sharing of a record. However, Sharing button only displays when appropriate.

Sharing button will be available when your sharing setting is either Private or Public Read Only for an object or related object. For example, Sharing button may appear on Account even though sharing model for Account is Public Read/Write, if your sharing setting for Opportunity is Private or Public Read Only.

Sharing button may not visible for particular user, while other user able to see that button in the same page layout. Some may think, user not able to see the button because do not have permission in the profile assigned, that is not correct.

If user is none of below criteria, user will not see the button, although user able to access the record. Sharing button will be shown if user in following criteria:

• Administrator
• Record owner
• User in a role hierarchy above the record owner, and
• User that has been granted with “Full Access” sharing

As you see, users with Read/Write permission is not in the list above, therefore they will not see the Sharing button.

But if user is one of above, but do not see the button, check this:

• Object is child record in master-detail relationship, see sharing setting = Controlled By Parent
• Sharing button is not added into Page Layout

Lastly, you cannot manually share a contact which not linked to an account.


Reference: Why can’t I see the Sharing button on a record?

Modify Salesforce report filters value with parameter

It is easy to build report in Salesforce using Report Builder. Any user have 'Create and Customize Reports' permission able to create report. User also able to add filters using Add filters button or just drag field to filter area, included to add filter logic to use AND, OR logic combination. Once save, all the filters will be stay in the report to be used by other users.

But, there is a tip we can use a same report with many different filters value without need to create a new report, as long as the filter field is the same by using parameters in the URL

Here we go:
This is simple report saved with URL
https://cs3.salesforce.com/00OQ0000000PQUB

By adding pv parameter, we can customize the filters value
https://cs3.salesforce.com/00OQ0000000PQUB?pv0=AP&pv1=Johan

Seems easy....?

Salesforce: How to check Schedule Reports

Salesforce.com report allow users to schedule reports run and deliver to their email or a group of users.

It is very simple and with just a few clicks and user can set it up.

This feature only available for user with Scheduled Reports permission enable in their user profile or user with added Permission Set contain Scheduled Reports permission.

But, there is maximum of reports can be scheduled for each organisation depend on their Salesforce edition use, from Professional, Enterprise and Ultimate. For more information on the limitation, look here.

Because of this limitation, system administrator need to know all reports is scheduled. It is very take time if you would like to check each report if you have hundreds or thousands of report in your company. Salesforce profile a simple way to check scheduled reports if you are system administrator or users with permission View Setup and Configuration.

Here we go:

To see all scheduled reports for your organization, click Your Name | Setup | Monitoring | Scheduled Jobs. Please note that in that section you will see all the scheduled jobs (dashboard refresh, data export, etc). However, you can simply create a list view in order to display only jobs where Type EQUALS "Report Run".

The same way you can monitor schedule jobs for: 

• Data Export
• Dashboard Refresh
• Analytic Snapshot
• Scheduled Apex
• Batch Job

You can add in the filter for Next Scheduled Run not equal to <blank> to show only active schedule.

 

Reference: 

• Salesforce1 Reporting Limits Per Edition
• Scheduled Reports Limitations Considerations