Enforce status progression through column validation

Hello,

We received this question from a client and wanted to share the answer with all of you, as many SharePoint users could find it useful.

The customer wanted to set up validation rules on a task list using Smart List Pro in such as way, that users would be allowed to change the task status only in a specific order:

• Not Started
• In Progress
• Completed

So, for instance, a task that is not started cannot be change to Completed without first going through In Progress stage.

To do so, we would need to store the previous value of the status column. This value, hidden from users, will be filled out automatically and used by our validation rules. We will need to install both Smart List Pro and Smart ID Pro for this to work.

• Add Old Status column to the list (it can be simple single line of text). Do not add it to the default view.
• Using Advanced column permissions, set the column to be hidden in all forms, users do not need to see it.
• Using Smart ID Pro set a rule to set Old Status column to the value of Status column

Now we are ready to set up our validation rules.

Number of rules is according to the number of status options, 3 in this case. For instance, rule #1 checks that the status value does not equal Completed when Old Status (the value of Status prior to attempted change) is equal to Not Started. This makes sure you cannot change from Not Started directly to Completed. The other two rules work in the similar way. The second rule makes sure you cannot change from In Progress back to Not Started. The third rule makes sure you cannot change Completed status to anything else.

You can also add rules to exempt your administrator, so you will have the ability to still change the values if a mistake was made. This is not shown here, but can be added just as easily.

When a user attempt to make an illegal change, he/she is blocked and the error message is shown: