How to use Integration Value in State & Country picklist in Salesforce
Recently there was an interesting requirement in using State & Country picklist of Salesforce. Salesforce saves ISO code of State & Country picklist in Alpha-2 format however external system connecting to Salesforce needed Alpha-3 format.
There has been already an Idea on Idea Exchange created around 4 year back to support the Alpha-3 format however doesn’t seems much progress from Salesforce product team.
Anyways, there could be multiple way to handle this like, Maintaining Custom Setting , Custom metadata or Custom object for mapping between ISO Alpha-2 and Alpha 3 and use trigger to populate custom ISO field on Account or Contact.
While going through implementation guide of State & Country picklist, there was mention of Integration Value setting, which could be used to store the ISO Aplha-3 code. To see this option, navigate to “Setup | Data Management | State and Country/Territory Picklists | Configure States and Countries and Territories | country name | Edit” . Below image shows the setting
Step by step guide to set up Continuous Integration (CI) for Salesforce using Team Foundation Server (TFS) with video tutorial
I have already written some Continuous Integration (CI) posts for Salesforce previously using different tools like Jenkins. In this blog post we will go through steps to use Microsoft Team Foundation Server (TFS) to set up Continuous Integration.
We can either use cloud based Team foundation Server (TFS) or locally installed on network. For ease, we would be using cloud based TFS for this blog post.
Step 1: Creating developer account on VisualStudio Online
Navigate to https://www.visualstudio.com/ and choose Get Started for free in Visual Studio Team Services section. You may need to create a new Microsoft developer account, if you don’t have it already.
Step 2: Using TFS as source code repository
Once we are able to login to TFS, lets start by creating a code repository. That’s right, you don’t need separate Bitbucket or Github account to save your code/metadata unlike in Jenkins. So, our start with TFS is really good and impressive till this point 🙂 . Wizard to create new project is self explanatory and would look like below image
List of my favorite features in Salesforce Spring 17
In this post, I would be only talking about Generally available (GA) features, excluding pilots and Beta. Saving those awesome features for suitable time to come. So, here are some of my favorite picks from Spring 17.
Imagine we are doing REST API call from external system by creating Account first and then contact. In between if internet connection is gone or user closes browser and only Account created without contact ? From Spring 17, we can reduce number of REST API calls made by combining them in single request. Advantage of this feature would be easier transaction management and API limit saving. You can have up to 25 subrequests in a single call. Up to 10 of these subrequests can be query operations, including Query, QueryAll, and “Query More” requests to obtain the next batch of query results. Continue reading “Salesforce Spring 17 release – My favorite features”
T-SQL Script to read all files in directory and store back in table – SQL Server
Recently I was in need to analyze Salesforce debug log for one stubborn issue which was very hard to reproduce. Was able to download 1500+ debug logs on my system, however to analyze it, I decided to take help of SQL Server.
Very soon I came into challenge to read all files in a directory and store it back in SQL Server table.
T-SQL Scripts to Export Blob or Binary data stored in SQL Server
I tried many ways to export files or documents saved as binary (Blob) datatypes in SQL Server. However, finally came up with below solution which worked very well. Below script was used to export around 25GB of files stored in SQL Server.
To understand this, lets create a table in Database which will store files from local system into SQL Server as binary / Blob .
If we try to see content in actual table, it will look like
Export Blob From SQL Server and save it as a file
For demo purpose, we want to save documents on local disc. We will use Doc_Num to be created as folder and document will be saved in that folder. To create folders using SQL Server, we will use stored procedure CreateFolder created in this post. Note : We will create only one folder per document. If multiple nested folder needs to be created then we need to iterate through each folder and call CreateFolder stored procedure for each folder. Its explained in this blog post.
Now time to see actual T-SQL which will iterate through all documents, create folder and save Blob as a file on local disc.
DECLARE @outPutPath varchar(50) = 'C:\G2\My POC\Blog\SQL Server\Extract Blob'
, @i bigint
, @init int
, @data varbinary(max)
, @fPath varchar(max)
, @folderPath varchar(max)
--Get Data into temp Table variable so that we can iterate over it
DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num] varchar(100) , [FileName] varchar(100), [Doc_Content] varBinary(max) )
INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])
Select [Doc_Num] , [FileName],[Doc_Content] FROM [dbo].[Document]
--SELECT * FROM @table
SELECT @i = COUNT(1) FROM @Doctable
WHILE @i >= 1
@data = [Doc_Content],
@fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName],
@folderPath = @outPutPath + '\'+ [Doc_Num]
FROM @Doctable WHERE id = @i
--Create folder first
EXEC [dbo].[CreateFolder] @folderPath
EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
EXEC sp_OASetProperty @init, 'Type', 1;
EXEC sp_OAMethod @init, 'Open'; -- Calling a method
EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources
print 'Document Generated at - '+ @fPath
--Reset the variables for next use
SELECT @data = NULL
, @init = NULL
, @fPath = NULL
, @folderPath = NULL
SET @i -= 1
Variable @outPutPath stores root folder path, where folders will be created and Blob content would be exported into it as a file.
Demo and Example, showing process of documenting Lightning Component
Its extremely important to keep and maintain the documentation of your newly built (and all existing) Lightning component. So that it can be easy to understand and easy to reuse. It will not take much time for your Salesforce Org to flood with Lightning components.
We will take example of previous post, where I shared source code of Circular Progress Bar Lightning Component.
Total – Either Number Or API Name of field. Used to derive percentage of Progress Bar
Actual – Either Number Or API Name of field. Used to derive percentage of Progress Bar. If Object contains percentage type of field, then Total can be blank and this field can only contain API name of field of type percentage
Search complete Database in SQL Server for some value
Recently, I was in need to search complete Database for some value and found below very useful script (T-SQL)
DECLARE @SearchStr nvarchar(100) = 'SEARCHSTRING'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
SET @ColumnName = ''
SET @TableName =
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
) = 0
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
SET @ColumnName =
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND QUOTENAME(COLUMN_NAME) > @ColumnName
IF @ColumnName IS NOT NULL
INSERT INTO @Results
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 4000)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
SELECT ColumnName, ColumnValue FROM @Results