Custom Merge Code Examples

These examples address commonly needed functionality.  No matter which platform used, review all the merge codes here to see how flexible OmniRush merging is.  Many of these examples can be pasted in to the OmniRush Administrator to get you running immediately.

Quick List:

All SQL Platforms:

Future Date & Date Calculation

Formatting Returned Data

Conditional Data: How to back fill a merge value in case field is NULL or empty

Stored Procedure Merge Examples

Stored Procedure Merge Examples - Advanced Example 1

GoldMine SQL:

Count of records

Most Recent Appointment from History

Detail / Profile records of type Serial Number

History Records of Specific ACTVCODE

Conditional Data: How to back fill a merge value in case field is NULL or empty

Merge Referred Contacts

Merge Opportunity Information

SalesLogix

Merging Account Manager Information

Microsoft CRM v1.x

Merge Business Unit Name

Future Date & Date Calculation

Microsoft SQL Server and Oracle have a number of date functions that can be called from a merge code.  The simplest need is often just to merge a date that is a few weeks in the future.  For example, a marketing campaign might email or fax out coupons that are good for only one week.  Or a special offer might want to loudly proclaim "Expires on <7 days from transmission date>!!!"

(These examples are based on MS SQL Server.  Oracle users, please consult the Oracle PL/SQL documentation.)

OmniRush can easily do this with a custom merge code.  For example, create a custom merge code called D7 (for 7 Days), and paste in this SQL:

SELECT DATEADD(day, 7, getdate())

AS orfuturedate

This will return a single field, 'orfuturedate' which will have a date 7 days in the future.  A test merge document with this code would look like this:

&Setdateformat(mmmm d, yyyy) &SetTimeFormat()

In 7 days it will be: &D7_ORFuturedate

The &Set codes control the format of the merged date.  For more information on formatting codes, see the page.  As you can see, the DATEADD function can add days, weeks, months, years, and more to any date.  In the example above, we are simply adding to the current date.  For full information, please refer to the Transact SQL documentation that is part of MS SQL Server.

The DATEADD and DATEDIFF functions can be put to many uses.  For example, say the CONTACT table has a CONTRACTEXP field that is the date when the customer's contract will expire.  The following can be put into merge documents to remind users how much longer their contract will last:

SELECT DATEDIFF(month, getdate(), ct.CONTRACTEXP)

as orExpDate

from contact ct, activity ac

where

ac.contactid = ct.contactid

and

ac.activityid = "&ACTIVITYID"

DATEADD can also be used against database fields, or DATEDIFF and DATEADD can be used together.

Formatting Returned Data

When merging from a SQL merge code, the SQL Server itself has a number of features that can be used to format data. (Of course, with a stored procedure, the formatting is completely under your control in code). On SQL Server, the TSQL CAST and CONVERT functions can be used to format all kinds of data.

For example, if a table has data in a column of type Money, SmallMoney, Int, or Char, this syntax will work to return a nice dollars and cents result. This example column is named OwedAmount from table Overdue:

select left(convert(varchar(10), cast(OwedAmount as money), 1),

len(convert(varchar(10), cast(OwedAmount as money), 1)) ) from Overdue

This would return data that is formatted in dollars and cents, e.g. 123.44

Conditional Data: How to back fill a merge value in case field is NULL or empty

In many cases, you are not assured that there will be data in the field you want to merge. For example, a document should be sent out with the Attention block to a field in your table called 'BillingName' but in some cases this field is empty. The Attention block should never be blank, so you want a merge code that will use the ContactName field as a fall back in case the BillingName field is blank or NULL.

There are a few different ways to attack this task. One way is to use a Stored Procedure (aka sproc) from the merge alias. An additional, perhaps more accessible, technique is to use the functionality of SQL itself. On the Microsoft SQL Server platform, the CASE, ISNULL, and IIF mechanisms can serve. (See the Transact SQL documentation for details.) Here is an example using the CASE statement.

SELECT 'RecipientName' =

  CASE

    WHEN len(BillingName) < 1 THEN ContactName     -- Handles cases where there is an empty string in BillingName

    WHEN BillingName IS NULL THEN ContactName      -- Handles cases where BillingName is NULL

    ELSE BillingName

  END,

  CONTACTID, BillingName, ContactName

from RecipientTable where CONTACTID = '&CONTACTID'

For example, to use this on the GoldMine CONTACT2 table where we have a field named UBillingName, the implementation would look like this:

SELECT 'uRecipientName' =

  CASE

    WHEN len(uBillingName) < 1 THEN C1.Contact

    WHEN uBillingName IS NULL THEN C1.Contact

    ELSE uBillingName

  END,

  c2.accountno, c2.uBillingName, c1.Contact

from

  contact1 c1, contact2 c2

where

  c1.accountno = c2.accountno and c2.accountno = '&CONTACTID'

GoldMine: Count of Records

Say you want to merge in a simple count of records.  For example, you want to remind customers that they have received X tech support calls in the last three months.  Say that tech calls are tagged with a result code of TS.  Create a custom merge code called CTS (Count Tech Support), and enter this SQL:

