All columns, types, and operators

This topic describes how to use the find query language together with all columns, types and operators in Hansoft.

All columns in Hansoft have an ideal type associated with it. For example, both column Work remaining and Earned Value have the type Hours. Detailed description, Item name and Comments have the type Text.

The type describes how to use find on a column and it also specifies how the operators work.

Keywords and parameters can be explicitly typed. This means that when Hansoft evaluates the find query it will override the type that would otherwise have been assigned to the keyword or parameter. For example you might want to explicitly type a keyword to Text to avoid it only finding items assigned to a user with the same name you want to search for as text in other fields.

Note: You can view the query syntax associated with each column in the report editor.

All columns

Column name Type Example Result
Assign tag Resource Assigntag:Resource(<Resource>) Find items for which the "Assign tag" column contains the resource <Resource>
Assigned to Resource Assignedto:Resource(<Resource>) Find items for which the "Assigned to" column contains the resource <Resource>
Board column Text BoardColumn:Text(<Name>) Find items for which the Board Column name contains <Name>
Board lane Text BoardLane:Text(<Name>) Find items for which the Board Lane name contains <Name>
Budgeted work Hours Budgetedwork= <Hours> Find items for which the "Budgeted work" column contains the value <Hours>
*Budgeted work is only accessible in Task scheduling mode
Bug priority Number Bugpriority= <Number> or <Priority> Find bugs for which the "Bug status" column contains the bug priority <Bug status>
*Only found in QA view
Category Backlog category Category= <Backlog category> Find items for which the "Category" column contains the category <Backlog category>
CC new comments to Resource CCnewcommentsto:Resource(<Resource>) Find items for which the "CC new comments to" column contains the resource <Resource>
Comments Text Comments:Text(<Text>) Finds items containing <Text> in the comment section
Committed to Item Committedto:Item(<Item>) Finds items for which the "Committed to" column contains <Item>
Confidence Confidence Confidence= <Confidence> Finds items for which the "Confidence" column contains <Confidence>
Custom column date Time MyColumn= <Time> Finds items for which "MyColumn" contains <Time>
*MyColumn is a custom column
Custom column drop list Custom column drop list MyColumn= <CustomColumnDroplist> Find items for which "MyColumn" contains the droplist choice <CustomColumnDroplist>
*MyColumn is a custom column
Custom column Function Number MyColumn:<Number> Find items for which "MyColumn" contains the number <Number>
*MyColumn is a custom column
Custom column hyperlink Text MyColumn:<Text> Find items for which "MyColumn" contains the text <Text>
*MyColumn is a custom column
Custom column multi line text Text MyColumn:<Text> Find items for which "MyColumn" contains the text <Text>
*MyColumn is a custom column
Custom column number Number Mycolumn:<Number> Find items for which "MyColumn" contains the number <Number>
*MyColumn is a custom column
Custom column users Resource MyColumn:<Resource> Find items for which "MyColumn" contains the resource <Resource>
*MyColumn is a custom column
Custom column text Text MyColumn:<Text> Find items for which "MyColumn" contains the text <Text>
*MyColumn is a custom column
Database ID Number DatabaseID= <Number> Find items for which "MyColumn" contains the number <Number>
*MyColumn is a custom column
Description Text Description:Text(<Text>) Find bugs for which the "Description" field contains <Text>

*Only works in QA view

Detailed description Text Detaileddescription:Text(<Text>) Find bugs for which the "Detailed description" field contains <Text>
*Only works in QA view
Duration Days Duration= <Days> Find items for which the Duration column contains a value equal to <Days>
Earned value Hours Earnedvalue= <Hours> Find items for which the "Earned value" column contains <Item>
*Earned value is an additional module that can be activated for free
Estimated days Days Estimateddays= <Days> Find items for which the "Estimated ideal days" column contains a value equal to <Days>
Finish Time Finish= <Time> Find items for which the "Finish" column contains the date <Time>
*Only in Task scheduling projects
General condition - Hidden Boolean GeneralconditionHidden= true Find items marked as hidden
General condition - Assigned to me Boolean GeneralconditionAssignedtome= true Find items assigned to me
General condition - Deleted Boolean GeneralconditionDeleted= true Will match items that have been deleted
General condition - Epic Boolean GeneralconditionEpic= true Will match items that have been flagged as epics
General condition - Has sub items Boolean GeneralconditionHassubitems= true Will match items that have sub items, i.e., they are parents
General condition - Has users Boolean GeneralconditionHasusers= true Will match items that have users
General condition - Not assigned Boolean GeneralconditionNotassigned= true Find items that are not assigned
General condition - Ongoing assigned tasks Boolean  GeneralconditionOngoingassignedtasks= true Find all non-complete assigned tasks for which the current date is between the start and end dates of the task/sprint.
General condition - Ongoing work Boolean GeneralconditionOngoingwork= true Find all non-complete items (sprints, sub projects, tasks) for which the current date is between the start and end date of the task/sprint. In the case of releases this will return all non-complete releases occurring on or after the current date.
General condition - Out of office task Boolean GeneralconditionOutofofficetask= true Find items that have been set to out of office tasks
General condition - Release overdue Boolean GeneralconditionReleaseoverdue= true Find items that are overdue their respective release date
General condition - User story Boolean GeneralconditionUserstory= true Find items that have an attached user story
Hyperlink Text Hyperlink:Text(<Text>) Find items for which the "Hyperlink" column contains the text <Text>
ID Number ID= <Number> Find items for which the "ID" column contains a value equal to <Number>
*(The item ID can be seen by activating the ID column)
*(Items receive their ID from the order in which they were created)
Immediate parent sub projects Item Immediateparentsubproject:Item(<Item>) Find <Item>s immediate sub project(s)
Immediate sub items Item Immediatesubitems:Item(<Item>) Will match items that have a set of direct sub-items that matches the specified set of items
Item name / description / activity name Text Itemname:Text(<Text>) Find items for which the "Item name" column contains the text <Text>
Item type Item type Itemtype= <Item type> Find items of the type <Item type>
Last commented on Time Lastcommentedon= <Time> Find items for which the "Last commented on" column contains the date <Time>
Last read comments Time Lastreadcomments= <Time> Find items for which the last comment was read on the date <Time>
Last updated by Resource  LastUpdatedBy=Resource(<Resource>) Find items for which the 'Last updated by' column contains the resource <Resource> 
Last updated on Time Lastupdatedon= <Time> Find items for which the "Last updated on" column contains the date <Time>
Leaf sub items Item Leafsubitems:Item(<Item>) Will match items that have a set of leaf sub-items recursively (i.e., they do not have any children in their turn) that matches the specified set of items
Linked to item Item Linkedtoitem:Text(<Item>) Find items for which the "Linked to item" column contains the item <Item>
Originally created / reported by Resource Originallycreatedby= Resource(<Resource>) Find items for which the "Originally created by" column contains the resource <Resource>
Originally created on Time Originallycreatedon= <Time> Find items for which the "Originally created on" column contains the date <Time>
Parent sub projects Item Parentsubprojects:Item(<Item>) Will match items that have a parent set that matches the specified set of items
Pipeline and Kanban tasks - Can start now Boolean PipelineandKanbantasksCanstartnow= true Find items with a pipeline/kanaban that can be initiated

*(If the entire pipeline has been completed it will not show up through this find syntax)

