Figure 2.33: Power Searches

How to Use the Power Search Functions

To do a power search you need to be able format the query commands correctly. You should read the Query Structure section to understand how to do this. SeeQuery Structure

Several examples will be provided to show you how to format certain types of power searches.

The following procedure describes how to use this document to do power searches.

To use the power search feature:

1. Decide which type of @task object that you want to find, for example, projects, tasks, or users. Open the related search screen and locate the power search field.

2. Decide which attributes of the object you want to search for. The attributes for each object are listed in the tables that follow. If you are looking for attributes for projects, they are listed under the table entitled Project.

SeeObject Attributes

3. Look in the tables for the attribute and make note of the attribute type and the attribute ID. The attribute ID is in the first column, the attribute type is in the second column, and the attribute name is in the last column. If the attribute has data in a valid values column, then these are the only values that you can use when searching for the attribute.

4. When you know the attribute type for the attribute you are using, consult in Table 2.7 on page 57 to see an example of how to write a correctly formatted query for that attribute type.

5. When you create the query command, use the attribute ID exactly as it is shown in the table. Query commands are case sensitive.

6. You can use power searches to do the following

Simple searches

Searches that look at related objects

Searches that comparable values of different attributes

Searches for specific dates

Searches for values that fall within a specific range

Searches for custom data fields.

There are examples of how to correctly compose each of these types of searches in the sections that follow

SeeExamples

N O T E : The power search bean for issues is called Op Task rather than Issues.

Query Structure

To use power searches you need to type certain command structures into the Power Search field. The command that you type depends on the type of search that you are doing. If you are doing a power search for tasks, then you would open the search screen for tasks and build your searches.

To use a power search, you must know how to build the queries correctly. The basic structure is:

<attributeID>=<value>

For example, a user who is searching for projects by name should type a query similar to the following:

name='my project'

You should keep the following in mind when doing power searches:

• Use single quotes when the value has spaces. Without quotes, the command in the previous example would return projects named `my', `my proj', and `my project'.
• Attribute IDs are case sensitive.
• All queries that you type return values that contain the string value you type. For example the command `name=my' would return both of the values `my project' and `army' (assuming those values exist) because they all have the string 'my' in them.
• You can also use multiple search commands at the same time. To do this, press Return after each command.

percentComplete=100

name=philip

• Some attributes, such as priority, have a set of valid values that you must use. These values are listed in the tables that follow. For example, in a task search to get the tasks with a priority of High, you would type the following:

priority=3

Query Modifiers

You can use the following list of query modifiers with your commands. The correct structure for using modifiers is:

name=smith

name_Mod=cicontains

In the preceding example, @task searches for all instances of the name attribute in an object that contains the string "smith". The the "ci" in the cicontains command represents case insensitive. Therefore, @task would return Smith, smith, smitH, smithy, smithson, Jones-Smithson, or any other instance with the string smith in it. You could use the contains modifier as well to retain case sensitivity. Table 2.6 shows the complete set of modifier commands you can use.

Query Modifiers

Modifier	Description
between	Falls within a certain range SeeSearching for Attributes that fall within a Range
cicontains	Case insensitive version of the attribute contains the string
contains	The attribute contains the string
isnull	The value is null
notbetween	Falls outside of a certain range SeeSearching for Attributes that fall within a Range
soundex	Sounds like the string

Additional modifiers for comparisons are described in the associated example.

SeeComparing Values Between Two Different Attributes

Examples

The following sections give examples of different options you have when doing power searches.

Simple Searches

As an example of how to do a power search, assume that you want to look for tasks with specific attributes. After opening a search window for tasks, the first thing you should do is check the Task table in this document to find what task attributes are searchable.

Assuming that you want to search for all tasks that must be approved, you have a couple of options. You can simply check the box in the standard search screen to find all approval tasks. But perhaps you need to do more than that. You may need to find all approval tasks that are set to role-based approvals.

Reading the table, you see that the correct attribute ID is approvalRequired and the valid value for role based is RB. In the power search you type:

approvalRequired=RB

Searching for Objects Related to Other Objects

