Sage CRM 7.2 : Shuffle that Workflow!

Workflows are an integral part of Sage CRM, and we all know how crucial they are to any business that relies on them to perform their daily processes.

In my experience as a consultant, there have been many times when users from client sites have contacted us to say that they have mistakenly progressed a workflow to an incorrect state and want to move it back to the previous state.

In today’s blog, I’ll be giving you step-by-step instructions on how you can ‘shuffle’ any workflow to the state your client user wants.

I’ll be taking an example of the Opportunity workflow and show how you can move it back from its current state to the previous one. We’ll assume a scenario where a lead is incorrectly set to “qualified” in the workflow; which means the workflow is in the ‘qualified state’, and we want to bring it back to the ‘lead state’.

So, let’s begin!

Step 1:

Let’s pick an opportunity in the Qualified state from a list of opportunities in Sage CRM. Now, when you hover your mouse on this opportunity, you will see a URL at the bottom of the screen that will tell you the Opportunity Id (Oppo_opportunityId – highlighted in green) for this particular opportunity. In this case, it is 22.

Step 2:

Having found the correct opportunity, it is time to look into the Opportunity table in your CRM database and find out its corresponding ‘workflow id’ (oppo_workflowid). In case you ever wondered what a ‘workflow instance’ was, this is it! You can find the workflow instance of an opportunity by executing the following SQL command.

SELECT  oppo_workflowid

FROM     Opportunity

WHERE  oppo_opportunityid = 22

The above SQL command returns the value of oppo_workflowid as 3165. With that information, we will now go to the WorkflowInstance table to get some more information about this workflow instance record in step 3.

Step 3:

There are two things that I always check in the WorkflowInstance table for a given workflow instance record

1. wkin_currentstateid, and

2. wkin_workflowid fields.

WkIn_currentstateid, is used to find out what current state the workflow under consideration is in, and WkIn_WorkflowId  helps me to figure out the right workflow, which in turn helps me to list down all the states for that workflow. You can find the wkin_currentstateid and wkin_workflowid by executing the following SQL command.

SELECT wkin_currentstateid, wkin_workflowid

FROM   WorkflowInstance

WHERE  WkIn_InstanceID = 3165

The above SQL command returns the values of wkin_currentstateid and wkin_workflowid as 11 and 2 respectively.

Step 4:

Now we will use the wkin_workflowid value, which is 2 in this case, from the above step, to list down all the states for this particular workflow from the WorkflowState table. You can execute the following SQL command for that.

SELECT *

FROM  WorkflowState

WHERE WkSt_WorkflowId = 2

Below is a snapshot of the result generated by the query above. As you can see, the table in the snapshot has got all the standard opportunity workflow states that comes with the vanilla Sage CRM install.

Step 5:

So as we know, this lead was incorrectly progressed to the qualified state, as you can see in the image below. We now have to bring it back to the lead state.

We can do so, by updating the current state field (wkin_currentstateid) for workflow instance 3165, which is 11 (Qualified state) at the moment, to 10 (Lead state). If you see in step 4, the state id (WkSt_StateId) for Lead is 10. Let’s write this in SQL

UPDATE WorkflowInstance

SET    WkIn_CurrentStateId = 10

WHERE  WkIn_InstanceID = 3165

Warning : It is extremely important to be certain of the workflow instance you will updating as a final step in the process, as updating the current state id of an incorrect workflow instance will progress another workflow created by someone else!

Now just right click on your browser and reload the frame, and you will see that this opportunity workflow is brought back one step to Lead state again as seen in the image below.

Similarly, you can ‘shuffle’ any workflow to any state you want by simply following the above 5 steps. I recommend you try this out on a test environment first to get a firm conceptual grasp of the steps above.

Let me know how you go. Questions, welcome!

Until next time…happy consulting!  🙂

Sage CRM 7.2 Client-Side API

Recently I was asked to hide the continue button on the ‘Case’ summary screen for one of our clients. As you can see in the image below, it is located on the top right corner of the screen.

'Continue' button on the top right corner of the screen.
Case summary screen with ‘Continue’ button

Since it was a client side customization, I was aware that it could be achieved by one of the many easy to use APIs released with the launch of Sage CRM 7.2. These new APIs can be accessed from http://help.sagecrm.com/js-api/classes/index.html

Sage CRM 7.2 client-side API
Sage CRM 7.2 client-side API

Coming back to our client’s requirement, I went to the above link, and found the ‘hideButton’ API to achieve the goal.

Having found the right API, following are the steps to plug it in Sage CRM 7.2

1.  Go to Administration -> Customisation -> Cases -> Screens

2. Select the correct screen you want the API to trigger for when it loads. Remember you can enable ‘inline customisation’ to find out the right screen.

3. Once you are sure of the screen name, select it from the list of screens in the Case entity (in my case it was ‘CaseWebPicker’). This will give you an access to the ‘Custom Content’ area where in you can type the following javascript code that uses the above API.