Pipelines, kanban or workflows Workflow / pipeline and status PipelineKanbanorWorkflow= MyPipeline Find items that have an attached pipeline or workflow called MyPipeline
*MyPipeline is a pipeline
Points Points Points= <Points> Find items for which the "Points" column contains a value equal to <Points>
Predecessor Item Predecessor= <Item> Find items who have predecessor items that are called item <Item>
*Only in Task scheduling projects
Product backlog priority Number Productbacklogpriority= <Number> OR <Priority> Find items for which the "Product backlog priority" column contains a value equal to <Number>/<Priority>
Project Project Project= <Project> Find all projects called <Project>
*Useful when attempting to filter items in the portfolio view
Release tag Text Releasetag:<Text> Find items for which the "Release tag" column contains the item status <Text>
Risk Risk Risk= <Risk> Find items for which the "Risk" column contains the risk value <Risk>
Scheduled condition - Completed Boolean ScheduledconditionCompleted= true Find scheduled tasks that are currently completed
Scheduled condition - Critical path Boolean ScheduledconditionCriticalpath= true Find scheduled tasks that are currently on a critical path
Scheduled condition - In progress Boolean ScheduledconditionInprogress= true Find scheduled tasks that are currently in progress
Scheduled condition - Incomplete Boolean ScheduledconditionIncomplete = true Find scheduled tasks that are currently incomplete
Scheduled condition - Not started yet Boolean ScheduledconditionNotstartedyet= true Find scheduled tasks that have not started yet
Scheduled condition - Overdue Boolean ScheduledconditionOverdue= true Find scheduled tasks that are overdue
Severity Number Severity= <Number> OR <Severity> Find items for which the "Severity" column contains a value equal to <Number>/<Severity>
*(Only works in QA view)
Sprint priority Number Sprintpriority= <Number> OR <Priority> Find items for which the "Sprint priority" column contains a value equal to <Number>/<Priority>
Start Time Start= <Time> Find items for which the "Start" column contains the date <Time>
*Only in Task scheduling projects
Status completion Item status Statuscompletion= <Item status> Find items for which the "Status completion" column contains the item status <Item status>
Steps to reproduce Text Stepstoreproduce:Text(<Text>) Find bugs for which the "Steps to reproduce" field contains the text <Text>
*(Only works in QA view)
Sub items Item Subitems:Item(<Item>) Find items that have a sub item called <Item>
Sub project path Text Subprojectpath:Text(<Text>) Shows the sub project path for items containing the string <Text> within their name
Successor Item Successor= <Item> Finds items who have successor items that are called <Item>
*Only in Task scheduling projects
User name Resource Username:Resource(<Resource>) Find items for which the "User name" column contains the resource <Resource>
User story Text Userstory:Text(<Text>) Find items for which the "User story" column contains the text <Text>
Work remaining Hours Workremaining= <Hours> Find items for which the "Work remaining" column contains a value equal to <Hours>
Workflow status set by user Resource (Userthatchangedtoworkflowstatus<ProjectName><WorkflowName><Workflowstatus>:Resource("<Resource>")) Find items for which a specified user changed to a specified workflow status.
Workflow status set on date Time Timewhenworkflowstatus<ProjectName><WorkflowName><WorkflowStatus>wasset = <Time> Find items for which a specified workflow status was set on a specified date.

Text type

The following operators can be used on text:

Description Operator
Contains :
Contains case sensitive ::
Not contains !:
Not contains case sensitive !!:
Starts with =>
Starts with case sensitive ==>
Ends with = <
Ends with case sensitive == <
Equals to =
Equals to case sensitive ==
Not equals to !=
Not equals to case sensitive !!=
Greater than >
Greater than case sensitive >>
Less than <
Less than case sensitive <<
Greater than equal >=
Greater than equal case sensitive >>=
Less than equal <=
Less than equal case sensitive <<=

Example

Description= Code returns all items that have a description that exactly matches Code

Explicitly typing

Text(keyword to type)

Example:

Resources:Text("john") returns all items with a resources column containing the text john

Number types

The following operators can be used on a number:

Description Operator Synonyms
Equals to = ==   :   ::  =>  ==>  = <  == <
Not equals to != !!=   !:   !!:
Greater than > >>
Less than < <<
Greater than equal >= >>=
Less than equal <= <<=
Contains (needs explicit typing to text, see example below) :  

Example

DatabaseID= 1193 returns the item that has Database ID 1193

DatabaseID:"11" returns items that has a Database ID which contains number 11

Explicit typing

Number

Example:

Estimatedidealdays >= 5.30

returns all items that have estimated ideal days greater than or equal to 5.30

Boolean types

Boolean columns can either be of value true (represented by number 1) or false (represented by number 0).

The following operators are ideally used on columns typed as boolean:

Equals to =
Not equals to !=

Since the Boolean column returns a number, the operators for the Number type can also be used.

Example

ScheduledconditionInprogress= 1 returns all scheduled tasks that are currently in progress

Explicitly typing

true or false

Example:

ScheduledconditionOverdue= true

returns all scheduled tasks that are overdue

Hours and Days types

Hours and days are used on columns such as Duration and Work remaining and are specified by a d for days and h for hours in the end of a number.

