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.