4. Code:

     <script>

                crm.ready(function()

                {

                                crm.hideButton(“Continue.gif”);

                })

   </script>

Click update and save the changes.

And voila! The continue button has disappeared!

Case summary screen without the 'Continue' button.
Case summary screen without the ‘Continue’ button.

Similarly, you can use many of the client side APIs with Sage CRM 7.2 to achieve your customisation requirements. The other benefit of using the new APIs is that they are browser independent, and you can use them across IE, Chrome or Firefox without any issues.

I would like to hear about you’ve leveraged Sage CRM 7.2 client side API. Feel free to drop in a line or two about your experience.

Happy Customising!

SQL Server: Copy database from recent to older versions

Recently, I needed to copy a database from SQL Server 2012 to SQL Server 2008 R2.  Unfortunately, database backups from one version of SQL Server cannot be read by a lower version.  To work around this, I had to use the “Generate Scripts” function to first create the structure and then populate the database.

Whilst none of this is particularly complex, I thought it might be useful to collate the steps in one document…

Step 1: Generate structure scripts

  1. In the SQL Server instance that contains the database to copy, right-click on the Database in the Object Explorer, select Tasks, then select Generate Scripts.
  2. In the Wizard, progress to Choose Objects and make sure that “Script entire databases and all database objects” is selected.

    Screenshot: Select "entire database" option
    Select the “entire database” option.
  3. Progress to the “Set Scripting Options” stage.  Click on “Advanced” and make sure that the “Type of data to script” option is set to “Schema only”.  Also set the “Script for Server Version” option to the other SQL Server’s version (I still ran into incompatibility problems with the generated script, however).

    Screenshot: Advanced options in Generate Scripts
    Advanced options in Generate Scripts
  4. Finish off the Wizard, copy the generated scripts to a location that the other SQL Server instance can access and run them in that Management Studio.  Here are the main problems and fixes I ran into during this part of the process:
    1. Error message that “CONTAINMENT is marked as incorrect syntax”:  Just comment out the “CONTAINMENT = NONE” line (source: http://stackoverflow.com/a/15410854)
    2. Error message that CREATE Database commands can’t be run in a multi-transaction statement:  Just run the create statement separately.
    3. Error when trying to alter user to add role:  This command is not supported in older versions; change those commands to use “sp_addrolemember” (e.g. EXEC sp_addrolemember ‘db_datareader’, ‘Administrator’) (source: http://technet.microsoft.com/en-us/library/ms187750.aspx).
  5. If that all goes well, you should now have the database skeleton ready.  The next step is to import the data.
  6. Back on the originating SQL Server, go to Generate Scripts again, but this time in the Advanced options, set “Type of data to script” to “Data only”.  Because this will probably require a lot of space, in the “Set Scripting Options” screen, set “Files to generate” to “Single file per object”, to split up all of the scripts by table.  Finish the Wizard.
  7. You should now have a bunch of script files, ready to be run.  Copy the containing directory to a location the older SQL Server can access.  Because I had 219 generated files, to save on running them manually, I used the following batch script (create and run it from the containing folder):
    for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
    pause

    (source: http://stackoverflow.com/a/6504317)

  8. The above script may take a while to run, but after that, you should be done!

 

How do I output a link to a CRM record, in an email?

Jeff Richards describes how to do it in this post:
community.sagecrm.com/…/embedded-links-in-workflow-emails.aspx

Basically, your Email template needs to have the following text:

#recordanchor# <Anchor text goes here> #recordanchorend#

If you’re manually constructing the link (e.g. via a script), you can use the same “SessionFind” URL structure e.g.:

/CRM/eware.dll/SessionFind?Act=200&CLk=T&Key0=1&Key1=300123

The above URL will redirect the user to the Company Summary page (Act=200) for the company (key0=1) with a comp_companyid of ‘300123’ (key1=300123).

NOTE: The user will always have to login when clicking these links (even if they already have an open session).

How do I set my account to login to a custom screen?

Sage CRM allows users to customise which screen should be displayed by default, when they login.

image2013-8-7 16-35-27

But what if you want to login to a custom screen (e.g. an ASP file)?  In that case, you need to:

  1. Open the following file for editing
    \CRM\WWWRoot\InteractiveDashboard\InteractiveDashboard.asp
  2. Put in a Javascript redirect for the page you would like to redirect to e.g.:
    <script type="text/javascript">
        var strURL = window.location.href;
        strURL = strURL.replace(/\/InteractiveDashboard\/InteractiveDashboard\.asp/, '/CustomPages/CA/Dashboard/index.asp');
        strURL = strURL.replace(/\?.*(SID=\d+).*/, '?' + "$1");
        window.location = strURL;
    </script>
  3. Because the Dashboard is hardcoded to display a set of tabs at the top of the iframe, you will also need to remove those – you could do this by hiding the tabs. For example, in ‘Dashboard/index.asp’, include the following:
    <script>
    // Hide the InteractiveDashboards tabs, that are included in the parent frameset.
    $("#landingpage_top_frame", parent.document).hide();
    </script>
  4. Now, any user with “Dashboard” set as their “Log Me In To” option should be redirected to your custom page.

How do I summarize, in days, when each Opportunity was last modified?

More specifically, this information is usually intended to be outputted as an additional column, in the Opportunity pipeline screen.

How the "Days in Stage" column appears in the Opportunity pipeline.
How the “Days in Stage” column appears in the Opportunity pipeline.

To do this, you’ll need to:

  1. Modify the database view used by the Opportunity pipeline.
  2. Create a translation.
  3. Add the new column to the pipeline screen.

 

Step 1: Modify vListOpportunities

The Pipeline screen uses the vListOpportunities view, to provide the columns for it to display.  In Sage CRM, go to the Administration section, select the Opportunties entity and under the Views tab, update the view definition to include our new field:

CAST((DATEDIFF(hh, wi.WkIn_UpdatedDate, GETDATE()) / CAST(24 AS DECIMAL(18,1))) AS DECIMAL(18,1)) AS 'oppo_daysinstage',

and an inner join to the Workflow Instance table (we’re using the Updated Date on the workflow instance as an indicator of when the Opportunity was last modified; you could just use the Opportunity’s Updated Date, but then that wouldn’t include workflow progression).

INNER JOIN WorkflowInstance AS wi 
		ON wi.Wkin_CurrentEntityId = 10 
			AND wi.WkIn_CurrentRecordId = oppo_opportunityid

Your view definition should end up looking like the following:

View definition for vListOpportunities.
View definition for vListOpportunities.

Step 2: Add a translation for the new field

To display a user-friendly name for the oppo_daysinstage field, go to Administration -> Customisation -> Translations and create a new translation using the following details:

Parameters for a new oppo_daysinstage translation.
Parameters for a new oppo_daysinstage translation.

 

Step 3: Add “Days in Stage” to the Pipeline screen

For the last step, go to Administration -> Customisation -> Opportunities -> Lists and select the Opportunities List item.  Select the “Days in Stage” field in the Field drop-down, click Add and then save your changes.

Adding the "Days in Stage" field as a new column.
Adding the “Days in Stage” field as a new column.

When you go back to the Dashboard, your Opportunities Pipeline should now display this brand new field!

My CRM buttons are no longer appearing in Outlook?

If the Sage CRM plugin for Outlook is working correctly, you should see  a “File e-mail” button in your Outlook toolbar.

Sage CRM plugin buttons in Outlook
Sage CRM plugin buttons in Outlook

If not, try the following steps to get it working:

Requirements

Check that your PC has the required libraries installed.  In the Add/Remove Programs window, you should see:

  • Visual Studio Tools for Office 2010 (VSTO)
  • Microsoft Office 2007 Primary Interop Assemblies
  • Microsoft .Net Framework 4 Client Profile

We’ve also encountered issues if the user account is setup as a roaming profile.  If so, try changing the account to a local profile.

Check Trust Centre

In the Outlook client, open the Trust Centre and check if the Sage CRM plugin is listed in the Add-ins section.  If it is, make sure that it is enabled and trusted by Outlook.

Re-install the plugin

You can find the installer for the plugin in the <Sage CRM Install Directory>\CRM\WWWRoot\Plugin directory (e.g. C:\Program Files (x86)\Sage\CRM\CRM\WWWRoot\Plugin).  Run the plugin appropriate for your setup (e.g. for Exchange integration, use CRMClientExSetup.exe; for just Outlook-client integration, use CRMClientSetup.exe).

Keep in mind that the Outlook user must have local administrator permissions on their PC, the first time that they use the plugin (e.g. in Windows 7, go to the Control Panel, select Users, click on Give other users access to this computer and then add the Outlook user account as an Administrator).

If this still does not work, try logging into the PC as Administrator and re-installing the plugin.

Cleaning up the registry

If the plugin is still not working, you might need to search the Windows registry to see if there are any old or corrupt entries that are confusing the plugin.  As always, make sure to backup your registry before making any changes.

Ideally, the plugin’s registry entries should be under HKEY_CURRENT_USER\SOFTWARE\Sage\Exchange Outlook Plugin.

Outlook itself will also keep registry entries for its plugins (e.g. HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\Outlook\Addins\OutlookAddin2003).

Delete any CRM Outlook entries you find in these folders.

After that, just try searching the registry for any matches on ‘CRM’ and manually checking each.

Remove downloaded files

Check C:\Windows\Downloaded Program Files for any CRM Outlook related files (e.g. crmoutlookplugin1.dll).  Delete all of these files.

Restart the CRM server

Sometimes the plugin will be unable to connect to the CRM server (either during the install or after), due to corrupted metadata on the CRM server.  In these cases, refreshing the metadata on the CRM server (or even restarting the CRM server) will fix the issue.