A calculated attribute can be helpful when an object has references to multiple products. The scenarios described below are examples of how calculated attributes could be used.
- Get List of References
- Get Value from Referenced Product
- Get Metadata Value Located on the Link from Product-to-Product
- Get Referenced By Using Reference Owner
- Get Reference Type ID
- Get Reference Source or Target Product Information on the Link
Get List of References
Calculated attributes could be used to get a list of references, and display them on the Product tab. For example, the Classroom Type Projectors product is linked to two other products: Office Projectors and Portable Projectors. A calculated attribute on Classroom Type Projectors will list the names of the referenced products.
Value Template
The formula for this calculated attribute could look like the formula below:
{i := iterate(iterate(references("product", "Alternative"), 'referencetarget()'), 'stepname()'),
x := list(i, ", ")
}
concatenate(x)
Notes
To break this formula down, the first ITERATE() function is used to pull information that is not on the current product. The second ITERATE() function (shown as bold above) returns a list of 'labels' for all of the references that Classroom Type Projectors has made to other products (i.e., reference targets) with the reference Type ID of 'Alternative'.
Because this list of labels is not needed, having the first ITERATE() function surround the second ITERATE() function, makes it so that the names of the referenced products are retrieved from the labels:
iterate(iterate(references("product", "Alternative"), 'referencetarget()'), 'stepname()')
Results
The result of the calculated attribute displays the name of the references on the object.
Get Value From a Referenced Product
Using this formula for a calculated attribute allows a user to look to a reference on an object, get a value that is on the reference, and display it on the object. To expand on this, get an attribute value from the target products, instead of the name of the target product. For example, the target products have a 'PowerCordLength' attribute:
Value Template
A calculated attribute with the following formula would allow the product Classroom Type Projectors to display the lengths of the power cords of the referenced products.
{i := iterate(iterate(references("product", "Alternative"), 'referencetarget()'), 'value("PowerCordLength")')
}
concatenate("Cord Lengths = ",list2multivalue(i))
Results
This is much like the prior example, but the LIST2MULTIVALUE() function is used since it automatically places a STEP tag <multisep/> between values. Based on the configuration of the tag, it is rendered as a slash in the value.
Get Metadata Value Located on the Link From Product-to-Product
Additionally, metadata values on references can also be returned. For example, the product-to-product 'Alternative' reference has an 'EquivalenceRating' attribute:
Value Template
A calculated attribute with the following formula will display the metadata on the original product.
{j := iterate(references("product", "Alternative"),'value("EquivalenceRating")'),
z := list2multivalue(j),
z := substitute(z, "<multisep/>", "\n ")
}
concatenate(z)
Results
The default separator used by the LIST2MULTIVALUE() function is converted to a carriage return, so that the workbench displays a new line for each entry within the same value field.
Get Referenced By Using Reference Owner
A product is being referenced by two other products. In addition to the name of the products being referenced, data from those products is also needed. For example, the last time the 'Alternative' referenced products were edited.
Value Template
The following calculated attribute formula could be written:
{refnames := iterate(iterate(referencesto("product", "Alternative"), 'referenceowner()'), 'stepname()'),
editdates := iterate(iterate(referencesto("product", "Alternative"), 'referenceowner()'), 'revisioneditdate()'),
combo := iterate(refnames, 'concatenate(item, " Last edited on ", listitem(editdates,index))'),
fin := substitute(list2multivalue(combo), "<lt/>multisep/<gt/>", "")
}
concatenate(fin)
Notes
This formula uses the following elements:
- List 1 'refnames' uses the REFERENCESTO() function in conjunction with the REFERENCEOWNER() function to get data from the referencing product, in this example it gets the STEPNAME().
- List 2 'editdates' gets a list of the last edited metadata on the referencing products.
- List 3 'combo' iterates through List 1 'refnames' and then concatenates the 'current' value indicated by the field 'item' (i.e., the referencing product’s name), with the corresponding list item number in the List 2 'editdates'.
- The function LISTITEM(editdates,index) uses List 2 'editdates' and gets the entry indicated by the number in the index field. So if you are on item 1 in List 1 'refnames', it gets item 1 value in List 2 'editdates'. If you are on item 2, it gets item 2 value in List 2 'editdates', and so on.
Important: When using a calculated attribute to 'marry' information from one list to the corresponding information in another list, the number of entries in each list MUST be identical.
Results
The results display the referenced product and the corresponding last edit date.
Get Reference Type ID
In this example, a product is being referenced by other products. A calculated attribute can display the reference types being used. For example, the product Classroom Type Projectors has four references to other products: two with the reference type of Alternative and two with the reference type of Spare Part.
Value Template
The following calculated attribute formula could be written:
{refids := iterate(references("product"), 'referencetypeid()'),
refids := list(unique(refids), ", ")
}
concatenate(refids)
Results
The results display each of the reference type IDs.
Although accurate, the display could be more informative with some modifications. You could filter the list so that each of the names and/or IDs of the referenced products together with the reference type displays.
Value Template
Similar to the above example of referencing product names and their edit dates, the formula could be written:
{refnames := iterate(iterate(references("product"), 'referencetarget()'), 'stepname()'),
reftypes := iterate(references("product", ""), 'referencetypeid()'),
combo := iterate(refnames, 'concatenate("Product ", item, " is being referenced with the Reference Type of ", listitem(reftypes,index))'),
fin := substitute(list2multivalue(combo), "<multisep/>", "")
}
concatenate(fin)
Notes
Again, three lists are made: a list of the names of the referenced products, a corresponding list of reference types, and a list that combines the corresponding values in list one and two. Finally, added text strings clarify the results.
Results
The results display each product being referenced, as well as the reference type ID.
Get Reference Source or Target Product Information on the Link
The calculated attribute can also be used to show metadata on the References link. A user could use it to show the Source product and the Target product for the link.
Value Template
iterate(referenceowner(),'stepid()') // to get the Source product ID iterate(referencetarget(),'stepname()') // to get the Target product name
Results
When configured properly, the metadata calculated attributes appear like below.
