Friday, January 31, 2014

Salesforce trend data (Analytic Snapshot)

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

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

User with "Manage Analytic Snapshots" permission able to create and configured analytic snapshot. Each edition have limited number of analytic snapshot can be created, so used it wisely.

Here is summary to create and configured analytic snapshot:

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

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

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

4. Create the analytic snapshot, from Setup - Administration Setup - Data Management - Analytic Snapshots. You need to enter: Analytic 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 field type.

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

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

For more information on analytic snapshot, click Salesforce documentation.

Lastly, you can monitor ALL Analytic Snapshot in your Salesforce instance from Setup - Administration Setup - Monitoring - Scheduled Jobs. Create new view with Type equal to 'Analytic Snapshot'. It will show the Job Name, Submitted By, Submitted Date, Started Date, and Next Scheduled Run. If Next Scheduled Run is blank, meaning that 'Analytic Snapshot' is no longer active.

Wednesday, January 29, 2014

Salesforce list all dynamic dashboard

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 dashboard using a fix user "viewing as".

But, each organization can have up to 5 dynamic dashboards for Enterprise Edition and 10 dynamic dashboards for Unlimited Edition, additional dynamic dashboards may be available for purchase separately.

So, it important for system admin to track all dashboards configured as dynamic dashboard. Many admin do not sure how to track or list down all dashboards configured as dynamic dashboard.

Here we go
1. Create a custom report type with primary object = Dashboards
2. Create new report using report type created in Step 1
3. Add filter of "Dashboard running user" NOT EQUALS TO "Run as specified user"
4. Run the report. You will see it will only show Dashboard Running User with data equal to 'Let authorized users change running user' and 'Run as logged-in user'

Have fun...

Tuesday, January 28, 2014

Salesforce sharing button

Sharing is a nice feature in Salesforce to share a record to other user, particularly when 'Organization-Wide Defaults' is set to Private or 'Public Read Only'.

By clicking Sharing button, you’ll see who can see that record and why (sharing rule, group, role hierarchy, manual share etc).

The Sharing button allows users to extend the sharing of a record such as a lead, opportunity, or case. However, the Sharing button only displays when appropriate.

The Sharing button is available when your sharing model is either Private or Public Read Only for a type of record or related record. For example, the Sharing button may appear on an account even though your sharing model for accounts is Public Read/Write if your sharing model for related opportunities is Public Read Only.

But, sometimes Sharing button is not shown for particular user, while administrator able to see the button in the same page layout. So many admins think, user not able to see the button because user do not have permission set in the profile assigned, and this is not correct.

Sharing button only will be shown if user fulfill following criteria:
- administrator
- the record owner
- users in a role hierarchy above the record owner, and
- users that has been granted with “Full Access” sharing
If user is not one of them, he/she will not see the button, although user able to access the record.

Furthermore, sharing is disabled if the sharing model is set to "Controlled By Parent", which happens when you create a master-detail relationship.

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

Sunday, January 26, 2014

Salesforce how to check schedule reports 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.

Hope this tip make your life easier and have a nice day :)

Friday, January 24, 2014

Mass add member to Chatter group give 5,000 chatter free licenses, it is good for organisation to use Chatter for collaboration as it is free. But, adding hundreds or thousands of user as member into many Chatter group is not fun, if do it manually.

If you are Salesforce admin, here tip to make your life easier :)
You can use Data Loader to mass add member to Chatter group.
1. Open Data Loader and select Insert
2. Select "Show all Salesforce objects"
3. Select "Chatter Group Member"

4. Map "CollaborationGroupId" to Chatter Group Id and MemberId to User Id

Done, easy...?

Chatter: can user delete feed or comment?

Sometimes user need to delete feed or comment post to Chatter, but can user (read: not system administrator) to delete it?

Yes, you can delete posts and comments that you and others have made in these areas: on the Chatter feed on your profile, on records you own, and on posts and comments you’ve made in other feeds. Deleted posts disappear from your feed and you can’t restore a post after you delete it. Users with the “Modify All Data” permission can delete all posts and comments.

Here a quick summary:

  • User can delete their own comment
  • User can delete other people comment to their feed
  • User cannot delete other people feed

But, if you are group owner of a group, you can do more things within the group:

  • User can edit group setting
  • User can edit group information
  • User can edit group description
  • User can delete people comment to group
  • User cannot delete feed on group change

Extra: use @username to show your post on people feed, even that people is not following you, and follower of that user will see that feed as well.

