Sage ACT! Universal Search

OK disclosure: This is an old post from a previous Blog that I fished out, I decided to leave it in its original format referring to ACT! 2012, but it is still pertinent to ACT! 2013.

Well now that ACT! 2012 has been released for a little while, I thought I’d highlight one of the new features that has had a real impact on me. I have to admit that initially I wasn’t much moved by the Universal Search feature. I mean I could see how it might benefit clients etc. but I really didn’t think it was cause any real fundamental shift in the way I use ACT!, after all I’ve been using ACT! for over 15 years now and am comfortable and set in my ways!

Well that was until I really started using Universal Search! Whenever we get support calls from any client, the techy dealing with the call afterwards will update the history and associate it with the Company etc. Sometimes we use the Sage ACC knowledgebase sometimes we have additional links etc., but we always ensure that within the History we have the Regarding filled out as well as key words within the body of the History such as “Synch error ‘Server not available’” so that if we are dealing with a client that is having the same issue repeatedly we can try and look further into what might be causing this or the next techy knows what steps have been taken so far to date…this is nothing new or amazing and I am sure every ACT! user in the world does this kind of thing in some shape or fashion.

Today I had a client that was having a problem with their ACT!, UI where half way through the day of heavy usage they got red diagonal lines across various parts of their UI. This is an indication that the machine has run out of Windows GDI objects. To rectify this we can go into the registry and make some small changes by increasing the GDI pool. The problem was I couldn’t remember the exact registry key and I couldn’t remember which client’s in the past we have done this for (as it turns out quite a few!). So I thought instead of the keyword search I would use Universal Search. Wow! I just plugged in the words ‘GDI’ and hit search. It came back with loads of histories and additional information like attached web pages,  Word documents all sorts of stuff almost instantly (I think there was a slight 2 second wait) and all I had to do was click on one history. The great difference between this and Keyword search was that in keyword search it takes you to the Contact and then you have to drill down in the history tab. Now when I clicked it brought up the History itself with all the details I was after! Cool! Well anyway thought it would be an interesting thing to put up here in case anyone else is a Universal Search virgin!

The Universal Search facility within ACT! is actually a licenced 3rd party component called dtSearch. The component is actually quite an advanced and powerful tool that has it’s own search “syntax”. Here is a table from the ACT! knowledgebase showing some of the search syntax:

Special Character/ Operator	Description	Example	Search Result
*	Match any number of characters	gre*	All items containing at least gre
?	Match any character	gre?	All items containing four-letter words withgre
=	Match any single digit	15=	All items containing at least the numbers 1 and 5
~~	Numeric range	10~~150	All items containing data of the numeric range from 10 to 150
and	Must match both terms	green and plastic	All items containing green and plastic
or	Must match either terms	green or plastic	All items containing green or plastic
w/[x]	Second term must appear within X words of first term	green w/10 plastic	All items containing the term plastic within 10 words of the term green
not w/[x]	Second term must not appear within X words of first term	green not w/2 plastic	All items not containing the term plasticwithin 2 words of the term green
and not	Only first term must be present	green and not plastic	All items containing the term green but not the term plastic
w/[x]xfirst word	Term must occur within the first [x] number of words	green w/5xfirstword	All items contain the term green within the first five words
w/[x]xlast word	Term must occur within the last [x] number of words	green w/5xlastword	All items contain the term green within the last five words

Another cool feature with Universal Search is that you can search for values within a specific field, so say you wanted to find all Contacts with Kristi in their Contact name you can use the following syntax:

Contact_contact::Kristi

The first part Contact_ lets the Universal Search know which table or Entity you are searching within, so if you were searching for an Opportunity field you would write Opportunity_. The second part contact:: is the field and the final part is the actual value, so in our case we are searching for all Contacts that start with Kristi. Pretty cool?

Now I’ll be the first to admit that this may not seem that useful when you can easily go into Contact detail view and right click in the field you want to search, but consider you are unable to utilise right click then this technique is a quick and easy way. Ok, so the next question is when are you not going to be able to use right-click? Simple, within the ACT! for Web interface, or more importantly if you are using ACT! Premium for Mobile. The search within that interface is driven purely by the Universal Search control.

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 4^th 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!