Form Setting

On Open (EtQScript)

In the On Open field, you can write an EtQScript that performs specific action(s) when the document is opened.

This event sets the default values for the text, number, keywords, names, and date/time fields whenever a document is opened. The EtQScript that you write in this field is performed after opening the document.  Remember to use the EtQScript Editor features.

Example (1):

Write a formula that checks the status of the document. If the document is change-requested, the system sets the status to Change Requested. If the document is archived, then the system sets the status to Obsolete.

Formula:

The following EtQScript should be written in the On Open field in the form settings (e.g., DocWork form):

statusField = thisDocument.getField("DOCUMENT_CHANGE_REQUEST_STATUS")

if(thisPhase.isChangeRequested()):

 statusField.setValue(Rstring.getText("CHANGE_REQUESTED", thisDocument.getLocale()))

if(thisPhase.isArchived()):

 statusField.setValue(Rstring.getText("OBSOLETE", thisDocument.getLocale()))

Example (2):

Write a formula that displays the links of one type of activities in one field and the links of another type of activities in another field.

Formula:

The following EtQScript displays the links of the Action Item Activities in the COMPLAINTS_ACTION_LINKS and the links of the Diligence Log Activities in the COMPLAINTS_DILIGENCE_LINKS field.

This code can be found in the On Open formula of the Document Control form settings. It shows the document status (Completed/Archived/Change Requested).

activities = thisDocument.getField("ETQ$ACTIVITY_LINK").getDocLinks()

if (activities != []):

 actionItemField = thisDocument.getField("COMPLAINTS_ACTION_LINKS")

 diligenceLogField = thisDocument.getField("COMPLAINTS_DILIGENCE_LINKS")

 actionItemField.clear()

 diligenceLogField.clear()

 for activity in activities:

  if (activity.getLinkedFormName() == "COMPLAINTS_ACTION_LINKS"):

   actionItemField.addDocLink(activity)

  elif (activity.getLinkedFormName() == "COMPLAINTS_DILIGENCE_LINKS"):

   diligenceLogField.addDocLink(activity)

Example (3): 

Write a formula that inherits common data between 2 forms (i.e., Main Form to Activity Form or Main Form to Main Form, via dual linking).

Formula:

Once the formula included in the following attached file is added in the On Open (EtQ Script) of the Corrective Action’s Form Setting, the common fields’ values (Subject and Problem Description) will be inherited to the Corrective Action form.

if (thisDocument.isNew()):

 parentDoc = currDoc.getParentDocument()

 if (parentDoc != None):

  if (parentDoc.getFormName() == 'CAPA_DETERMINATION'):

   subject = parentDoc.getFieldText("CAPA_DETERMINATION_SUBJECT")

   problemDescription = parentDoc.getFieldText("CAPA_DETERMINATION_PROBLEM_DESCRIPTION")

   thisDocument.setFieldValue("CORRACT_SUMMARY_PROBLEM",subject)

  thisDocument.setFieldValue("CORRACT_PROBLEM_DESCRIPTION",problemDescription)

The common fields in the 2 forms will be copied as follows:

Example (4):

Write a formula to show a record or X number of records under an empty subform.

Formula:

The following EtQScript can be added in the On Open formula of the CAPA Form setting to show a new record under the Customer Information subform when creating a CAPA.

if (thisDocument.isNew()):

 corractDocument = thisDocument

 customerSubform = corractDocument.getSubform("CORRACT_CUSTOMERINFO")

 if(customerSubform.size() == 0):

  customerSubform.newRecord()

Formula:

The following EtQScript can be added in the On Open formula of the CAPA Form setting to show 10 new records under the Customer Information subform when creating a CAPA.

if (thisDocument.isNew()):

 corractDocument = thisDocument

 customerSubform = corractDocument.getSubform("CORRACT_CUSTOMERINFO")

 if(customerSubform.size() == 0):

  for i in range(0, 10):

   customerSubform.newRecord()

On Refresh (EtQScript)

In the On Refresh field, you can write an EtQScript that performs specific action(s) after the document is refreshed. For example, after clicking [Refresh], the cost values must be calculated.  Remember to use the EtQScript Editor features.

Example:

Write a formula that calculates the total of the values entered in the Cost fields and displays the result in the Total field of the CAR document.

Formula:

The following EtQScript should be written in the On Refresh field in the Advanced Settings section of the Corract form:

COST_MATERIALS = thisDocument.getFieldValue("COST_MATERIALS")

COST_LABOR = thisDocument.getFieldValue("COST_LABOR")

Total = 0.0

if COST_MATERIALS != None:

 Total = Total + COST_MATERIALS

