"Enabler for Excel" for Salesforce (part 2)

This blog is continuation from part 1. Some features not really directly access Salesforce data, but it help admin in analysing data downloaded from Salesforce to Ms Excel.

1. Convert IDs from 15 to 18 chars
This is very simple feature which will do convert Salesforce Id from 15 to 18 characters. Please not that both Id (15 and 18) are valid Id. So, what is the different?
- 15 character Id is case-sensitive, where when you work in Ms Excel function, such as VLOOKUP(), it will not check case sensitivity and cause duplication.
- 18 character Id is case-insensitive, so it is easier when work in Ms Excel.

You just need to select a cell or row of cells with 15 IDs character and the result of 18 characters ID will overwrite the original 18 characters IDs.

2. Diff
This is another simple feature to compare value of cells in 2 columns, you can choose for case-sensitive or case-insensitive. This tool will not compare value of cell in order.

3. Settings
In Setting, you can adjust batch size of update, insert and delete separately. Also path of log file and it will you if any new version available.

4. Field Utilization
Heard about Field Trip app in AppExchange? This feature is almost similar, but you do not need to install anything in your Salesforce.
Often system admin add new field based on some requirements, but over the time those fields no longer used. This feature is good to analyse field utilization when we need to do fields clean-up
- Select an object
- Select fields you want to analyze, hold Ctrl key to select more field
- You can leave Criteria text area blank to select all data
- Click OK button
- It will create a new worksheet with information total number of records with column: Field, % utilized and No. of records of that field

5. VisualForce from Layout
This is pretty cool feature for new developer interested to learn Visualforce page. It will create a visualforce markup file from a page layout in Salesforce.

6. Analyze Layouts
This last feature will get all fields information of an object, information will show in columns:

• Object Name
• Label
• Field Definition/Description
• Field Type (Roll-Up Summary will show as Formula field)
• Pick list Values (if applicable)
• API Name
• Calculation (if formula field)

Salesforce: Custom Field and Custom Object Search

For those of you have hundreds of fields in many custom objects, do you wish if you search any of them easily without have to go through each object? If yes, you have to mostly welcome Summer '14 release.

If you have enable Enable Advanced Setup Search (Beta), from Setup - Customize - Activities - User Interface, and fine it in Advanced section.

Before Summer '14 release, if you have Enable Advanced Setup Search (Beta), when user type in the setup search box, user immediately see menu pages whose names match what you're typing.

When you press Enter, you'll see a page listing any matching Groups, Permission Sets, Profiles, Users, and Roles.

Good news from Summer '14 release onwards, the search result will include Custom Field and Custom Object.

With custom field search, an administrator or developer can find the custom field definitions he/she is searching for dramatically faster by searching for them by name. For example, searching for “loc” returns all custom fields across all objects whose name includes “loc”, such as “Account Location”, “Asset Location,” and “Vehicle Location.”

With custom object search, you can find the custom object definition you need immediately by typing in at least two letters of its name. For example, searching for “ex” returns “Exam,” “Expense,” “Expertise,” and so on.

"Enabler for Excel" for Salesforce (part 1)

Enabler for Excel is a Microsoft Excel add-on enables easy mass updates, inserts, and deletes Salesforce data directly from Microsoft Excel. It has extra functionality of determining which fields are in and out of layouts, as well as a tool for fields utilization calculation. Visualforce from layout page generation. It is listed in AppExchange, so it mean has passed through security check by Salesforce team.

For those of you who used and like Excel Connector, a nice tool for admin to works with Salesforce data easier using Microsoft Excel, but it has been not updated by the developer, the latest version using API 16 and was written with Windows XP and Office 2003. So, Enabler is a good option for those of you looking for Excel Connector replacement.

I found Enabler for Excel is an awesome tool for Salesforce admin, it works with latest Windows OS machine (tested in Windows 7, Windows 8 and Windows 8.1) and latest Microsoft Excel 2010 and 2013.

You can download the add-on from Taralex website, as I am writing this blog, latest version 1.9.0.2, special thanks to Alexander Tarasevich who write this add-on and share it for free (as of now).