select count(*) as howmany

from

conthist

where

accountno = '&CONTACTID'

and

(actvcode = 'TS')

and

3  > datediff(month,createon,getdate())

Now use the &CTS_Howmany merge code in the document to merge in the count.

Here is another way to do the same thing, using a stored procedure (sproc):

Step 1: Using Query Analyzer, create the sproc:

CREATE PROCEDURE conthist_count

@Contact varchar(30)

AS

select count(*) as howmany from conthist where

accountno=@Contact

and

(actvcode = 'TS')

and

3  > datediff(month,createon,getdate())

Now create a custom merge code of CTS, that invokes the sproc:

EXECUTE conthist_count '&ContactID'

Now use the &CTS_Howmany merge code in the document to merge in the count.

GoldMine: Most Recent Appointment from History

Create a new merge code, name it HAP (for example).  Use this SQL (you can paste this in):

set rowcount 1

select * from conthist where

accountno = '&CONTACTID'

and

rectype = 'A'

order by ONDATE DESC

set rowcount 0

Create a new merge form, name it HAPTEST.RTF, and paste in this text:

Most recent history record for &Company:

&SetTimeFormat()

&Setdateformat(dddd, mmmm d, yyyy)

&HAP_Ref

&HAP_Ondate

&HAP_Notes

Schedule HAPTEST.RTF on a record that has multiple appointment records in conthist.

GoldMine: Serial Number Records from Detail/Profile tab

Create a new merge code, name it DSN (for example).  Use this SQL (you can paste this in):

select *

from contsupp

where rectype = 'P'

and

contact = 'Serial Number'

and

accountno = '&CONTACTID'

Create a new merge form and create a table, and use the appropriate fields from the GoldMine CONTSUPP table.

GoldMine: Specific History Records within Last 30 Days

Create a new merge code, name it DSN (for example).  Use this SQL (you can paste this in):

select *

from

conthist

where

accountno = '&CONTACTID'

and

actvcode = 'DN'

and

31 > datediff(day,ondate,getdate())

Note: Conthist.OnDate is normally not indexed. You may want to add an index to Conthist ("ondate, recid" or "ondate,actvcode,recid") to improve the performance of this query if you have more than 30,000 records in ContHist.

Create a new merge form and create a table, and use the appropriate fields from the GoldMine CONTHIST table.

GoldMine: Merging the referred contact1 data (e.g. not the current contact data, but contact data from records referred to the current contact).

Create a new merge code, call it RC1.  Paste in this SQL:

select * from contact1 c1

where c1.accountno IN (select title from contsupp

where

accountno = '&CONTACTID'

and

rectype = 'R' )

Create a new merge template, RC1TEST.RTF, and insert a table of four columns and two rows.  Type in fields as shown:

Note, OmniRush includes, out of the box, the merge demonstration form GM_Referral_List.rtf based on the CRL merge code.  The out-of-box CRL merge code should work on SQL Server v7, but will fail on SQL Server 2000 and higher due to a new syntax rule.  Modify the existing CRL merge code to the following SQL to bring it back to life:

select c1.* from

        contact1 c1

where

        c1.accountno IN

                (select title from

                        contsupp cs

                where

                        accountno = '&CONTACTID' and

                        rectype = 'R'

)

ORDER BY c1.accountno DESC

GoldMine: Merging Opportunity Information

OmniRush v5.2 has a new merge template, GM-Oppty-Merge.RTF, which demonstrates opportunity merging.  The merge codes for this form must be added to OmniRush by pasting in the SQL listed in the template.   The instructions here will let you set up this merge form:

Then make this merge form:

SalesLogix: Merging Account Manager Information

Say you want to merge in the account manager name or signature bitmap.  In SalesLogix, the account manager can be associated with the Contact or the Account.  Here are two merge codes, one for Contact, one for Account.  Create a custom merge code called AMA (Account Manager in Account view), and enter this SQL:

select ui.*, us.* from

userinfo ui, usersecurity us

where

ui.userid = us.userid

and

ui.userid = (select accountmanagerid from account

where

accountid = "&CompanyID")

Now create another custom merge code called AMC (Account Manager in Contact view), and enter this SQL:

select ui.*, us.* from userinfo ui, usersecurity us

where

ui.userid = us.userid

and

ui.userid = (select accountmanagerid from contact

where

contactid = "&ContactID")

Now all the fields in the UserInfo and UserSecurity fields can be merged for the Account Manager.  The following merge template can now be created:

When merged and faxed/emailed/printed, the output will be similar to the following:

MSCRM v1.x: Merge Business Unit Name

Merge Business Unit Name.  This merge code grabs the Business Unit name, that can then be merged into documents.  So a single template could be used by multiple Business Units.

Steps:

1. Create a new Merge Code

2. Name it BN (for Business Name)

3. Set it to: One Row

4. Paste in the following SQL:

select

  name

from

  businessunitbase

where

businessunitid in

(

select owningbusinessunit from

contactbase where contactid='&CONTACTID'

)

5. Now use the merge code: &BN_Name in merge documents