live report 2

kandadai

Dear all,

please help to let me know which table stores the workflow form related data entered by user.

Thanks

 

Bhupinder_Singh

Let me know if this article I wrote on the subject is of help.

 

Article Title:

 

How is form data stored in a SQL table associated with a form template?

 

 

Summary:

 

This article explains how SQL table storage works for forms, with examples for forms both inside and outside of workflows.

 

 

Details:

 

SQL table storage is commonly used for collecting form data so that administrators can write reports based on the information collected therein. A SQL table associated with the form template is like any other database table, except that each field on the form template becomes a column in the table, and four additional Livelink columns are added at the beginning of the table, namely VolumeID, DataID, VersionNum (Version Number), and Seq (Sequence).

 

 

For forms not associated with any workflow:

-------------------------------------------

 

VolumeID stores the negative ID of the Livelink Volume in which the form is located. For example, it may store the negative ID of the Enterprise Workspace, a user's Personal Workspace, or a Project Workspace.

 

DataID stores the DataID (i.e. objectID) of the form from which the data was entered.

 

VersionNum incrementally tracks how many times the form's revision mechanism was activated.

 

Seq incrementally tracks how many times the form's submission mechanism was activated. When a form has a User Revisable Records (SQL) submission

mechanism, Seq is used to store the ID of the user who submitted the data.

 

Example:

 

Examples of a SQL table for non-workflow forms are provided below that assume various revision and submission mechanism options. Included are the results from a SQL command that do not include the actual data in the form, just the columns that explain where the data came from.

 

VOLUMEID   DATAID     VERSIONNUM SEQ      

---------- ---------- ---------- ----------

a)     -2000      11210          0          1

b)     -2000      11210          0          2

 

 

For rows (a) and (b) above, a form is stored somewhere within the Enterprise Workspace that is using "SQL Table" as a submission mechanism.  Since it has no revision mechanism, the VersionNum value always remains 0 (zero). The incrementing Seq values of 1 and 2 refer to the number of times that the form has been submitted, i.e. the number of times the submission mechanism associated with the form was activated.

 

If rows (a) and (b) had looked like this:

 

VOLUMEID   DATAID     VERSIONNUM SEQ      

---------- ---------- ---------- ----------

a)     -2000      11210          1          0

b)     -2000      11210          2          0

 

...then the data could be coming from a form that has a revision mechanism of Livelink Versions and no submission mechanism. The VersionNum values of 1 and 2 indicate that the form was completed twice, i.e. the revision mechanism was activated twice. The same results would be seen if the revision mechanism was "SQL Table".

 

If rows (a) and (b) had looked like this:

 

VOLUMEID   DATAID     VERSIONNUM SEQ      

---------- ---------- ---------- ----------

a)     -2000      11210          1          0

b)     -2000      11210          0          1

 

...then the data came from a form that could have had a revision mechanism of SQL Table and a submission mechanism of SQL table. The VersionNum,Seq value pair (1,0) indicates a row that contains data entered as a result of the revision mechanism being activated, i.e. the user filled out the form and clicked the "Apply" button. The VersionNum,Seq value pair (0,1) indicates a row that contains data as a result of the submission mechanism being activated, i.e. the user clicked the function icon for the form and selected the "Submit" function.

 

 

For forms associated with workflows:

------------------------------------

 

VolumeID stores the SubWorkID of the workflow associated with the form.

 

VersionNum stores the workflow step ID (TaskID).

 

DataID stores the form index, which is useful if you have more than one form in a workflow.

 

Seq stores the form template version number.

 

Example:

 

An example of a SQL table associated with a workflow-related form is provided below, with a detailed explanation. Included are the results from a SQL command that do not include the actual data in the form, just the columns that explain where the data came from.

 

VOLUMEID   DATAID     VERSIONNUM SEQ      

---------- ---------- ---------- ----------

c)     11026          2         -1          1

d)     11026          2          0          1

e)     11026          2          1          1

f)     11026          2          2          1

g)     11239          2         -1          1

h)     11239          2          0          1

i)     11043          2         -1          1

j)     11043          2          0          1

k)     11043          2          3          1

 

The VolumeID corresponds to the workflow's SubWorkID.  In this example, three workflows (indicated by numbers 11026 and 11239 and 11043) have been initiated from the same map using the same form. The DataID having a value of "2" points to the fact that the form data was entered using the second form added to the workflow map.

 

The VersionNum column corresponds to the step number in the workflow, i.e. the TaskID of the workflow step.  A value of –1 in the VersionNum column means "This row contains the data in the form submitted for the current step in the workflow."  A VersionNum value of 0 corresponds to the data in the form submitted at the start step of the workflow, i.e. when the workflow was initiated.  A VersionNum value of 1 or greater corresponds to data entered at subsequent steps in the workflow. Keep in mind that in any workflow, the steps are numbered, or given a TaskID, in the order the steps are created and thus not necessarily numbered in the order they may appear on the workflow map. A VersionNum value of 1 or greater will NOT be recorded if the "Disable Versioning" checkbox was checked when the form was added to the workflow; in such a case, Livelink will retain only the values for the start step (VersionNum=0) and the current step (VersionNum=-1).

 

Seq refers to the form template version number.  In all these cases, there was only one version of the form template.

 

- Bhupinder
----------------------------------------------
Bhupinder Singh, B.Math, B.Ed.
Systems Administrator, Cloud Services
Open Text, Waterloo, Ontario, Canada
----------------------------------------------