View Settings

View Data Source

Every view requires an associated Data Source setting. You can customize the view to display certain data using SQL statements.

Example (1)

To write an SQL statement to show records of a specified workflow type, do the following steps:

  1. Open the view’s data source that you intend to modify using the link found in the view properties tab
  2. Make sure you have the following SQL JOINS in the data source statement field:

LEFT JOIN ENGINE.PHASE_SETTINGS PHASE ON (<VIEW_FORM_TABLE_NAME>.ETQ$CURRENT_PHASE = PHASE.PHASE_ID)

LEFT JOIN ENGINE.WF_SETTINGS WORKFLOW ON (PHASE.WORKFLOW_ID = WORKFLOW.WORKFLOW_ID)

  1. If you added the previous SQL JOINs to the data source or if you already have them, check the SQL WHERE condition and add the following condition to it:

If you already have some WHERE conditions add this line:

AND WORKFLOW.WORKFLOW_NAME = ‘<YOUR WORKFLOW DESIGN NAME>’

Otherwise (If you do not have a WHERE condition) add the following lines:

WHERE

WORKFLOW.WORKFLOW_NAME = ‘<YOUR WORKFLOW DESIGN NAME>’

  1. Save the data source setting and check the view.

SQL Statement:

For instance, the data source of the “2. CAPA\1. Open CAPA\by Past Due” view:

If your database vendor is MySQL, then check the following attached file that illustrates the example:

../../../Mysql.sql

Example (2)

To write an SQL statement to show the year, month, and quarter in which the document has been created, you need to perform the following:

  1. Modifying the view Data Source:

Open any view builder (using Reliance Designer) then open its view setting.

Navigate to the properties tab and click on the data source link to open the related data source.

Copy the value (i.e., SQL statement) of the “Statement” field and paste it to any text editor.

In the SELECT part of the SQL statement add the following:

For MySQL database vendor

YEAR(CORRACT_DOCUMENT.ETQ$CREATED_DATE) YEAR,

CASE

WHEN (CAST(CAST(MONTH(CORRACT_DOCUMENT.ETQ$CREATED_DATE) AS UNSIGNED) AS CHAR) IN ('1','2','3')) THEN 'Q1'

WHEN (CAST(CAST(MONTH(CORRACT_DOCUMENT.ETQ$CREATED_DATE) AS UNSIGNED) AS CHAR) IN ('4','5','6')) THEN 'Q2'

WHEN (CAST(CAST(MONTH(CORRACT_DOCUMENT.ETQ$CREATED_DATE) AS UNSIGNED) AS CHAR) IN ('7','8','9')) THEN 'Q3'

WHEN (CAST(CAST(MONTH(CORRACT_DOCUMENT.ETQ$CREATED_DATE) AS UNSIGNED) AS CHAR) IN ('10','11','12')) THEN 'Q4' END QUARTER,

MONTH(CORRACT_DOCUMENT.ETQ$DUE_DATE) MONTH

Signature with solid fill Make sure to add a coma “,” before adding the YEAR, Quarter, Month lines.

Copy the updated SQL statement and paste it into the data source setting then save.

  1. Creating new columns to display the Year, Quarter and Month values.

Open the Reliance engine application and click on the [New Document] action.

Choose column setting then click [OK].

In the display name field add “Year” then navigate to the ‘Properties’ tab and add “YEAR” in the “Table or Query Field Name” field.

Choose your view in the ‘Parent View(s)’ field then save.

Repeat the steps (1 – 4) for the Quarter and Month columns.

Creating Views based on the currently logged in User

You can create a special view to display certain documents based on the currently logged in user. For example, you can configure a view to display the documents that are assigned to the currently logged in user or to display the documents that are created by that user, etc.

When you want to create such a view, you must enter a query in the Selection Statement field that returns specific data. The SQL statement should contain a ‘where’ clause that contains ETQ&USER_NAME or ETQ$USER_ID to filter the result.

For example, the following scripts return the name of the assigned user, the title of the document, and the number of the documents that are assigned to the current user (who is already logged to the machine).

Use this script for MySQL

SELECT

DOCWORK_DOCUMENT.DOCWORK_ID,

DOCWORK_DOCUMENT.ETQ$NUMBER DOCUMENT_NUMBER,

DOCWORK_DOCUMENT.TITLE TITLE,

USER_SETTINGS.DISPLAY_NAME ASSIGNED

FROM

DOCWORK.DOCWORK_DOCUMENT DOCWORK_DOCUMENT