if COST_LABOR!= None:

 Total = Total + COST_LABOR

thisDocument.setFieldValue("COST_TOTAL", Total)

Where: COST_MATERIALS and COST_LABOR are the design names of the Cost Information fields. The initial value for the Total field = 0.0.

Then the formula checks the value in each field. If the value is not Null (i.e., the field includes a value), then it adds this value to the Total.

On Save (EtQScript)

In the On Save field, you can write an EtQScript that performs specific action(s) before the document is saved. I.e., it executes the formula then saves the changes. This can be used to set field values, or for validation on multiple fields. Notice that the field setting validation formula is used to validate single fields.  Remember to use the EtQScript Editor features.

Dynamic fields (ETQ$) can only be validated using EtQScript in On Save formula

Example:

Write a validation formula that displays messages if any of the Document Number, Effective Date, and Approvers fields is empty in the document (ETQ$NUMBER, ETQ$EFFECTIVE_DATE and ETQ$APPROVERS).

Formula:

number = thisDocument.getFieldText("ETQ$NUMBER")

if (number == ""):

 raise ValidationException, "The Field Number is required."

effective = thisDocument.getFieldValue("ETQ$EFFECTIVE_DATE")

if (effective == None):

 raise ValidationException, "The Field Effective Date is required."

approvers = thisDocument.getFieldValues("ETQ$APPROVERS")

if (approvers == []):

 raise ValidationException, "The Field Approvers is required."

Example:

Write a formula that checks if the country is US. If the selected country is US, then the State field must be filled in. If the State is not populated, the following error message should be displayed: "Please choose the State".

Formula:

if (thisDocument.getField("COMPLAINTS_SITE_WHERE_REPORTED").getDisplayText() == "US") and (thisDocument.getFieldValue("COMPLAINTS_STATE") == None):

 raise ValidationException, "Please choose the State"

Example:

Write a formula that copies the description of all links in the (ASPECTS_REFERENCE_LINKS) field  to the (ETQ_AUDIT_LOG_DESCRIPTIONS) text area field so the changes can be tracked in the Field History dialog:

Formula:

descriptionText = ""

descField = thisDocument.getField("ETQ_AUDIT_LOG_DESCRIPTIONS")

descField.setValue("")

userTimeZone = thisUser.getTimeZone()

userLocale = thisUser.getLocale()

linkField = thisDocument.getField("ASPECTS_REFERENCE_LINKS")

if linkField != None:

 links = linkField.getDocLinks()

 if(links!=[]):

  for link in links:

   descriptionText += link.getDescription(userLocale,userTimeZone)

   descriptionText += '\n'

descField.setValue(descriptionText)

Document Title

EtQScript

The Document Title field, in the Customization Formulas section under the Advanced Tab, is used to write a formula (EtQScript) that returns the Document Title. The EtQScript that you write in the Document Title field is executed by a custom tag (etq:makeWindowTitle) that must be inserted in the appropriate place in the HTML layout.  Remember to use the EtQScript Editor features.

Example:

This script gets the number of the document from the ETQ$NUMBER field and displays the number along with ‘8-D CAPA #’.

print "8-D CAPA # " +  thisDocument.getEncodedFieldText("ETQ$NUMBER")

Expression Builder

A document Document Title is generated based on its state. For instance, by default, the Document Title of a Document Control document is generated as follows:

For a New Document, the document title is [Current Workflow] (New).

E.g., External (New).

For other document states, the document title is [Current Workflow] # [Number].

E.g., External # E-00001-DOC.

You can customize the Document Title of any form using the Document Title Expression Builder dialog, this dialog allows you to set multiple expressions for a document title to be used according to the state of the document. For each expression you want to set, do the following:

From the Document State field, select a state from the available document states that you want to associate the expression with. These states include: Awaiting Release, Completed, New, Open, Voided, and Other, which represents any state that does not have an expression set in the expression builder.

Within the Expression field, write the document title that will be used for the document when its state matches the specified state.

You can add dynamic fields to the expression by pointing the cursor to the location where the dynamic field’s value will appear within the Expression field and selecting the field from the Insert Field Value field, which lists all (Single-Value) fields that are available in the related form of the types, including: Text, Text Area, Number, Names, Currency, Combo Box, Dialog, List Box, Radio, Date, Time, and Date and Time.   

For instance, to set the Document Title of new Document Control documents to New [Current Workflow] Document, open the Document Title Expression Builder dialog of Document Control form and apply the following:

This example assumes that the Document Title Expression Builder dialog does not contain a record for the New state because it is not allowed to create two records for the same state within the same Document Title Expression Builder dialog.

