Field Settings

Fields can be controlled using Formulas. The below linked examples explain how to control different types of fields using a formula. A Field formula cannot be used to access/set values in other fields. The Form Formula should be used for this purpose instead.

1. Default Formula
2. Correction Formula
3. Validate Based On
4. Keywords SQL Query (SQL)
5. Keyword Formula

Default Formula

Use the default formula (EtQScript)to return a specific value in the field. This value will be displayed in the field when the document is created.

Typically, you can use this field to return:

1. A value for editable fields when the document is created.
2. A value for computed fields, when the document is created, refreshed or saved.
3. A value for computed for display fields when the document is created or refreshed.
4. A value for computed when composed fields when the document is created.

Remember to use the EtQScript Editor features.

Example 1:

Write a formula that displays "ETQ LLC" in the Title field of the Document Control documents.

Formula:

The following EtQScript should be written in the Default Formula field in the Advanced Settings section of the DOCWORK_TITLE field setting:

thisField.setValue("ETQ LLC")

Example 2:

Write a formula that displays Today’s date in the Origination Date field of the Document Control by default.

Formula:

The following EtQScript should be written in the Default Formula field in the Advanced Settings section of the DOCWORK_ORIGINATION_DATE field setting:

thisField.setValue(Rdate.currentTime(“User1”))

Example 3:

Write a formula that displays ‘100’ as a default value in the Equipment field of the Corrective Action Cost Information section.

Formula:

The following EtQScript should be written in the Default Formula field in the Advanced Settings section of the COST_EQUIPMENT field setting:

thisField.setValue(100)

Example 4:

Write a formula that sets ‘Normal’ as a Default value in the ‘Priority’ field, i.e., whenever a new document is created, the Priority field will be populated with ‘Normal’ by default.

Formula:

The following EtQScript should be written in the Default Formula field in the Advanced Settings section of the Priority field setting:

availablePriorities = thisField.getOptions()

if (availablePriorities.has_key("Normal")):

thisField.setValue(availablePriorities['Normal'])

Use the ID of the keyword value you want to set as a default value; the name of the value cannot be displayed. For example, do not write the script as:

thisField.setValue("Normal")

Correction Formula

Use the EtQScript to return any value (e.g., text of the value of the current field) for editable fields when the document is refreshed or saved.

Remember to use the EtQScript Editor features.

Example

If a negative value is entered in the field, this formula will set the absolute value.

value = thisField.getValue()

if (value < 0):

 thisField.setValue(value * -1)

Example

Replace a specific substring in the current field value.

Rstring.replaceSubstring("DISPLAY_NAME","_"," ")

Validate Based On

EtQScript

Use this field to write an EtQScript formula in an editable field when the document is routed or saved, that returns an error message. For example, return an error message if the field is empty under conditions where it must not be empty, if the field does not equal a specific value, if field does not fit a specific numeric range, if field is before today, etc.

The Validation Exception message will be displayed on the top of the document when the formula that raises the validation is written on the Form Setting. If the formula that raises the Validation Exception is on the Field Setting, you will see a message on the top of the document saying that an error occurred in one or more fields, while the error message will be displayed above the field.

Use the formula on the Form Setting in the following cases:

1. When you execute the formula even though the field you are looking for is hidden and regardless of the Validation Settings on the Form Settings (Validate on Save Only).
2. When you compare, calculate or copy values among multiple fields. On the Field level, the field cannot read from/write on other fields straight forward, so you should write the formula on the form level.

Use the formula on the Field Setting in the following cases:

1. When you need to execute the formula only if the field is displayed.
2. When you need to control the validation on the Form Settings > Validation Settings (Validate on Save Only).

Very important note:

For every single if statement in the validation formula there should be else. Either the “if” part or the “else” must “print true”. The part that prints true cannot be the one raises the error message.

Remember to use the EtQScript Editor features.

Example 1:

This formula checks the Minimum Score field. If the minimum score is less than zero, it displays a validation message: Negative values are not allowed in the Minimum Score field, please change it with an acceptable value (0 or above).

Formula:

The following EtQScript should be written in the Validation Formula field in the Advanced Settings section of the Minimum Score field setting:

if (thisField.getValue() < 0):

 raise ValidationException, " Negative values are not allowed in the Minimum Score field, please change it with an acceptable value (0 or above)"

 print false

else:

 print true

Example 2:

Write a formula that checks the E-mail field of the Complaint document. If this field is filled in without "@", the system displays "Please enter a valid E-mail".

Formula:

The following EtQScript should be written in the Validation Formula field in the Advanced Settings section of the E-mail field of the Complaints document.

mail = thisField.getText()

if (mail != None) and (mail != ""):

 if not (Rstring.contains(mail,"@")):

  raise ValidationException, "Please enter a valid E-mail"

  print false

 else:

  print true

else:

 print true

Example 3:

Write a formula that checks the Start Date field in the Course Schedule form. If the date falls before today’s date, then an error message is displayed.

Formula:

The following EtQScript should be written in the Validation Formula field in the Advanced Settings section of the ‘TRAINING_START_DATE’ field setting:

startDate = thisField.getValue()

currentDate = Rdate.now()

if Rdate.compare(currentDate,startDate) == 1:

 raise ValidationException,"Start Date should be greater than the current date "

 print false

else:

 print true

Example 4:

Write a formula that displays an error message if the value of the Duration field in the Audit document is less than 0 or greater than 10.

Formula:

The following EtQScript should be written in the Validation Formula field in the Advanced Settings section of the ‘Duration’ field setting:

duration = thisField.getValue()

If (duration <0) or (duration > 10):

 raise ValidationException, "Duration should be greater than or equal 0 and less than 10"

 print false

else:

 print true

Expression Builder

By default, some fields have validations (error messages) configured on them to be triggered depending on the field’s value. Text, Number, Date, Date and Time, and Time field’s validations can be set and configured using the Field Validation Expression Builder, which allows you to set multiple validation messages to be shown according to the field’s value; where, for each validation message you want to set, do the following:

1. Select the validation rule to be applied on the field’s value from the If Value field and specify the value against which the field’s value will be validated in the field that is shown according to the type of the related Field Setting and the selected validation rule. For instance, the following figure shows the Value field, which is displayed when the Does not contain validation rule is selected for Text fields.
2. In the Then show this message field, write the validation message that will be shown if the validation rule is met.

The permitted validation rules are changed according to type of the related Field Setting, i.e., Text, Number, Date, etc. According to the selected validation rule, a different field is displayed within the expression builder to specify the validation value (i.e., the value against which validation will be performed). The following are the permitted validation rules for each Field Type, how each of the validation rules works, and the field displayed to specify the validation value or select a validation field:

Text

Number

Date/ Date and Time

Time

 

Keyword Formula

Use this field to write a formula (EtQScript) that provides values for custom parameters included in keyword SQL query.

Remember to use the EtQScript Editor features.

Example:

This formula checks the Minimum Score field. If the minimum score is less than zero, it displays a validation message: Negative values are not allowed in the Minimum Score field, please change it with an acceptable value (0 or above).

Formula:

The following EtQScript should be written in the Keyword Formula field in the Advanced Settings section of the Department field setting:

thisField.setQueryParameter("VAR$USER",thisUser.getID())

Assuming that the following SQL is available in the Keyword SQL Query field:

SQL Statement (Compatible to all vendors)

SELECT

DEPARTMENT.DEPARTMENT_ID,

DEPARTMENT.DEPARTMENT_NAME

FROM

TRAINING.PERSON_PROFILE PERSON

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

WHERE

PERSON.USER_ID = VAR$USER

The SQL Statement gets the department of the logged in user from their profile in the Training database.

Keywords SQL Query (SQL)

Within this field, you can add an SQL Script to:

1. Retrieve certain lookup values for keywords from the keyword table. For example, use this field if you do not need to retrieve all the values in the table.

Customize keywords display values.

You can use the SQLScript editor to validate your script.

Retrieving certain lookup values for keywords from the keyword table

Example 1:

The following script retrieves the Accounting department from the LOOKUPS.DEPARTMENT table. 

SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM LOOKUPS.DEPARTMENT WHERE DEPARTMENT_NAME = ‘Accounting’;

The result of this statement will be as in the following figure Note that only the name of the department is displayed because the department name is selected in the Keywords Display Value field:

Example 2:

The following SQL script retrieves only the users who are members of the Administrators group.

SELECT USERS.USER_ID, USERS.DISPLAY_NAME

FROM ENGINE.USER_SETTINGS USERS

JOIN ENGINE.GROUP_MEMBERS MEMBERS ON

(USERS.USER_ID = MEMBERS.MEMBER_USER_ID)

JOIN ENGINE.USER_SETTINGS GROUPS ON

(MEMBERS.USER_ID = GROUPS.USER_ID)

WHERE GROUPS.USER_NAME = 'ADMINISTRATORS'

Customizing Keywords Display Values

After writing the SQL Script, make sure to fill in the Keywords Display Value field -under the Properties tab within the field setting- with the alias that is used in the SQL Script to represent the customized value.

Example:

The following SQL script customizes the display value of the document title to be displayed in the format: Document title #ID# Docwork ID within the keywords field.

select DD.DOCWORK_ID,DD.TITLE+'-#ID#-'+cast(DD.DOCWORK_ID as varchar) as TT1 from DOCWORK.DOCWORK_DOCUMENT DD

Where DOCWORK.DOCWORK_DOCUMENT is the Keywords Table Name and the DOCWORK_ID is the Keywords Primary Key.

For the Query to work, you must populate the Keywords Display Value field with TT1.