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.

Blogging and Social Media

revolution

Well I’ve got to hang my head in shame. I have to admit that wise people such as Pat Egan from Egen Consulting was right in what she has been advising not only me, but all ACCs for a great many years now, engaging in Social Media is not only important, it can be fun! I have been one of those rather boorish, luddite techies that has been known to proudly exclaim  “I don’t do, like or trust social media! What’s the point? If I want people to know what I ate for dinner last night, I’ll phone them and tell them or email them!”

I began a Facebook Page a couple of weeks ago and started to engage with this “social stuff” about a week ago purely as an experiment to see what kind of data is generated and collected by Facebook and to see what use it could be to extract this raw data. I had downloaded an excel plugin/tool from Microsoft for data analysis and reporting which is part of it’s drive towards Self-Service BI. One of the out-of-the-box features included the ability to directly connect to the backend data source that Facebook Insight uses. Now at the time, I had absolutely no idea what Insights was, but after conferring with other ACCs and Swiftpage employees that were more versed in the mysteries of Facebook I was advised to start my own Facebook page which I did. One of the problems I immediately faced was to use Insights, your page needs to acquire 21 Likes. When I had started out I had not thought that this would be a particularly difficult target to achieve and expected to achieve this within a few days so I could get down to the true task of analysing the data and comparing what I got within Excel against Insights and which would be of more use. One key question I had was, what was the point of connecting Excel to Facebook?

It’s now been almost 3 weeks and I have only managed to scrape up 18 Likes within Facebook and I have found myself reading many Blogs, learning about a whole new medium, starting a Blogg but above all for the first time I can say that I am actually genuinely enjoying myself! This has been the biggest shock to me, one that I had never envisaged. My plan had been to accumulate the required Likes get the data analyse and report back to the ACC community. I’m actually glad I have failed in my primary task currently. What it has forced me to do is read articles such as 7 Deadly Sins of Blogging by CopyBlogger or the historically interesting piece by Mike Lazarus Happy 25th Birthday SageACT!.

I think the biggest impact on me has been the concept of free-sharing. This is something that is very close to my heart and I have spent much time involved with open source projects. When we started Caldere, we wanted to try and create a community of sharing between ACT! professionals within the UK, sadly we met many road blocks and quite often we were taken advantage of due to our naivety, I am glad to see that the rest of the business world is closer to what had hoped for. The social media revolution (if I can call it a revolution), has really changed the face of how organisations, especially smaller businesses, engage not only with their clients, but also with their competition and peers. There does seem to be a much more of a sharing feel which is like a fresh breath of air. It is only a very few Facebookers that now still embrace the “old-world” mentality of stripping out originator wrappers to shared posts. There seems to be a genuine air of gratitude, collaboration and a general attitude of “playing with a straight bat”, I’m glad I’m now taking an active part in this new revolution!

The big question facing me now is how to mould this blogg and Facebook page since I had started it without any real direction. My suspicion is that I will most probably position it predominantly as a technical blogg, I’ve also importantly learnt that blogg is spelt with a double ‘g’ and that getting “Likes” has become an obsession!

WordPress Tags: Self service BI,Social Media,Facebook,data analysis,Microsoft,Insights,Swiftpage,Excel,SageACT,Caldere,revolution,collaboration

Windows 8 Keyboard Shortcuts

I’m the office guinea pig when it comes to new software, I was the first and only miserable wretch that had to endure the sheer agony of running Windows Vista on my machine so I could become familiar with the new OS and educate everyone else. In fact after my initial experience with Vista we held off till Windows 7 came to the rescue and everyone upgraded to that. So when Windows 8 arrived in our download folder at the partner site, I kindly offered my machine up once again for the “greater good”.

I must be honest, I really was not happy and had tried to “volunteer” the new intern, but everyone decided that it would be wiser for me to do as I had with Vista. I hated Windows 8 even before I had it installed on my machine! Well it’s been quite a few months now that I have been using Win8 as my primary OS on my product dev machine, I did leave my Win7 dual boot in case things got too hairy, and the truth is I don’t actually mind Windows 8. All my reservations regarding the Metro screen have not materialised, in fact I only ever see the Metro screen when I boot up, need to search for something or need to play Pinball.

