Category: formula

How to Query Multi-Currency Fields in Salesforce

 In continuation to my earlier post about setting up multi-currency in Salesforce, this post will focus on how to query the multi-currency fields in SOQL and some cool formula features which you can use while dealing with multi-currency fields.SetupLet’s do the setup first.The corporate currency set in the Salesforce org is the Canadian Dollar i.e. CAD.Apart from the Canadian Dollar, other currencies supported are – U.S. Dollar (USD), Swiss Franc(CHF), and Indian Rupee(INR).Rates are also uploaded in the Salesforce org as shown below -Scenario:Opportunity record is having the currency field named “Opportunity Amount”. We need to fetch the Opportunity Amount based on the user’s currency mentioned in their user record.Solution:In order to fetch the Opportunity Amount based on the user’s currency, we can use Format() and convertCurrency() functions in SOQL.Format(): This function can be used in SOQL select statement to format date, time, and currency fields based on the given user locale.convertCurrency(): This function can also be used in SOQL select statement to convert the currency fields to the user’s currency.Using the above two functions here is the SOQL statement:SELECT Name, CurrencyIsoCode, Amount,FORMAT(convertCurrency(amount)) ConvertedinUserCurrency FROM Opportunity WHERE Name = ‘Burlington Textiles Weaving Plant Generator’Here is the result:As you can see, even though in this particular opportunity,  the amount is saved in USD, but with the help of above two functions, we are able to convert the currency to the...

Read More

Service Console – How to open the detail page by clicking on hyperlink(formula field) in Sub-Tab

This post will explain how to open the detail page in a sub-tab after clicking on the hyperlink.Recently I faced one problem where I need to open the details page of a record in sub-tab inside Service Console by clicking on the hyperlink.Normally if you have any lookup field in service console layout, the field will appear as hyperlink and clicking on the link will open the detail record in sub-tab. But in my case, I need to make a custom formula field with return type as Hyperlink. Making a formula field as hyperlink is very easy and if you add the field in the page layout, that field will appear as hyperlink. But the problem comes when you click on the link. Clicking on the link will open the page in another browser tab, which is a little odd. User may feel that they are thrown out of the service cloud console by clicking on a record.So to improve this user experience, we need to open the detail page in the sub-tab of service console. Below is the formula field which display “Product Name” in the page layout as hyperlink and clicking on that link will open the product detail page in sub-tab by passing the product id.HYPERLINK(“javascript: if(typeof(srcUp)==’function’) { srcUp(‘/” & Product2.Id & “?isdtp=vw’); }”+ ” else { window.location.href=’/” & Id & “?isdtp=vw’ }”,Product2.My_Product_Name__c, “_self”)Sharing the information...

Read More

“The Power of One” in Salesforce Formula Field

Most administrators think formula field is a way to do calculation in the pages such as finding out the final price after deduction, calculate date operation etc. But in Salesforce, formula field can be utilised to do more powerful stuffs. In today’s post, I will explain the “The Power of One” in Salesforce Formula Field.Business Case -Company “Universal Bank” is using Salesforce’s out of the box contact and activity standard object to track the each contact’s activities. Now a contact can have multiple activities. So the requirement is to run a report which will tell the total number of distinct contacts having some activities. Implementation -Now if you run a report, you will get something like this below -Now if you see the report is showing grand total as 12 as we have many contacts having more than one activities. But our requirement is to show total number of distinct contacts having some activities.And here comes “The Power of One”. This will help us to achieve our requirement. First we need to create a formula field in Contact object as shown below -Point to be noted – the value of the formula is field is 1 and that is why the name is “The Power of One”. Cool.Now add this field in the report and do Summarize by this field using Sum option. Picture is shown below.One done, save and run...

Read More

Display Progress Bar using Formula Field in Salesforce – No coding is required

In this post, I will show how you can create a progress bar just using formula field in Salesforce. No coding required.The requirement is that for “Job Application” object, we have a picklist called “Status” which is having the values as -Based on the value chosen in pick list, we need to display the progress bar showing the progress of Job Application. So let’s do that -Step 1 -We need to upload two image files as Static Resources first. Yellow bar -Green bar -Step 2 -Create a field called in the Job Application object which will calculate the progress based on the values chosen in the Status field. The screenshot given below -Step 3 -Create another formula field to display the progress. The detail of the field is given below -Done.!!Let’s check how our Job Application page looks like -When Status = ‘New’ -When Status = ‘Review Resume’When Status = ‘Phone Screen’When Status = ‘Schedule Interview’When Status = ‘Extend an offer’When Status = ‘Hired’ / ‘Rejected’Great. Any feedback, please let me know....

Read More

Link in a formula text field – Explained with Use Case

Use Case:I have three objects-·         Application User·         Bank·         Credit Card.Application User is having master detail relationship with both Bank and Credit Card where in both the cases Application User is on the master side and Bank/Credit Card are on the details side.Schema is as below –Now when I click on the Credit Card tab, I would like to display the Application User’s detail in the list. So I need to credit a new view to include the Application User detail. I did that (View Name: All Credit Card)and below is what I can see-Now the problem is – with the above view, from “Credit Card Holder” column, I am not able to identify the Application User. I need to click on individual “Credit Card Holder” entry to go to the details page of the Application User object and then identify the user. OMG!!! Definitely not a good and efficient way. We have to be smart. So let’s think about something innovative –Why not display the “LastName, FirstName” in the Credit Card Holder column so that from this page only, I can understand the owner of the Credit Card. Great good idea. Let’s see how we can do that –Let’s create a formula field which will fetch the Application User’s LastName and FirstName and populate like below-Now let’s add the field in the previously created view i.e. All Credit Card....

Read More