Salesforce: Data Loader Time Zone

For most Salesforce administrator and consultant, Data Loader is a familiar tool when work with data. Data Loader is a simple yet powerful tool provide by Salesforce to insert, update, upsert, export, and delete data in Salesforce. To insert, update, upsert and delete Salesforce record, you need to prepare the data in CSV file format. After execution, it will provide you success and error log in the CSV format as well.

But, many users not realize about Time Zone setting in Data Loader. By default, after you install Data Loader, it will follow your computer time zone. This will cause issue when you load data for Date or Date Time field.

If you refer to my blog earlier regarding upload Quota, where Start Date in Revenue Quota is not correctly loaded, this is one of the sample caused by incorrect Time Zone setting in Data Loader.

In this exercise, we are not turning on "Use European date format (dd/mm/yyyy)".

Scenario 1: insert or update Date field in Salesforce:

• You set Data Loader time zone to eastern GMT time zone (from GMT+01:00 to GMT+14:00), example: Asia/Singapore GMT+08:00
• You provide the data in CSV with date only (format yyyy-MM-dd), without time, example: 2015-02-14

Result: this will cause the date auto adjust to 1 day earlier of the date specify in CSV file.

Analyse: even for Date field (not Date Time field), if you do not provide time in the CSV file, it will treat the time as 00:00:00. So, 2015-02-14 added with time 00:00:00 for Asia/Singapore time zone, it is equal to 2015-02-13T16:00:00 GMT. In Salesforce, it will stored as 2015-02-13, instead of 2015-02-14.

Solution:

• Add time x hours as your time zone setting in Data Loader, example: 2015-02-14T08:00:00.000 SGT
• To make our life easier, change Data Loader time zone setting to GMT, you do need to add time anymore.

Scenario 2: insert or update Date Time field in Salesforce:

Option (a): CSV is prepared with date only, Salesforce accept the date in 3 formats below:

• yyyy-MM-dd
• MM/dd/yyyy
• yyyyMMdd

In this exercise, Salesforce user time zone is (GMT+08:00) Singapore Standard Time (Asia/Singapore). Let us see how is the result, when Data Loader time zone is set as GMT and as Asia/Singapore.

Source	GMT	Asia/Singapore
2015-02-16	2/16/2015 8:00 AM	2/16/2015 12:00 AM
02/16/2015	2/16/2015 8:00 AM	2/16/2015 12:00 AM
20150216	2/16/2015 8:00 AM	2/16/2015 12:00 AM

Summary from this exercise, if we load date time field without time given, set Data Loader time zone the same as User time zone to get 12:00 AM

Option (b): CSV is prepared with date and time, Salesforce will accept following format:

• yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
• yyyy-MM-dd'T'HH:mm:ss.SSS SGT
• yyyy-MM-dd'T'HH:mm:ss.SSSSGT
• yyyy-MM-dd'T'HH:mm:ss.SSS GMT-08:00
• yyyy-MM-dd'T'HH:mm:ss
• yyyy-MM-dd HH:mm:ss
• yyyyMMdd'T'HH:mm:ss
• MM/dd/yyyy HH:mm:ss

and NOT following format:

• yyyy-MM-dd'T'HH:mm:ss.SSS Singapore Standard Time
• yyyy-MM-dd'T'HH:mm:ss.SSSSingapore Standard Time
• yyyy-MM-dd'T'HH:mm:ss.SSSGMT-08:00
• yyyy-MM-dd'T'HH:mm:ss.SSS -800
• yyyy-MM-dd'T'HH:mm:ss.SSS-800

Source	GMT	Asia/Singapore
1) 2015-02-16T09:00:00.000Z	2/16/2015 5:00 PM	2/16/2015 5:00 PM
2) 2015-02-16T09:00:00.000 SGT	2/16/2015 9:00 AM	2/16/2015 9:00 AM
3) 2015-02-16T09:00:00.000SGT	2/16/2015 9:00 AM	2/16/2015 9:00 AM
4) 2015-02-16T09:00:00.000GMT+08:00	2/16/2015 9:00 AM	2/16/2015 9:00 AM
5) 2015-02-16T09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM
6) 2015-02-16 09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM
7) 02/16/2015 09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM
8) 20150216T09:00:00	2/16/2015 5:00 PM	2/16/2015 9:00 AM

