I have a calculation scenario where I would like to display text based on the evaluation of certain fields. There are five fields which are basically subtotal fields that contain a number
Field1 - Unsatisfactory
Field2 - Needs Improvement
Field3 - Meets
Field4 - Exceeds
Field5 - Far Exceeds
In a 6th text box, i need to be able to show a result based on the conditions below. The line starting with asterisks is what I am most interested about since I am able to evaluate based on a single value. All of the conditions below I need to put in the same text box if possible.
1. If Field1 has a value of 2 or higher, display "Unsatisfactory"
Concat(substring("Unsatisfactory", 1,(Field1 > 1) * String-Length("Unsatisfactory"))
2. If Field2 has a value of 3 or higher, display "Needs Improvement"
3. If Field2 has a value of 2 and Field1 has a value of 1, display "Needs Improvement"
***4. If Field4 has a value of 7 and Field3 has a value of 5, display "Exceeds" ( I thought I had it working, but it displays duplicate values if more than one condition is met)
concat(substring("Unsatisfactory", 1, (Field1 > 1) * string-length("Unsatisfactory")), substring("Needs Improvement", 1, ((Field1 = 1) + (Field2 = 2)) * string-length("Needs Improvement")), substring("Needs Improvement", 1, (Field2 > 2) * string-length("Needs Improvement")), substring("Exceeds", 1, ((Field4 > 6) + (Field5 > 4)) * string-length("Exceeds")))
5. If Field5 has a value of 8 and Field4 has a value of 4, display "Far Exceeds"
6. Display "Meets" in any other situation
Any help would be much appreciated!
Thanks,
Jason