#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

Sudden Realisation!

Bruce-Lee: A goal is not always meant to be reached, it often serves simply as something to aim at.This is a bit of a personal-life blog entry and not one of my more usual tech/work related posts. Whilst I was at Perfecting ACT! this year I had the privilege of talking to and drinking with some of my great heroes from the ACT! and CRM world. During one of the conversations I was asked how I got into development, this simple conversation starter led me to a point where I recounted a part of my long journey, which is still largely unfinished, and I thought I would share here.

My background is in Biochemistry and Molecular Biology and just before I left academia and research I was working in a research lab on mitochondrial genetics. I won’t go into the story whereby I abandoned research and went into a field I knew nothing about; namely IT, except to say that to me at the time it felt that maybe this time I had over-stretched myself by over estimating my abilities to learn new skills!

During this period of learning, I discovered an interest in development and coding so proceeded towards learning HTML and Javascript (including a failed attempt to self-learn Java). I remember late one night trawling through the internet trying desperately to find a very specific kind of tool to carry out a particular task, I kept coming across the roadblock of very expensive utilities or shareware tools that I could not afford. At the time I rather whimsically thought of a future “me” that would be an experienced developer who would simply develop a tool to fulfil the task at hand. This little daydream became, at the time, all-consuming and not only drove me further in my learning, but also tormented my confused mind with, what seemed at the time, an unachievable dream.

Many years have passed and such thoughts were forgotten as I got on with my life in IT and continued study in development, till one day I was working with one of junior members in the tech team on a project of migrating a badly designed and implemented ACT! solution to a new model.

The client had been managed by another consultancy who had made some peculiar decisions which were causing major issues downstream post-implementation. Basically the client had 7 separate dBs, each had roughly 20 remote users who would synch at set times over a 24 hour schedule. The problem was that the server hosting the master had a very poor internet connection and the remote users were all working from a satellite office and synch was very unstable, failing many times.

The obvious fix to this was to migrate all 7 dBs onto a local server at the satellite office and use the scheduler to carry out synch thereby reducing the multiple points of error. This simple task was further complicated by each dB having, in some cases, in excess of 60 child dBs that had failed and so new RDBs had been re-cut but they had not removed the old RDB from the manifest. *sigh*. So we would have to manually clean up the whole system and deleted old RDBs.

Both of us were quite irritated by the time it would take to do this and my trainee/junior like a good ACC started manually disabling and then deleting RDBs from each manifest. I decided the quickest and most efficient approach would be to simply create a utility that would do this and within half an hour I had a a working (albeit ugly) tool that went into each dB and disabled and then deleted RDBs from the manifest one by one based on the last date of successful synch. If it was greater than 6 months it would remove them.
The task was completed in a very short period of time and we were ready to continue with the rest of the project. My junior remarked to me that it was lucky that I was a dev since it was like having a Genie in a Bottle!

For some reason this suddenly sparked from the darkest recesses of my brain memories of those tortured nights stooped over a cheap desktop with a copy of Visual Studio Learning Edition feeling frustrated, tired and with an over-riding sense of defeat, idly dreaming of a better time when I could code confidently but never expecting to achieve this goal.
It then suddenly hit me like a brick. I was actually living my dream! I had and continued to do the one thing that used to motivate me to not give up at my darkest times! I must admit that afterwards in the privacy of my room I did feel a little emotional since I had achieved for myself something that truly seemed impossible and I was doing these kind of things almost on a daily basis without even thinking about it.

The reason I wanted to share this out publicly was not to trumpet my own horn, but to give others an example of how not losing faith in ourselves and our dreams, but focusing on our daily hurdles/challenges and not overwhelming our minds with what seem like impossible goals, we slowly and gradually without much fuss or theatre, find ourselves at the summit wondering how we got there.

My hope is that maybe someone who is beginning the long, lonely and painful journey towards being a developer might read this and derive some hope and confidence that the dream is achievable.

#PerfectingACT! 2013 with #Swiftpage

new act! logo

The newly revealed act! logo by Siwftpage