Summary:
1) both time zone will return 5 PM, because Asia/Singapore = GMT+08:00 plus 9 hour = 5 PM
2 - 4) both time zone will return 9 AM, because we have defined time zone in the source data
5 - 8) GMT setting will return GMT+08:00 (this is user time zone) plus 9 hour = 5 PM, while Asia/Singapore setting will return GMT+08:00 (this is user time zone) minus Asia/Singapore (this is Data Loader setting time zone) plus 9 hour = 9 AM

Following information cite from Data Loader guide. If a date value does not include a time zone, this value is used:

• If no value is specified, the time zone of the computer where Data Loader is installed is used.
• If an incorrect value is entered, GMT is used as the time zone and this fact is noted in the Data Loader log.

The value can be a full name such as America/Los_Angeles, or a custom ID such as GMT-8:00


Reference:

• Salesforce Supported Time Zones
• Data Types Supported by Data Loader
• How can I ensure my dates are correct when loading with the Data Loader?

Salesforce: Setup Audit Trail

Setup audit trail helps to track setup changes done by system administrators or users with extra permissions. This feature is very useful for organizations with multiple administrators; or even to find if you forget when to a feature is enabled.

You can view the setup audit trail history, from Setup - Security Controls - View Setup Audit Trail. It will show latest 20 entry,  you also can download up to past 180 days history into CSV file. Audit Trail will show information of Date, User, Action, Section, and Delegate User (if any).

Please note that NOT ALL changes is tracked in Audit Trail, such as: Outlook Configurations, List Views and etc. Here list of type tracked in Audit trail :
- Administration
- Customization
- Security and Sharing
- Data Management
- Development
- Various Setup
- Using the application

For the complete list of changes tracked, please click here.

Salesforce: Mass Upload files to Attachment using Data Loader

Background: user need to mass upload files as attachment in Account or Opportunity.

Solution: Using Data Loader to mass upload files to attachment. If you need to regularly do mass upload, you can schedule Data Loader, see this user guide.

For loading file as Files (not as Attachment), check this blog Mass Upload files to Files using Data Loader.

1. Prepare CSV file

You can use Ms Excel to create new CSV file with following columns:

• ParentId - Id of the record to which the attachment should be associated 
• Name - Name of the attachment (you can put file name here)
• ContentType - Format of the extension (e.g. image/jpeg, application/vnd.ms-excel, etc), if you not sure, you can run SOQL query to Attachment object for similar file type. However, this field is not mandatory, so you can leave it blank or un-map it.
• OwnerId - Id for the owner of the attachment (UserId). If you not put any UserId here, your UserId will be OwnerId
• Body - File path to the attachment on the local machine (C:\temp\how to make gold.png)

2. Login to Apex Data Loader

• Select the "Insert" button. 
• In the 'Select Salesforce Object' window, select the 'Show all Salesforce Objects' checkbox and then select "Attachment". 
• Choose the CSV file you have prepare above. 
• In the mapping step, map the following fields:

Click "OK" to proceed with the insert. It may take a few minutes depends on the number of file and file size, but the attachments should be successfully uploaded to your Salesforce.

Hope this help.

Salesforce: Hidden Tab for Custom Object

Tab is one of the very useful feature in Salesforce and it is very simple. It provide user ability to create view to list all records or records with some criteria.

After create a custom object, admin can simply create a new tab from Setup - Create - Tabs and select Custom Object Tabs then create New button. But, if you are not using Unlimited Edition, there are number of maximum tab we can create based on the Salesforce edition, see the limit here.

Here a tip to create a hidden tab for custom object, meaning admin do not need to create new Tab, but we can use the function, here we go:

1. After custom object created, get the 3 characters Id Prefix
How to get the the prefix?

• If you are using Force.com IDE, look for salesforce.schema for that object

• Using Developer Workbench

2. Type the prefix after Salesforce.com URL 
Example: object prefix is a03, type in the URL https://na3.salesforce.com/a03
Change 'na3' with your Salesforce.com instance and make sure NOT to add / after the prefix.

