Description
GETITEMVALUES is a powerful function which is used to return a value of an attribute/calculated value that other items hold. The calculation is commonly used in conjunction with WHERE and IF functions, to create a more defined calculation. It is very similar to the GETITEMVALUE function, which will return a single item’s calculated value/attribute value.
Syntax
GETITEMVALUES(x, y, z)
Parameters
x = Item Identifier: The name (default) or internal/external ID of the RETURNING items you wish to find the value for. If the Type is not specified, name is the default property that will be used in searching for the returning items. With GETITEMVALUES, an array of items’ names/ID’s/values can be placed here by using square brackets ([]).
Y = Code Value: This is the attribute/calculated value code that you wish to return.
Z = Type (OPTIONAL): What property type does the Item Identifier map to, either name (default) or external/internal ID.
Type of Data Returned
GETITEMVALUES returns any data type that you specify in the calculated values dialog when creating a new calculation.
Example
Returning the cost attribute value of ITEM 1 and ITEM 6:
GETITEMVALUES([‘ITEM 1’, ‘ITEM 6] ‘cost’) – This will return the cost value that ITEM 1 and ITEM 6 hold to ALL ITEMS (including Items 1 and 6), in the format of “Cost 1, Cost 6”.
Being able to not only gain the values of several items but using attributes to indicate a roll-up feature by using “Parent/Child” style of nesting. Being able to calculate individual costs but then the cost for the parent item based upon its children’s values.
Item(name) | Cost | Parent | Total (calculated) |
ITEM1 |
|
| 201 |
ITEM2 | 100 | ITEM1 |
|
ITEM3 | 101 | ITEM1 |
|
ITEM4 |
|
| 205 |
ITEM5 | 102 | ITEM4 |
|
ITEM6 | 103 | ITEM4 |
|
All items will return the same value, including ITEM1.
GETITEMVALUES([‘ITEM2’, ‘ITEM3’], ‘Cost’) Will return [100,101]
item(name) | Cost | Parent | Result |
ITEM1 |
|
| [100,101] |
ITEM2 | 100 | ITEM1 | [100,101] |
ITEM3 | 101 | ITEM1 | [100,101] |
ITEM4 |
|
| [100,101] |
ITEM5 | 102 | ITEM4 | [100,101] |
ITEM6 | 103 | ITEM4 | [100,101] |
Using a WHERE find the cost of ‘Child’ items, identified via the Parent field, i.e. Total.
GETITEMVALUES(WHERE(Parent=={name}), ‘Cost’)
item(name) | Cost | Parent | Result |
ITEM1 |
|
| [100,101] |
ITEM2 | 100 | ITEM1 |
|
ITEM3 | 101 | ITEM1 |
|
ITEM4 |
|
| [102,103] |
ITEM5 | 102 | ITEM4 |
|
ITEM6 | 103 | ITEM4 |
|
Using a WHERE and SUM statement to find the cost of ‘child’ items, identified via the parent field, i.e. Total.
SUM(GETITEMVALUES(WHERE(Parent=={name}), ‘Cost’))
item(name) | Cost | Parent | Result |
ITEM1 |
|
| 201 |
ITEM2 | 100 | ITEM1 |
|
ITEM3 | 101 | ITEM1 |
|
ITEM4 |
|
| 205 |
ITEM5 | 102 | ITEM4 |
|
ITEM6 | 103 | ITEM4 |