If you have an external database that controls User Certification you can use this to control step signoffs.
For instance, a User is part of the 'Testing' group and is certified for 6 more months. You would like this User to have the ability to perform Testing, but when the certification expires you wish to prevent the User from signing off the Step.
Pre-Requisite
You must first have a database running and have access to the server. You can test connection to the database from within EZ-MES, which we will cover later. All you need to have available at the moment is the connection string.
You should also already have a Flow Definition configured with at least one Step Definition in it. We will edit the configuration of this Step later so that it verifies the Users certification.
Configuration
Step Definition
On the Step Definition that you wish to validate the Users Certification you need to add a Spec to define the allowed Role that can sign off this Step. This particular Step allows for the 'TESTING' role to sign off the Step. Use the following values:
- Spec Name: CERT REQ
- Data Type: Text
- Spec Value: TESTING
Event Definition
Create an Event Definition with the following attributes.
- Event Name: Cert Check
- Event Type: Records
- Do Not Check on a Time Loop: true
Trigger
Add a Trigger with the following attributes to the Event Definition you just created (Cert Check).
- Trigger Name: true
- Done String Formula: true
Action
Add an Action with the following attributes to the Event Definition you just created (Cert Check).
- Action Name: SQL ADAPTER Cert Check
- Action Type: SQL Adapter
- Connection String (the following is an example): Server=YourSeverName;Database=CertificationDB;User Id=username;Password=password;
- Select-Update Statement: this will be the SQL Statement used to query the result of your externally controled User Certification. In our case we have a stored procedure in which we provide the current username and certification role name. It looks as follows:
- SELECT dbo.ValidateCertification('<%USER%>', '<%CP%>', 'all') CERTIFIED
- Notice that the above statement is calling 2 variables, USER and CP.
- Select-Update: DataTable
- Create the 2 variables previously mentioned, USER and CP.
- USER = Pre-Defined Variables [-- Current User --]
- CP = Variable SP [ CERT REQ ]
After the above configuration is complete, you can use the Test Connection String button 
. If this fails than you should adjust your connection string values.
Continued Step Definition Configuration
It is necessary to perform more configuration on the Step Definition. This time, on the Advanced Tab scroll down to the 'Step Validation Event Configuration Section'. User the following settings:
- Event: Cert Check
- Event Evaluation Equal to: true
- Event Error Message: This is optional. You can put a custom error message.
Execution of Step Sign Off
Below is an example of a non-valid certification sign off of a Step. If the certification for the User is not expired then the sign off will proceed as usual.