If you write no letter after the number Hansoft will auto type to the default type for that column (for example Duration will auto type to days if you write Duration > 5).

Days and hours can be converted between each other so you can search for example Duration > 16h and it will automatically convert the duration expressed as days to hours. The conversion always assumes 8 hour days.

Operators for use with Hours or Days type

Since the Hours and Days are a number, the Number type operators apply.

Example

Duration > 5d returns all scheduled tasks that have a duration longer than 5 days

Explicit typing

Xd for days

Xh for hours

Example:

Work remaining <= 3.5d

returns all items that has work remaining less than or equal to three and a half days

Points type

The following operators can be used on points:

Since points are expressed as a number, the Number type operators apply.

Example

Points > 20 returns all items with more than 20 points

Explicit typing

Xpoints

Example of explicit typing

Points <= 10points returns all items with less than or equal to 10 points

Item type

The type item specifies one or several of any kind of item, task or bug in Hansoft.

Operators

Description Operator Synonyms
Any of items : ::
Any of items, some other > >>
Any of items, no other < >>
None of items !:  
None of items, some other !!:  
All of items, no other = ==
None of items or some other != !!=
All of items, some other >= >>=
At least all of items <= <<=
First is any of items => ==>
Non-first are any of items = < == <

Example

Committedto= "Email application - Prototype" returns all items committed to "Email application - Prototype"

Explicit typing

Item([ST, M, BI, AT, S, SP, B]"task name", ...)

ST is a Scheduled task

M is a Milestone

BI is a Backlog item

AT is a Agile task

S is a Sprint

SP is a Sub project

B is a Bug

Example of explicit typing

Linkedtotaskitembug : Item(BI"As an application user, I can reply to emails")

returns all items linked to backlog item (BI) "As an application user, I can reply to emails"

Linkedtotaskitembug= Item(BI"As an application user, I can reply to emails")

returns all items linked to ONLY backlog item (BI) "As an application user, I can reply to emails"

Linkedtotaskitembug : Item(BI"As an application user, I can reply to emails", BI"As an application user, I can forward emails")

returns all items linked to backlog item (BI) "As an application user, I can reply to emails" or backlog item (BI) "As an application user, I can forward emails"

Resources type

The type people specifies both individual users and user groups.

The following operators can be used on people:

Description Operator Synonyms
Any of selected : ::
Any of selected, some other > >>
Any of selected, no other < >>
None of selected !:  
None of selected, some other !!:  
All of selected, no other = ==
None of selected or some other != !!=
All of selected, some other >= >>=
At least all of selected <= <<=
First is any of selected => ==>
Non-first are any of selected = < == <

Example

assignedto = "Example Proj Manager"

returns all items that have only "Example Proj Manager" assigned

Explicit typing

R specifies individual resource and is written before the typed keyword

G specifies a resource group and is written before the typed keyword

resource([R or G]"keyword to type", [R or G]"keyword to type" etc.)

Example:

CCnewcommentsto:resource(G"Business Unit 3", R"Example Proj Manager")

returns all items that have a CC to a user in user group "Business Unit 3" or to the individual user "Example Proj Manager"

Using the currently logged in user as a resource type

loggedinresource

Example:

assignedto:Resource(loggedinresource)

Time type

The time type is used for all kinds of date columns such as Start and Last Commented on. All dates are specified in the ISO 8601 format YYYY-MM-DD.

The following operators can be used on dates and times:

Description Operator Synonyms
On date = ==   :   ::   =>   ==>   = <   == <
Not on date != !!=   !:   !!:
After date > >>
Before date < <<
On or after date >= >>=
On or before date <= <<=

Example

Start >= 2009-10-01 returns the scheduled tasks that have a start date on or after 1st of October 2009

Explicit typing

Time

Example:

Finish <= 2009-10-05

returns the scheduled tasks that have a finish date on or before 5th of October 2009

Syntax

There are three ways to specify a date.

Gregorian date and time YYYY-[MM[-DD]][:HH[:MM[:SS]]]
ISO week, day and time YYYY-['W'WW-[D]][:HH[:MM[:SS]]]
Offset from the current time Now[(+,-)X(Y,M,D,W,H,m,S)][:HH[:MM[:SS]]]

