SQL Triggers to the rescue.

I took part in an interesting internal meeting today with the sales guys. Currently our internal ACT! db is still very much an “old school” solution where we stick firmly to the under-lying principles of the ACT!, sales philosophy…people do business with people. This means the majority of user interaction with ACT!, is via the Contact Detail and List views.

There has been a drive to try and work more primarily from the Opportunities views which would make reporting a little simpler for on-the-fly reporting by the managers without always requiring input from technical to create Inner Joins between SQL tables or Views.

The sales guys prefer working from the Contact view since with their Sales layout they can see all the relevant info readily without having swap views or flick between Opps view -> Contact view which can lead to GDI issues on busy days or just be slow!

The resolution for the meantime has been to limit the use of the Link Contact to Opportunity plugin that I had created a while ago for a client that basically links a Contact to a Opportunity similarly to how the Contact – Company link works for fields pushed from Company -> Contact so as to avoid schema bloat and duplicating too much data across entities. Another issue we have that is related is the organic misuse and growth of items in the ID/Status fields. Ideally this field should provide a “bird’s eye view” of the type of Contact we are dealing with i.e., is it a Customer, Supplier, Contactor, Hot Lead or Cold Lead etc. The list now also contains Sales Stages that the sales guys manually set following an Opportunity. What we have decided to do is create another Contact field that shows the Sales Stage of the most recently updated Opportunity. Everyone is happy but I’ve got to do this without slowing down the “user experience”! Great! Because we are currently weighed down with quite a bit of work in technical I didn’t feel I had time to sit and create a nice plugin that would work seamlessly so the next thing was to investigate using Triggers.

SQL Triggers shouldn’t really be used to do business logic but due to the nature of ACT!, we can ignore the academic arguments against this technique and plough on ahead!

Basically I created a trigger

FOR UPDATE, INSERT

whenever an Opportunity was either updated or created that would show the Sales Stage in the Contact Field Sales Stage. Here’s the full script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Vivek Gargav>
-- Create date: <Create Date,,>
-- Description:   <Copies the Sales stage of the recently updated or 
--                        newly created Opportunity to the Contact.[Sales Stage] 
--                        field.>
-- =============================================
CREATE TRIGGER [dbo].[Show_Sales_Stage_In_Contact]
ON [dbo].[TBL_OPPORTUNITY]
FOR Update, Insert
AS
BEGIN.
      SET NOCOUNT ON;
    Update TBL_CONTACT
    SET CUST_Sales_Stage_100817945 = (Select TBL_STAGE.NAME from inserted
            Inner Join TBL_STAGE On inserted.STAGEID = TBL_STAGE.STAGEID)
    FROM inserted,( TBL_CONTACT C Inner Join
                          TBL_CONTACT_OPPORTUNITY CO On CO.CONTACTID = C.CONTACTID Inner Join
                          TBL_OPPORTUNITY O On CO.OPPORTUNITYID = O.OPPORTUNITYID Inner Join
                          TBL_STAGE S On O.STAGEID = S.STAGEID)
      WHERE inserted.OPPORTUNITYID = o.OPPORTUNITYID
END
GO

I think that I didn’t really need the sub-select in Line 23 but it works and that’s good enough for the moment. I would be interested in seeing if anyone who might be reading this might have any comments on improving the SQL.

The cool thing with this is that it took only a few minutes to write and just seconds to deploy. I didn’t need to install any plugins on anyone’s machines I just executed the script on the server and it was done immediately and no one needed to log off the dB either. The speed of the script is at SQL speeds so much much faster than the SDK could ever hope to achieve.

Currently, doing this is technically against the EULA for ACT! but as I understand it Swiftpage are willing to turn a blind eye if a Certified Consultant does this kind of thing and work is in progress @ Swiftoage, Scottsdale to make a change to the EULA to allow this kind of messing around. Very cool I think!

Advertisements