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!

Advertisements

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