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.
y = 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?”