A date is recognized as such when an integer with a - after is found. You don't need to specify the whole date and time, but can stop at any precision (see below). You cannot include any white space within the date expression and thus need to write the whole date without any spaces.

When searching on dates or time the keyword now can be used (start >= now). Together with keyword now you can also add + or - and

Second s
Minute m
Hour h
Day d
Week w
Month M
Year y

Example of now syntax

Start > Now-3M

returns scheduled tasks with a start date greater than three months ago.

LastUpdatedOn > Now-2h AND LastUpdatedOn <= Now

returns all items, tasks or bugs updated the last 2 hours.

Precision

When specifying a date or time it will implicitly have a precision attached to it. The precision is determined by the amount of accuracy that the date is specified with.

The precision of the time/date determines the range of time that the search sees as a match. If you for example specify a specific hour (2008-08-09:19) all times that fall within that hour will be matched in the search.

Precision examples

Syntax Accuracy
2008- Year
2008-08 Month
2008-08-09 Day
2008-08-09:19 Hour
2008-08-09:19:30 Minute
2008-08-09:19:30:11 Second
2008-W5 Week
2008-W5-1 Day
2008-W5-1:09 Hour
2008-W5-1:09:30 Minute
2008-W5-1:09:30:11 Second
Now Second
Now+1y Day
Now+1.5y Day
Now+1M Day
Now+1.5M Day
Now+1w Day
Now+1.5w Hour
Now+1d Day
Now+1.5d Hour
Now+1h Hour
Now+1.5h Minute
Now+1m Minute
Now+1.5m Second
Now:09 Hour
Now:09:30 Minute
Now:09:30:11 Second

Ranges

You can also specify a time range when specifying time. This is done with the FromDateToDate keyword.

Example

Start = FromDateToDate(now-2y, now+1y)

returns scheduled tasks with a start date from two years ago to one year into the future.

Drop list types

The drop list types are

Pre-defined and custom drop lists all follow the same pattern with regards to how to find on them. Please note that the drop lists here are separate types.

The following operators are ideally used on drop list types:

Equals to =
Not equals to !=

Since the drop list types are represented as a number, the operators for the Number type can also be used. This can make sense when the drop lists have a specified order such as Severity. You could then search for all items that have a severity worse than or equal to B: Severity <= Severity(B)

Example

Confidence = "High confidence"

ItemStatus != Completed

Project = "Development project"

BugStatus = Assigned

MyCustomColumnDropList = "Drop list item A"

Pipeline = Cutscene

Workflow = "Sign off"

Explicit typing

Confidence(High, Medium or Low)

Risk(High, Medium or Low)

Priority(High, Medium or Low)

BacklogCategory("Requirement", "Enhancement", "Technology upgrade", "Bug (A)", "Bug (B)", "Bug (C)" or "Bug (D)")

Severity(A, B, C or D)

ItemStatus("Not done", "In progress", "Completed", "Blocked" or "To be deleted")

ItemType("Scheduled task", "Milestone", "Backlog item", "Agile task", "Sprint", "Sub project" or "Bug")

Project(Project name)

BugStatus([Project name], Bug status name)

ColumnDrop(Column Name, Custom drop list item name)

Pipeline([Project name], Pipeline name)

Workflow([Project name], Workflow name)

WorkflowStatus([Project name], Workflow name, Workflow status)

When searching in the To do list or Global find the [Project name] mentioned above is relevant, because in these circumstances you need to specify the project you want to specify for example a bug status from.

When explicitly typing drop list types some of the values are fuzzily matched. For example BacklogCategory("Bug (A)") is the same thing as BacklogCategory(BugA) or BacklogCategory("A Bug").

Example of explicit typing

Severity(A)

returns all bugs that has Severity A

Project("My game project")

returns all items in project "My game project"

Pipeline("Cutscene")

Pipeline("My project","Cutscene Generic")

WorkflowStatus("Sign off", "First sign off status")

WorkflowStatus("My project", "Sign off", "First sign off status")

Column type

It is possible to compare two different columns by using the column type.

Example

Workremaining > Column(Estimatedidealdays)

returns all items where the work remaining exceeds the estimated ideal days.