Salesforce login parameter

As normal user, we access Salesforce.com from web browser https://login.salesforce.com to access production instance or https://test.salesforce.com to access sandbox instance (if you have any).

But, some organisations need to access Salesforce directly from another application (and not implemented SSO), we can add username and password include as login parameters to direct login to Salesforce, here we go:

https://www.salesforce.com/login.jsp?un=user@email.com&pw=secret

The same applied for sandbox:

https://test.salesforce.com/login.jsp?un=user@email.com&pw=secret

You also can redirect user to a specific record, such as: account, contact or report:

https://test.salesforce.com/login.jsp?un=user@email.com&pw=secret&startURL=/00190000005lXMZ

NOTE: Since your password is in the URL, everyone could see it, so be cautious.

Salesforce Searchable Field

As Salesforce admin, sometimes we confuse when user come to us and ask, why a record not return in search result when I search Phone Number or Email or a custom picklist field.

Here is the guide on searchable field types in using Salesforce global search:

• All custom auto-number fields and custom fields that are set as an external ID field (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

You see that FORMULA field is not searchable, it make sense because formula field is not stored in database.

When user search for a value, for records contained that value, global search result will include those records, even field contained for the search value is exist in a field that are not visible for that user, because of field-level security setting. Ideally, if user cannot see the value on that field, it should be not search-able for that particular user, vote for this Idea, currently it have only 160 point (9/9/15).

However, not all standard field is searchable, example: for Account only following fields are searchable:

• Account Name
• Account Name (Local)
• Account Number
• Account Site
• Billing Address
• Description
• D-U-N-S Number (This field is only available to organizations that use Data.com Prospector)
• Fax
• Phone
• Shipping Address
• Ticker Symbol
• Website
• 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 of type text, text area, long text area, rich text area, email, and phone

For complete searchable fields by object, see this reference below.


Reference:

• Which Fields Are Searchable?
• Searchable Fields: Business Account

Salesforce converted currency in report

In Salesforce, there is a field type "currency", for example, Amount in Opportunity, and we can also create custom fields with the data type of currency.

If you enable multi-currency in your Salesforce instance, and if the record currency is not in your user currency, you will see your user currency show in parenthesis after the amount.

In the report, you will find the (converted) fields for each currency field. You can add it to the report like another normal field. But, you cannot add the converted field as a report filter (as of now).

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

How to add single quote at beginning and end of word

For you who do query a lot to a database, from: MSSQL, Oracle, including Salesforce.com, you may need to think is there anyway to add single quote at beginning and end of words and add comma after end of word. It is very time consuming if you need to add it manually if you want to query for 500 Id.

You can do this much faster using tool, such as TextPad, download from www.textpad.com and install it.

Let say you need to query for Id from an Excel file. Copy and paste the Ids into TextPad, example:

Go to Search menu and select Replace... or F8 for shortcut
in Find What: .+
in Replace With: '\0',
in Conditions: select Text and Regular expression 
and click Replace All.

And the result:

You may need to remove comma in last line. So you can put this Ids into your query filter, example:
SELECT Id, Name, Email FROM Contact Where Id IN ('0038000000zYkACAA0',
'0038000000zYkE5AAK',
'0038000000zYkJWAA0',
'0038000000zYlBIAA0',
'0038000000zYl8xAAC',
'0038000000zYkKSAA0'
)


If you are using Notepad++ since it is free, change to this:
in Find What: .+
in Replace With: '$0',
in Search Mode: select Regular expression 

Salesforce: How to get Approval Process status

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

But, Salesforce.com 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.

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

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. I wrote a blog earlier for Master-Detail relationship. This blog on roll-up summary field and the field type.

More items to notice when create roll-up summary field:
Parent record is locked
If you implement approval process with lock record in master object, 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 because of validation rule, 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.

Salesforce: report folder

This blog related to Salesforce report folder access and permission, as of Winter '13 release. 

Manage Public Reports permissions

Anyone with "Manage Public Reports" permissions can overwrite (edit and delete) anyone else reports in Public Reports folder.

Public Folder Access

Read/Write access allows user to save new report to that public folder, but not to Delete or Overwrite, even reports are created by user himself. User able to Edit the report, but have to Save As new report name.

Read Only access allows user to read report from that specific public, but not able to Save into that folder. 

User with higher role hierarchy able to access reports accessible by his subordinate, although the report folder is not visible. But user will not able to Save or Save As to the same folder, user only able to Save into personal folder or public folder that he has Read/Write access.

For more information, click here

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 Salesforce.com. In this blog, we will share 2 tips:

Login to sandbox instance
By default, after you install Data Loader, the default setting is configure to login to Production instance. You need to change in Data Loader setting to point it to sandbox instance.
Go to Settings and change the default value of "Server host (clear for default)" from https://login.salesforce.com to https://test.salesforce.com

Update fields to null
If you update a field with blank value, if the value have existing value, notice the value will stay the same (not updated to blank). To overwrite existing value with blank, you need to change Data Loader setting.
Go to Settings and select "Insert null values".

This option is not available if the "Use Bulk API" option is selected. Empty field values are ignored when you update records using the Bulk API. To set a field value to null when the Use Bulk API option is selected, use lue of  #N/A  in the CSV file.

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.

Salesforce: Useful Formulas related to Date

Here are some useful formula related to date. In this sample, I have a custom field call Execution Time with data type = Date/Time

Find me Execution Date
Create a formula field with return type = Date --> Execution_Date__c
DATEVALUE(Execution_Time__c)


Find me last date of the month of Execution Date
Create a formula field with return type = Date
CASE( 
MONTH(Execution_Date__c), 
1, DATE( YEAR( Execution_Date__c ), 01, 31), 
2, DATE( YEAR( Execution_Date__c ), 02, IF( MOD( YEAR(Execution_Date__c), 4) = 0, 29, 28)), 
3, DATE( YEAR( Execution_Date__c ), 03, 31), 
4, DATE( YEAR( Execution_Date__c ), 04, 30), 
5, DATE( YEAR( Execution_Date__c ), 05, 31), 
6, DATE( YEAR( Execution_Date__c ), 06, 30), 
7, DATE( YEAR( Execution_Date__c ), 07, 31), 
8, DATE( YEAR( Execution_Date__c ), 08, 31), 
9, DATE( YEAR( Execution_Date__c ), 09, 30), 
10, DATE( YEAR( Execution_Date__c ), 10, 31), 
11, DATE( YEAR( Execution_Date__c ), 11, 30), 
12, DATE( YEAR( Execution_Date__c ), 12, 31), 
null 
)

This is another simplify formula and will get the same result
IF( 
MONTH( Execution_Date__c ) = 12, 
Date( YEAR( Execution_Date__c ), 12, 31 ), 
Date( YEAR( Execution_Date__c ), MONTH ( Execution_Date__c ) + 1, 1 ) - 1
)


Find me last date next month of Execution Date
Create a formula field with return type = Date
CASE( 
MONTH(Execution_Date__c), 
1, DATE( YEAR( Execution_Date__c ), 02, IF( MOD( YEAR(Execution_Date__c), 4) = 0, 29, 28)), 
2, DATE( YEAR( Execution_Date__c ), 03, 31), 
3, DATE( YEAR( Execution_Date__c ), 04, 30), 
4, DATE( YEAR( Execution_Date__c ), 05, 31), 
5, DATE( YEAR( Execution_Date__c ), 06, 30), 
6, DATE( YEAR( Execution_Date__c ), 07, 31), 
7, DATE( YEAR( Execution_Date__c ), 08, 31), 
8, DATE( YEAR( Execution_Date__c ), 09, 30), 
9, DATE( YEAR( Execution_Date__c ), 10, 31), 
10, DATE( YEAR( Execution_Date__c ), 11, 30), 
11, DATE( YEAR( Execution_Date__c ), 12, 31), 
12, DATE( YEAR( Execution_Date__c )+1, 01, 31),
null 
)


Find me calendar week of Execution Date
Create a formula field with return type = Number
CEILING(((DATEVALUE(Execution_Time__c) - DATE(YEAR(DATEVALUE(Execution_Time__c)), 1, 1) + 1) + MOD(DATE(YEAR(DATEVALUE(Execution_Time__c)), 1, 1) - DATE(1900, 1, 7), 7)) / 7) - 1 

Another option:

CEILING((DATEVALUE(Execution_Time__c) - DATE(YEAR(DATEVALUE(Execution_Time__c)), 1, 4) +1 ) / 7)

*** to confirm week is correct, use this website http://www.calendar-365.com

*** 1 Jan 1900 = Monday

Find me day of the week from Execution Date
Create a formula field with return type = Text

CASE( MOD( Date__c - DATE(1900, 1, 7), 7),
0, "Sunday",
1, "Monday",
2, "Tuesday",
3, "Wednesday",
4, "Thursday",
5, "Friday",
6, "Saturday",
"Error")

*** 7 Jan 1900 = Sunday


Find me the day of Execution Date

Create a formula field with return type = Number

DAY(Execution_Date__c)

Find me number of day between Request DateTime with Execution Date Time

Create a formula field with return type = Number

DATEVALUE(Execution_Time__c) - DATEVALUE(Request_Time__c)


Find me first date of the week of record Creation Date

Create a formula field with return type = Date

DATEVALUE(CreatedDate) - MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7)
This formula will return Sunday as the first date of the week.

Find me first date of the week of record Creation Date, with MONDAY as the first date of the week

Create a formula field with return type = Date

CASE( MOD( DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7), 0, 
DATEVALUE(CreatedDate) - MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7) - 6, 
DATEVALUE(CreatedDate) - MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7) + 1)
*** Case result 0 = Sunday


Reference: 

• Common Date Formulas
• Sample Date Validation Rules

Last update: 20 Jun 2016

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.