I am try to populate/calculate a text field in Survey123 based on the values of two other fields collected as per the matrix in the image below. For Example: The Surface Hazard Rating is M and the Near Surface Hazard Rating is H, Therefore the Adjusted Surface Hazard Rating will be H. How can I populate/calculate the Adjusted Surface Hazard Rating based on the values of the other two recorded fields?
Assigning a numeric value to the letters and performing a calculation doesn't fit in this case, due to teh way the matrix values are determined
Many Thanks
Chris
Solved! Go to Solution.
Hi Chris,
There doesn't appear to be a simple formula relating the result based on the input; however, you can construct a series of nested if() statements to emulate the behavior:
if(${surf} = "E", "E", if(${surf} = "VH", if(${near} = "L" or ${near} = "M", "VH", "E"), if(${surf} = "H", if(${near} = "L", "H", if(${near} = "E", "E", "VH")), if(${surf} = "M", if(${near} = "L", "M", ${near}), if(${near} = "E", "VH", if(${near} = "VH", "H", if(${near} = "H", "M", "L")))))))
This was ordered by the surface hazard rating, breaking out by that value (E to L):
if(${surf} = "E", "E",
if(${surf} = "VH", if(${near} = "L" or ${near} = "M", "VH", "E"),
if(${surf} = "H", if(${near} = "L", "H", if(${near} = "E", "E", "VH")),
if(${surf} = "M", if(${near} = "L", "M", ${near}),
if(${near} = "E", "VH", if(${near} = "VH", "H", if(${near} = "H", "M", "L")))
))))
Hi Chris,
There doesn't appear to be a simple formula relating the result based on the input; however, you can construct a series of nested if() statements to emulate the behavior:
if(${surf} = "E", "E", if(${surf} = "VH", if(${near} = "L" or ${near} = "M", "VH", "E"), if(${surf} = "H", if(${near} = "L", "H", if(${near} = "E", "E", "VH")), if(${surf} = "M", if(${near} = "L", "M", ${near}), if(${near} = "E", "VH", if(${near} = "VH", "H", if(${near} = "H", "M", "L")))))))
This was ordered by the surface hazard rating, breaking out by that value (E to L):
if(${surf} = "E", "E",
if(${surf} = "VH", if(${near} = "L" or ${near} = "M", "VH", "E"),
if(${surf} = "H", if(${near} = "L", "H", if(${near} = "E", "E", "VH")),
if(${surf} = "M", if(${near} = "L", "M", ${near}),
if(${near} = "E", "VH", if(${near} = "VH", "H", if(${near} = "H", "M", "L")))
))))
this would go in the calculate field correct? how much there is limited docs for the calculate field? I didnt even know I could use if statements in there.
Hi Joe,
Yes, calculate fields can use if() and any other functions - we don't call it out because it works like the other questions in that respect
Excellent!
Thank you so much James, this is just what I was after!!