Thursday, January 23, 2014

Salesforce searchable field type

As Salesforce admin, sometimes we confuse when user come to us and ask, why my data is not searchable using Phone number or Email or a custom field with type equal to picklist .

Here is guide on searchable field type in using Salesforce Global search:

  • All custom auto-number fields and custom fields that are set as an external ID (You don't need to enter leading zeros.)
  • All custom fields type: email and phone
  • All custom fields type: text, text area, long text area, and rich text area
If you see FORMULA field is not searchable, it make sense because formula field is not stored in database.

For complete searchable fields by object, see this documentation and tips from Salesforce

Tuesday, January 21, 2014

Salesforce: Master-Detail relationship (in detail)

One of the most advantage using Master-Detail relationship compare to Lookup relationship is Roll-Up Summary. In Master-Detail relationship, we can create roll-up summary field in master record to summarize / count data from child records.

Few items to notice when create roll-up summary field:
Parent record is locked
If you implement approval process with lock record, user will not able to create new child record if Sharing Setting in Master-Detail option is Read/Write.
Solution: change the sharing setting to Read Only.

Validation rule on parent record
If user not able to edit parent record and you have roll-up summary in parent record, user will not able to create or edit child record.
Solution: remove roll-up summary fields.

Can I change field type from Master-Detail to Lookup relationship?
Yes, make sure there is no roll-up summary fields in parent record, also in Deleted Fields (erase from Deleted Fields).

Can I change field type from Lookup to Master-Detail relationship?
Yes, all parent record should be not blank.

Monday, January 20, 2014

Salesforce Data Loader: sandbox and null values

If you are using Salesforce Enterprise Edition or Unlimited Edition, you can use Data Loader to import, update, delete or export data to/from any object in

In this blog, I'll give tips on:

Login to sandbox instance
By default, after Data Loader installed, it is configure to login to Production instance. You need to change in Data Loader setting to point it to sandbox instance.
Goto Settings and find "Server host (clear to turn off)", type:

Update fields to null
If you update a field to null / blank, and you notice the data is stay the same (not updated to blank), you need to change in Data Loader setting.
Goto Settings and find "Insert null values" and tick this checkbox.

Saturday, January 18, 2014

Salesforce Opportunity Roll-Up

Roll-Up summary field is a nice feature in Salesforce to summary data from child record to parent within Master-Detail relationship object.

Although, Account and Opportunity is standard Salesforce object, but we can set Roll-Up summary for many purposes.

Date of last won deal
We can easily know and display when closed won opportunity in Account page layout without complex triggers.
Create roll-up summary field in Account with
- Summarized Object = Opportunity
- Field to Aggregate = Opportunity: Close Date
- Filter Criteria = Won EQUALS True
- Summary Type = MAX

Number of Opportunities

Display number of opportunities in Account page layout

Create roll-up summary field in Account with
- Summarized Object = Opportunity
- Filter Criteria =
- Summary Type = COUNT

Number of Won Opportunities
Display number of won opportunities in Account page layout
Create roll-up summary field in Account with
- Summarized Object = Opportunity
- Filter Criteria = Won EQUALS True
- Summary Type = COUNT

Total Amount of Won Opportunities
Display total amount of won opportunities in Account page layout
Create roll-up summary field in Account with
- Summarized Object = Opportunity
- Field to Aggregate = Opportunity: Amount
- Filter Criteria = Won EQUALS True
- Summary Type = SUM

Win Percentage
Display percentage of closed won opportunity for account
Create formula field with return type = Percent, with 2 decimal place
Formula = IF(Count_of_Opportunities__c > 0, Count_Won_Opportunities__c / Count_of_Opportunities__c,0)

For more information on Roll-Up Summary, click here for Salesforce documentation.

Friday, January 17, 2014

Salesforce delete record: 'Insufficient privileges' error

In some organisation, user allow to delete records, but sometimes user get error message 'Insufficient privileges' although Delete button exist. What's happening? Let us analyze:

1. User able to access the record
There are few scenario:

  • Organization-Wide Defaults sharing setting on that object is Public Read Only or Public Read/Write; or 
  • Sharing rule added based on record owner or criteria for Public Groups or Roles; or
  • User is the owner of the records; or
  • User in higher role hierarchy of record owner; or
  • User profile have "View all Data" or "Modify all Data" permission

2. User see Delete button
This is happened because:

  • Delete button is added in record page layout; and
  • User profile have permission to delete that object

3. Error 'Insufficient privileges' 

The ability to delete records in Salesforce is controlled by the role hierarchy. Setting a sharing model to "Public Read/Write" alone does not give users the right to delete others records. There are 2 scenarios in which a user can delete a record:

  • The user attempting to delete the record is a System Administrator.
  • The user attempting to delete the record is the owner, or higher on the role hierarchy than the record owner.
Any other user that attempts to delete a record will receive an "Insufficient Privilege" error message.
Those below you on the role hierarchy may have read/write privileges according to the sharing model rules, however, they may not delete information from those individuals above them in that hierarchy. 

For complete knowledge from Salesforce, click here

Thursday, January 16, 2014

Cross-Object Workflow in

With Spring '12 Release, cross-object field updates in workflow rules and approval processes now support standard objects. Both custom-to-standard and limited standard-to-standard relationships are supported.
This feature only available in Enterprise and Unlimited edition.

Only Master-Detail relationship support using cross-object workflow, so cross-object will not work in Lookup relationship.

Here is a sample case for using Cross-Object workflow.
Master object: Customer__c
Child object: Order__c
Company would like to know how much and when latest order for each customer, but date use to determine is payment date, not order date, and payment date will be blank when customer have not make payment.

So, here we go:
1. Create a workflow in Order which will run on create and every time edit
Rule criteria:
NOT ISNULL( Payment_Date__c ) && 
( Payment_Date__c >= Customer__r.Payment_Date_hidden__c || 
  ISNULL( Customer__r.Payment_Date_hidden__c )) 
- if payment date is blank, stop workflow
- if payment date in order is newer than stored payment date in customer, proceed workflow OR
- if stored payment date in customer is blank

2. Create field update in Order
a. Update Payment Date in Customer
Select object = Order
Field to Update = Customer, the select field Payment Date
Formula Value = Payment_Date__c from Order

b. Update Amount in Customer
Select object = Order
Field to Update = Customer, the select field Amount

Formula Value = Amount__c from Order

If you see in above field update process, workflow in Order will update field in Customer.

For more information and standard objects supported, see this release document and find section Cross-Object Workflow.

Wednesday, January 15, 2014

Record Type in

If you are using with Enterprise, Unlimited and Developer edition, Record Type is a powerful feature. We can define business processes, picklist values, and page layouts to different users based on their profiles. So, even for the same object, user can have different page layout with different fields, different picklist value and etc.

But, sometimes admin overkill of Record Type usage. Not all condition need to use record type, may be a custom picklist field is enough. Please note that record type is different with a normal custom field and you cannot just edit record type like normal picklist field.

So, when do you need to setup record type?
1. Different Page Layout
Use record type to assign different page layout based on user profile. User will see different field position,  required fields, buttons, related list for the same records.

2. Different Picklist Value
Configure available values in picklist field depends on the record type.

3. Assign record type only for certain profiles
We can set available record type and default record type for each profile.

This is some scenario you do not need to setup record type actually
1. Use as Type picklist
This is very bad, do you notice once you have record type, when user create new record, they have to select a record type before come to the main screen, so please plan ahead correctly.

2. Page layout usage
Not always need to have record type to differentiate user not see some fields in page layout. Admin can adjust using field level security, so some profiles will NEVER see that field, in page layout, report, etc.

Once you have more record type, it is more effort need to maintain, so if possible use less record type.

Here link to record type cheat sheet from

Tuesday, January 14, 2014 Queue SOQL

Queues allow groups of users to manage a shared workload more effectively. A queue is a location where records can be routed to await processing by a group member. The records remain in the queue until a user accepts them for processing or they are transferred to another queue. You can specify the set of objects that are supported by each queue, as well as the set of users that are allowed to retrieve records from the queue.

Queues available in almost all edition, from: Contact Manager, Group, Professional, Enterprise, Unlimited, and Developer. Queues can be created for Leads, Cases, Knowledge article version, Service contract and custom object which not a child object in a master-detail relationship.

SOQL to query all queue:
Select g.Id, g.Name, g.Email from Group g where g.Type = 'Queue'

A queue can assign into one or  many objects, here SQOL to retrieve object assigned in a queue:
Select q.Id, q.QueueId, q.Queue.Name, q.SobjectType from QueueSobject q

Note: you can you Apex Data Loader to mass insert or mass update the data in Group and QueueSobject object.

Click here for help link for queues overview.

Monday, January 13, 2014

Custom Button to Pre-populate Field

Out-of-the-box, when we create a new record from a parent object related list, provide parent name pre-populated in child object. This is efficient and save time, user do not need to look for the parent record again.

This is good, but, often we needs more fields to be pre-populated to make our life easier :)
We can make this happen by create new custom button whether in related list or in page layout. In this sample, we'll use Account as parent and Opportunity as child.