LEFT JOIN DOCWORK.ETQ$DOCWORK_DOCUMENT_ASN ASSIGNED ON (DOCWORK_DOCUMENT.DOCWORK_ID = ASSIGNED.DOCWORK_ID)

LEFT JOIN ENGINE.USER_SETTINGS USER_SETTINGS ON (ASSIGNED.ETQ$ASSIGNED = USER_SETTINGS.USER_ID)

WHERE

USER_SETTINGS.USER_ID = ETQ$USER_ID

Signature with solid fill If you use ETQ$USER_NAME in the script, make sure to write it between single quotes such as:

WHERE

USER_SETTINGS.USER_NAME = 'ETQ$USER_NAME';

Moreover, you can use more than one condition in the SQL statement to retrieve certain data. For example, the following SQL returns the number, title, and the name of the user of the documents that are created by and at the same time assigned to the logged in user:

SELECT DOCWORK_DOCUMENT.DOCWORK_ID,

DOCWORK_DOCUMENT.ETQ$NUMBER AS NUMBER,

DOCWORK_DOCUMENT.TITLE AS TITLE,

USER_SETTINGS.DISPLAY_NAME AS ASSIGNED

FROM

DOCWORK.DOCWORK_DOCUMENT AS DOCWORK_DOCUMENT

LEFT JOIN DOCWORK.ETQ$DOCWORK_DOCUMENT_ASN AS ASSIGNED ON (DOCWORK_DOCUMENT.DOCWORK_ID = ASSIGNED.DOCWORK_ID)

LEFT JOIN ENGINE.USER_SETTINGS AS USER_SETTINGS ON (ASSIGNED.ETQ$ASSIGNED = USER_SETTINGS.USER_ID)

WHERE

USER_SETTINGS.USER_NAME = 'ETQ$USER_NAME'

AND

DOCWORK_DOCUMENT.ETQ$AUTHOR = 'ETQ$USER_NAME';

On Before Open Formula (EtQScript)

The EtQScript that you write in this field is performed before opening the view. Remember to use the EtQScript Editor features.

Example:

Write a formula that filters the view to display the documents that relate to the department of the logged-in user.

Formula:

The following EtQScript should be written in the On Before Open Formula field in the View Settings:

thisView.setQueryParameter("VAR$ASSIGNED",thisUser.getID())

The following SQL must be entered in a Data Source:

For MS SQL Server

SELECT

DOCWORK_DOCUMENT.DOCWORK_ID,

DOCWORK_DOCUMENT.TITLE AS TITLE,

DOCWORK_DOCUMENT.ETQ$REVISION AS ETQ$REVISION,

ISNULL(DOCWORK_DOCUMENT.ETQ$NUMBER,CHAR(63)) AS DOCUMENT_NUMBER,

DOCWORK_DOCUMENT.ETQ$DUE_DATE AS DUE,

ISNULL(USR.DISPLAY_NAME,CHAR(63)) AS ASSIGNED,

PHASE.DISPLAY_NAME AS PHASE_NAME,

PHASE.PHASE_TYPE AS PHASETYPE,

WF.DISPLAY_NAME AS WORKFLOW_NAME,

DEPARTMENT.DEPARTMENT_NAME AS DEPARTMENT_NAME

FROM

DOCWORK.DOCWORK_DOCUMENT AS DOCWORK_DOCUMENT

LEFT JOIN TRAINING.PERSON_PROFILE AS PERSON_PROFILE ON (DOCWORK_DOCUMENT.DEPARTMENT_ID = PERSON_PROFILE.DEPARTMENT)

LEFT JOIN LOOKUPS.DEPARTMENT AS DEPARTMENT ON (DOCWORK_DOCUMENT.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID)

LEFT JOIN ENGINE.PHASE_SETTINGS AS PHASE ON (DOCWORK_DOCUMENT.ETQ$CURRENT_PHASE = PHASE.PHASE_ID)

LEFT JOIN ENGINE.WF_SETTINGS AS WF ON (PHASE.WORKFLOW_ID = WF.WORKFLOW_ID)

LEFT JOIN DOCWORK.ETQ$DOCWORK_DOCUMENT_ASN AS ASSIGNED ON (DOCWORK_DOCUMENT.DOCWORK_ID = ASSIGNED.DOCWORK_ID)

LEFT JOIN ENGINE.USER_SETTINGS AS USR ON (ASSIGNED.ETQ$ASSIGNED = USR.USER_ID)

WHERE

PHASE.PHASE_TYPE = 0

AND

