#ActCRM and Attachment Files Anomalies

Q#ActCRM and Attachment Files AnomaliesOne of the great things with Act! is the ability to attach files to any entity like a Contact, Company or Opportunity etc. We received a database recently from client that they wanted cleansed, upgraded and re-designed. As this dB was quite an old system that had “been through the wars” and several upgrades over the years (I think it started life as an Act! 2000 dB!) there was a fair amount of cleansing to do for us within the tech team.

dB cleansing is usually not only a complex task but also time intensive where we run a dB through various internal tools and scripts developed by us over the years. One region within most Act! dBs that requires our intense gaze is upon the Activities and then the Attachments as these sections touch so many areas with an Act! dB.

One interesting thing about Act! attachments is that when you delete a Contact, the attached files within the dB are not physically removed from the system. Attached files reside in the Attachments folder of the supplemental folders, e.g. if your dB is called Act2014Demo at the location of the ADF and ALF files you will find a folder called .\ACT2014Demo-database files and within that a folder named Attachments.

Supplemental_folders

Act! supplemental folders

It is generally accepted as a rule of thumb to carry out system purging of cleared activities etc on a fairly regular basis to reduce lock-ups in the dB and negative impacts on overall dB performance, unfortunately there is no native facility to remove unattached or orphaned files from the Attachments folder. Having too many orphaned files not only is a waste of physical space on your hard drive, but can also slow Act! down since the Indexing server within Act! that allows you to perform Universal Searches will list and index the contents of these orphaned files as well.

The real problem comes in trying to work out which files are correctly attached and which are not so the following simple script shows an example of a SQL statement that lists all the files that are currently not attached to a record in Act! and so can be safely removed.

declare @files table (ID INT IDENTITY, Filename varchar(250))
insert into @files execute master.dbo.xp_cmdshell 'DIR "C:\Users\Public\Documents\ACT\ACT Data\Databases\ACT2014Demo-database files\Attachments\" /a /b'
select  F.Filename[OrphanFiles], (select COUNT(F.filename) from @files[F] where F.Filename not in (select DISPLAYNAME from TBL_ATTACHMENT))[OrphanCount]
from @files[F]
where F.Filename not in (select DISPLAYNAME from TBL_ATTACHMENT)

What’s interesting here is that you can ask SQL to run a cmd line utility to list the contents of a folder. There are many DBAs that would balk at the idea of leaving this security gap wide open, however since we are working in the “lab” this is something that we do fairly routinely. If this ability has been turned off, then your only option then is to create a custom CLR for your SQL instance which is quite a bit more involved.

What we usually do with this SQL statement is embed it within a Powershell script with literates through the file list and deletes each file. The result is an Act! dB with a nice new hair-cut.

Advertisements

An Automated Weekly History Report Email for #ActCRM Using PowerShell

Windows_PowerShell_icon

I just want to apologise firstly that I haven’t been updating my blog for a little while and this could be my first post this month, but I’ve been in hospital for a little while and then on medication which has left me a little…incapacitated!

Today’s post brings in a couple of technologies and techniques together to produce an email that contains  a simple table showing the Histories created by all Users during the past week. This is actually a very simplified request that we received a little while ago from a client who was going away on holiday for a couple of weeks but still wanted to keep a tab on how their team were performing.

We’re going to use Microsoft’s PowerShell to run a SQL statement against the Act! SQL instance and then use a SMTP connection to the internal SMTP server (Exchange Server in our client’s case) to them email it out on a schedule set by nothing more exciting than the Windows Scheduler!

Now there are add-ons that will do this in a much more WYSIWYG and point-‘n’-click manner, but my view on this is; where’s the fun in that and isn’t it cheaper for the client to use the native skills of their Act! Consultant and existing free technology already present within their architecture?

Below is the script for doing this, I’ve added some comments to explain some of the parts and have tried to segment the scripts into blocks. In the current state, the script would need to be run on the server hosting the Act! dB and the scheduler to be running logged in as either a local server administrator or as a Domain administrator since it uses that to log into the SQL instance.

One word of caution though, if you’re not too comfortable with PowerShell or SQL, I would recommend that you don’t try this on your own and try to liaise with someone who is more comfortable with the technologies before using it on a production dB.

History_Summary_via_PowerShell_and_SQL

