Internal Database Views

A user with IT Admin access can create an internal SQL View in Process Director’s database. The views will be created on the database after clicking the Create Internal VIEWs link on the IT Admin’s troubleshooting page.

We have included multiple pre-constructed Views for use with the reporting and they could also be used in your Knowledge Views. Here is a detailed description of each field in the Views. At the end of this section, there is a short explanation of some of the more advanced database concepts that are implicated by using these Views.

Enumerators #

Several of the Views use common enumerators for status, termination reason, etc.

IsTimeline Enumerator

The IsTimeline field will contain one of the following values.

STATUS	DEFINITION
0	Workflow.
1	Process Timeline.

Status Enumerator

The Status field will contain one of the following status codes.

STATUS	DEFINITION
0	Pending. The process is pending (not yet running.
1	Active. The process is running.
2	Inactive. The process has been completed

TermReason Enumerator

The TermReason field will contain one of the following termination codes.

STATUS	DEFINITION
0	NotSet. Not yet terminated.
1	Normal. Normal termination.
2	Timeout . Timeout occurred causing termination of step or Workflow or project (ripple to "non-done" users in step).
3	Did Not Finish. The user did not finish because enough users responded to transition to next step (e.g. approval %).
4	Canceled. Admin canceled Workflow, a step, or a user in a step.
5	Reassigned. This user step/activity was reassigned to another user.
6	NotNeeded. The step/activity was not needed (because of rule).
7	Notified. This value indicates it was a notify step/activity and the user was notified (so it shows up in the routing slip).
8	Stopped. Process stopped at this activity, no dependencies will be met.

Views

The following SQL Views are available for use as a default.

bpProcess

COLUMN	DATA TYPE	LENGTH	NULLABLE	EXPLANATION	POSSIBLE VALUES
DefinitionName	nvarchar	256	No	The name of the process definition.
InstanceName	nvarchar	256	No	The name of the process instance.
ID	varchar	36	No	The ID of the process definition.
INSTID	varchar	36	No	The ID of the process instance.
InitiatorUserID	varchar	36	Yes	The User UID of the initiator of the process.
IsTimeline	int		No	If this process is a timeline.	See IsTimeline Enumerator.
PARENTPROCESSID	varchar	36	Yes	The instance ID of the parent process if this process has been launched as a sub-process of a timeline.
FormInstanceID	varchar	36	Yes	The Instance ID of the form instance.
Status	int		No	Whether or not this process is active.	See Status Enumerator.
TermReason	int		No	The reason this process is no longer active.	See TermReason Enumerator.
StartTime	datetime		Yes	The date and time that this process was started.
EndTime	datetime		Yes	The date and time that this process ended.
PredictedEndTime	datetime		Yes	The date and time that this project is predicted to end. These dates are constantly updated to reflect changes to processes.
IsComplete	int		No	Is the process complete?
IsActive	int		No	Is the process active?
ElapsedSeconds	int		No	The number of seconds that have elapsed since the process started, or the total runtime for the task, if complete.

bpProcessTask

COLUMN	DATA TYPE	LENGTH	NULLABLE	EXPLANATION	POSSIBLE VALUES
DefinitionName	nvarchar	256	No	The name of the process definition.
InstanceName	nvarchar	256	No	The name of the process instance.
TaskName	nvarchar	128	Yes	The name of the task definition.
ID	varchar	36	Yes	The ID of the process definition.
INSTID	varchar	36	Yes	The ID of the process instance.
TASKID	varchar	36	Yes	The definition ID of the task.
TASKINSTID	varchar	36	Yes	The instance ID of the task.
IsTimeline	int		No	If this process is a Timeline.	See IsTimeline Enumerator.
IsUserTask	int		No	Is the task a User task?
IsProcessTask	int		No	Is the task a Process task?
SUBPROCESSINSTID	varchar	36	Yes	The Instance ID of the subprocess instance for the task, if any.
SUBPROCESSID	varchar	36	Yes	The Process ID of the subprocess for the task, if any.
Status	int		No	Whether or not this process is active.	See Status Enumerator.
TermReason	int		No	The reason this process is no longer active.	See TermReason Enumerator.
StartTime	datetime		yes	The date and time that this process was started.
EndTime	datetime		Yes	The date and time that this process ended.
DueTime	datetime		Yes	The assigned / calculated due date for this task.
PredictedStartTime	datetime		Yes	The date and time that this project is predicted to start. These dates are constantly updated to reflect changes to processes.
PredictedEndTime	datetime		Yes	The date and time that this project is predicted to end. These dates are constantly updated to reflect changes to processes.
Result	nvarchar		Yes	The activity result, or the branch name taken for a step.
IsPredictedLate	int		No	Is the task predicted to be late?
IsRunningLate	int		No	Is the task running late?
IsRunningOntime	int		No	Is the task running within the predicted time frame?
IsCompletedLate	int		No	Did the task end later than scheduled.
IsCompletedOntime	int		No	Did the task end within the predicted time frame?
IsComplete	int		No	Is the task complete?
IsActive	int		No	Is the task running currently?
IsPending	int		No	Is the task in Pending status?
ElapsedSeconds	int		No	The number of seconds that have elapsed since the process started, or the total run time for the task, if complete.

bpProcessTaskUser

COLUMN	DATA TYPE	LENGTH	NULLABLE	EXPLANATION	POSSIBLE VALUES
DefinitionName	nvarchar	256	No	The name of the process definition.
InstanceName	nvarchar	256	No	The name of the process instance.
UserUID	varchar	36	Yes	The User UID of the assigned user.
TaskName	nvarchar	128	Yes	The name of the task.
TASKID	varchar	36	Yes	The task definition ID
TASKINSTID	varchar	36	No	The instance ID for the task
ID	varchar	36	Yes	The Definition ID for the process
INSTID	varchar	36	No	The instance ID for the process
IsTimeline	int		No	If this process is a Timeline.
Status	int		No	Whether or not this process is active.	See Status Enumerator
TermReason	int		No	The reason this process is no longer active.	See TermReason Enumerator.
StartTime	datetime		Yes	The starting time of the task.
EndTime	datetime		Yes	The ending time of the task.
UserComment	nvarchar	Max	Yes	These are the routing slip comments.
Result	nvarchar	128	Yes	These is the activity result, or the Workflow branch taken in a task that has been completed.
IsComplete	int		No	Is the task complete?
IsActive	int		No	Is the task running currently?
IsPending	int		No	Is the task in Pending status?
ElapsedSeconds	int		No	The number of seconds that have elapsed since the process started, or the total runtime for the task, if complete.

bpTaskList

COLUMN	DATA TYPE	LENGTH	NULLABLE	EXPLANATION	POSSIBLE VALUES
TLID	varchar	36	No	The Task List ID. A unique identifier for this particular Task.
UserUID	varchar	36	Yes	The user that has been assigned this task.
TaskListType	int		No	The kind of task that has been assigned to the user.	0 = NotSet 1 = Workflow Task 2 = Check Out 3 = Saved Form Instance 4 = Process Timeline Task
ObjectID	varchar	36	Yes	The specific and unique object ID for this document or process instance.
ObjectType	int		No	The type of object that this task represents.	0 = NotSet 1 = Document 7 = Workflow Instance 60 = Process Timeline Instance
TASKID	varchar	36	Yes	The ID of the task definition.
TASKINSTID	varchar	36	Yes	The ID of the task instance.
TaskName	nvarchar	128	Yes	The name of the Task like “User Step 3” or “Notification”
TaskInstructions	nvarchar	1024	Yes
Priority	int		No	The assigned priority.	1 = High Priority 2 = Medium Priority 3 = Low Priority
AssignTime	datetime		Yes	The time that the task was assigned.
DueTime	datetime		Yes	The time that the task is due.

bpUser

COLUMN	DATA TYPE	LENGTH	NULLABLE	EXPLANATION	POSSIBLE VALUES
UserID	nvarchar	64	Yes	The user logon name.
UserName	nvarchar	128	Yes	The name of the user.
EmailAddress	nvarchar	256	Yes	The email address of the user.
UserUID	varchar	36	No	The User UID of the user.
ADID	varchar	36	Yes	The active directory ID of the user.
AuthType	int		No	The type of authorization used to allow the user into Process Director	0 = BuiltIn 1 = Windows 2 = LDAP 3 = External
LastActivity	datetime		Yes	The last time the user was authorized inside Process Director.
LastLogin	datetime		Yes	The last time the user logged in.
Disabled	tinyint		No	Flag that indicates whether the user account is disabled.
CustomString	Nvarchar	1024	Yes	The Custom String user attribute.
CustomString2	Nvarchar	1024	Yes	The Custom String 2 user attribute.
CustomNumber	int		No	The Custom Number user attribute.
CustomDate	DateTime		Yes	The Custom Date user attribute.
Phone	Nvarchar	64	Yes	The Phone user attribute.
Description	Nvarchar	1024	Yes	The Description user attribute.
Title	Nvarchar	256	Yes	The Title user attribute.
Office	Nvarchar	256	Yes	The Office user attribute.
Company	Nvarchar	256	Yes	The Company user attribute.
Department	Nvarchar	256	Yes	The Department user attribute.
LegalEntity	Nvarchar	256	Yes	The LegalEntityuser attribute.
BusinessUnit	Nvarchar	256	Yes	The BusinessUnit user attribute.
Country	Nvarchar	256	Yes	The Country user attribute.
Location	Nvarchar	256	Yes	The Location user attribute.
ManagerUID	Nvarchar	36	Yes	The User UID of the user's Manager.

Documentation Feedback and Questions

If you notice some way that this document can be improved, we're happy to hear your suggestions. Similarly, if you can't find an answer you're looking for, ask it via feedback. Simply click on the button below to provide us with your feedback or ask a question. Please remember, though, that not every issue can be addressed through documentation. So, if you have a specific technical issue with Process Director, please open a support ticket.

Send Feedback to BP Logix