You can found the documentation and installation guide from their wiki site. Once you install it successfully, you will find new ribbon called Salesforce in you Microsoft Excel.

1. Web Login 

Type in your Salesforce username and password into Salesforce website, this option do not need security token. Or you can login manually and store the password with security token.

2. Get Data

As Salesforce admin, I like this function very much. Let say you want to extract data from Salesforce and you already have criteria, maybe Account Name or Account Id, etc

How it works?

- Put your criteria data into a columns, let say 20 rows of Account Id

- Click "Get Data" icon

- It will open Get Data window and click "Grab a list..." icon

- Highlight your mouse over the data and it will copy them to "Selected values" window

- Close that window and the value will be available in Get Data window

- If you familiar with SOQL, you can just type the query syntax, example: 

SELECT Id, Name FROM Account WHERE Id IN ('0015000000qiJe9AAE','0015000000QqeisAAB','0015000000psTSSAA2') ORDER BY Name

- Once you get the SOQL ready, click Execute button. Enabler with create new sheet and name it as the object name. But if you already have the name in worksheet, it will create sheet with object name (1) or (2) or continue.

3. Refresh

Let's say you type in your soql query and hit Excute button, if nothing wrong with the query syntax, it will return data from Salesforce into a new worksheet. Save your excel file and done. The next time when you open that Excel file again, if you click Refresh icon, it will update data in that worksheet from Salesforce, cool, isn't it? 

4. Reports

This feature is pretty cool, it access the report directly from Salesforce and present the data in Ms Excel directly. On top of that, when you click the button, the order of report is exactly the same as in Report tab in Salesforce. When you run the report in Ms Excel from Enabler, the report position in Salesforce will move to the top (if your report in Salesforce select as "Recently Viewed")

5. Delete, Insert, Upsert, Update

As normal delete and update process, you need to provide record Id. It will prompt for Map Columns. While when you choose ".... Selected", make sure you select correct data, otherwise it will throw error. 

Once done, it will provide the Operation result and ID at the right of the data in Ms Excel.

To be continue in part 2...

Chatter Moderator Profile

For Salesforce.com user, most of us should know and use Chatter for collaboration. But, if user do not need to access CRM or Force.com app built in your company, admin can assign user with Chatter Free license. Salesforce give 5000 Chatter Free licenses and 500 Chatter External licenses.

By default, you will see 2 profile for Chatter Free license: Chatter Moderator User and Chatter Free User. Both profiles dedicated only for Chatter users without access to any Salesforce or Force.com objects and cannot be reference as User lookup field. In profile setting Administrative Permissions, both profiles only able to customize Invite Customers To Chatter. If you notice, the different between this two profile is Moderate Chatter permission and Password Never Expires.

Here we will discuss what is Moderate Chatter permission? You can tell if someone is a moderator by the special banner on their profile picture.

A moderator is a Chatter user with some additional privileges, such as:

1. Assign a Chatter Free user as moderator or take the privilege away
Red arrow in screenshot above will give Chatter Free user with Moderator privilege, this will automatically change user profile to Chatter Moderator User.

2. Activate or deactivate Chatter Free user
Yellow arrow in screenshot above will deactivate Chatter user

3. Delete posts and comments that they can see

Above screenshot taken from a Chatter group, where user is not Owner or Manager of the group, but since he has Moderator privilege, user can delete a post or comment.

But, moderator is not system administrator with View All Data permission, moderator not able to see Private Group, so for Private Group, moderator need permission to join / access a private Chatter Group.

Chatter External User
If you enable Customer Invitations from Setup - Customize - Chatter - Settings - Allow Customer Invitations, you will see another profile called Chatter External User.
This profile is designed for external user, so you can invite your customers join your company Chatter and collaborate with internal employee.

You can set Chatter External User as Manager of a group, but you can limit their permission not to invite user from Profile Setting.

Reference:

• Chatter Moderators
• Deactivating Chatter Free Users
• Assigning Moderator Privileges to Chatter Free Users

Salesforce: Dashboard Snapshot