I’ve noticed that the image is a little small so here is the script itself that you could copy and paste if you’re feeling especially lazy!

clear #clears the screen of any other commands etc.
#-----------This is the PS connector to the native SQLCMD of SQL Server
if((Get-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyContinue) -eq $null){
add-PSSnapin sqlserverprovidersnapin100
add-PSSnapin sqlservercmdletsnapin100
}
#--------------------------------------------------------------------------------------------

$dbInstance = ".\act7" #The machine hosting the Act! dB SQL Instance
$actDb = "Act2013Demo" #The Act! dB we want to report on

#----------------SQL Statement----------------------------------------------------------------
$myExp = invoke-Sqlcmd -ServerInstance $dbInstance -Database $actDb -Query "
            SELECT     
                    Count(TBL_HISTORY.HISTORYID)[Count], 
                    TBL_HISTORYTYPE.NAME AS [History Type], 
                    TBL_USER.USERLOGIN AS [User]
                FROM         
                    TBL_HISTORY INNER JOIN
                    TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN
                    TBL_USER ON TBL_HISTORY.CREATEUSERID = TBL_USER.USERID
                WHERE
                    TBL_HISTORY.CREATEDATE >= DATEADD(WW,DateDiff(WW,0,GetDate())-1,0)
                AND
                    TBL_HISTORY.CREATEDATE <= DATEADD(WW,DateDiff(WW,0,GetDate()),0)            
                GROUP BY
                    TBL_HISTORYTYPE.NAME, TBL_USER.USERLOGIN
            " | Select-Object Count, 'History Type', User
#-----------------------------------------------------------------------------------------------
#---------------Send out Email------------------------------------------------------------------
$emailFrom = 'actAdmin@caldere.com'
$emailTo = 'SalesManager@caldere.com'
$subject = "History Activity Report for week: {0}" -f((Get-Date -UFormat %V)-1)
Send-MailMessage -SmtpServer 'MyMailServer' -From $emailFrom -To $emailTo -Subject $subject -Body $myExp -BodyAsHtml
#------------------------------------------------------------------------------------------------

I’d be genuinely interested to read any comments anyone would like to make on this or if they can find ways to improve this. The original script created for the client was a little more complicated to include CSS styling and further SQL queries specific to their request.

Update ACT! Phone Formats via SQL

Field Phone Military

I saw a request on the public ACT! Community Forums from a user asking if there was a way to carry out a batch update of Phone Field formats throughout an ACT! dB. This is a fairly common request and something that can cause much heart ache for ACT! Users. In many cases an innocent User may update a Phone Field with a new display format and loose the phone number, the easy work-around to this is to simply copy-paste the number back, but it is of concern that if a User is not aware of this bug or “known-issue” data can be lost. The other problem is that you can only employ this method on single field at a time which is a very repetitive, tedious and frustrating exercise. We’ve written small utility apps that do this in the past for clients and there are other plugins/add-ons available both free and paid for that can do this, but I thought it would be interesting as a blog article to show how quickly and simply this can be done via SQL. Obviously the usual caveats apply that this should not be done without exercising extreme caution and care and I wouldn’t always recommend doing this within a synch environment (however the update triggers for synch are activated by the update).

There are basically 2 tables involved with storing Phone field data within the ACT dB:

  • TBL_PHONE
  • TBLPHONEMASK

The TBL_PHONE as you would expect stores the phone data and the TBL_PHONEMASK records the various Phone display masks or formats within the dB. Within the relational model for ACT!, if an entity such as Contact, Company, Group or Opportunity has a Phone field, it will have a row within the TBL_PHONE table relating back to it via the CONTACTID, COMPANYID, GROUPID or OPPORTUNITYID column. Below is an example of the relationship between the CONTACT-PHONE-PHONEMASK tables.

Contact-Phone-PhoneMask relationship

The relationship between the Contact, Phone and PhoneMask tables

