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.


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!  🙂