If you are not aware, in Salesforce we can post a snapshot of a dashboard component to Chatter feed, whether to a User or Chatter Group or to Dashboard feed. A snapshot is a static image of a dashboard component at a specific point of time when the dashboard last refresh.

Posting a dashboard component to a group is a good practice to share and collaborate with team member. While posting to dashboard feed will help us to follow changes of the data in the dashboard.

How to post it? Just hover mouse over a dashboard component, see arrow at top right of the chart component. If you not see it, contact your system admin to enable it. But if you are the admin, see following steps:

1. Enable Feed Tracking for Dashboards
Go to Setup - Customize - Chatter - Feed Tracking
Look for Dashboard object and tick Enable Feed Tracking

2. Enable Post to User or Chatter Group
Go to Setup - Customize - Reports & Dashboards - User Interface
Look for Chatter Options and tick Enable Dashboard Component Snapshots

If you have enable both option above, go back to your dashboard and make sure the page is refresh.

Screenshot below taken when step (2) has not enabled, notice you will see 1 option only.

This screenshot after enable step (2), notice it has option to post snapshot to User or Group feed.

Here is the screenshot post to Dashboard feed and to Chatter Group.

Reference: Posting Snapshots to Chatter

How to report Salesforce mobile app usage?

So, you just roll-out Salesforce1 for your internal company usage, congratulations!!! Next step is to monitor your user adoption and present it to the management team.

Bad thing, in the user login report, you cannot identified which login is coming from Salesforce1. Work around, you can download Login History into CSV file and using Ms Excel to filter Application contain Salesforce1. It is pretty complex using Login History and you cannot run it as a report to use it as source for Dashboard and present it your management team.

Instead of using standard User report type, create a new custom report. Here is the step:

Create Custom Report Type
Start with create a new custom report type:
•  From Setup, click Create | Report Types
•  Click New Custom Report Type button
•  Enter the following values.
   •  Primary Object: Users
   •  Report Type Label: a unique label such as Login History with Identity
   •  Report Type Name: this field will be automatically populated based on Label, just skip it.
   •  Description: Give a useful description that other user will understand in the future.
   •  Store in Category: Pick a category for this report; such as Administrative Reports.
   •  Deployment Status: Keep as In Development until ready to deploy this report for other users.
•  Click Next
•  Select Click to relate to another object
•  Select Identity Event Logs (Users)
•  Keep A to B Relationship to default "Each "A" record must have at least one related "B" record"

•  Click Save.

Create Custom Report

•  Click the Reports tab and New Report button
•  In Administrative Reports, select Login History with Identity and click Create.
•  Drag-and-drop fields on to the report. Some useful fields to add into report: Full Name, App: Connected App Name, Timestamp, Status and Usage Type
•  Add filter “App: Connected App Name contains Salesforce1”
•  Give the report a name, such as: Salesforce1 Adoption.
•  Click Save
As this is a normal report, you can adjust the Time Frame.

Here is the screenshot of the report created:

If you see above report, in Usage Type:
•  OAuth Authorization: when the user first logs in
•  OAuth Token Exchange: when user has been idle for some time and the app needs to re-authenticate
In the Status, Error: App access denied tell you if user hit "Deny" in Salesforce1 app

Another example of report below, App: Connected App Name tell you each transaction of login attempt or re-authenticate attempt from Salesforce1 app from iOS or Android devices.

Using the "Power of One", you can easily count how many users login to Salesforce1 app from iOS and Android device, then add a simple chart to the report. In sample below, it show number users for Android = 34 and iOS = 276, with total of 305 users. This mean 29 (34 - 5) users using Android only, 271 (276 - 5) users on iOS only and 5 users are using both Android and iOS devices to access Salesforce1.

If you need to do further analysis, you can download the CSV file and use Pivot Table in Ms Excel.

Anyway, this only work in Enterprise edition and above (included Unlimited and Performance edition), so if you are using Professional edition, this is not applicable and hope Salesforce will implement Salesforce Identity for Professional edition in the future.

Salesforce: Change Picklist Value

Picklist field is very commonly used in Salesforce. User can easily select a value from picklist, while for admin, it give better data quality, because user cannot just anyhow type in picklist.