1. Select New from the Document State field within an empty record, illustrated below as Step 1.
2. Write the expression in the Expression field. I.e., Write New Document as illustrated in Step 2.
3. Place the cursor where the Current Workflow dynamic field will be added (i.e., between the words New and Document), as in Step 3.
4. Select the dynamic field from the Insert Field Value field as in Step 4.
5. The selected field is shown in the field where the cursor points in Step 5.

As illustrated above, the created document’s workflow replaces the [Current Workflow] dynamic field, i.e., in the figure above; the current workflow is Job Description.

When you set more records within the Document Title Expression Builder dialog for other document states, a document’s Document Title will be generated based on the document state. If the Document Title Expression Builder has no expression set for a specific form, Default Document Title Expressions are used:

For New Document, the document title is [Current Workflow] (New).

For other document states, the document title is [Current Workflow] # [Number].

URL Description

Use any of the available methods to retrieve a description for a link (i.e., linked documents or linked activities) based on certain conditions. It is recommended to use EtQScript formulas as it reflects the logged in user’s locale (Localized Text and Time Zone) on the URL Description.

SQL Statement

Example: Activity Links

Write a formula that displays Activity Workflow Name (Phase, Due Date) for the open Feasibility Analysis activities and Activity Name (Phase) for closed completed, rejected, archive or obsolete, Feasibility Analysis Activities in the Corrective Actions application.

This formula applies only on MySQL.

The following SQL Statement should be written in the URL Description field with SQL option selected in the Advanced Settings section of the Feasibility Analysis form:

SELECT

PHASE.PHASE_TYPE AS PHASE_TYPE,

(IFNULL(WF.DISPLAY_NAME,"") || IFNULL(" # " || FEASIBILITY_ANALYSIS.ETQ$NUMBER,"")  || " (" || IFNULL(PHASE.DISPLAY_NAME,"") || IFNULL(", due " || DATE_FORMAT(FEASIBILITY_ANALYSIS.ETQ$DUE_DATE,'%b %e, %Y'),"") ||")" ) AS OPEN_DESCRIPTION,

(IFNULL(WF.DISPLAY_NAME,"") || IFNULL(" # " || FEASIBILITY_ANALYSIS.ETQ$NUMBER,"")  || " (" || IFNULL(PHASE.DISPLAY_NAME,"") ||")" ) AS COMPLETED_DESCRIPTION

FROM

?.FEASIBILITY_ANALYSIS AS FEASIBILITY_ANALYSIS

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

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

WHERE

FEASIBILITY_ANALYSIS.FEASIBILITY_ANALYSIS_ID = ?

Example: Document Links

Write a formula that displays the document link as: Document Type #Number (Phase, Due Date) if the document is pending and Document Type #Number (Phase) if the document is completed, Rejected, Archived or Obsoleted.

This formula applies only on MySQL

The following SQL Statement should be written in the URL Description field in the Advanced Settings section of the linked document form:

SELECT

PHASE.PHASE_TYPE AS PHASE_TYPE,

(WF.DISPLAY_NAME || " - Corrective Action " || IFNULL("# " || CORRACT_DOCUMENT.ETQ$NUMBER,"") || " (" || PHASE.DISPLAY_NAME || ", due " || DATE_FORMAT(CORRACT_DOCUMENT.ETQ$DUE_DATE,'%b %e, %Y') || ")") AS OPEN_DESCRIPTION,

(WF.DISPLAY_NAME || " - Corrective Action " || IFNULL("# " || CORRACT_DOCUMENT.ETQ$NUMBER,"") || " (" || PHASE.DISPLAY_NAME || ")") AS COMPLETED_DESCRIPTION

FROM

?.CORRACT_DOCUMENT AS CORRACT_DOCUMENT

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

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

WHERE

CORRACT_DOCUMENT.CORRACT_ID = ?

Keep the following in mind:

1. The SQL Script must return a record set that contains the following columns:

PHASE_TYPE: This is an Integer that represents the phase type (0 if open, 1 if completed, etc.). If PHASE_TYPE is not specified, then OPEN_DESCRIPTION will be used (regardless of the phase of the document.) If PHASE_TYPE is specified, then OPEN_DESCRIPTION is required for static and normal phases, and COMPLETED_DESCRIPTION is required for completed, archived, and rejected documents.

OPEN_DESCRIPTION: This is a String that returns the link description that will appear when the document is open. This string is required for documents in Normal and Static phases. Otherwise, the following error message will be displayed when a link to external document is created:

COMPLETED_DESCRIPTION: This is a String that returns the link description that will appear when the document is completed. This string is required for documents in Completed, Rejected, or Archived phases or an error message will be displayed when a link to external document is created.