Well, PA 2013 is over and I’m back in the UK, comfortably sat in my “dev hole” (as wifey describes it) and though I am completely spent from 6 days of little or no sleep, I haven’t felt this energised for years! Swiftpage are the new owners of ACT! and simply saying that they are bringing in a new breath of fresh air would be a complete under-statement. To quote from one of my all-time favourite songs by AC/DC “Back in Black”:

Yes I’m let loose
From the noose
That’s kept me hanging about
I keep looking at the sky
‘Cause it’s getting me high
Forget the hearse ‘cos I’ll never die
I got nine lives, cats eyes
Using every one of them and running wild
‘cause I’m back!

..and wow is ACT! back…sorry that should be act! is back! From the very on-set of PA, both Bob and John set the scene for me with the single word “ENGAGEMENT”. When Swiftpage talk about engagement, they want to re-engage with the product, re-engage with the platform, re-engage with partners, re-engage with add-on partners, re-engage with ex-Sage employees, re-engage with end-users and re-engage with the entire product vision! For once I genuinely believe that this is not the usual vacuous marketing led  series of hollow promises. I, and many others, had the pleasure of having direct conversation time with each member of the senior executive and management team that were present at Perfecting ACT!. This level of commitment from their busy schedules would have been unparalleled during Sage’s tenure. Every member of Swiftpage I spoke with had the same level of energy and enthusiasm that we partners have had for the product and they genuinely wanted to hear from us. We are all Swifities now, part of the team and in from the cold!

So that’s the euphoria part done with, what can we expect for the immediate and medium term future?

  1. We ACT! 2014 has been renamed and re-branded to act! v16, what this means is that this will not only be the final version of ACT! that Sage was involved in, but also the last 12 month product cycle release. Yes you understood that correctly, act! will no longer be released every 12 months but will settle down to longer version releases with smaller more frequent Service Pack/ update releases. This one thing alone will remove much of the pain that has been associated with the act! product for many years now, and will allow the product to adapt to a constantly changing landscape.
  2. A SaaS version of act! is due imminently based on the Swiftpage Engage Engine. The little that I saw of this edition still in it’s early stages of development, was a much simpler and cleaner version of act! Professional with just the core Contact Management functionality.
  3. Consolidation of all regional sectors outside of North America into a single office based in Newcastle-Upon-Tyne in the UK, and a thorough commitment that regional issues and impacts of version and update roll-outs will be given equal weighting within product design, vision and support.

For us ACCs in the UK, the request for an increased level of communication and involvement with the senior management headed by Dan Ogden and Guy Clack is such refreshing news that the voices of all the clients we represent will now not go unnoticed or unheard. An early indication of this was the dramatically increased level of activity and meetings Dan and Guy had with French partners prior to PA to ensure that a French version of ACT! 2013 was made available after a failure by Sage to ensure that ACT! 2013 was released in French.

So here’s to a bright new orange future with Swiftpage and a return of Perfecting ACT! as a central opportunity for all act! business partners globally to meet, learn and above all ENGAGE!

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!

Extending Universal Search in ACT!

Search

One of the things we do at Caldere is work closely with the ACT! retention team to help clients that need ACT! to exceed its core feature set. Sometimes this can be simple such as providing a reporting solution that integrates ACT! data with other data sources within the organisation and other times this can be more complicated with the use of code or SQL scripting. This is actually one of the parts of our job in the technical team that we really enjoy, it’s always with projects from the retention team that we are stretched professionally and get to really sink our teeth into very interesting and complex client issues. Usually our first engagement with such clients involves a discussion with one of our consultants who analyse the first most basic premise; is ACT! the right solution for what they are trying to achieve as a business. The discussions can result in the following conclusions:

  • ACT! is not the right tool they should be looking to achieve their goals.
  • A re-design of their ACT! system and additional bespoke training is required.
  • In some cases clients need assistance with translating their processes into the overall ACT! architecture.
  • The technical team is “let loose” upon the client to do development or other technical work.

