State & Country picklist in Salesforce supporting Alpha-3 ISO code

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

State & Country Picklist in Salesforce
State & Country Picklist in Salesforce

Continue reading “State & Country picklist in Salesforce supporting Alpha-3 ISO code”

Continuous Integration (CI) in Salesforce using Team Foundation Server (TFS) – Video

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

Visual Studio Team Foundation Server - New Project Screen
Visual Studio Team Foundation Server – New Project Screen

Continue reading “Continuous Integration (CI) in Salesforce using Team Foundation Server (TFS) – Video”

Salesforce Spring 17 release – My favorite features

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.

Composite Resource

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”

SQL Server – Read all files in directory and store in Table

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.

Found very good article here which helped me to come up with below queries. If you need to read more in detail about below T-SQL and stored procedures please navigate to original article. Continue reading “SQL Server – Read all files in directory and store in Table”

Export Documents saved as Blob / Binary from SQL Server

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 .

CREATE TABLE [dbo].[Document](
	[Doc_Num] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[Extension] [varchar](50) NULL,
	[FileName] [varchar](200) NULL,
	[Doc_Content] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

How to Insert Blob into Database

For demo purpose, we will save two files in our table using below queries

INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'pdf', 'Salesforce Lightning.pdf',[Doc_Data].*
FROM OPENROWSET 
    (BULK 'C:\G2\My POC\Blog\SQL Server\Source\lightning.pdf', SINGLE_BLOB)  [Doc_Data]

INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'html', 'Progress.html',[Doc_Data].*
FROM OPENROWSET 
    (BULK 'C:\G2\My POC\Blog\SQL Server\Source\Progress.html', SINGLE_BLOB)  [Doc_Data]

If we try to see content in actual table, it will look like

Insert Blob into Database
Insert Blob into Database

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.

USE [POC]
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
BEGIN 

	SELECT 
	 @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
END

Variable @outPutPath stores root folder path, where folders will be created and Blob content would be exported into it as a file.

Below image shows output in action :

SQL Server Export Blob as File
SQL Server Export Blob as File

Circular Progress Bar with Conditional Theme – Salesforce Lightning Component

Salesforce Lightning Component – Circular Progress Bar with Conditional Theme. Ready to be used by Developers and Admins on App builder for any object. No External Javascript Library, Lightweight CSS based.

Just after two days of writing Circular Progress Bar , came up with few more requirements. So, In this post, will share updated code of Circular Progress Bar. This component can be easily used from Lightning App Builder, check this video to get an idea on how it can be used and configured.

Complete source code of this component is available on my git repository. Make sure to follow me to get updated by other free source codes. 🙂

In additional to all previous capabilities, below features are added:

  1. Conditional Theme – Let’s say before 50% progress bar should be displayed as red and after 50% green.
  2. Threshold – On basis of this value, theme will change
  3. Added one more theme – red
  4. We can show value inside component in three format now – percentage, Actual value or Mix
  5. Legend font size changes according to size of component

Continue reading “Circular Progress Bar with Conditional Theme – Salesforce Lightning Component”

How to Document Lightning Component

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.

First, we will create a new Lightning Component which will have possible examples of using Lightning Component. Continue reading “How to Document Lightning Component”

Circular Progress Bar – Salesforce Lightning Component

Demo and Complete Source code of Circular Progress Bar, Salesforce Lightning Component

In this blog post we will create reusable Lightning Component to show progress of record using Circular Progress Bar. This component is mostly build using CSS. Javascript is used only for Lightning component support and calling Apex Class. Check video demo here on how to configure and use this component.

Note – There is updated version of this component here.

Circular Progress Bar LEX Component Capabilities

  1. Size – small, medium , large
  2. Theme – blue, orange , green
  3. Legend – Legend to display
  4. Total – Either Number Or API Name of field. Used to derive percentage of Progress Bar
  5. 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

Continue reading “Circular Progress Bar – Salesforce Lightning Component”

SQL Server – Search complete database for value

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

BEGIN
    SET @ColumnName = ''
	BEGIN TRY
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
END TRY
BEGIN CATCH
END CATCH
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1) 
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName 
        )
         

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 4000) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

If we run above script for word ‘king’ then output in below format will be displayed Continue reading “SQL Server – Search complete database for value”

SQL Server – Create Folders using T-SQL & OLE automation

Tutorial to create folders in SQL Server using Transact-SQL and OLE Automation Stored Procedures

SQL Server has some standard stored procedures that allow OLE automation.

First step, is to check whether Ole Automation Procedures are enabled in SQL Server or not ? It can be enabled by simply executing below T-SQL commands.

T-SQL to enable Ole Automation Procedures in SQL Server

        sp_configure 'show advanced options', 1;
	GO
	RECONFIGURE;
	GO
	sp_configure 'Ole Automation Procedures', 1;
	GO
	RECONFIGURE;
	GO

Next step is to create a stored procedure, which will use an OLE Automation procedures and create a folder on system drive Continue reading “SQL Server – Create Folders using T-SQL & OLE automation”