In the example I am using for this article, we have our ACT! dB Contacts that are in free-form format, we would like to apply a new phone format to all UK Contacts with the following mask: (####) ### #### . To make things easy we create this format within the ACT! application. We can then go into our SQL query editor and type the simple query:

SELECT PHONEMASKID
FROM TBL_PHONEMASK
WHERE PHONEMASK='(####) ### ####'

This will give us the unique ID of the new Phone Mask. Either copy or note it down as we will need it for the next stage. We would like to make this Phone Mask the default format for all new phone numbers so we need to set the value for the ISDEFAULT field in the TBL_PHONEMASK table as 1 and then we want to update the entire TBL_PHONE table to use this mask for any phone number within the UK which has the country code of 44 (the USA is 1). The following code shows the final SQL statement that will update the dB.

   1:  UPDATE TBL_PHONEMASK
   2:  SET ISDEFAULT =1
   3:  WHERE PHONEMASKID='BC13A500-E182-4663-AC30-06C5E119BDEA'
   4:  UPDATE TBL_PHONE
   5:  SET PHONEMASKID = 'BC13A500-E182-4663-AC30-06C5E119BDEA'
   6:  WHERE COUNTRYCODE=44

Line 3 uses the unique ID for this mask within my dB which will most probably be different in your dBs. The statements between lines 1-3 set this mask as the default mask in the TBL_PHONEMASK table and lines 4-6 update the TBL_PHONE table to use the new Phone Mask.

Once you have run the SQL, you just have to refresh the ACT! application view and you will see your new format in place. Hopefully you have found this helpful or at least interesting!

ACT! Reporting, OLEDB & SQL

This is something we get asked to do for clients a lot…History Summary Reports. ACT!, has a built in report using its native reporting engine called the History Summary Classic Report. This report is a very cool report but it highlights one of the greatest weaknesses of ACT!, namely Reporting and BI. In short it’s terrible! Almost every client (if not all) of ours use customised Activities and to modify the History Summary Classic report means that we have to go into many sections of the report and do VBScript…all in all its nasty, ugly, cumbersome and very time intensive.

We actually stopped doing that a very long time ago and now we approach it in a different way.

  • Do you want the report to be viewed within ACT! as a dashboard without any further interaction?
  • Would you prefer to have the report viewable within Excel and interact with the report?
  • Would you like it to be published using SQL Server Reporting Services (SSRS)?

Each of these requires the use of SQL to varying levels of complexity. The first can initially feel a little involved and so can be intimidating, but it’s fairly simple surprisingly. Sometime back, Allen Duet (the Product Management Director for ACT!) developed a free plugin that allowed us to drop in custom OLEDB reports into the ACT!, Dashboard. It surprised me on a couple of levels but firstly was that a Product Director from Sage actually knew what OLEDB was, secondly a Product Director could code (shock horror!) and thirdly why on earth hadn’t I thought about this! This free plugin has now been incorporated (and a little diminished truth be told from the original) into the core product.

**********EDIT**********

This post has been changed, originally I had gone into all the steps to do this with pictures etc. I’ve now made a YouTube video called “Create a History Summary Classic Report in Sage ACT! Within the Dashboard” that can be watched instead.

**********EDIT**********

Excel, Pivot Charts & OLEDB

I have to be honest and say that this one of my more preferred methods since it’s so simple and powerful for reporting! If you’re going to only report from a single table/view then you do not require any SQL input at all, it’s just a simple case of selecting your table and creating a pivot table/chart.

Since Excel 2003 pivot charting has become so easy anyone can do it. I remember that Pivot Tables used to be really complex things to create in earlier versions and in fact I used to avoid them like the plague! But now it’s all point-&-click and you get some very good looking and powerful reports as a result. You can also save these xlsx files and just click refresh later to get the most up-to-date data.

**********EDIT**********

This post has been changed, originally I had gone into all the steps to do this with pictures etc. I’ve now made a YouTube video that can be watched instead.

**********EDIT**********

SSRS & BI

Ok so this is unquestionably the most complex route taken but results in publicly shared and published reports that auto refresh themselves from live data.

I’ve found varying levels of performance of SSRS reports and the quality of the SQL you write can have a direct influence over the performance of these reports as well. To use SSRS you’ll need three components:

  1. SQL data and queries
  2. IIS (Webserver)
  3. Microsoft BI Designer

There is also an optional 4th component SharePoint. One of the very cool things with SSRS when you have SQL Standard or Enterprise Edition is that you can have these reports emailed out to your users on a schedule! Unfortunately this facility is not available in the EX edition which comes with ACT!, but if you publish the reports to SharePoint it is possible to get SharePoint to do this kind of thing I am led to believe.

The steps involved in creating an SSRS report really are too long and involved for me to create a simple video here and, besides, I have gotten too bored of this post now!

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!