But one day, we may have requirement to change existing picklist value with a new one, maybe for Classification from Bug to Defect. Depends on the requirement, you may need to leave all existing data with old value or change existing value to new value.

There are 2 options if you need to change existing value:

1. Create and Replace with New Picklist Value

• Click New button to add New values (some fields such as Opportunity Stage, you need to enter Stage Name, Type, Probability, and Forecast Category).
• Click Replace button and type the exact value you want to change in 'Exact Value Changing From', and select replacement value created above.
• For custom field and some standard field, you will see "Replace all blank values" checkbox to update picklist field without value with a value.
• Once done, you can delete the old picklist value.

Note:

• This is a global change and will replace the picklist value for all existing records, including records in the Recycle Bin. 
• This will update the Modified By date and time for all records where the value was replaced.

2. Rename Picklist Value
Go to a picklist value and click Edit link, then type in the new value. But some picklist fields, example: Opportunity Stage, it will not allow editing of the name, so have to use option no. 1 above.

Note:

• All existing records value will be updated with new values.
• This will NOT update the Modified By date and time.

For both option above:

• It will by pass any validation rules and triggers
• It will not tracked in field history audit

If you need to keep the old values, here you go:

1. Create new picklist value
2. Hide the old value (if you have record type, or dependent picklist) or delete the value.

Extra: delete a value in picklist will not change existing values for the record with that value.


Reference:

• Difference between Renaming a Picklist and Replacing a Picklist Value
• Replacing Picklist Values

Singapore's First Salesforce MVP

1st May 2014 marked a new milestone for my journey with Salesforce. I am honored to announce I am now a Salesforce MVP, and the first for Singapore.

I started my journey with salesforce.com as a developer with a consulting firm in September 2004. This means (in the next few months), I’ll be celebrating my 10th year within the salesforce.com community.

As time passes by, I’ve seen my career grow from being a developer to a consultant. Two years ago I joined a US MNC as a Business System Analyst based in Singapore, still within the salesforce.com community.

Why do I like Salesforce? As an admin or developer, I do not need to worry about maintaining servers, databases, backup and patches because everything is in the cloud. I can access Salesforce from anywhere, on any device through the Salesforce1 mobile app.

My passion and enthusiasm for salesforce.com products has increased my willingness to learn, grow and share with the community. Salesforce Answers Community is a place where I spend my free time. This is a place where members can ask questions about Salesforce. I stay active in the community and it helps me to learn more about Salesforce. I can learn more about the requirements, solutions and best practice of other members. If my organisation has similar requirements, I will already know a possible solution. Staying active in this community helps me to prepare to take Salesforce Certifications, right now I hold 3 active Salesforce/Force.com certifications.

Around early 2013, I attended a local Singapore Salesforce User Group, and it was great to meet up with people with the same passion. Since that very first meeting, I have not missed a single user group meeting. In mid-2013, I became a user group co-leader to run and grow the community, and now we have more than 300 members and it keeps growing.

To be selected as an MVP is a dream come true. It’s for anyone with the heart and passion to help each other - by writing blog posts,using social media, being active in the Success Community, andparticipating in local user groups. With my new status as MVP, I hope I can do more for my company and the Salesforce.com community.


Reference: http://sfdc.co/sg-mvp

Salesforce: Enhanced Lookups

This blog is continuation of my previous blog about Lookup Auto-Completion. Beside Auto-Lookup, Salesforce also have Enhanced Lookup feature, and this is also not enable by default. Currently only account, contact, user, and custom object lookups can use this feature.

How to enable it? Go to: Setup - Customize - Search - Search Settings, scroll down to Lookup Settings and check objects that you need to enable for Enhanced Lookups.

What is the 'extra' when enable this feature?

• Ability to sort search result by any column
• Ability to search not just Name, but also All Fields in that object
• Ability to modify columns shown in the search result, to modify available columns in lookup result, go to Search Results in the object Search Layouts menu.

This is the same auto-completion, only the parent object (not child object) need to enable for enhanced lookups.

Before enabling Enhanced Lookups

After enabling Enhanced Lookups

