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

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.