Salesforce: Log A Call with Activity Type auto populated

Type is a standard picklist field in Salesforce Activity object. User can add / remove values in the picklist.

When user click "Send an Email" button from Activity History related list of Account, Contact, Opportunity, or from any other object page layout, system admin can define default value equal to "Email" (for example). So, task created will be always assigned to Email.

But, when user click "Log A Call" button (also in Activity History related list ), Type will be blank. This is not ideal, system should be smart enough to auto-populate Type to Call (or any other value default in the system set by system admin).

Is the any workaround? YES. Here we go:

• Create a custom button from: Setup - Customize - Activities - Task Buttons, Links, and Actions
• Click "New Button or Link" button
• Enter following data as screenshot below:

• Click "Save" button to save it.
• Go to object page layout, such as Contact page layout, edit the page layout and scroll to "Activity History" related list
• Untick standard "Log A Call" button and add custom button created above

• Done

But, here is the downside, if you notice in screenshot above, you see many buttons: "Contact Log A Call", "Account Log A Call", etc... this is because you need a button for each object. This is caused by the URL in the button
/00T/e?who_id={!Contact.Id}&tsk10=Call&retURL=%2F{!Contact.Id}

See there is Contact Id in who_id parameter, and for Account and Opportunity, you need to put their Id to what_id parameter.

Salesforce: change email address without confirmation

As security measure in Salesforce, when admin change user email address (maybe in sandbox, typo or other reasons), Salesforce will send an verification email to the new email address, user need to verify by click a link in the email. Before user verify the link, email address will not change, no matter how many times you do it, even using API.

But, can admin change the email address without confirmation email? Yes, here is the trick:

• Click Edit on user detail page
• Change the email address AND check "Generate new password and notify user immediately" checkbox (all the way at the bottom of the edit screen) at the same time
• Click Save button

Salesforce will change email address immediately, no confirmation is needed! But, of course password will be reset and user will get email from Salesforce with a new temporary password. This is still better than ask user to verify email changed then reset password.

Salesforce: SOQL Reference

Using a query tool of your choice (e.g., the Developer Console Query Editor, Workbench), you can execute the SOQL (Salesforce Object Query Language) query to get raw data from Salesforce, where some of this is unavailable from the report.

Simple query
SELECT Id, Name FROM Account WHERE Name LIKE 'A%'
SELECT Id, Name FROM Account WHERE Name LIKE 'A%' AND BillingCity = 'Redwood City'
SELECT Id, Name FROM Account WHERE CurrencyIsoCode = 'USD' OR CurrencyIsoCode = 'SGD'

Query with LIKE and NOT
SELECT a.Id, a.Name FROM Account a WHERE NOT a.Name LIKE 'A%'
SELECT Id, Name FROM Account WHERE AND BillingCity = 'Redwood City' AND (NOT Name LIKE '%Vendor%')
** NOT should be put before the field name
** Notice usage of a variable

However, in some fields like the Id or polymorphic relationship field, we cannot use LIKE.

For Task and Event object, if you know the related object, you can filter with the object name

SELECT Id, WhatId, What.Type, What.Name  FROM Task WHERE What.Type IN ('Account','Opportunity') LIMIT 100

Query filter on DateTime
SELECT Name FROM Account WHERE CreatedDate >= 2011-04-26T10:00:00-08:00 this is GMT-8
SELECT Name, CreatedDate FROM Account WHERE CreatedDate > 2011-04-26T10:00:00Z this is GMT

Query with Date function
SELECT Amount FROM Opportunity WHERE CALENDAR_YEAR(CreatedDate) = 2011

SELECT Amount FROM Opportunity WHERE CreatedDate = THIS_YEAR

Query filter on Null value
SELECT Id, AccountId FROM Event WHERE ActivityDate != Null

same result using <> SELECT Id, AccountId FROM Event WHERE ActivityDate <> Null

SELECT Id, AccountId FROM Event WHERE ActivityDate = Null

SELECT Id, AccountId FROM Event WHERE AccountId = Null

same result using '' SELECT Id, AccountId FROM Event WHERE AccountId = ''

but you can't use '' for Date field

