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.