One of the most common “wish list” items we hear from Vision users is:
“Can’t Vision set up my project file folder when I take out a new project?”
Really what we’re talking about here is a small piece of “Process Automation“. To an end-user it’s about convenience and common sense – why do things twice? But to me this is a great opportunity to develop a business process that will ensure we have consistent naming and filing, save time and make our end-user’s lives that much easier.
Well from a purely technical perspective, sure just about anything is possible right? But how exactly do we go about something like this?
We will need to know three things: a little bit of Vision workflow, a little bit of SQL Stored Procedure work and lastly some Powershell scripting. Once we break it down it’s actually not that difficult.
Let’s take a very simple example:
We want to have Vision create a new project folder using a concatenation of the WBS1 project number and the project short name (a common project folder naming convention).
I won’t go into great detail in this post but rather provide a high level overview of the basic steps to accomplish this:
Powershell is Microsoft’s flagship scripting language and is built into its operating systems and can even be coded right into SQL. Powershell is needed in this case because we need to “step out” of Vision and access the file structure of our network to create the folder. This is where Powershell excels.
Creating our script is the first step of the process and at its heart we’re simply going to call the it from a SQL stored procedure and pass it 2 parameters: the folder name to create, and the base location to create it in. Once the script is called and passed the parameters it will use some old school DOS commands to create the folder. The script might look something like:
‘Grab the base folder from the parameter and store it
$RootFolder = $MyRootFolderParameter
‘Grab the new folder name from the parameter and store it
$FolderName = $MyFolderNameParameter
‘Create the new folder in the base location using make dir command
md $RootFolder + ‘\’ + $FolderName
We can test that this works by calling the script from the Powershell console and passing it some dummy parameters to perhaps simulate in a sample folder structure.
- Stored Procedure:
Now that we have our script working we need a way to access it from Vision. Vision can call a SQL Stored Procedure from a workflow and easily pass parameters to it (our WBS1 and Short Name).
I won’t go into the details of how Stored Procedures are written in SQL but it is quite easy to create a simple one such as this.
The Stored Procedure is really an intermediary for us to get from a Vision workflow to our Powershell script. It will require our 2 input parameters which it will then pass to the script when we call it.
To call a Powershell script from a Stored Procedure we need to use a built-in SQL procedure called xp_commandshell.
Note that xp_commandshell is not enabled by default and enabling it should be done only once you fully understand the potential security risks and how to enable it safely!
Once our procedure has pulled in the input parameters we have lots of ways to manipulate them before passing them to the Powershell script. So for instance say we want the folder name to be a concatenation of WBS1 and the ShortName with a hyphen between. We can do this like so:
Declare @NewFolderName varchar (50)
Set @NewFolderName = @InputParamWBS1 + ‘ – ‘ + @InputParamShortName
- Vision Workflow:
Before we start creating any workflow we need to know if the workflow will fire automatically to generate the folder or if we want the end-user to be in control with a button that fires the workflow. A button will require a “scheduled workflow” while a “user initiated workflow” can be triggered automatically. For testing purposes a button works best since we can fire it at will and not have to create a new project record every time we want to test.
So to create a scheduled workflow we navigate to Configuration–>Workflow–>Scheduled Workflows, select Project from the drop down and insert a new workflow. Give it a description that is meaningful and don’t forget that we have to provide a condition for it to run. Typically we would restrict it to only run on Active projects or some other simple condition.
Next we insert an Action for the workflow and choose Stored Procedure and enter the name of the Stored Procedure we created in step 2. Conveniently, Vision will see the Parameters you have created in the Stored Procedure and automatically list them. You can then either enter a static value to pass (not likely) or you can hit the look up button (…) and now you can assign each parameter a dynamic value from the Project Info Center fields (PR table) as well as from the ProjectCustomTabFields table which keeps all of your custom fields in the Project Info Center.
Lastly we need to add a button somewhere in the Project Info Center using the screen designer. The button will need to be linked to your scheduled workflow and given a caption and that’s it!
We now have a button that when clicked will pass the desired parameters from the Project record to a workflow, which will in turn pass them to a stored procedure, which will in turn call our Powershell script and create our directory.
In summary this is a very simplistic overview of some of the steps to create this sort of automated folder creation. Is it the only way or the best way to do it? That’s open for debate since there are number of ways this could be accomplished. My purpose was merely to demonstrate what is possible and how it can be done relatively easily. Every situation is different and you will need to fully understand your security requirements and your business process needs. But most importantly I hope to demonstrate that with a little thought, many of our biggest pet peeves and mundane tasks can be automated and form the basis for consistent data entry and record keeping both in and outside of Vision.
Feel free to contact me if you’d like more info on accomplishing this or a similar process automation task!