SLAM_mapping_template

Overview

Creates a mapping template ready for copy and paste into a spreadsheet to use in data mapping work. The template is based on the latest field metadata for the object. The list of fields returned represents all fields on the objects, but some attributes may be subject to Salesforce permissions.

When copying the output for pasting into a spreadsheet, SQL Server Management Studio allows for the copying of the column headers with the data (right click and choose "Copy with Headers"). Doing this transfers all the column headings in the spreadsheet.

Out-of-the-box, three additional "empty" fields are added to the front of the output. These can then be used to added the source system table and field names along with any transformation required

Parameters

Name	Type	Default	Description
@sObject	VARCHAR	Required	Table to generate the template for
@dialect	NVARCHAR	'Excel'	Modifies rendering of values for different destinations. Possible values: 'Excel', 'GSheets'

Customizing Mapping Templates

The output of the SLAM_mapping_template command can be configured to meet custom needs, both in terms of the outputted columns and row order.

Customizing Output Columns

The output columns and relative position are customizable by modifying the data in SLAM_Settings.mapping_template_columns. The out-of-the-box configuration suppresses many Salesforce fields that may be of interest to you. The distinction between the (desc) and (part) fields are described in SLAM_sObject_fields

You can retrieve the current configuration by running the query below. Note it is recommended to enable the "Retain CR/LF on copy or save" option within SSMS (Tools->Options: Query Results/SQL Server/Results to Grid: Retain CR/LF on copy or save. Then open a new query window.)

SELECT mapping_template_columns FROM SLAM_Settings

You can set a new configuration by running the query below. This content becomes the "field select" clause of a SQL query and must therefore be a valid SQL clause. Single quotes must be properly escaped during the UPDATE statement as demonstrated in the example below (e.g. replace each ' with '').

UPDATE SLAM_Settings SET mapping_template_columns = '
    ''''                                         AS [Source Column Name]
   ,''''                                         AS [Transformation]
   ,''''                                         AS [Mapping Status]
   ,[Qualified API Name (part)]                  AS [Field Name]
-- add additional fields
'

Customizing Row Order

The row order are customizable by modifying the data in SLAM_Settings.mapping_template_orderby.

You can retrieve the current configuration by running the query below

SELECT mapping_template_orderby FROM SLAM_Settings

You can set a new configuration by running the query below. This content becomes the "ORDER BY" clause of a SQL query and must therefore be a valid SQL clause. Single quotes must be properly escaped during the UPDATE statement as demonstrated in the example below (e.g. replace each ' with '').

-- an alternate ordering that prefers required and custom fields, secondarily sorting by type
UPDATE SLAM_Settings SET mapping_template_orderby = '
CASE WHEN [Qualified API Name (part)] = ''Id'' THEN 0
    WHEN [Required] IN (''X'', ''TRUE'') THEN 1
	WHEN [Custom (desc)] IN (''X'', ''TRUE'') OR [Qualified API Name (part)] LIKE ''%[_][_]c'' THEN 2
	WHEN [Custom (desc)] = ''UNKNOWN'' THEN 3
	ELSE 4
	END,
CASE WHEN [Data Type (part)]=''reference'' THEN ''0''
     WHEN [Data Type (part)]=''string'' THEN ''1''
     WHEN [Data Type (part)]=''picklist'' THEN ''2''
     WHEN [Data Type (part)]=''multipicklist'' THEN ''3''
     ELSE [Data Type (part)]
END,
[Qualified API Name (part)]'

Usage Examples

Retrieve mapping template for pasting into Excel

EXEC dbo.SLAM_mapping_template
    @sObject = 'Account',
    @dialect = 'Excel'

Retrieve mapping template for pasting into Google Sheets

EXEC dbo.SLAM_mapping_template
    @sObject = 'Account',
    @dialect = 'GSheets'