I just came up with a really cool way of doing some potentially really cool and complicated things from with the SalesLogix Word Mail Merge engine utilizing a combination of SQL merge fields and SQL functions.
The beauty of the SQL function is that it could potentially do some really powerful things, like querying an external data source or doing powerful calculations that could not otherwise be achieved in the SalesLogix mail merge.
Lets take a look at a real simple example that should explain what I am talking about.
The first step is to create a SQL function. In my demo I am just going to return the SalesLogix account name with “FX” prepended. My function will pass in one variable (the accountid of the account I want to return my special formatted account) and return a string (of my special formatted account name). Here it is:
Create function [sysdba].[FXAccount](@Accountid varchar(12))
returns varchar(100) as
Declare @Account varchar(100)
Declare @TempString varchar(100)
select @Account = isnull(account,'') from account where accountid = @Accountid
set @TempString = 'FX' + @Account
Take note that I created the function under the sysdba user. Now with that created, I can use it in a mail merge.
When in the SalesLogix mail merge, click the Insert Special Field button on the Word toolbar, then choose SQL…Text.
Name you field in the Field Identifier, in the screen shot above I have it set to “Custom Username”. Click the Ellipse in the SQL box. This will open the SQL dialog:
Now type in your SQL query that utilizes the SQL function you had built. It is important to prefix your function with sysdba again. Note that the mail merge queries also need to have a reference to the record the query is being run against. This is done via the Insert Parameter button on the toolbar. In my case I am running this query against the current merge record’s account, represented by :AccountID.
That is all there is to it.
I think it is pretty cool.
BTW, this same approach could be used in queries from the SalesLogix client, either web or LAN based via calls through the SalesLogix OLEDB Provider using ADO.