Sharepoint - populating a field based on priority lookup of other fields

  • 9 March 2021
  • 1 reply
  • 7 views

Hello all,



 



Right now I have several status fields which I require users to fill in status of Green, Amber, or Red. There's a Status 1, Status 2, and Status 3 which they have to indicate the colour of. There's also an "Overall Status" which should indicate the color based on the status which is most behind (ie. if any of the 3 status is red, this will be red, or if one status is amber while others are green, this will be amber). 



 



I'm looking for a way to automatically populate the value in "Overall Status" based on the aforementioned logic, but don't know where to start. Please would anyone have any pointers for me? 



 



Much appreciated.


1 reply

@grixon I'm not the strongest Nintex user, but it has been my experience, that the Nintex forms "If" in-line function doesn't work with nested if statements, for example, If(x=1,0,If(x=2,1,3)). That's what is required for this.  If you don't need to display the Overall status on your form, but can get away with just having it displayed in a list view, you can accomplish this with a calculated field in SharePoint.  I have not been able to find a way to use a calculated field in Nintex forms, though.  And because it is a calculated field, which only updates when an item is saved, it will not appear on the SharePoint standard NEW or EDIT forms either, it will only display on the standard DISPLAY form, or in the list view.



The other alternative is to make your Overall Status a text field that is set to the calculated value by a workflow when the form is saved.  Workflows allow you to set multiple conditions, so workflow handles this type of requirement quite easily.  But, again, your form users won't see it updated in real time. 



Here is the formula that will work in the calculated field, and I have included some images as attachments.



=IF(OR(Status1="Red",Status2="Red",Status3="Red"),"Red",IF(OR(Status1="Yellow",Status2="Yellow",Status3="Yellow"),"Yellow",IF(AND(Status1="Green",Status2="Green",Status3="Green"),"Green")))



But I would be interested to know if anyone else has been able to get nested "If" inline function to work.



 

Reply