Custom button in page layout
Go to Account object in Setup menu and choose Buttons and Links. Click New button and select option as screenshot below. Once button created, you need to add the button into Account page layout.

Custom button in related list
Go to Opportunity object in Setup menu and choose Buttons and Links. Click New button and select option as screenshot below. Once button created, you also need to add the button in Opportunity related list in Account page layout.

OK, now we understand how to create the custom button, but what is the URL??? Here we go...

If you click Opportunity button in related list, see the URL

So, what is the parameters and values? Let's parse it:

In this sample there is record type in Opportunity and we skip Record Type selection page. So, in custom button URL, we can enter:

Now, we want to pre-populate other field from Account. Here we go.. you need to find out the id of textbox from web browser, in page edit the record and right click at textbox then select Inspect element

Once you get the id, add it into the URL, so it will become:

Custom object
There is a different for custom object, see sample below:

Let's parse it:{object_prefix}/e?CF00N900000022p5z={parent_object_name}&CF00N900000022p5z_lkid={parent_object_id}&saveURL=/{parent_object_id}&retURL=/{parent_object_id}

Notice that parent name is CF00N900000022p5z and parent id is CF00N900000022p5z_lkid and a new parameter saveURL to redirect the page after user hit Save button.

Before you add more and more fields pre-populated, please consider this:
  • Sometime, the field in child object is not edit-able, consider to use formula field in child object
  • If the field do not need to copy from parent, you can hardcode it into the URL
  • To pre-populate data with some logic, you need visualforce page with apex code.