2. When there is a join to multi value field (like the assigned to), multiple records will be generated for the same document, the code will take only the first one. For instance, when the document is assigned to multiple users, only the name of the first assigned user will be shown in the link.

EtQScript

Example: Activity Links

Write a formula that displays Activity Workflow Name (Phase, Due Date) for the open Feasibility Analysis activities and Activity Name (Phase) for closed completed, rejected, archive or obsolete, Feasibility Analysis Activities in the Corrective Actions application.  Remember to use the EtQScript Editor features.

The following EtQScript should be written in the URL Description field with the EtQScript option selected in the Advanced Settings section of the Feasibility Analysis form:

fieldsNames = ["ETQ$CURRENT_WORKFLOW","ETQ$NUMBER","ETQ$CURRENT_PHASE","ETQ$DUE_DATE"]
‎values = thisDocLink.getEncodedFieldsValues(fieldsNames, true)
‎EtQNumber = values["ETQ$NUMBER"]
‎if EtQNumber != None and EtQNumber != "":
‎ EtQNumber = " # " + EtQNumber
‎
‎dueDate = ""
‎if values["ETQ$DUE_DATE"] != None and values["ETQ$DUE_DATE"] != "":
‎ dueDate = Rstring.getEncodedText("DUE_DATE",thisLocale) + values["ETQ$DUE_DATE"]
‎ 
‎if (thisDocLink.isLinkedDocumentNormal() or thisDocLink.isLinkedDocumentAwaitingRelease()):
‎ normalCAPAFeasibilityLink = values["ETQ$CURRENT_WORKFLOW"] + EtQNumber + "(" + values["ETQ$CURRENT_PHASE"] + dueDate + ")"
‎ print normalCAPAFeasibilityLink
‎else:
‎ completedCAPAFeasibilityLink = values["ETQ$CURRENT_WORKFLOW"] + EtQNumber + "(" + values["ETQ$CURRENT_PHASE"] + ")"
‎ print completedCAPAFeasibilityLink

Expression Builder

The link description of a document is generated based on its state. For instance, by default, the Link Description of a Document Control document is generated as follows:

For a Completed Document, the window title is [Document Title], [Current Workflow Name] [document Number] [[Document Revision]] ([Current Phase]).

1.  For other document states, the window title is [Document Title], [Current Workflow Name] [document Number] [[Document Revision]] ([Current Phase] [Due Date]).

Using the Link Description Expression Builder dialog, you can customize the Link Description of any form by setting multiple expressions for Link descriptions to be used according to the document’s state. For each expression you wish to set:

1. Select the state from the Document State field to associate with the expression. The available document states are: Awaiting Release, Completed, Open, Voided, and Other, which represents any state that does not have an expression set in the expression builder.

Within the Expression field, write the Link Description that will be used for the document when its state matches the specified state.

1. You can add dynamic fields to the expression by pointing the cursor to the location where the dynamic field’s value will appear within the Expression field and selecting them from the Insert Field Value field, which lists all (Single-Value) fields available in the related form: Text, Text Area, Number, Names, Currency, Combo Box, Dialog, List Box, Radio, Date, Time, Date and Time, Editable, Computed, and Computed when Composed. In addition, you can add the Display Name of the current form to the link description by selecting the Linked Form Display Name option from the Insert Field Value field.

For instance, to set the Link Description of open Document Control documents to [Document Title] document, open the Link Description Expression Builder dialog of Document Control form and apply the following:

This example assumes that the Document Title Expression Builder dialog does not contain a record for the New state because it is not allowed to create two records for the same state within the same Document Title Expression Builder dialog.

1. Select New from the Document State field within an empty record, illustrated below as Step 1.
2. Write the expression in the Expression field. I.e., write Link to the document as illustrated in Step 2.
3. Place the cursor where the form Title dynamic field will be added (i.e., between the words Link to the and document), as in Step 3.
4. Select the dynamic field from the Insert Field Value field as in Step 4.

If the user selects a multi-value field from Select Field Value, the first three selected values will be shown in the generated link description and (...) at the end of the link.

1. The selected field is shown in the field where the cursor points, as in Step 

A link to an Open Document Control document will be generated.

As an example, in the figure above, the document’s title is DCF Procedure, where the created document’s Title replaces the [DOCWORK_TITLE] dynamic field.

When you set more records within the Link Description Expression Builder dialog for other document states, a Document’s Link Description will be generated based on the document state and according to the following:

1. For a New Document, the Link Description is [Current Workflow] (New).
2. For other document states, the Link Description is [Current Workflow] # [Number].