Query Multi-Select Picklists
SELECT Id, Country__c from Account WHERE Country__c INCLUDES ('Indonesia;Singapore','Malaysia')

Semi-Join Query
SELECT Id, Name FROM Account WHERE Id IN ( SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost' )

Reference Field Semi-Join Query
SELECT Id FROM Task WHERE WhoId IN ( SELECT Id FROM Contact WHERE MailingCity = 'Twin Falls' )
To query attachment from an object only
SELECT Id, Name, ParentId FROM Attachment WHERE ParentId IN ( SELECT Id FROM Account )

Anti-Join Query
SELECT Id FROM Account WHERE Id NOT IN ( SELECT AccountId FROM Opportunity WHERE IsClosed = False )

Reference Field Anti-Join Query
SELECT Id FROM Opportunity WHERE AccountId NOT IN ( SELECT AccountId FROM Contact WHERE LeadSource = 'Web' )

Multiple Semi-Joins Query
SELECT Id, Name FROM Account WHERE 
Id IN ( SELECT AccountId FROM Contact WHERE LastName LIKE 'apple%' ) AND 
Id IN ( SELECT AccountId FROM Opportunity WHERE isClosed = False )

Relationship Query: parent to child
SELECT Id, (SELECT Id,Name from OpportunityLineItems) FROM Opportunity
SELECT Id, (SELECT Id,Name from Account_Requests__r) FROM Account
Notice for the child object name in yellow highlight, it is plural. You can get the name from the lookup field in the child object "Child Relationship Name" and add __r for custom field.

Relationship Query: child to parent 
SELECT Id, Name, Account.Name FROM Contact

Relationship Query: Polymorphic 
A polymorphic relationship field in an object being queried that can reference multiple object types. For example, the What relationship field of an Event or Task could be an Account, or a Campaign, or an Opportunity. Check references at the bottom of this blog.
SELECT Id FROM Task WHERE What.TYPE IN ('Account', 'Opportunity')

SELECT Id, WhatId, 

  TYPEOF What WHEN Account THEN BillingState, BillingCity END 

FROM Task WHERE WhatId IN (SELECT Id FROM Account)

SELECT 

  TYPEOF What

    WHEN Account THEN Phone, NumberOfEmployees

    WHEN Opportunity THEN Amount, CloseDate

    ELSE Name, Email

  END

FROM Event

Select ALL

In standard SQL, we can use SELECT * from table_name, but this is not available in SOQL

In API 51.0, Salesforce introduced 

  FIELDS(ALL)—to select all the fields of an object.

  FIELDS(CUSTOM)—to select all the custom fields of an object.

  FIELDS(STANDARD)—to select all the standard fields of an object

Sample: SELECT FIELDS(ALL) FROM Account LIMIT 200

Reference FIELDS()

Using OFFSET
Use OFFSET to specify the starting row offset into the result set returned by your query.
SELECT Id, Name FROM Opportunity ORDER BY Name OFFSET 5

Using COUNT
Returns the number of rows matching the query criteria
SELECT COUNT() FROM Account WHERE Name LIKE 'a%'
SELECT COUNT(Id) FROM Account WHERE Name LIKE 'a%'

If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT()

Using COUNT_DISTINCT
Returns the number of distinct non-null field values matching the query criteria
SELECT COUNT_DISTINCT(Company) FROM Lead

This will return the number of distinct values from the Company field

Using GROUP BY
From API version 18.0 and later, you can use GROUP BY with aggregate functions, such as COUNT(), SUM(), MAX(), MIN(), AVG()
SELECT Stagename, COUNT(Id) FROM Opportunity GROUP BY Stagename

SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource

SELECT StageName, SUM(amount), MAX(amount), MIN(amount), AVG(amount) FROM Opportunity GROUP BY StageName

SELECT CALENDAR_YEAR(CloseDate) Year, CALENDAR_MONTH(CloseDate) Month, COUNT(Id) TotalCount FROM Opportunity WHERE IsClosed = False GROUP BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate) ORDER BY CALENDAR_YEAR(CloseDate) ASC

Note: you cannot use a formula field for grouping.

