The following are some basic SQL commands. If you want to read data through a REFERENCE (parent/child)
field, read up on LEFT OUTER JOIN. CQ uses multiple intermediary tables to link records, so is beyond the scope of these examples.
-- Display field values:
SELECT field1, field2, field3
FROM recordtype;
-- Filter on field values:
SELECT field1, field2
FROM recordtype
WHERE field1 = 'value'
If the value contains a single quote, enter it as a double-single quote.
For example, "Eric's" would get entered as 'Eric''s'.
Numerical comparisons don't need quotes, as in "WHERE field1 > 100".
-- Filter on mulitple fields:
WHERE field1 = 'value' AND field2 < 1000
-- Filter on multiple values (or) within a field:
WHERE field1 IN ('value1','value2','value3','value4')
-- Field starts with "valu":
WHERE field1 LIKE 'valu%'
-- Field contains "valu":
WHERE field1 LIKE '%valu%'
-- Filter on a range of values (field1 is an INTEGER):
WHERE field1 BETWEEN 1000 AND 2000
-- Group nested filters (field2 is a SHORT_STRING):
WHERE field1 BETWEEN 1000 AND 2000
AND
(field2 BETWEEN '00004040' AND '00005050'
OR
field2 BETWEEN '00006000' AND '00006050')
-- Field does not equal a value:
WHERE field1 <> 'value'
-- Find out if a reference field is empty or not:
WHERE field1 is NULL
WHERE field1 is not NULL
-- Field does not contain a value:
WHERE field1 NOT LIKE '%valu%'
-- Sort in ascending (ASC) or descending (DESC) order:
WHERE field1 <> 'value'
ORDER BY field2 ASC
-- Sort multiple fields in the order in which they are listed:
ORDER BY field1 ASC, field2 DESC
-- Query on dynamically determined values (select in select):
SELECT field1
FROM recordtype
WHERE field1 >
(SELECT field2
FROM recordtype
WHERE field3 = 'value')
-- Mathematically analyze a set of returned values with AVG, COUNT, MAX, and SUM.
These four will return a single value unless GROUP BY is used.
SELECT SUM(field1)
FROM recordtype
-- Group a set of returned values. This will return the sum of "field2"s for each
different value of field1.
SELECT field1, SUM(field2)
FROM recordtype
GROUP BY field1
-- If a returned row is identical to another, only return one of them:
SELECT DISTINCT field1
FROM recordtype
-- Assign each table (recordtype) a variable when more than one table is involved:
SELECT T1.field1, T2.field2
FROM recordtype1 T1, recordtype2 T2
WHERE T1.dbid = T2.parent_dbid
-- Change a field value. Note that UPDATEs are not allowed in the CQ SQL Editor.
UPDATE recordtype
SET field = 'newvalue'
WHERE field1 = 'oldvalue'
-- Get a listing of all tables:
SELECT * from cat;
-- Get a listing of all columns in a table. This only seems to work with sqlplus
and not pdsql.
DESC tablename;
Back to the INDEX.
Determine the next dbid that will get used.
Updated: 09/08/06
New in CQ 7.0, the installutil can return information about dbids. The output will
need to be parsed if peforming the "get" programmatically.
installutil getrecordlimits -dbset dbset username password { user_db | -all }
Back to the INDEX.
Increase the record ID (dbid) limit.
Updated: 09/08/06
New in CQ 7.0, the installutil command has the subcommand "sethighrecordlimit" for Windows only.
The command has no affect if the db is already at the higher limit.
installutil getrecordlimits -dbset dbset username password { user_db | -all }
Back to the INDEX.
Rational ClearQuest Diagnostics utility.
Updated: 09/12/06
The Rational ClearQuest Diagnostics utility was introduced in CQ 7.0. The utility examines
your schema repository and user databases to identify conditions that might cause integrity
or performance problems. On Windows, it lives in the default directory of "Program Files\Rational\ClearQuest\diagnostic".
Before editing the diagnostics PERL script, make a copy of the original. Give the copy a name
that describes the specific configuration. By doing this you can have multiple diagnostic PERL scripts
that don't have to be reconfigured. Also, because these files are in the CQ directory tree, you should keep
your configured files under source control, just in case CQ ever gets uninstalled and reinstalled.
1) Edit "cqdiagnostics.pl". Set all the variables in sections 1 thru 4. Note that the paths specified
in section 4 for must already exist. Also, those files are not written for the "describe" subcommand; that
information is sent to STDOUT.
2) On the CLI, change to the ClearQuest\diagnostic subdirectory. Execute the PERL script using cqperl.
NOTE: I ran the validatedb against a large userdb on a decent server and it took almost two hours.
Moreover, the resulting text file was ~1.5GB; too large to be opened in Notepad.
NOTE: The describe subcommand simply lists the rules the engine will evaluate; it doesn't describe
your database.
Back to the INDEX.
CQ feature levels
Updated: 09/13/06
A schema repository has a feature level and metaschema version.
- CQ 1.0, metaschema 13, feature level 1
- CQ 1.1, metaschema 17, feature level 2
- CQ 2.0/2000/2001, metaschema 20, feature level 3
- CQ 2001A/2002/2003/7.0, metaschema 22, feature level 5
A schema repository must be upgraded to a new feature level if the newly installed version
of CQ has a new feature level.
Determine the current metaschema version and feature level, use pdsql:
select name, feature_level, metaschema_version from master_dbs;
To upgrade a feature level, see the full instructions in the install manual.
Back to the INDEX.
Use pdsql.
The pdsql command lives in CQ-home and is used to modify CQ databases.
There are many SQL operations that can be run to alter a database, but only the ones
in pdsql are supported. Alwyays end pdsql commands with a semicolon.
pdsql -u db-owner -p password -v ss -db userdb-name -s server
MS Access
pdsql -u admin -p password -v access -db full-unc-path-to-schemarepo.mdb
The -u option is the owner of the SQL database, independent of any instance. The -p option
is the owner's password. The -v option is type of database, in this case ss = SQL Server.
The -db option is the name of the user database to be modified. The -s option specifies
the hostname of the server on which the SQL database resides.
Back to the INDEX.
Update schema repository after copying new data.
Updated: 07/12/07
If you make a copy of a database, perhaps to set up a testing area, be sure to
update the schema repository properties.
If you create a new connection for it in the Maintenance Tool on a brand-new
machine, you still need to run Schema Repository -> Update -> Selected Connection.
This is done because while the external connection properties (stored in the registry)
may get changed, the internal row data will not.
WARNING: Updating the selected connection is especially
important if you copy tables into an existing CQ database. The maintenance tool will show you
it is connected to the existing CQ database (the one you copied the data into). But, if you
log into that schema repo, perhaps to update the user database connection, because the internal
row data has not been updated, you are actually erroneously/unwittingly logging into the database
from which the CQ tables were taken. Be safe and update the schema repo connection even if you
think it is already ok.
Back to the INDEX.
Add free-form text to an email rule.
Updated: 05/23/06
You can add any existing field to the email that an email sends out.
But, there is no out-of-the-box way to add free-form text, perhaps to tell
the user why they are receiving the email. The following is a work-around.
For each string of text that you'd like to send to the user, create
a SHORT_STRING or MULTILINE_STRING field to hold it whose Default Value is
CONSTANT. However, you can also have the text contain dynamic information,
as in the Code_Reuse_Message example below. Don't place the field on any form.
Then, simply reference that field as a Display Field in the email rule.
Note that a SHORT_STRING defaults to 50 chars long, but can be lengthened
when the field is initially defined.
Here are some fields and associated value examples:
Notification_Review
You've been assigned or removed as a Reviewer.
Code_Reuse_Message
The $this_project project has submitted a Problem Report against reused $other_project code.
Nofication_Need_More_Info
This is being sent back to you for more info.
Web_Login_Link
http://webserver/cqweb
etc...
To add a URL into another field, see
Embed a clickable URL in a field.
Back to the INDEX.
E-mail notification.
Updated: 05/23/06
ALL users must enable their own email notification in the Client; go to View->Email Options.
In the web interface, a CQ admin needs to go to Operations, Edit Web Settings.
To get email sent automatically for certain state or field changes,
create Email_Rule records. However, even if
an automatic email rule is in affect, the end-user still must enable email notification
to recieve emails. To export/import email rules,
see "import data into CQ".
Generate emails from within the schema instead of with Email Rules.
There are several reasons why you should place email notifications within the schema code.
The one downside to having email generated from the schema instead of Email Rules is that you can
only make email notification changes when the schema is next released, which may be a couple months
at some institutions.
1) Email Rules are inefficient. When you commit a record, all Email Rules fire to determine
if they are to send an email. In order to make that determination, an Email Rule may need run a query.
If you have several Email Rules, there is a marked performance hit when committing a record.
2) You have much greater flexibility in deciding whether or not an email should be sent.
The Email Rule record type is fine for simple rules, but some may take extended logic, which simply
cannot be done in an Email Rule.
3) You have complete control over the content of the Subject and Body text, which means that you
can place free-form text in the body of the email.
4) No email configuration necessary. You don't have to worry about users of the Client interface making
the personal decision to turn off email notifications.
Use the following VBScript snippet as an example of how send email from within the schema. As with
Email Rules, the person logged when this fires will automatically be set in the From address. Most often
this code would be placed in an action Notification hook. However, at that stage, you cannot modify any
fields. For example, in the code below, we are adding a note entry, which cannot be done that late in the commit.
For that reason, the code should be placed in Validation hook. But, because there may be errors, you don't
want the email sent out until the record validates properly. For that reason, you would place this code at the very
end of any other code with an if statement surrounding to check the status of, say, the Defect_Validation variable.
' '''''''''''''''''''''''''''''
' Send email to the new Tester if that field changed, but only in the Testing state.
if GetFieldValue("State").GetValue = "Testing" and GetFieldValue("Tester").GetValue <> GetFieldOriginalValue("Tester").GetValue then
set mailObj = CreateObject("PAINET.MAILMSG")
address = GetFieldValue("Tester_Email").GetValue
mailObj.AddTo(address)
subject = "You have been assigned as the Tester for Defect " & GetFieldValue("id").GetValue
mailObj.SetSubject(subject)
body = "Headline: " & GetFieldValue("Headline").GetValue & vbCrLf
mailObj.SetBody(body)
status = mailObj.Deliver
' Retry if this first deliver failed for some reason.
if status = 0 then
status = mailObj.Deliver
end if
end if
Ensure SMTP server is responding.
Once the system administrator has given you the hostname of the SMTP server,
you can verify connectivity via the following. The ping will verify simple name
resolution. The telnet will open a window and report something like
"220 hostname ESMTP Server...ready". If you get anything resembling
and error, then hostname is not "talking" SMTP.
# ping hostname
# telnet hostname 25
Debug email notification issues.
New in CQ 2001A, email debug information can be viewed using dbwin32 on Windows.
Create registry files for the client and/or webserver machines. You must have local
Admin rights to view debug output.
Native Client
REGEDIT4
[HKEY_CURRENT_USER\Software\Rational Software\ClearQuest\Diagnostic]
"Trace"="Email"
"Output"="ODS"
"EMailSendVB"="ODS"
Webserver
REGEDIT4
[HKEY_USERS\.default\Software\Rational Software\ClearQuest\Diagnostic]
"Trace"="Email"
"Output"="ODS"
"EMailSendVB"="ODS"
Stop the ClearQuest service, import the registry files, and restart ClearQuest.
Back to the INDEX.
Lock down Email Rules.
Updated: 05/23/06
If you want to lock down all Email Rules so that only a certain group can create/modify them,
use the built-in USER GROUPS option in the Access Control column of the Email Rule Submit and Modify actions.
If you want to set it up so that only a set of Email Rules is not modifiable by users not in a given
group (perhaps to lock down administrative-type rules), perform the following steps.
In the Name of the Email Rule place in parentesis a comma-separated list of groups that are allowed access.
In the Modify action Access Control hook, place code similar to the following. For this and other uses,
it's a good idea to have a CQ group of "admins" set up that is independent of the "superuser" access.
That group should always have access to do administrative stuff where others cannot.
' '''''''''''''''''''''''''''''''''
' This function will check to see if the current logged in user is a part of a group
' allowed to Modify the Email Rule. Ensure users in the "Admin" group can get in.
usergroups = GetSession.GetUserGroups
name_text = GetFieldValue("Name").GetValue
email_rule_AccessControl = FALSE
if InStr(name_text,"(") and IsArray(usergroups) then
string1 = split(name_text,"(")
string2 = split(string1(1),")")
allowed = split(string2(0),",")
for each group in userGroups
if group = "Admin" then
email_rule_AccessControl = TRUE
exit for
end if
for each allowed_group in allowed
if group = allowed_group then
email_rule_AccessControl = TRUE
exit for
end if
end if
next
end if
Back to the INDEX.
Create an Email_Rule record.
Updated: 05/23/06
Email rules are created from the client by users that have permission to modify the schema.
Select Actions -> New... -> Email_Rule. If the schema with which you are working does
not have the Email_Rule record type, it can be added as a
package in the Designer.
By filling out all the required information in the Submit Email_rule screen, you are establishing
the criteria that triggers email notification.
In the Rule Controls tab, give it a name unique within the current schema, associate
it with a record type (most often Defect) and list field(s) to monitor. If one of the monitored
field's values changes, an email is sent. In addition, one can use a Public Query as a trigger to
send email when the record meets multiple criteria. The check box at the bottom toggles whether this
rule is active or not. That is, one can disable the rule without deleting it.
In the Action Controls tab, one can select an action (Actions) or an action type
(Action) to trigger the email, or emails can be triggered by moving a record between states
(Source and Destination).
In the Display Fields tab, though not mandatory, the email's appearance is configured
here.
The To Addressing Info and CC Addressing Info tabs should be self-explanatory.
If adding addresses external to CQ into the bottom-right pane, you are limited to 22 chars.
NOTE: To ensure users are able to send email, in their CQ client, select
View -> E-mail Options... and configure it accordingly.
Back to the INDEX.
Modify an Email_Rule record.
Updated: 05/23/06
In the CQ client, Edit -> Find Record -> Entity (Email_Rule) &
ID (email-rule-name) -> Actions -> Modify. If you don't remember the name of
a particular Email_Rule record, run Query -> New Query... -> Email_Rule
and simply run a query with Name as the only field displayed.
WARNING: Do not modify the Email_Rule record type itself,
as it will break the code that supports e-mail rules.
Back to the INDEX.
Set up the Rational Email Reader.
Updated: 05/24/06
With the Rational Email Reader, you can send emails to CQ with new submissions or modifications
to exiting records. There are more details in the Admin Manual, but the following will give you an
idea of what is needed.
1) Each user database that is to work with the Rational Email Reader needs to have a dedicated
email address. This may be a sticking point at companies that don't like giving out email addresses
that do not have a human attached to them.
2) If using SMTP, you're going to need a login and password to access a POP3 server.
Back to the INDEX.
Send email via Outlook from within CQ.
Updated: 08/01/06
Usually email is sent via an Email_Rule record or perhaps by calling up SMTP. However,
you can also send an email directly to Outlook from within CQ. In this example, the email
address is stored in a field on a record and there is a button next to it that calls this
global script. Note that CreateItem requires an argument, though it can be anything.
NOTE: The downsides to this are that it only works in the fat Client interface
and the button that runs the global script is not active unless the record is currently editable.
The alternative to launching Outlook to send the email is to simply put "mailto:" before
the user's email address. But, while that will work in the web or Client, you can't
pre-fill the subject or body content.
Function Email_This_User (entity_type, entity_id, field_name)
email_to = GetFieldValue(field_name).GetValue
email_subject = entity_type & " " & entity_id
email_body = "This email was sent from ClearQuest."
Set oOutlook = CreateObject("Outlook.Application")
Set oEmail = oOutlook.CreateItem(dummy)
oEmail.To = email_to
oEmail.Subject = email_subject
oEmail.Body = email_body
oEmail.Display
end function
Back to the INDEX.
Set default values.
If entering similar data into a Submit form many times over, it is useful to
save the common field entries as defaults for the next submission. After you have entered
all the common data, select the Values button on the right-hand side and choose "Save as
Default". Then, continue to fill out unique field values and submit the form. The next
submission you make, select "Load Default" under the Values button and the common fields will
be populated with the saved values. As of CQ 2.0, the saved values stay saved forever.
However, if you want to set a permanent default value for all users, you'll need to
set up a hook in the Fields matrix under the Default Value column. Default values set with
a hook in the schema are only set upon Submit.
NOTE: The information is stored in the Bucket table of the user db.
If a user accesses the same schema in a different user db, they will have to set
up the defaults there as well.
Back to the INDEX.
Create a new field.
1) In the CQ Designer, open the schema in which the form lives.
2) Open the appropriate record type folder (most often Defect)
and double-click on Fields.
3) Right-click anywhere in the right-hand pane and select Add Field...
4) Fill in the Field Name and select an appropriate field Type.
The Help Text is optional.
5) The field is created when you click on the close (X) in the upper-right
corner of the window. The field's position among other the fields listed can be changed by
clicking on the box to the left of the fieldname and dragging the field to its new location.
However, I'm unsure at this time how to preserve that location across schema versions. That is,
if you check-in and then check back out the schema, the new field will again be a the very bottom.
The default behavior for a new field is OPTIONAL.
6) Open the Forms folder for the same record type. In turn, double-click on the
"Submit" and "Record" forms. The actual names for those two forms vary between record types.
7) Select the new field in the Field list; a separate window to the right
of the CQ Designer. Drag it to the desired location on the form. Its properties and relationship
to other fields can be modified via the Form Layout menu. Alternatively, you can first
select a field type from the controls palette (also a separate window to the right of the Designer).
Associate that field type with an actual field by right-clicking on it and selecting Properties.
From there, fill in the Field Name.
8) Validate the schema via File -> Test Work... This will validate the
schema in the designated test database. That database is selected via Database ->
Set Test Database... If 0 error(s) found, the CQ Client will automatically launch so
that you can test your changes in context.
9) If satisfied with the changes, save the schema in the Designer via File -> Check In...
10) Upgrade the real database with the new schema version. Database ->
Upgrade Database...
Back to the INDEX.
Set up a parent/child control field.
1) Use steps 1-4 in
"Create a new field".
The field type is REFERENCE_LIST and the additional field called "Reference To" needs
to be filled in; the name of the field with which this field
will be associated. The parent/child relationship is most often set up as a connection
between records, but can be used to relate any two arbitrary fields. However, since the
first field that automatically shows up when using a REFERENCE_LIST is "id" and that is
tied automatically to the three push buttons, you must change the three buttons if not
using the "id" column. The second tab in the field Properties is Extended. It is a single
List View ID. This must be unique among all parent/child control lists for the tab of
that form. It is the same name as referenced in the Properties of the three buttons
under their Extended tabs in their Associated Component fields.
2) Use steps 6-10 in
"Create a new field".
Back to the INDEX.
Remove a field.
If you delete a field from a form, it remains active in the database.
That is, users can still query on it. In addition, if you attempt to create
a new field with the same name, CQ will yell at you. Once a field exists
in the schema repository, it's there forever.
Back to the INDEX.
Toggle whether a field is mandatory.
Check out the appropriate schema. Go to Record Type folder -> Record Type ->
States and Actions -> Behaviors. For the field in question, in turn right-click in
each state and toggle Mandatory or Optional.
Back to the INDEX.
Create a pull-down menu field.
In the Fields matrix, right-click in the Defect:Fields pane, select
Add Field... and give it appropriate properties. Choose CONSTANT_LIST
in the Choice List column and fill in the list values. If this field's values
are not going to be dependent on another's, check the Limit To List box.
Open Forms -> Defect_Base_Submit and instead of dragging the new field from the
"Field list for 'Defect'", click on the Control Palette icon for Drop-down
List Box and place it on the form. Go to the Properties sheet of the new drop-down
field and enter the Field Name of the field you just created in the Fields
matrix. Test the work.
Back to the INDEX.
Add help text to a field.
In the Fields matrix/grid, right-click on the field in question and select
Field Properties. Then, select the Help Text tab and enter the information
there. Once entered, the help text will show up in the Client by right-clicking
on that field and choosing Help.
Back to the INDEX.
Create a dynamic/static/dependent choice list field.
Static choice list.
Simply select CONSTANT LIST in the Choice List column of the field matrix. A window will
pop up asking for the values of the list. Use the Recalculate Choice List check-box
if you want the choice list recalulated for each action, perhaps because this field's
choices depend on another's. The Limit To List check-box means that you don't
want the field's choices recalculated. The value choices are static, independent of other
field values. To make the list a pull-down menu, see
"Create a pull-down menu field".
Dynamic choice list.
A dynamic choice list allows users that have permission to edit a choice list
from the Client. In the Designer Workspace, right-click on the Dynamic List Names folder and select
Add. Give the new list an appropriate name. In the Fields matrix, click in the Choice
List column for the field to be the dynamic choice list and choose DYNAMIC LIST. Choose the
dynamic list you want to use and click OK to close that box.
In the Client, select Edit -> Named Lists -> dynamic-list. To add a
new value, simply select a blank line a type it in. To insert a value between two existing
ones, right-click in the box and select Insert. Do likewise to Remove a value.
Multiple fields can be associated with a single dynamic list.
NOTE: If a record is currently open for edit, the changes made to a dynamic
list will not show up until the next time the record is opened.
See also "Populate a dynamic list from a file".
Note that the contents of a dynamic list can only be populated from the Client and
not the web interface.
Dependent choice list.
There are two ways to accomplish this, one from the parent side and the other from the
dependent side.
1) In the "parent" field (the field upon which the other field is dependent),
create a CONSTANT_LIST of choices in the Choice List column. Set it to Limit to List. Write a hook
in the Value Changed column that retrieves the value of the current field and uses it to
set the choice list of the dependent field. The upside of doing it this way is that you
don't have to set "Recalculate Choice list" for the dependent field. The dependent's field
choice list changes ONLY whent the parent's value changes. The downside is you can't take
advantage of dynamic lists in the dependent field, and also, up to at least CC 5.0, the
SetFieldChioceList method doesn't work in VBScript. The array used to populate the dependent
field's choice list can be generated as a list hard-coded in the parent field, as a query of
information in CQ, or possibly retrieved from an external flat file. Note that in SetFieldChoiceList,
you must pass the array itself and not values contained therein.
my $value = $entity->GetFieldValue($fieldname)->GetValue();
my @dependent_array = <populate array somehow>;
$entity->SetFieldChoiceList("dependent-field-name",\@dependent_array);
2) To have the dependent field be a list of values based on the value of
the parent, write a hook in the Choice List column of the dependent field. Must set
"Recalculate Choice list" in the dependent field's Choice List definition. Note
that this field's values would be reevaluated ANY time ANY field value
changes on the form. That's the unfortunate performance reality of "Recalculate Choice list".
$OS = $entity->GetFieldValue("OS_version")->GetValue;
if ($OS eq "UNIX") {
push (@choices, "Solaris", "HP-UX","LINUX");
} elsif ($OS eq "Windows") {
push (@choices, "NT 4.0","98","95");
}
return @choices;
Back to the INDEX.
Add a push button form control.
Push buttons are normally associated with
record scripts that perform
an action when the button is selected. To add a button to a form:
1) Open the form to be edited.
2) A box containing button type choices should appear automatically to the right of the
form in the Designer. Look for the Push Button type and drag & drop it into the desired location
on the form.
3) Right-click on the new button and set its name in the General tab and associate it with
a Record Script in the Extended tab.
NOTE: In CQ Web, hook code associated with buttons is executed on the web server.
Therefor, one should not invoke GUI based objects, as they will appear on the server, not on the
web client. If the button returns text, to ensure the button is disabled for web clients,
go to the Properties sheet of the button and deselect "Enabled for Web" in the Extended tab.
Back to the INDEX.
Add a combo box form control.
This control allows the user to select from a pull-down list, type in one of the given
choices (doesn't automatically complete based on minimum match) or type in their own
choice (assuming the choice list wasn't set to "Limit To List"). It's a combo between
a simple text field and pull-down menu.
1) Open the form to be edited.
2) Select the Combo Box form control from the Control Pallette and place it on the form.
There are two different ones depending on whether you want the list to appear in full or be a
pull-down menu.
3) Right-click on the new control and select Properties:
In the General tab, associate the new control with an existing field. That field should
be one that has a choice list associated with it. The Label can be any name, but for clarity
should probably correlate closely to the fieldname just selected. The X and Y selections designate
the coordinates of the upper-left corner of the new control on the form. The Width and Height are
self explanatory. If the height is smaller than the number of choices in the list, CQ will automatically
add a vertical scroll bar.
In the Extended tab, the Auto Sort check box selects whether the entries in the choice list
are sorted alpha-numerically. If not selected, choices appear in the order in which they were added.
In the Context Menu Hooks tab,
record scripts can be added to
the control's shortcut menu (right-click menu). The record script associated with this field must
actually SetFieldValue.
In the Web Dependent Fields tab, in order to have
dependent fields work with a
web client, the field on which the dependency is based needs to be explicitly declared.
Back to the INDEX.
Add a new tab.
If your form has more controls than can comfortably fit on the Main tab or need to be
separated by category, one can simply add additional tabs to the form.
Once the form is displayed in the Designer for editing, select Edit -> Add Tab.
Right-click on the new tab and select Tab Properties to give it a new name, select where
it appears among the other tabs and optionally set User/Group access.
Back to the INDEX.
Move a field between tabs.
With the form displayed for editing in the Designer, simply right-click on the field
and select Cut. Go to the destination tab and Paste the field. It will by default be placed
at the bottom of the tab. To move it to its permanent location, simply drag and drop both
the box and its Label. Fields can be copied or moved between tabs on different forms as well.
Back to the INDEX.
Remove a tab.
In the CQ Designer, with the form open for edit, select the tab to be deleted and
Edit -> Delete Tab. If a tab is deleted, all controls in the tab are removed
as well. They aren't deleted from the schema, just that tab. Delete Tab cannot be undone.
Back to the INDEX.
Clone a form.
With CQ Designer open to the schema containing the form, simply export the form
and then re-import it. CQ will automatically prompt for a new name upon import.
See Export/import a form.
Back to the INDEX.
Export/import a form.
To export, in the CQ Designer, right-click on the form and select Export Form. If the form is
currently open, close it so that all changes are saved prior to export.
To import, in the CQ Designer, right-click on the Forms folder and select Import Form.
The imported form must have a unique name.
Back to the INDEX.
Create a "Keywords" type control.
The control type that is normally associated with Keywords or Symptoms isn't a
single button type that can be chosen from the Form Controls menu. It's actually
a combination of a few items.
Create a new field of type MULTILINE_STRING whose Choice List can either
be DYNAMIC LIST or CONSTANT LIST . On the form, add a generic List Box control that isn't yet
associated with any particular field. Don't drag the newly created field over from
the "Field List for ..." dialog window. Enter the Properties sheet of the newly
added control and associate it with the newly created field. You'll notice that the
elipsis [...] button magically appears next to the List Box. You now have a
Keywords type control.
Back to the INDEX.
Export/import dynamic lists.
Dynamic lists are normally built up by users logged into the CQ Client.
However, lists can be populated via scripts with the following command. In
the file, each dynamic list entry must be alone on separate lines. Note that in
either use case listed here, if the existing list already has some entries,
the imported data will be appended to that list, if the entry is different.
# importutil importlist [-dbset dbset] cqlogin cqpassword db_name list_name inputfile_name
The same command(s) can be used copy the contents of a dynamic list to "seed"
the contents of another list. Unfortunately, it can't be done completely from within the Designer.
In the Designer, create a new dynamic list definition and push that change out to the
user database. Use the following command to export a known list from a user database
and then use the above importlist utility to populate the new, empty list.
# importutil exportlist [-dbset dbset] cqlogin cqpassword db_name old_list_name inputfile_name
# importutil importlist [-dbset dbset] cqlogin cqpassword db_name new_list_name inputfile_name
Back to the INDEX.
Create a variable Keyword list.
Keyword lists are useful when there is a need to run queries against
a category of tickets, or tickets that are similar across projects.
However, even though several projects may use the same schema, they may want
different keyword lists. It's possible to have the keyword list be hooked
to the value of another field, such as Project (as in the example steps below).
1) Create a Keywords field (if necessary) of Type MULTILINE_STRING.
Set its Choice List to CONSTANT_LIST, but don't put any values in the list.
Set the "Limit to List" checkbox if desired. If the field already exists in
your schema change its properties to match those listed here.
2) Import the Project package (if necessary) to create the Project
field and Project stateless record type.
3) In the Project field's Value Changed hook, place the following code:
my @Choices = ("");
$entity->SetFieldChoiceList("Keywords",\@Choices);
$entity->SetFieldValue("Keywords","");
my $project = $entity->GetFieldValue($fieldname)->GetValue;
if("$project" ne "") {
my $list = $session->GetListMembers($project);
$entity->SetFieldChoiceList("Keywords",$list);
}
4) For any Project that will be defined/created in the Client,
define/create a corresponding Dynamic List in the Designer with naming
convention "project_keywords". Because dynamic list
names cannot have any spaces, the project names cannot either. Yes, the hook
code could be modified to allow for that, but it isn't written that way currently.
5) In the client, populate the dynamic lists for each project. The
Keywords list should follow the name of the project. The hook will clear the
Keywords list if the value of Project is changed.
Back to the INDEX.
Add a bitmap image to a form.
Bitmap images can be added to a form for a Windows Client ONLY.
It won't work for UNIX or CQ Web users.
Back to the INDEX.
Add a Duplicate form control.
The control called Duplicate is a built-in function that cannot
be altered and has no fields associated with it. When a form is open
for edit, simply click on the button types called Duplicate Base and
Duplicate Dependent and place them on the form. The Duplicate Base
is a single record id that this ticket is a duplicate of. The Duplicate
Dependent is a list of records that are duplicates of this ticket.
Back to the INDEX.
Add an Option (radio button) control.
Radion buttons are a series of on/off switches connected in such a
way that only one switch can be "on" at a time. The following
example will create radion buttons to indicate whether a ticket
is a defect or enhancement.
1) Create a field called "Defect_or_Enhancement" of type SHORT_STRING.
Give it a Default Value of "Defect". Whatever Value is assigned in steps 3 & 4
will be stored in this field.
2) Place a pair of Option Buttons on a form.
3) In the Properties of one of the buttons, on the General tab,
set the Field Name to the field created in step (1). Set the Label to "Defect?".
On the Extended tab, set the Group Name to any unique string. Set the Group Label to
"Defect or Enhancement:". Set the Value to "Defect".
4) In the Properties sheet of the other button, on the General tab,
set the Field Name to the field created in step (1). Set the Label to "Enhancement?".
On the Extended tab, set the Group Name to the same one used in step (3). The Group
Label will automatically be set to the same one set in step (3). There is only one
Group Label per Group Name. Set the Value to "Enhancement".
When a user runs a query on the field from step (1), it will be a string whose
value is either "Defect" or "Enhancement" and can't be anything else. You
should probably make the field READ_ONLY for states such as Resolved, Closed,
Postponed, etc...
Back to the INDEX.
Set the field tab order.
The tab order of controls determines which control receives
focus when a user presses the Tab key. Each time the user presses Tab,
the focus moves to the next control in the tab order. By default,
the tab order of controls is the order in which you added the controls
to the form. You can change the tab order so that it reflects the order
in which you expect your users to use the controls.
To change the tab order of controls:
1. Expand Record Types or Record Types - Stateless in the Workspace until you see the desired form.
2. Double-click the form to display it.
3. Select the dialog tab containing the controls whose tab order you want to set.
4. Select Form Layout > Set Tab Order. ClearQuest changes to tab-order mode.
In this mode, each control displays a number indicating its position in the tab order.
5. Click the control you want to be first in the tab order.
6. Click each of the remaining controls in the order you want them to receive the focus.
As you click each control, its displayed number changes to match the new tab order.
After you click each of the controls once, ClearQuest exits tab-order mode. You can
also exit tab-order mode by clicking in an empty portion of the dialog tab.
Back to the INDEX.
Embed a URL in a description field.
Updated: 09/13/06
As of CQ 2002, a complete URL embedded in a description field will be automatically
detected and made clickable for the user. If the URL contains spaces, it must be enclosed
in double-quotes.
As of CQ 7.0, you can generate URLs using a wizard in the web interace. Log into the
database and select "Shortcuts" under the New menu in the toolbar.
To embed a URL in an email notification, see
Add free-form text to an email rule.
Note that if the URL that the user clicks is, say, a MAILTO: URL, and, say, the body of
that email is the URL for the current record, the embedded URL will need to be URL encoded to
protect its special characters. Unfortunately, as of this writing, a "mailto" hyperlink doesn't
work in the web interface. For example:
If the URL you want to appear in the body of the email is:
http://cqwebserver/cqweb/main?command=GenerateMainFrame&service=CQ&schema=Enterprise&contextid=DB01&entityID=12345678&entityDefName=Defect
you would create the MAILTO link such that the above special characters are URL encoded:
MAILTO:elvis.presley?subject=DB0100001234&body=http%3A%2F%2Fcqwebserver%2Fcqweb%3Amain%3Fcommand%3DGenerateMainFrame%26service%3DCQ%26schema%3DEnterprise%26contextid%3DDB01%26entityID%3D12345678%26entityDefName%3DDefect
: %3A
= %3D
/ %2F
? %3F
& %26
See http://www.blooberry.com/indexdot/html/topics/urlencoding.htm
WARNING: The URL format was changed in CQ 7.0. Your URL generator will need to be modified
and URLs in existing records will need to be updated.
Back to the INDEX.
Date/time form control WARNING.
Updated: 08/23/06
Field definitions in CQ can fire a hook when the field's value has changed. Under normal circumstances, that hook
doesn't fire unless the field's value has actually been changed. That sounds like an obvious statement, but, that hook
will also fire for a date/time field if the "time" is not displayed. That is, if a date/time field isn't configured
to display the "time", if you even just Modify a record, that field's Value Changed hook will fire immediately, regardless. Beware!
The reason, it is surmised, is that the data retrieved from the database has a time associated with, but because
it isn't displayed, CQ is interpreting it as a difference. If you don't have any code in the Value Changed hook, no harm
done. But, if there is code that perhaps makes a modification elsewhere in the ticket based on the new value, CQ WILL
pick up on that as a change. The real problem here is that you don't expect that hook to be firing and changing
the ticket when that field hasn't been modified. This gets even more insidious if you are editing a bunch tickets in "batch" mode in
the Client. Since the first ticket picked up on a change to that date/time field, the changed value elsewhere in the first ticket
will be applied to all subsequent tickets, even though you did not actually change the date/time value. Beware!
Back to the INDEX.
Dynamically change a field's label on a form.
Updated: 09/20/06
When creating a field in the Designer, you give that field a "label" (the name that appears
next to the field for the user). That label is then static. However, it is possible to dynamically
change text that appears next to a field.
Create the field you want (FieldA), but don't give it a label. Create a second field called, say,
FieldA_label. Place FieldA on the form. Next to that field on the form, place a "static text" form control.
Assign that static text form control to FieldA_label. Now, any value you place into the FieldA_label field
will appear next to FieldA as text, which will look like the field's label to the user. That value can be
changed at any time in hook code.
However, this trick has three downsides:
1) FieldA cannot be mandatory at any time. The way a user
knows that a field is mandatory is that the field's label turns red. But, since FieldA doesn't have a label,
the user won't know that it's mandatory. Well, actually, they will know it when they Save the record. So,
if you can live with getting the abrupt error message when you Save, you can have the field be mandatory.
2) In the web, if you change the value of FieldA_label, that change may not show up immediately, as
the web record doesn't always refresh. You can give the user a Refresh Record button, but that's a hassle
for the user. So, there is a record refresh problem when changing the FieldA_label text.
3) The user will see think that the name of the field is whatever value is in FieldA_label. It will
be difficult for them to remember what the actual field name is when writing a query.
Back to the INDEX.
Manually set the db column name.
Updated: 03/28/07
When adding a new field, CQ automatically assigns it a db column name.
The column name is the all lower-case version of the field's name. However, at that
time, you are allowed to change/set the column name to any unused name.
NOTE: When a field is deleted from CQ, it is only a logical deletion. The column
of data remains in the database. Therefore, if a field was previously deleted, while a
new field can have the same name, the column name must be different.
NOTE: If a schema is imported into a different schema repo and assigned to a database,
the column names will all be the default values. If there are scripts that work at
the SQL level, they might have to take the different column names into account.
Back to the INDEX.
Create a field/action hook.
Field and action hooks can be one of the following types. The following are executed
in order when and action begins:
| Action Access Control
| Is the user allowed to execute this action?
|
| Field Permission
| Determines whether a field is mandatory, optional or read-only.
|
| Action Initialization
| Sets up field values before the action begins.
|
| Field Default Value
| Sets a default field value. Submit actions only.
|
| Field Validation
| Validates the field entry immediately after a change. Not applicable to import action.
|
| Field Choice List
| Runs for each field that uses the Recalculate Choice List option.
|
The following are executed in order when a field value is set:
| Field Value Changed
| Runs for each field that changes.
|
| Field Validation
| Runs for each field that changed.
|
| Field Choice List
| Runs for each field that changed and whose Recalculate Choice List is set.
|
NOTE: In CQ Web, field hooks only run when the Submit button is selected.
The following are executed when a record is validated:
| Field Validation
| Runs for each field on the record.
|
| Action Validation
| Runs for the performed action.
|
The following are executed when a record is committed. The record is first saved to
the database without committing it, then the following are run:
| Action Commit
| Executes for the performed action.
|
| -
| Commits the changes to the database.
|
| Action Notification
| Sends out notifications based on established e-mail rules.
|
In the CQ Designer, from the Workspace, open a Fields or Actions table.
Single left-click in the appropriate column next to the field/action in question.
Select the arrow, then SCRIPTS and the hook language of your choice (PERL or BASIC).
CQ will automatically open a script editor with a subroutine already set up for you.
NOTE: A given schema will only support hooks written in one or the
other language. The Windows Scripting Language is set in Workspace pane -> Schema Properties.
The default is BASIC. Once the language is chosen, all hooks for that schema must be
written in the same language.
Once you have edited the script, select Hooks -> Compile and
CQ will run a syntax check on your script. If error free, simply close the editor
and the changes will be saved. Then, File -> Test Work.
Some common ways to retrieve information. The placement of
the capital letters is mandatory. The current entity is already set in $entity
for you.
NOTE: It isn't possible to directly get the output of a system call.
That is, a command like ($var = `command`;) won't return the output of "command".
At best, you can redirect the output to a file and then open read the contents
of the file.
get a field value
Perl: $field_value = $entity->GetFieldValue("fieldname")->GetValue;
VBScript: fieldvalue = entity.GetFieldValue("fieldname").GetValue
choice lists
$entity->InvalidateFieldChoiceList("list-fieldname");
$entity->SetFieldChoiceList("fieldname","dynamic-list-name");
@members = $session->GetListMembers(dynamic-list-name);
$fieldChoiceListObj = $entity->GetFieldChoiceList("list-fieldname");
$entity->SetFieldValue("other-fieldname",$fieldChoiceListObj->Item (0));
@fielChoiceListValues = @$fieldChoiceListObj;
get the previous field value
$old_fieldvalue = $entity->GetFieldOriginalValue("fieldname")->GetValue;
set a field value
$entity->SetFieldValue("fieldname","value");
get the current action
$action = $entity->GetActionName();
get the current state
$state = $entity->LookupStateName();
get the previous state
$old_state = $entity->GetFieldOriginalValue("State")->GetValue;
current user
$login_name = $entity->GetSession->GetUserLoginName;
refer to a different record
$other_entity = $entity->GetSession->GetEntity("other-entity-name",record-key);
output debug statements (on Windows, the debug statements can be viewed with dbwin32)
$session->OutputDebugString("string");
set a default date
$entity->SetFieldValue("fieldname",GetCurrentDate);
set requiredness in a different field
the requiredness is one of MANDATORY, OPTIONAL, or READONLY
$entity->SetFieldRequirednessForCurrentAction("other-fieldname",$CQPerlExt::requiredness);
get the current record type's name
record_type = sessionObj.GetEntityDef(GetEntityDefName).GetName
For more information on the CQ API, see \apihelp\index.htm.
NOTE: Duplicate "my" declarations in a Perl hook will cause your
script to produce erratic and unpredicable results. For example, even though "my" declaration
can occur once per invocation of the following code, the multiple declarations will cause the
whole "if" statement to not work.
if ( "$character" eq "x" ) {
my $letter = "x";
} elsif ( "$character" eq "y" ) {
my $letter = "y";
} else {
my $letter = "z";
}
Instead, use:
my $letter;
if ( "$character" eq "x" ) {
$letter = "x";
} elsif ( "$character" eq "y" ) {
$letter = "y";
} else {
$letter = "z";
}
Back to the INDEX.
Remove a field/action hook.
In the Defect:Fields matrix, simply click on the hook, click on the little
arrow and choose NONE. The hook code (subroutine) will go away for that field for
that type.
Back to the INDEX.
Create a permission field hook.
Create the hook
in the Permission column of the field matrix and give it code similar to the following:
if ( $username ne "admin" ) {
$result = $CQPerlExt::CQ_READONLY;
} else {
$result = $CQPerlExt::CQ_OPTIONAL;
}
If username <> "admin" Then
result = AD_READONLY
Else
result = AD_OPTIONAL
End If
Another option to return is $CQPerlExt::CQ_MANDATORY or AD_MANDATORY.
Back to the INDEX.
Create a validation field hook.
In the Fields matrix/grid, click in the Validation column of the field you
want validated and choose the preferred scripting language. As an example, a hook
that checks whether the field's value is between 1 & 10 would look similar to the
following:
my $value = $entity->GetFieldValue("fieldname")->GetValue;
if ($value < 1 || $value > 10) {
$result = "Field value must be between 1 and 10";
}
When satisfied with the changes, select Hooks -> Compile.
Back to the INDEX.
Create a global script.
In CQ, one has the ability to write code that can be called from within any hook
as a way to help centralize hook code maintenance. Global scripts are not called directly
anywhere in CQ.
1) In the Workspace, expand the Global Scripts folder.
2) Right-click on the desired language and choose Add.
3) Right-click on the new global script and choose Rename; must be unique.
4) The script must be self-contained within a subroutine appropriate for the language
chosen. When done, select Hooks -> Compile to test and save the code. The subroutine
can now be called from within any hook.
Back to the INDEX.
Create a record script.
In CQ, one can have the user invoke a script by selecting it via a pull-down menu,
push button or called from within a hook. The script can then do most anything, most often
returning information to the user.
1) In the Workspace, expand the record type with which this script will be associated
until you see the Record Scripts folder.
2) In the Record Scripts folder, right-click on the desired language and choose Add,
renaming the new script to something nmemonic and unique.
3) Double-click on the script name to invoke the editor. Unlike Global Scripts, these are
already placed in an appropriately named subroutine for you. Select Hooks -> Compile to
test and save your work.
NOTE: If the Record Script is associated with a push button and invoked from the
web and the script returns a string, CQ Web will interpret is as an error message and the
hook will fail. Also, do not invoke GUI based objects from the script if working with the web,
as they will appear on the server, not on the client.
Back to the INDEX.
Get an array of values for a specific user group's field.
# Usage: @result = GetGroupMembersField(<group_name>,<field_name>);
# ex: @fullnames = GetGroupMembersField("Managers","fullname");
sub GetGroupMembersField {
my @result;
my @group = $_[0];
my $ref = \@group;
my $field = $_[1];
my $queryDef = $session->BuildQuery("users");
$queryDef->BuildField($field);
my $filterNode = $queryDef->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_AND);
$filterNode->BuildFilter("groups",$CQPerlExt::CQ_COMP_OP_EQ,$ref);
my $resultSet = $session->BuildResultSet($queryDef);
$resultSet->Execute();
my $status = $resultSet->MoveNext;
while ( "$status" eq "$CQPerlExt::CQ_SUCCESS" ) {
push(@result,$resultSet->GetColumnValue(1));
$status = $resultSet->MoveNext;
}
return @result;
}
Back to the INDEX.
Determine a user's groups.
Updated: 06-12-06
Use the following API call:
$userGroups = $sessionObj->GetUserGroups;
if ( ! @$userGroups ) {
print "There are no groups in the user db.\n";
} else {
print "Groups:\n";
foreach $group (@$userGroups) {
print "\t$group\n";
}
}
Or, use a query:
# Usage: @groups = user2groups("$username");
sub user2groups {
my $username = $_[0];
my @groups;
my $session = $entity->GetSession;
# Start building a query of the users.
my $queryDefObj = $session->BuildQuery("users");
# Have the query return the desired field for the user object(s).
$queryDefObj->BuildField("groups");
# Filter on the designated login.
my $filterOp = $queryDefObj->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_AND);
my @array = ("$username");
$filterOp->BuildFilter("login_name",$CQPerlExt::CQ_COMP_OP_EQ,\@array);
my $resultSetObj = $session->BuildResultSet($queryDefObj);
# Run the query.
$resultSetObj->Execute;
# Add each value in the returned column to the choices array.
while ($resultSetObj->MoveNext == $CQPerlExt::CQ_SUCCESS) {
push(@groups,$resultSetObj->GetColumnValue(1));
}
return @groups;
}
Back to the INDEX.
Determine a group's users.
The following hook query returns a list of users belonging to a specified group.
Because this is useful in many situations, it should be created as a subroutine in the Global Scripts.
It filters out inactive group members.