Category: SOQL

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

Fetch All Fields In SOQL Query | Spring 21 New Feature

 If you worked with SQL or any other query language before, you probably familiar with Select * from …, right? This is the way to fetch all fields from a particular table in other(mostly all) query languages. But in the Salesforce world, we were not having this feature. In SOQL, there is nothing with which we can fetch all the fields unless we specifically mention all the field names in the query.But now in Spring 21, Salesforce is bringing a function through which we can fetch all fields in SOQL Query.We have now FIELDS(ALL), FIELDS(STANDARD), and FIELDS(CUSTOM) which we can use in the SOQL query.FIELDS(ALL) – This fetches all the fields of an object. This is similar like Select * from SQL.FIELDS(STANDARD) – This fetches all standard fields of an object.FIELDS(CUSTOM) – This fetches all custom fields of an object.Here is how the query will look like -List<Contact> allConatcts = [SELECT FIELDS(ALL) FROM CONTACT LIMIT 200];In Subqueries, it will look like -List<Account> allAccounts = [ SELECT Account.Name, (SELECT FIELDS(ALL) FROM Account.Contacts LIMIT 200) FROM Account];BOUNDED AND UNBOUNDED QUERIESWhen API cannot determine the set of fields in advance, then it will consider query as Unbounded Query, other if API can determine the set of fields in advance, then it is Bounded Query. For example – The number of custom fields for an object is not pre-determined, so FIELDS(CUSTOM) and FIELDS(ALL)...

Read More

SOQL Query Builder in VS Code, the fastest way to write SOQL statements

Salesforce introduced the fastest way to write SOQL statements, the SOQL Query Builder. SOQL Query Builder is the Visual Studio Code Extension that enables you to write, execute SOQL statements write inside your project workspace. Not only that, this tool will allow you to download the query result in either CSV or JSON format.How to install SOQL Query BuilderOnce you open the Visual Studio Code, search for SOQL in Marketplace. From there install the SOQL extension from Salesforce. After installation, it is always recommended to restart VS Code. Below is the screenshot How to use SOQL Query BuilderAfter installation, all your soql files will be stored under the folder scripts -> soql. You can save your own soql commands, just make sure the extension of the file is .soql.Below is the example where I have opened account.soql file and executed the statement. As you can see, with this new tool, we have the opportunity to do everything we are doing today with workbench w.r.t. executing soql statements. Also, you can download the query result in either CSV or JSON format.Youtube VideoVideo Link: https://www.youtube.com/watch?v=LG13jPWYqVwReference...

Read More

Handy SOQL/SOSL Queries for Knowledge

 Happy Monday!! In my last post, I have explained the Data Model around Salesforce Lightning Knowledge. If you haven’t gone through that post, I highly recommend you going through the post “Understanding Salesforce Lightning Knowledge Data Model” as it is going to help you to understand the queries in this post.Let’s first create the article – “Hello World” and below are the different versions with different publishing status. Below article properties which I will be using the query -Article Number: 000001000Knowledge Article Id: kA03t00000063w2CAAFetching Latest Knowledge Article VersionSELECT Id,PublishStatus,Title FROM Knowledge__kav WHERE KnowledgeArticleId = ‘kA03t00000063w2CAA’ AND IsLatestVersion = TrueFetching Published Knowledge Article VersionSELECT Id,PublishStatus,Title FROM Knowledge__kav WHERE KnowledgeArticleId = ‘kA03t00000063w2CAA’ AND PublishStatus = ‘Online’Fetching Draft Knowledge Article VersionSELECT Id,PublishStatus,Title FROM Knowledge__kav WHERE KnowledgeArticleId = ‘kA03t00000063w2CAA’ AND PublishStatus = ‘Draft’Fetching Archived Knowledge Article VersionsSELECT Id,PublishStatus,Title FROM Knowledge__kav WHERE KnowledgeArticleId = ‘kA03t00000063w2CAA’ AND PublishStatus = ‘archived’ AND IsLatestVersion = FalseThis one is tricky, as to query Archived article versions, KnowledgeArticleId and IsLatestVersion = False and PublishStatus = archived should be mentioned.Note -Using bind variables with Apex SOQL Statements with KnowledgeArticleVersion is not allowed. So you need to use dynamic SOQL like below -final String ONLINE_ARTICLE = ‘Online’;final String myQuery = ‘SELECT Id FROM Knowledge__kav WHERE PublishStatus = :ONLINE_ARTICLE’;List<Knowledge__kav> allArtciles = Database.query(myQuery);Working with DATA CATEGORYWITH DATA CATEGORY is an optional clause in SOQL and it helps to identify articles linked with one or more...

Read More

Query Plan Tool from Salesforce – A Hidden Gem

Query Plan in the developer console can help to speed up SOQL queries done over large volumes of data. This is such a powerful tool when dealing with bad performance. Developer can use this tool to optimize and speed up SOQL queries.We can enable Query Plan in the Developer Console by -Before we go into details, it’s better to understand how Salesforce uses indexes while running the SOQL. As we all know it is always good to go for selective query instead of full table scan. But even with selective query, when we are using different filter criteria, it is not always the best SOQL query we are writing. The reason behind is that just writing where clause in the query does not guarantee that the filter is selective. So it is very important to understand which are the fields are good to be used in filter. Let’s understand that first in the below section -All we need to use is the indexes in the filter.If we are using filter on standard fields, then we are using index if -it is primary key (Id, Name, OwnerId)it is a foreign key (CreatedById, LastModifiedById, Lookup, Master-Detail)it is an audit field (CreatedDate, SystemModstamp).If we are using filter on custom fields, then we are using index if that field is marked as Unique or External Id.Without indexed filter, the query will not be...

Read More
Loading