Follow

All columns, types and operators

This is a guide describing 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>

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 sprint

Item

Committedtosprint:Item(<Item>) Finds items for which the "Committed to sprint" 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 - Archived

Boolean

GeneralconditionArchived= true Find archived items

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

Synonyms

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

Committedtosprint= "Email application - Prototype" returns all items committed to sprint "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

·Confidence

·Severity

·Priority

·Item status

·Risk

·Backlog category

·Item type

·Project

·Bug status

·Custom column drop list

·Workflow

·Workflow Status

·Pipeline

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.

Was this article helpful?
0 out of 1 found this helpful
Have more questions? Submit a request

Comments