Reference: Enabling Enhanced Lookups

Salesforce: Lookup Auto Complete

Many of us may not aware that Salesforce have Auto-Complete feature, it is a simple feature for someone, but for many, this feature help a lot for daily works.

By default it is not enable, so admin need to activate it manually, although it is pretty easy. The setup is buried deep inside Setup menu. Here we go: Setup - Customize - Search - Search Settings. Scroll down to Lookup Settings and look for objects that you need enable for auto-completion then check Lookup Auto-Completion for the objects.

Few items to note for Auto-Completion in Salesforce:

1. Admin have to manually enable objects for auto-completion. So, when a lookup field created, the parent object (not child object) need to enable for auto-lookup.
2. Same as other area in Salesforce, it is case-insensitive
3. It only work for both Master-Detail and Lookup relationship, not for text field or other.
4. It displays suggestions from My Recent Items list as you type, but recent items here is not limited to the items in side bar, but including in recent tab and list view.
5. It will catch character in a word start with, so for example: when you type ed, it will catch Kia Edan and edwin, but not medan.

Here a screenshot before turn on auto completion, when user type in Customer Id field, nothing show up:

After turn it on, when user type a character it will show recent record from parent object.

Salesforce: Unable to save when lookup to a record - Insufficient Privileges

Background: a user complain that she get error when edit a record to a lookup record, the same error she got when try to create a new record from related list, both objects are custom object.

We request user to send the screenshot with error message: Insufficient Privileges - You do not have the level of access necessary to perform the operation you requested. Please contact the owner of the record or your administrator if access is necessary.  

From a quick discussion with user, here are few items we notice:  

• Error is not happen to all lookup record, some of the lookup record do not throw error
• User profile has Create permission in both objects, and user able to create new record
• Relationship between this two objects is Master-Detail, and Sharing Setting Organization-Wide Defaults for master object is Read-Only

Further check, we conclude this error only happen to lookup records not editable by user, so this is related to the role hierarchy, but why it not allow user to just link a child record to that parent, where the sharing setting for the parent/lookup object is Read-Only.

A quick thought, is there any Roll-Up summary field in parent to that child? If yes, this lookup will do 'edit' activity to parent -- negative, no Roll-Up summary field in parent to that child.

Finally: we found the issue: this is because Master-Detail field in the child object to parent is set to Sharing Setting = Read/Write: Allows users with at least Read/Write access to the Master record to create, edit, or delete related Detail records.

Once, we change this to Read Only, everything is working well.

Reference: Master-Detail relationship (roll-up summary)

Salesforce MVP Summit 2014

This is my first time attending Salesforce MVP summit. This is the 2nd summit, where 1st summit happened last year.

Yesterday, we end a two full impressive days of Salesforce MVP Summit. Salesforce flew all Salesforce & Force.com MVP from around the world to beautiful city San Francisco. I met many familiar person and familiar name in real person, where we used to met virtually in Salesforce Success Community and just see their small photo in the Chatter Community, from the legendary Steve Molis, Jeff May, Dan Appleman, my Summit buddy Alex Sutherland and many more.

The summit take place in a luxury hotel in the heart of San Francisco. A box with my name come to my room 5 minutes after I check-in. It come with summit agenda and cool Salesforce MVP stuffs, from: jacket, sneaker to cool shades.

We are presented many good stuff by Salesforce product mangers and executives, a sneak peak of what’s coming next on the platform. As we are under NDA not to disclose any, nothing allow to share to public. I only can say Salesforce.com is keep innovating and hearing to our feedback. 

So, now I am in SFO airport ready for a long flight back to home, sunny Singapore. Last words, thanks to Salesforce.com for arranging this great summit and especially to Matt and Erica -  #awesome !!! 

Blogs posted by other MVPs:

• 2014 Salesforce MVP Summit
• Second Annual Salesforce MVP Summit!
• 2014 Salesforce MVP Summit
• We’re at the MVP Summit!
• 5 things I learned from the Salesforce MVP Summit (and more)!
• MVP Summit Diary
• MVP Summit 2014 - video

Reference: Salesforce MVP™ Program