The previous command returns role-based approval tasks for all projects, but perhaps you want to search for tasks in a specific project. To do a search for role-based approval tasks in a project named Proj3, first you should check the table to ensure that the task object is linked to the project object. A data type of Bean indicates a linkage. Because the linkage exists, you can search for tasks associated with specific projects. Next you search the table for the project bean to see the correct attribute ID for project names. The attribute ID is 'name'. To search for role-based tasks in the project Proj3, you type the following commands in the power search field. The commands must be separated by hard returns.

approvalRequired=RB

project:name=Proj3

Another example of this would be if you wanted to search for all projects that have a task name of `task3'. When you look in the Project attribute table, you see that the task attribute is a bean. This means that this attribute references an object with its own set of attributes. You look at the task attribute list to find the attribute ID for the name attribute. So you write a command that tells @task to search for projects that have tasks where the name attribute of the task is `task3' The command looks like this:

task:name=task3

This ability to search for objects related to other objects allow power searches to provide you with options to search for objects in ways that you might not normally do. For example, in a task search screen you could search for tasks assigned to people who earn $20 per hour. Task objects have an 'assigned to' attribute that links to the user object, or the user bean. So while doing a power search for tasks, you could use the following command:

assignedTo:costPerHour=20

You would get all tasks assigned to any user who earns $20 per hour.

A few more examples are:

• In a task search, to find tasks assigned to the engineer role, type the following command:

role:name=engineer

• In a user search to find all users who report to Janis Dunst:

manager:firstName=Janis

or

manager:lastName=Dunst

• In a project search, search for all projects that have a task with the word equipment in the task description.

task:description=equipment

task:description_Mod=cicontains

Comparing Values Between Two Different Attributes

You can compare attribute values as well. If you want to compare the planned cost to the actual cost of tasks in a project, you could use the following set of commands to see all tasks (or projects) where the planned cost is less than the actual cost.

plannedCost=FIELD:actualCost

plannedCost_Mod=lt

When you compare values, you must use the FIELD delimiter and you must use the Mod command to modify the search. In the preceding example, you could have used gt rather than lt to find tasks where the planned cost is greater than the actual cost. Without the Mod command, the query finds tasks where the planned cost is equal to the actual cost. The Mod command always modifies the first element in the command. Other values that you can use with the Mod command are:

• lt - less than
• gt - greater than
• lte - less than or equal
• gte - greater than or equal
• ne - not equal.

If you are comparing strings, you can put a ci in front of the Mod value to make the search case insensitive. For example, cigt equates to case insensitive, greater than.

Searching for Things that Fall on Specific Dates

Several of the attributes you can search for use dates. Use the following date formats:

plannedStartDate=03/22/2006

For a date range, use the following command set:

plannedStartDate=03/22/2006

plannedStartDate_Range=03/29/2006

You can also search for exact times of the day using the following format:

plannedStartDate=2006-03-22T17:30:00:000

The previous example is for tasks that start on 5:30 PM on March 22, 2006. You can use the format to find times down to the thousandth of a second. The Range modifier also works with this format. The following example finds dates between noon and 5:30 PM on March 22, 2006.

plannedStartDate=2006-03-22T12:00:00:000

plannedStartDate_Range=2006-03-22T17:30:00:000

All times are in the time zone where the server resides.

Searching for Attributes that fall within a Range

You can also use the range modifier on strings, integers and doubles in addition to dates.

For example, if you do a project search and want projects with names between d and h, you can use the following command set:

name=d

name_Range=h

Ranges also work for beans.

role:name=e

role:name_Range=t

You can also use a modifier command to find values that fall outside of a range.

name=d

name_Range=h

name_Mod=notbetween

Searching for Custom Data Fields

For objects that have custom data, you can search the custom data fields associated with the objects. For example, a painting company could have a project to paint a house. Painting the kitchen is one task and painting each of the other rooms are also tasks. The company may have created custom data for the tasks with fields for paint color, number of coats, and type of paint, such as flat or glossy. This company could search for all tasks where they used red paint.

To search for custom data, you must use the names of the fields exactly as they appear in the custom data forms. You can look at the forms to find the exact names of the fields.

SeeCustom Data Tab

You can also look directly at the parameters.\

SeeCreating and Using Parameters for Custom Forms

In the above example, the painting company could use the following command to find all tasks where the paint color is red (assuming that the name of the custom data field is paint color".)

DE:paint color=red

N O T E : `DE:' must be used in queries when searching for custom data.

If the company wants to find all tasks where the color is green and the type of paint is glossy, they would use the following set of query commands:

DE:paint color=green

DE:paint type=glossy

Possibly some of the users who typed the paint color used different capitalization when entering data or may have misspelled the word. To find all instances where the paint color is orange and to find possible misspellings, use the case-insensitive contains command:

DE:paint color=ora

DE:paint color_Mod=cicontains

This finds all instances where the string `ora' is in the paint color field, regardless of capitalization and whether it is part of a larger word.

Searching for Data Extension Values on a Related Object

To add one more level of complexity, you may want to search for objects where a related object has a custom parameter value of `x'. For example, you may want to find all tasks in projects that have a custom parameter called Paper Color, with a value of Blue.

DE:project:Paper Color=Blue

Notice that the search option starts with DE, followed by a colon, then the related object, then another colon, then the parameter name.

Another example: To find all tasks that are assigned to users with a custom parameter called Employee that is set to true.

DE:assignedTo:Employee=true

Data Types

The attributes come in several data types. You must format the value differently depending on the attribute data type. The following table describes and gives examples for each attribute data type.

Data Types

Attribute Data Type	Description	Examples
Integer	A whole number.	maxUsers=53
Boolean	A true of false value.	isPrivate=true
String	A word or phrase.	name='my task'
Double	A number that can use decimals.	costPerHour=33.75
Date	A calendar date. Use the date format shown here.	plannedCompletionDate=05/24/2007
Bean	This references another searchable @task object with its own set of attributes.	enteredBy:lastName=Smith In this case, you may be using a task search. The enteredBy attribute references the user object. The lastName attribute of the user object is Smith.

Object Attributes

For each searchable @task object the following tables list the attributes, the data type, the attribute ID, and where applicable, valid values.

Access Level

Attribute ID	Data Type	Attribute Name
description	String	Description
hasFinancialAccess	Boolean	Has Financial Access
ID	Integer	ID
isAdmin	Boolean	Admin Access Level
licenseType	String	License Type
name	String	Name
rank	Integer	Rank

 

Access Scope Action

Attribute ID	Data Type	Attribute Name
accessLevel	Access Level Bean	Access Level
accessLevelID	Integer	Access Level ID
accessScope	Access Scope Bean	Access Scope
accessScopeID	Integer	Access Scope ID

 

Access Scope

Attribute ID	Data Type	Attribute Name
description	String	Description
displayOrder	Integer	Display Order
ID	Integer	ID
name	String	Name

 

Account Rep

Attribute ID	Data Type	Attribute Name	Valid Values
adminLevel	String	Admin Level	N - none R - account rep A - admin
country	String	Country
emailAddr	String	Email Address
firstName	String	First Name
ID	Integer	ID
isActive	Boolean	Is Active
lastName	String	Last Name
password	String	Password
reseller	Reseller Bean	Reseller
resellerID	Integer	Reseller ID
username	String	Username

 

App Event

Attribute ID	Data Type	Attribute Name	Valid Values
description	String	Description
eventType	String	Event Type	A - add D - delete E - edit H - hourly timed T - daily timed
ID	Integer	ID
name	String	Name
queryExpression	String	Query Expression
scriptExpression	String	Script Expression

 

App Info

Attribute ID	Data Type	Attribute Name
attaskVersion	String	Attask Version
buildNumber	Integer	Build Number
hasUpgradeError	Boolean	Has Upgrade Error
ID	Integer	ID
lastUpdate	Date	Last Update
upgradeBuild Number	Integer	Upgrade Build Number
upgradeStep	Integer	Upgrade Step

 

Approval

Attribute ID	Data Type	Attribute Name	Valid Values
customer	Customer Bean	Customer
customerID	Integer	Customer ID
durationMinutes	Integer	Duration Minutes
durationUnit	String	Duration Unit	M - minutes H - hours D - days W - weeks T - months EM - elapsed minutes EH - elapsed hours ED - elapsed days EW - elapsed weeks
estStartDate	Date	Est Start Date
ID	Integer	ID
plannedStartDate	Date	Planned Start Date
projectedStart Date	Date	Projected Start Date

Approver

Attribute ID	Data Type	Attribute Name
ID	Integer	ID
opTask	Op Task (Issue) Bean	OpTask
opTaskID	Integer	OpTask ID
project	Project Bean	Project
projectID	Integer	Project ID
role	Role Bean	Role
roleID	Integer	Role ID
status	String	Status
task	Task Bean	Task
taskID	Integer	Task ID
template	Template Bean	Template
templateID	Integer	Template ID
templateTask	Template Task Bean	TemplateTask
templateTaskID	Integer	TemplateTask ID
user	User Bean	User
userID	Integer	User ID

 

Assignment

Attribute ID	Data Type	Attribute Name
assignedTo	User Bean	Assigned To
assignedToCompanyMM	Company Bean	Assigned to Company
assignedToDirectReportsOM	User Bean	Assigned to Direct Reports
assignedToHomeGroupMM	Group Bean	Assigned to Home Group
assignedToID	Integer	Assigned To ID
assignedToManagerMM	User Bean	Assigned to Manager
assignedToOtherGroupsMM	Group Bean	Assigned to Other Groups
assignedToRolesMM	Role Bean	Assigned to Roles
assignmentPercent	Double	Assignment Percent
avgWorkPerDay	Double	Average Work Per Day
customer	Customer Bean	Customer
customerID	Integer	Customer ID
ID	Integer	ID
ID	Integer	ID
isPrimary	Boolean	Is Primary
opTask	Op Task (Issue) Bean	opTask
opTaskID	Integer	OpTask ID
project	Project Bean	Project
projectID	Integer	Project ID
role	Role Bean	Role
roleID	Integer	Role ID
task	Task Bean	Task
taskID	Integer	Task ID
workingDays	Integer	Working Days
workRequired	Integer	Work Required in minutes

 

Billing Record

Attribute ID	Data Type	Attribute Name
amount	Double	Amount
billingDate	Date	Billing Date
customer	Customer Bean	Customer
customerID	Integer	Customer ID
description	String	Description
ID	Integer	ID
invoiceID	String	Invoice ID
PONumber	String	PO Number
project	Project Bean	Project
projectCompanyMM	Company Bean	Project Company
projectGroupMM	Group Bean	Project Group
projectID	Integer	Project ID

 

Category

Attribute ID	Data Type	Attribute Name
category	Category Bean	Category
categoryID	Integer	Category ID
customer	Customer Bean	Customer
customerID	Integer	Customer ID
ID	Integer	ID
name	String	Name
parameterValues	Parameter Value Bean	Parameter Values

 

Category Parameter

Attribute ID	Data Type	Attribute Name
category	Category Bean	Category
categoryID	Integer	Category ID
displayOrder	Integer	Display Order
isRequired	Boolean	Is Required
parameter	Parameter Bean	Parameter
parameterGroup	Parameter Group Bean	Parameter Group
parameterGroupID	Integer	Parameter Group ID
parameterID	Integer	Parameter ID
rowShared	Boolean	Row Shared

 

Company

Attribute ID	Data Type	Attribute Name
category	Category Bean	Category
categoryID	Integer	Category ID
customer	Customer Bean	Customer
customerID	Integer	Customer ID
ID	Integer	ID
name	String	Name
parameterValues	Parameter Value Bean	Parameter Values

 

Customer

Attribute ID	Data Type	Attribute Name	Valid Values
accessLevels	Access Level Bean	Access Level
accessScopes	Access Scope Bean	Access Scope
accountRep	Account Rep Bean	Account Rep
accountRepID	Integer	AccountRep ID
address	String	Address
adminAcctName	String	Admin Account Name
appEvents	App Event Bean	App Event
categories	Category Bean	Categories
city	String	City
country	String	Country
currency	String	Currency
description	String	Description
docQuota	Integer	Uploaded Document quota
documents	Document Bean	Documents
emailAddr	String	Email Address
evalExpDate	Date	Evaluation Expiration Date
eventHandlers	Event Handler Bean	Event Handlers
firstname	String	First Name
fullUsers	Integer	Full Users
groupPreferences Type	Integer	Group Preference Type
groups	Group Bean	Groups
hasDocuments	Boolean	Has Documents
hourTypes	Hour Type Bean	Hour Type
ID	Integer	ID
lastname	String	Last Name
licenseOrders	License Order Bean	License Orders
limitedUsers	Integer	Limited Users
locale	String	Locale
milestonePaths	Milestone Path Bean	Mile Stone Path
name	String	Name
notes	Note Bean	Notes
parameterGroups	Parameter Group Bean	Parameter Groups
parameters	Parameter Bean	Parameters
phoneNumber	String	Phone Number
portalProfiles	Portal Profile Bean	Portal Profiles
portalSections	Portal Section Bean	Portal Sections
postalCode	String	Postal Code
requestorUsers	Integer	Requestor Users
reseller	Reseller Bean	Reseller
resellerID	Integer	Reseller ID
roles	Role Bean	Roles
schedules	Schedule Bean	Schedules
state	String	State
status	String	Status	E - Evaluation A - Active D - Deactivated
styleSheet	String	Style Sheet
timeZone	String	Time Zone
uiViews	UI View Bean	UIView

 

Document

Attribute ID	Data Type	Attribute Name
category	Category Bean	Category
categoryID	Integer	Category ID
checkedOutBy	User Bean	Checked Out By
checkedOutByID	Integer	Checked Out By ID
currentVersion	Document Version Bean	Current Version
currentVersionID	Integer	Current Version ID
customer	Customer Bean	Customer
customerID	Integer	Customer ID
description	String	Description
groups	Group Bean	Groups
hasNotes	Boolean	Has Notes
ID	Integer	ID
isDir	Boolean	Is Dir
isPrivate	Boolean	Is Private
lastModDate	Date	Last Mod Date
name	String	Name
notes	Note Bean	Notes
owner	User Bean	Owner
ownerID	Integer	Owner ID
releaseVersion	Document Version Bean	Release Version
releaseVersionID	Integer	Release Version ID
subscribers	User Bean	Subscribers
versions	Document Version Bean	Versions

 

Document Version

Attribute ID	Data Type	Attribute Name
docSize	Integer	Doc Size
document	Document Bean	Document
documentID	Integer	Document ID
enteredBy	User Bean	Entered By
enteredByID	Integer	Entered By ID
entryDate	Date	Entry Date
ext	String	Ext
fileName	String	File Name
ID	Integer	ID

 

Event Handler

Attribute ID	Data Type	Attribute Name	Valid Values
appEvents	App Event Bean	AppEvents
customer	Customer Bean	Customer
customerID	Integer	Customer ID
description	String	Description
handlerType	String	Event Handler Type	E - email L - Log
ID	Integer	ID
isActive	Boolean	Is Active
isSystemHandler	Boolean	Is System Handler and uneditable
name	String	Name

 

Group

Attribute ID	Data Type	Attribute Name
customer	Customer Bean	Customer
customerID	Integer	Customer ID
description	String	Description
name	String	Name
ID	Integer	ID

 

Hour

Attribute ID	Data Type	Attribute Name
actualCost	Double	Actual Cost
customer	Customer Bean	Customer
customerID	Integer	Customer ID
description	String	Description
dupID	Integer	Dup ID
entryDate	Date	Entry Date
hours	Double	Hours
hourType	Hour Type Bean	Hour Type
hourTypeID	Integer	Hour Type ID
ID	Integer	ID
opTask	Op Task (Issue) Bean	OpTask
opTaskID	Integer	OpTask ID
owner	User Bean	Owner
ownerCompanyMM	Company Bean	Owner Company
ownerHomeGroupMM	Group Bean	owner Home Group
ownerID	Integer	Owner ID
ownerManagerMM	User Bean	Owner Manager
ownerOtherGroupsMM	Group Bean	owner Other Groups
project	Project Bean	Project
projectCompanyMM	Company Bean	Project Company
projectGroupMM	Group Bean	project Group
projectID	Integer	Project ID
projectOverhead	Project Bean	Project Overhead
projectOverheadID	Integer	Project Overhead ID
resourceRevenue	Double	Resource Revenue
role	Role Bean	Role
roleID	Integer	Role ID
task	Task Bean	Task
taskID	Integer	Task ID
timesheet	Timesheet Bean	Timesheet
timesheetID	Integer	Timesheet ID

 

Hour Type

Attribute ID	Data Type	Attribute Name	Valid Values
description	String	Description
ID	Integer	ID
name	String	Name
overheadType	Integer	OverheadType	0 - task hours 1 - project hours 2 - general hours 3 - non-work hours

<