Video created by 'Christian Deckert' is simply good to guide you step-by-step in creating URL for your custom button.


Friday, January 10, 2014

Field Tracking

Out-of-the-box, provide field tracking for up to 20 fields for standard and custom object. For custom object, you need to enable it by edit the object enable "Track Field History".

Once enabled, you will find a button "Set History Tracking" in "Custom Fields & Relationships" related list. For standard object, it will be there by default.

From "Set History Tracking" button, select fields you want to track up to 20 fields and yay... DONE!!

But, for Long Text Area field, it will just show who changed the field and when, that's all, no more information about what is the prior value and new value :( :(

Using Workflow and Field Update we can achieve this:
1. Create another Long Text Area to hold the history, in this sample, let's call it Response History to store history of Response (a long text area field).

2. Create a workflow with criteria ISNEW() || ISCHANGED( Response__c ).

3. Create an immediate workflow action with field update to update Response History field using this formula: 

"Last Modified: " + LastModifiedBy.FirstName + ' ' + LastModifiedBy.LastName + ' ' + 
TEXT(DAY(DATEVALUE(LastModifiedDate))) + '/' + 
TEXT(MONTH(DATEVALUE(LastModifiedDate))) + '/' + 
TEXT(YEAR(DATEVALUE(LastModifiedDate))) + ' ' + 
CASE(LEFT(RIGHT(TEXT(LastModifiedDate),FIND(" ", TEXT(LastModifiedDate))-2),2), 
LEFT(RIGHT(TEXT(LastModifiedDate),FIND(" ", TEXT(LastModifiedDate))-4),6) 
+ BR() + "------" + BR() + 
PRIORVALUE( Response__c ) + BR() + 

Please note this formula only work with user without daylight saving timezone, because it will manually parse the time from GMT to your timezone (in sample above to GMT+8), because as of now do not have TIMEVALUE() function yet. Otherwise, you can only capture the date OR display it in GMT timezone.

Here is the screenshot of the result:

Thursday, January 9, 2014

Master-Detail relationship

Sometimes new admin confuse what is the different between Master-Detail relationship with Lookup relationship, since both are used to link an object to another object and you can use parent-child relationships in SOQL queries.

But, fundamentally it is different between Master-Detail with Lookup relationship. Few items you need to notice on Master-Detail relationship:

  1. When a record of the master object is deleted, its related detail records are also deleted.
  2. The owner field on the detail object is not available and is automatically set to the owner of its associated master record.
  3.  The detail record inherits the sharing and security settings of its master record.
  4. The master-detail relationship field is required on the page layout of the detail record.
  5. By default, records can’t be reparented in master-detail relationships. Administrators can, however, allow child records in master-detail relationships on custom objects to be reparented to different parent records by selecting the Allow reparenting option in the master-detail relationship definition.
  6. You can define master-detail relationships between custom objects
  7. You also can define master-detail relationships between a custom object and a standard object, but the standard object cannot be on the detail side of a relationship with a custom object. In addition, you cannot create a master-detail relationship in which the User or Lead objects are the master.
  8. You can display detail object record as custom related list on master object page layouts.
  9. You can use master-detail relationships to model many-to-many relationships between any two objects. Maximum 2 master-detail relationship from a custom object.
  10. If the object is master of 1 or more detail object, you only can have 1 parent object for that object.

Wednesday, January 8, 2014 hidden features come with tons of features for administrator to make everything their live easy. But, do you know if there is more features which is not enable by default. You need to contact support to enable it.

Here are few features a consultant usually love:

1. Ability to update created date and last modify date

This sound crazy? But yes, sometimes you need this when do initial data migration to

2. Extended Mail Merge

With Extended Mail Merge, you can easily generate mail merge Word documents

3. Person Account

For some companies, not all of your customer is company, instead just linked all contacts to the same Account, you can request to enable Person Account.

4. Remove Price and Quantity fields from Opportunity Product

Rather than always put 1 to quantity, you can request support to remove price and quantity from opportunity product page layout

5. Upgrade file attachment size to 10 MB

By default, user allowed to upload file to attachment maximum 5 MB per file, but we can request to make it to 10 MB.

6. Email Relay

We can request all email send out from via our email server for audit or security purpose or etc, we can enable this feature.

7. Encrypted Field

Some confidential data, like credit card number; need to be protect. User should not able to see the information unless he is the person needed. With enabling encrypted field, admin can manage only some users able to view sensitive data, but be aware that encrypted field is not searchable and the data length is shorter.

8. Multi Currencies

Multi national companies will get most advantages once multi currency enabled. We can use it in opportunity, forecast in reports.

9. Sandboxes

Number and type of sandbox is depend on your edition, but you can purchase sandboxes as needed.

For complete information, please check this wiki.

Tuesday, January 7, 2014

Enable Inline Editing in Salesforce View List allow user to edit data directly in page layout when your System Administrator enable inline editing feature, except for read-only and system fields. Editable fields display a pencil icon (Editable Field) when you hover over the field, while non-editable fields display a lock icon (Uneditable Field).

On top of inline editing in page layout, user also can do inline editing in view list without any effort to write any code. Many system administrators may not aware of this feature and sometimes it will not work because of some conditions.

Here are the items need to check for inline editing in page view:

1. Enable Inline Editing and Enhanced Lists

Go to Setup - App Setup - Customize - User Interface

2. Do you have record type for specific object?

If the object has multiple record types, you have to filter the view only showing 1 record type. So if you have 3 record type for Contact, you need to create 3 view. Please note, you should not have more than 1 record type in the record type filter, otherwise inline editing in view will not work.
If there is no record type for that object, you can skip this filtering.

3. Do not add Filter Logic

You are not allowed to use filter logic to enable inline editing in list view. 

4. Not for User object
As of now, this is not available for user object, please vote in IdeaExchange.

5. Edit multiple record at the same time
This feature is very nice if you need to edit many records to a same value. Select checkbox at the left of records and click pencil icon. Then select apply changes to all selected records.

Monday, January 6, 2014

Can we set user password?

As administrator we often get questions from our user, " can you check what is my password? ", hmmmm, user often think it like some legacy application, where system admin able to see or modify it as we would like.

But, is different, system admin not able to see or modify user password. We often offer the user to reset their password, where will email that user with instruction to set new password by  their own. And it works fine.

But again, some user (read: senior management) not really want to bother by checking email and so on, even this request is come from his/her secretary. So how? No problem, we can change user email to her secretary email, secretary confirm email change in her email (although also will email to the original email that her email has been changed to new one), and reset password. It works, yay!!! 

But, how to change back the user email to the original one? The senior management need to click a link again in her email (she may have hundreds or thousands email never read). Problem.

So, I just start think if there is a way just to set user password easily without have to go through such complicated process? It is worst when the user is going to present something in 5 minutes and we do not have much time to do above process. So, can we set user password?

YES, it is possible, even this function is not exist in configuration menu, but kind enough to provide system admin with Developer Console. Yes, Developer Console is for developer as it named, but admin also can use it for other purpose, like set user password, yay!!!

How? It is easy, just few click and copy paste a sentence will do. Go to your name and click Developer Console menu. 

It will open new window and you need to copy and paste below sentence to the Execute textbox.

System.setPassword('00550000000rlrX', 'hello123');

00550000000rlrX is the User Id, if you are admin, you should now this Id well, and hello123 is the new password to set.

But, if you still too lazy to check the User Id and user pass you their username, you can just copy and paste statement below into Apex Code execute box:

System.setPassword([SELECT Id FROM User WHERE username=''].Id, 'hello123');

Below sample script to set password of all active user in one go:

List<User> userList = new List<User>();
userList = [SELECT Id from User WHERE IsActive = true];
for (User u : userList)
    System.setPassword(u.Id, 'hello123');
    System.debug('DONE: ' + u.Id);

You can modify SOQL above as the need, may be to include only profile = "Sales Rep".

All statements above is apex code, but we can utilize it for other productive work as needed. 

Saturday, January 4, 2014

Can we set standard field to be unique? administrators and consultants often get business requirement to set standard fields to be unique. In some companies, we need to make sure every mobile phone in Contacts have to be unique (if it is not blank).

And as we known, until now, we are not able to set standard field be unique, only custom field have this option.

So, any solution? Yes, there are few ways to get this done, from a simple one until a difficult one.

1. Trigger
Using trigger may be not a simple way, we need a developer who well understand Salesforce Apex Trigger. Developer need to write apex code in Developer instance or Sandbox instance, make sure the test method coverage above 75% and deploy it to production. 

Developer may be like this approach, although it is not something prefer by administrator. Trigger in Contact before insert and before update, query all Contact with mobile phone enter, if query result is > 0, add error and don't allow to save or update that Contact.

2. Using new custom field

Instead of using standard standard field, using the same sample as above, we can build a new Mobile Phone field with field type = Phone. And hide standard Mobile Phone field from Field-Level Security (Prefer not just hide from Page Layout).

But, if this is an enhancement, it would be more effort to extract existing data to external file, verify there is no duplicate value and import the data back to the new fields. This even become worst, if the standard field has been used in many places, such as: reports, apex code, workflow, etc.

3. Workflow + Field Update

This is prefer option with less effort. Create a new custom field and enable Unique option then hide it from page layout. Create a workflow with criteria ISNEW() || ISCHANGED(MobilePhone). Create immediate workflow action to update the new field above. 

This option may be more simple, but the error message shown is not clear as it is not say what field is duplicate, but just inform it is duplicate with which record, example: "Duplicate value on record: John Wood". User do not know exactly what is duplicate between Contact working on with John Wood, if we have more than 1 field need to be set unique. 

Salesforce converted currency in report

In Salesforce, there is a field type "currency", as a standard field for example Amount in Opportunity, and we can create custom field with data type is currency.

If you enable multi-currency in your Salesforce instance, if the currency is not in the Corporate currency, you will find the corporate currency show in parenthesis after the amount.

In report, you will find (converted) field for each currency field. You can add it into report as other normal field. But, you cannot add converted field as report filter (as of now).

Solution: use original currency as filter and add currency code. Example: USD 300, SGD 500, JPY 10,000 and etc.

Thursday, January 2, 2014

Salesforce: how to get Approval Process status

Out-out-the-box, has Approval Process featured with lock mechanism to lock record has been submitted for Approval, so user unable to change the record.

But, do not provide approval history by default. So, user cannot run a report to show which records has not approved or approved or rejected. Ideally, admin should configured field update actions to update a field with current status: pending, approved, rejected or recall.

I meet a situation where approval process has been setup and deployed for few months back, but later user  realize unable to run report on the approval status. How we can get the approval status for all opportunity has been submitted for approval? Is it possible, any workaround?

YES, as long as you are in Enterprise Edition or Unlimited Edition or Professional Edition with API enabled.

Here is the steps:
1. Run SOQL with query syntax Select p.TargetObjectId, p.Status from ProcessInstance p then  export into CSV or Excel file.

2. Export all Opportunity with Id into CSV or Excel file.

3. Open both files in Microsoft Excel as two worksheets, then use VLOOKUP() function in Microsoft Excel to get all Opportunity with approval Status from ProcessInstance object. Status should be: Pending, Approved, Rejected, Removed.

Good luck and hope this solution help

| ,