PERSON_PROFILE.USER_ID = VAR$ASSIGNED

For Oracle Server

SELECT

DOCWORK_DOCUMENT.DOCWORK_ID,

DOCWORK_DOCUMENT.TITLE TITLE,

DOCWORK_DOCUMENT.ETQ$REVISION ETQ$REVISION,

NVL(DOCWORK_DOCUMENT.ETQ$NUMBER,CHR(63)) DOCUMENT_NUMBER,

DOCWORK_DOCUMENT.ETQ$DUE_DATE DUE,

NVL(USR.DISPLAY_NAME,CHR(63)) ASSIGNED,

PHASE.DISPLAY_NAME PHASE_NAME,

PHASE.PHASE_TYPE PHASETYPE,

WF.DISPLAY_NAME WORKFLOW_NAME,

DEPARTMENT.DEPARTMENT_NAME DEPARTMENT_NAME

FROM

DOCWORK.DOCWORK_DOCUMENT DOCWORK_DOCUMENT

LEFT JOIN TRAINING.PERSON_PROFILE PERSON_PROFILE ON (DOCWORK_DOCUMENT.DEPARTMENT_ID = PERSON_PROFILE.DEPARTMENT)

LEFT JOIN LOOKUPS.DEPARTMENT DEPARTMENT ON (DOCWORK_DOCUMENT.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID)

LEFT JOIN ENGINE.PHASE_SETTINGS PHASE ON (DOCWORK_DOCUMENT.ETQ$CURRENT_PHASE = PHASE.PHASE_ID)

LEFT JOIN ENGINE.WF_SETTINGS WF ON (PHASE.WORKFLOW_ID = WF.WORKFLOW_ID)

LEFT JOIN DOCWORK.ETQ$DOCWORK_DOCUMENT_ASN ASSIGNED ON (DOCWORK_DOCUMENT.DOCWORK_ID = ASSIGNED.DOCWORK_ID)

LEFT JOIN ENGINE.USER_SETTINGS USR ON (ASSIGNED.ETQ$ASSIGNED = USR.USER_ID)

WHERE

PHASE.PHASE_TYPE = 0

AND

PERSON_PROFILE.USER_ID = VAR$ASSIGNED

For MySQL Server

SELECT

DOCWORK_DOCUMENT.DOCWORK_ID,

DOCWORK_DOCUMENT.TITLE AS TITLE,

DOCWORK_DOCUMENT.ETQ$REVISION AS ETQ$REVISION,

IFNULL(DOCWORK_DOCUMENT.ETQ$NUMBER,CHAR(63)) AS DOCUMENT_NUMBER,

DOCWORK_DOCUMENT.ETQ$DUE_DATE AS DUE,

IFNULL(USER.DISPLAY_NAME,CHAR(63)) AS ASSIGNED,

PHASE.DISPLAY_NAME AS PHASE_NAME,

PHASE.PHASE_TYPE AS PHASETYPE,

WF.DISPLAY_NAME AS WORKFLOW_NAME,

DEPARTMENT.DEPARTMENT_NAME AS DEPARTMENT_NAME

FROM

DOCWORK.DOCWORK_DOCUMENT AS DOCWORK_DOCUMENT

LEFT JOIN TRAINING.PERSON_PROFILE AS PERSON_PROFILE ON (DOCWORK_DOCUMENT.DEPARTMENT_ID = PERSON_PROFILE.DEPARTMENT)

LEFT JOIN LOOKUPS.DEPARTMENT AS DEPARTMENT ON (DOCWORK_DOCUMENT.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID)

LEFT JOIN ENGINE.PHASE_SETTINGS AS PHASE ON (DOCWORK_DOCUMENT.ETQ$CURRENT_PHASE = PHASE.PHASE_ID)

LEFT JOIN ENGINE.WF_SETTINGS AS WF ON (PHASE.WORKFLOW_ID = WF.WORKFLOW_ID)

LEFT JOIN DOCWORK.ETQ$DOCWORK_DOCUMENT_ASN AS ASSIGNED ON (DOCWORK_DOCUMENT.DOCWORK_ID = ASSIGNED.DOCWORK_ID)

LEFT JOIN ENGINE.USER_SETTINGS AS USER ON (ASSIGNED.ETQ$ASSIGNED = USER.USER_ID)

WHERE

PHASE.PHASE_TYPE = 0

AND

PERSON_PROFILE.USER_ID = VAR$ASSIGNED

On After Open Formula

The EtQScript that you write in this field is performed after opening the view. Remember to use the EtQScript Editor features.