Recently we had a client referred to us by the retention team who had purchased ACT! for their sales team but wanted to extend the use of it to another team. As an organisation, they provide out-sourcing solutions for a wide range of customer types and so had to maintain a very large bank of CV’s (resumes for any American reader!) of contractors, during the migration of these attachments into ACT! we estimated it to about 30GB. The existing HR software they had in place was no longer able to service the kind of detailed querying they required, to invest in a new dedicated system would have cost them over £250k. Their hope was to use the comparatively cheaper ACT! product to do this with it’s new Universal Search feature. The problem was that natively Universal Search could not return a Contact List that could then be narrowed down, at face value this would mean that all teams would have to migrate to something new and expensive, which the teams didn’t want to do. Our challenge was to find a solution that would extend the native Universal Search within ACT!

  • Return a Contact List of queried skill types from applicants CV’s
  • Allow this Contact List to be further narrowed down on based on other Contact fields
  • Display a Hit-Count of the number of times a skill type appeared within each person CV

This would allow the recruitment consultants to look for all contractors that had skills in e.g., UNIX, C++ and Banking but were based in Birmingham (ACT! field) and had salary expectations between type B – D (ACT! field), showing that Fred Bloggs had 29 occurrences of the terms within his CV whereas John Doe only had 12. This I must admit was the most complex solution request I had faced all year!

The first challenge we faced in creating the plugin is that developing on the Universal Search is not supported by the ACT! SDK and there is no documentation. In addition, licencing restrictions prohibit us from coding to the Universal Search components directly. We contacted the developers of the Universal Search utility within ACT! to see if purchasing a licence, but due to the cost of the licence we rapidly withdrew from that approach. Thanks to a few carefully placed tequilas during summit, I was able to unofficially discuss the project with the ACT! development team who were able to advise me a workaround to the licencing roadblock, which was later confirmed by the owners of the component, we were able to develop a plugin that not only met all the requirements but also saved our client from having to spend over a £1/4Million and so the 80 strong team could continue using ACT! as their core business solution.

The important take home message for anyone who uses ACT! is that in most cases developers are able to mould ACT! to do what you need it to do. There are of course limitations and this usually comes where the business aim or process being modelled is beyond the product vision/design or scope. An example similar to this would be if such a solution was required for processing greater than 40k documents per day within a global deployment and regional languages, which some larger departments of the same organisation do manage. In those cases we have advised them that ACT! would not be the best solution  for them as to the sheer complexity and volume of their requirements, ACT! has a single regional setting for each database and relies entirely on standard Windows Server file handling and indexing.

I plan on doing a couple more posts in the future regarding Universal Search including optimisation and advanced features etc, if there is anything anyone would like me to particularly post on let me know either by email or via comments.

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!

Create Opportunity From Contact Plugin

Well since Opportunities became bona fide Primary Entities back in ACT! 2009, we have been using them quite heavily internally at Caldere. One of the real problems that user’s highlighted was that now we had an Opportunity Detail screen the sales guys were wasting a lot of time flicking backwards and forwards between views. This constant switching was also leading to multiple GDI crashes on controls within ACT!. We have increased everyone’s GDI pools via Group Policies but even then this was starting to become quite an issue.

The question that was asked was if we could develop a plugin that would be able to copy Contact fields over to the Opportunities as well as all the Notes, Histories and finally also associate the activities to that new Opportunity as well.

Well truth be told I feel that this should be a native built-in feature within ACT! personally. I decided to create a plugin that would mimic the Create Company from Contact functionality and have the field mappings within an XML file. I had toyed with the idea of centralising the XML file but almost immediately rejected that stupid idea due to concurrency and file lock issues. It would be easier to just install the plugin on each machine and have the XML there as well. The downside of this would be will be when people want to include additional fields then we will have to each machine and overwrite the XML. Actually a more likely scenario is that we’ll create a BAT file and run that from a Windows Logon script, but still not ideal!

I’m actually really quite pleased with this simple plugin and as with our usual policy at  Caldere, code that we feel would be of direct benefit to our client’s, we give them the applications/plugins etc for free as part of their support contract. By the end of the day, there was already 3 clients that were quite excited about the plugin since they had been experiencing the same kind of issues as our sales guys.