Note: This function can only be used for ITEMS and must be created in the items tab.


The WHERE function is designed to find items that meet specific conditions. The statement returns a collection of items’ names or ID’s by evaluating a specified ‘condition’ for each item in the story. Items where the condition are TRUE are included. If there are no items meeting the condition, an empty set ([]) is returned. Use curly brackets to provide a value that is evaluated for each item before the condition is tested


Syntax


WHERE(x, y*)


Parameters


x = A Boolean (TRUE/FALSE) condition that must be evaluated for each item, with any item that meets “TRUE” to be included in the resulting set.


= Type, either a name/external ID/internal ID of the returning items. If no type is set or incorrect, the name will be returned by default.


* OPTIONAL PARAMETER


Data Type Returned


The WHERE statement will return an array of text values, either the names/external ID’s/Internal ID’s (depending on type set in the function) of the items that meet the function’s condition.


Examples


For all the examples, assume the following data exists:


External ID

Name

Category

Parent

Cost

1001

A

Internal

 

2

1002

B

Internal

A

3

1003

C

Internal

A

3

1004

D

External

A

4

1005

E

External

D

3

1006

F

External

D

6

 

Using the WHERE function to find items that are in a specific category


WHERE(category == “Internal”) will return [A,B,C].


Using the WHERE function to find the external ID’s of items that are in a category


WHERE(category == “External”, ‘externalid’) will return [1004,1005,1006].


Implementing the WHERE function to find items that have a cost greater than or equal to 4


WHERE(Cost >= 4) will return items [D,F].


Using the WHERE function to create a “Child” calculated value using the “Parent” attribute in the above table. There will be two examples to show the requirement for the curly brackets.


WHERE(Parent == name) and WHERE(Parent == {name})


Name

Parent

WHERE(Parent == name)

WHERE(Parent == {name})

A

 

[] (empty)

[B,C,D]

B

A

[] (empty)

 

C

A

[] (empty)

 

D

A

[] (empty)

[E,F]

E

D

[] (empty)

 

F

D

[] (empty)

 

 

Curly brackets are required in the WHERE function often, as the curly brackets denotes to evaluate that section of the function for each item before the formula is evaluated for all items.


In the example above, the WHERE(Parent == name) asks for each item “Is this item’s Parent attribute equal to this item’s Name attribute?”  A null value is returned because this is not true for any item. 


The WHERE(Parent == {name}) function, however, asks for each item “Do any other items have a Parent attribute that is equal to this item’s Name attribute?”