GoldMine Contacts SQL View to combine Contact1, Contact2, and prmary email address from ContSupp

GoldMine Contacts View.sql

/*
	Written by:		Patrick Pawlowski
	Company:		Ticomix
	Created On:		2012.04.20
*/

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Contacts]'))
DROP VIEW [dbo].[Contacts]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create view [dbo].[Contacts] as
SELECT c1.[ACCOUNTNO]
      ,[COMPANY]
      ,c1.[CONTACT]
      ,[LASTNAME]
      ,[DEPARTMENT]
      ,c1.[TITLE]
      ,[SECR]
      ,[PHONE1]
      ,[PHONE2]
      ,[PHONE3]
      ,c1.[FAX]
      ,[EXT1]
      ,[EXT2]
      ,[EXT3]
      ,[EXT4]
      ,c1.[ADDRESS1]
      ,c1.[ADDRESS2]
      ,c1.[ADDRESS3]
      ,c1.[CITY]
      ,c1.[STATE]
      ,c1.[ZIP]
      ,c1.[COUNTRY]
      ,c1.[DEAR]
      ,[SOURCE]
      ,[KEY1]
      ,[KEY2]
      ,[KEY3]
      ,[KEY4]
      ,[KEY5]
      ,c1.[STATUS]
      ,c1.[NOTES]
      ,c1.[MERGECODES]
      ,[CREATEBY]
      ,[CREATEON]
      ,[CREATEAT]
      ,[OWNER]
      ,c1.[LASTUSER]
      ,c1.[LASTDATE]
      ,c1.[LASTTIME]
      ,[U_COMPANY]
      ,c1.[U_CONTACT]
      ,[U_LASTNAME]
      ,[U_CITY]
      ,[U_STATE]
      ,[U_COUNTRY]
      ,[U_KEY1]
      ,[U_KEY2]
      ,[U_KEY3]
      ,[U_KEY4]
      ,[U_KEY5]
      ,c1.[recid] c1RecID

 --start contact2
      ,[CALLBACKON]
      ,[CALLBACKAT]
      ,[CALLBKFREQ]
      ,[LASTCONTON]
      ,[LASTCONTAT]
      ,[LASTATMPON]
      ,[LASTATMPAT]
      ,[MEETDATEON]
      ,[MEETTIMEAT]
      ,[COMMENTS]
      ,[PREVRESULT]
      ,[NEXTACTION]
      ,[ACTIONON]
      ,[CLOSEDATE]
      ,[USERDEF01]
      ,[USERDEF02]
      ,[USERDEF03]
      ,[USERDEF04]
      ,[USERDEF05]
      ,[USERDEF06]
      ,[USERDEF07]
      ,[USERDEF08]
      ,[USERDEF09]
      ,[USERDEF10]
      ,[USERDEF11]
      ,[USERDEF12]
      ,[USERDEF13]
      ,[USERDEF14]
      ,[USERDEF15]
      ,[USERDEF16]
      ,[UTWITTER]
      ,[ULINKEDIN]
      ,[UFACEBOOK]
      ,c2.[recid] c2RecID
-- You should add your custom contact2 fields here

		-- pulling the primary email address from contsupp
      ,em.CONTSUPREF + isnull(em.ADDRESS1,'') EmailAddress
FROM [CONTACT1] c1 
	left join CONTACT2 c2 on c1.ACCOUNTNO = c2.ACCOUNTNO
	left join CONTSUPP em on c1.ACCOUNTNO = em.ACCOUNTNO and em.CONTACT = 'E-mail address' and em.ZIP like '_1%'

GO

A simple view that combines GoldMine's Contact1, Contact2, and the primary email address from the Contsupp table. It's nothing complicated but handy for making things like SSRS reports easier to write or creating stored procedures based on it much easier to write and read.

It also allows you to to update fields in one table based on fields in the other table without doing a join.

eg,
Update Contacts set Source /* CONTACT1 field / = 'Web Site' where USERDEF07 / Contact2 field */ = 'Yes'

You can only update fields in one table at a time though.