I made an early decision not to install any of the GUI apps that add a START menu since I wanted to get familiar with the vanilla version of Windows 8, this meant that I have had to learn or re-learn many run line commands as well as keyboard shortcuts. There are a multitude of sites out there that already list these, but what kind of technical blogg would this be if I didn’t include my very own list!

Shortcut Action
Win + C Opens the charms
Win + F Opens Search
Win + I Opens the Settings charm
Win + K Opens the Devices charm
Win + Q Search for an Application
Win + W Search for a Setting
Win + X Shows the Admin List bottom left

I think the two most used shortcuts from Windows 7 are Win + E (Windows Explorer) and Win + R (Run command). Win+R allows me to then run applications without needing to further litter my desktop with icons, so MS Word = winword, Excel = excel, Outlook = outlook,  Services = services.msc, SQL command tool = sqlcmd –S.\act7 and finally Powershell ISE = powershell ise….it’]s not rocket science as you can see! LOL

Windows Live Writer

Well this post isn’t ACT! related at all and I’m still trying to get used to writing a Blog  Initially I was using the web-based interface from the WordPress Admin screen. After that I started to write in Word and copy-paste into the web editor.

I did a quick Google to see if it was possible to directly connect Word to my Blog  and as I suspected it was possible but the really interesting thing was everyone seemed to be talking about Windows Live Writer. I did remember seeing some reference to this when I downloaded the newest version of Windows Messenger but hadn’t really paid any attention to it. Now that I have a Blog, I thought I might as well try it out and see what everyone was talking about.

I have to say I am impressed with this little utility! I’m actually using it at the moment to write this up! It’s light, quick and very simple to use and I get the opportunity to Preview as well with my chosen theme. I’ve installed a couple of plugins such as Insert Video and Insert Code. The only downside to all this is that the Insert Code plugin seems to be a little buggy in this Windows 8 version of Live Writer 2012, it seems to work fine when I boot into Win 7. The other point is that as I understand it, Microsoft have stopped any further development on Live Writer and thus it retains the old “Live” brand name. I would love to hear from others about their views of offline Blog editors.

Perfecting ACT! 2013

Wow only a few weeks left till this year’s Perfecting ACT! (PA) kicks off, and this time it’s going to be an absolutely can’t miss event! As anyone reading this most probably already knows, Swiftpage have purchased ACT! (and Saleslogix) off Sage and are now our new Overlords. All praise and all glories to our new rulers and masters…yadda yadda yadda!

So what does this mean to us humble ACT! fanbois and ACCs? Well it’s really exciting and one of the best bits of news we could have hoped for. ACT! has been floundering for years under Sage’s stewardship, the secret truth was that Sage never invested in the product vision or the R&D. The management team behind ACT! had a passion and drive for the product that was both bipolarly divergent and far out reached the vision that Sage ever had for ACT!. Sage as a company are, by behaviour, primarily a M&A vehicle i.e., they acquire software companies to capitalise upon a market rather than innovate or develop. ACT! was such a product that had languished in budgetary anorexia due to executive disillusionment in the revenue it generated. The expectations far out stripped the reality that any business productivity software requires…R&D and marketing investment.

Swiftpage is a very different beast entirely. It is a software company that has grown only by investment as an add-on as well as a stand alone e-marketing product. I have to be honest here and confess that I have felt in the past that Swiftpage was a clunky and slightly out-dated product compared to some of the market leaders such as MailChimp etc. I think with not only ACT! and SLX within their portfolio now, all products can symbiotically benefit with the renewed investment, vision and above all budget.

PA 2013 will be the first time I will get to meet the key strategic members of the Swiftpage team and we will all get the opportunity to see the future vision and road map for ACT!.

The other point of note for me personally, it will be the first PA that I shall be presenting to the delegates. The topic I have chosen is reporting and PowerPivot. Expect some posts in this blog regarding PowerPivot, either in the near future or in the past (please see my About page to understand that weird comment!).

Sage ACT! Universal Search

search buttonOK 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 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!