Using Alias with GROUP BY
You can use an alias for any field or aggregated field in a SELECT statement in a SOQL query. Use a field alias to identify the field when you’re processing the query results in your code.
SELECT StageName, SUM(amount) Jumlah, MAX(amount), MIN(amount), AVG(amount) FROM Opportunity GROUP BY StageName ORDER BY SUM(amount) Desc

Using GROUP BY ROLLUP
Same with GROUP BY, with additional ROLLUP, it adds subtotal for aggregated data in the last row
SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY ROLLUP(StageName)

Using GROUP BY ROLLUP with 2 fields
SELECT Status, LeadSource, COUNT(Id) FROM Lead GROUP BY ROLLUP(Status, LeadSource)

*Yellow highlight = total subgroup

*Green highlight = total all  

HAVING in GROUP BY
You can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions, same with WHERE with normal query
SELECT LeadSource, COUNT(Id) FROM Lead GROUP BY LeadSource HAVING COUNT(Id) > 2

Querying Currency Fields in Multi-currency Organizations
SELECT Id, Name, Amount, CurrencyIsoCode FROM Opportunity WHERE Amount > SGD5000 ORDER BY Amount
** without currency code it will use the organization's default currency
** Amount in query result will be in record currency



Reference: 

• Salesforce Object Query Language (SOQL)
• TYPEOF
• Filtering on Polymorphic Relationship Fields
• Salesforce SOQL query LAST_WEEK ; LAST_N_DAYS:n ; N_DAYS_AGO:n

Last update: 10 Jun 2021

Edit Data in Salesforce Report

Using report builder, creating a tabular report in Salesforce is something very easy can be done by any users. But, how about adding an Edit link in the report, would it be awesome? Once user click the link, it will bring user to the page layout in edit mode without have to open the record, so less click!!! (as long as user have the permission to edit the data)

Here sample for Opportunity:

1. Create a formula field in Opportunity

• Do not need to add the field to any page layout, but make it visible to Profiles as required
• Set Formula Return Type = Text
• Type Label = Action
• Formula = HYPERLINK('006/e?id='&Id, 'Edit' , '_blank'), this formula will open new tab in the web browser when user click Edit link in report, or change _blank to _self if you prefer user to open the record in the same tab, meaning it will close the report.

2. Create Opportunity report

• Select Tabular format report
• Drag fields needed to the report and add the filters as normal report
• Drag Action field from field list, preferably at the most left.

Note: if you need to have Action field for each object in the report, you need to create the formula field for that object.

Using Salesforce joined report for User target

Background: 
Business would like to have a report in Salesforce to list number of opportunities owned by a sales rep, include information of number of closed won opportunity with target of number of report for each sales rep.

Solution:
Create a joined reports.

Steps:
1. Create a custom field with number type in User object

2. Create custom report type with Primary Object = Opportunity

3. In the report type, add custom field created to the Custom Report Type from Add fields related via lookup » then Select Opportunity Owner >> and fields needed.

4. Create a new report using new report type created
Select joined report, add report blocks, set filter and date range for each block then and add fields needed. You also can Add Formula and Cross Block Formula as needed for the report.

Improve Salesforce Report Performance (2)

In previous blog, we discussed about how to improve Salesforce report performance, a report may running slow or timeout because of:

• It is querying too many objects
• It is dealing with intricate lookups
• It has too many fields

Here are few more items you can look into to improve Salesforce report performance:

• If you can’t view a report and want to edit it to avoid the time out, you can append ?edit=1 to the end of report URL to get to edit mode, where you can adjust the criteria. 
• When filtering, use the equals or not equal to operators instead of contains or does not contain. 
• Use time frame filters to narrow your report’s date range if possible. For example, use Last 30 Days instead of Current FY.

To determine the improved performance, it's a trial and error process.  Each user might have different report performance results as it also depends on many factors such as their role.

If the report results in a time out error message, then your system administrator can ask Salesforce Support to possibly "Index" a report filter on the database tables.  This does not always resolve the issue as the above solution is the best method.

If your reports sometimes get time out error and sometimes is running well, this may cause by caching mechanism applied by Salesforce backend. After implement all suggestion above, if still get the same issue, you can log a ticket to Salesforce for the best advise.

