Calc that can return multiple values into a text field
I have a calculation question. On my Devices application, I’m trying to use a calculation that captures the values of the cross-referenced Applications and writes them to a calculated text field to then be used as a helper field that can be referenced by the Vulnerability Scan Results application.
So on Devices, there is a cross-referenced field named “Applications” which cross-references applications in the Applications application.
Also on Devices, I created a text field called Application Text Helper and then configured it as a calculated field with the calculation below:
This formula successfully writes the value from the Applications cross-reference field to this field as long as there is only one value in the cross-reference. If there is more than one value in the Applications field cross-reference, then the calculation returns an error with the code below:
A parameter "!3!A1:A2" expected to be a single value was multiple values. Parameter name: !3!A1:A2
I would like to be able to get multiple values into this Application Text Helper field.
Can someone please let me know if this is possible, and if so, how?
- Community Thread
- Forum Thread
- RSA Archer
- rsa archer 6.2
- RSA Archer Suite
Chad from a calculation perspective there's no way to return multiple selections other than a values list (using COMBINESELECTIONS() function.
You'd probably be able to do it with a data feed with a custom xslt or a custom object using the REST APIs to grab the Application Name
This is Enhancement Request ARCHER-21924 "Calculation function that combines text field data across multiple records listed in an XRef field."
There is already a number of customers that have requested it. We've moved our Enhancement Request system to the ideas exchange. https://community.rsa.com/community/products/archer-grc/ideas
If the idea hasn't already been posted, it'd be a good idea to post it over there. If it gets enough upvotes, it will be considered as a feature in a future release.
If you post it over there, be sure to include Enhancement Request ARCHER-21924 in the message for reference.
Thanks for the feedback. Ultimately, I am looking to establish a link between the Applications application and Vulnerability Scan Results, but I want to do it through Devices since that is where both the Applications and Vulnerability Scan Results applications have existing mappings. I wanted to get the Application onto a Vulnerability Scan Results record natively because I don't like adding additional relationships to my reports because they get messy due to the hierarchical nature of the data. Any thoughts on the simplest, and most logical way to do this?
Edit: Just re-read your question and realised that you're referring to a text field in a cross-reference, which means the workaround below wouldn't apply, unless the Application Name were a values list field, which doesn't make sense - so ignore.
I've used a workaround successfully in formulas that don't take in multiple values.
1) Assigning numeric values to the values in your values list field.
2) Creating a numeric helper field that sums the values of your values list field.
Instead of passing the MVL field directly to the formula, I pass the numeric value of the helper field, and then in the formula, I use nested IFs to reset the multiple values.
Values List Field:
- Value A - 1
- Value B - 10
- Value C - 100
If Value B, and C were set, the Helper Field would sum the values to be 110.
Then in the Calculated Field to reset the multiple values, it would be something like:
IF ([Helper] = 1, VALUEOF ("A"),
IF ([Helper] = 10, VALUEOF ("B"),
IF ([Helper] = 100, VALUEOF ("C"),
IF ([Helper] = 11, VALUEOF ("A","B"),
IF ([Helper] = 110, VALUEOF ("B","C"),
IF ([Helper] = 101, VALUEOF ("A","C"),
IF ([Helper] = 111, VALUEOF ("A","B","C"),
This workaround is only feasible for a small values list, otherwise the number of combinations gets unwieldy.