Salesforce: Merge Account button

Out of the box, Salesforce have merge account function. User can merge 2 or more Accounts into Account, where user can select values from each account for each field to survive.

To launch Merge Accounts, just click Account tab and scroll down to Merge Accounts in Tools panel.

But, do we have Merge button in Account page layout? Out of the box, NO, but we can create custom Merge button without have to write any code. Here we go:

1. Go to Account object, click Buttons, Links, and Actions
2. Click New Button or Link button
3. Enter label, name, description
4. Behavior = Display in existing window without sidebar or header
5. Content Source = URL
6. Button or Link URL = /merge/accmergewizard.jsp?srch={!Account.Name}

After button created, you need to add the button into Account Page Layout.

This button exactly use the same function from Merge Accounts tool, we just add button to make user easier to merge Accounts from Account detail, so nothing fancy.

Salesforce: Status = In Development

When standard Report Type is not enough, you can create Custom Report Type to define the set of records and fields available to a report based on the relationships between a primary object and its related objects. Reports display only records that meet the criteria defined in the report type. To create Custom Report Type, navigate to Setup | Create | Report Types and click New Custom Report Type.

The same when you create custom object from Setup | Create | Object, you need to choose Deployment status: In Deployment or Deployed.

Custom Report Type

Custom Object

Deployed - select this option when you're ready to let all users access the report type or object.

In Development - select this option if you want to test it before making it available to all your users, the report type or object will be hidden from all users except those with “Customize Application”user permission and user with “Manage Custom Report Types” permission for custom report type. Only users with those permission able to create and run reports using report types or related to that custom object.


Reference:  Create a Custom Report Type

Salesforce: Filter Search Results

Global Search is an awesome feature of Salesforce, you can search everything in your Salesforce org, from Account, Contact, User (People) until Chatter. You also can use Options to search only "Limit to items I own" and "Exact phrase".

But, one thing that many user do not know that we also can filter search result. How?
By default, you will not see Filter in the search result. Your Salesforce admin need to set specified fields to use as filter.

Example for Account, go to Setup - Customize - Accounts - Search Layouts. Then look for 'Search Filter Fields' and click Edit link. Admin can select fields want to show as Filter in the search result.

Screenshot below show, user can filter on Account, but not on Contact. This is because admin has set fields for Account Search Filter, but not for Contact.

Once you click Show Filters link, you can enter any search criteria.

Note: if you leave the field blank, it will not use as search criteria.

The criteria you specify are AND based. That is, if you specify more than one criteria, the result includes only records that match all of the criteria. For example, if you enter 'acme california' in the Account Name filter field, your results include account with contain both acme and california. So 'acme california' and 'california acme' will return the same result.

For date field, such as Created Date, you can enter literal date values such as YESTERDAY or NEXT MONTH.


Reference: How Do I Refine Search Results?


Last update: 8 May 2016

Salesforce: How to show more records in related list?

When viewing a record detail page (e.g. Account detail) the related lists shows a limited number of related records at a time, e.g. for Opportunity related list. So, if you have more than 5 (by default) opportunities tagged to an account, in the Account detail page, you will see You will see Show x more » | Go to list (y+) ». By default x will be 5 and y is total number of the records.

You can scroll down to the bottom of page, and look for Always show me more records per related list or Always show me fewer / more records per related list if you have click more before (even in previous login).

Clicking more or fewer increases and decreases the default number of related list records displayed for all object record detail pages for the logged in user. If you notice, when you click more or fewer link, in the URL, salesforce will add parameters rowsperlist=xx, for example: https://cs30.salesforce.com/0018000000Ril4o?rowsperlist=10, this parameter will show related list up to 10 records for ALL related list.

Please note: this change will be permanent for the logged in user for ALL tab.

How is this different from the "Show more" link on the a related list?
The more / fewer link at the bottom of a record detail page is not the same as the Show more link on the bottom of individual related list sections on the record detail page. The "Show more" link increases the number of records displayed for that related list only (not all related lists on the page) for one time only.

If the user views another record and returns to the previous record, they will have to click the "Show more" link again in order to increase the number of related list records for the related list they wish to view.