Sharepoint Conditional Formatting IF Statements etc

Question

I am trying to make a risk register through Sharepoint lists with the view to have I guess a series of IF statements for the final risk rating. Eg, If Likelihood is "highly unlikely" and consequence is "insignificant" then the risk rating column returns the value of "Negligible" and so on.

If tried various IF statements, conditional formatting but im not skilled in coding tbh and nothign seems to work.

Thanks

Answer 1

Dear Andrew,

Column Formatting can be used to color the column or the item rows based on the condition.

To auto-populate the values on a column such as Risk Rating, you can use the calculated column.

Create a choice type column Likelihood with four choices Highly Unlikely, Unlikely, Likely and Highly Likely.

Create a calculated column such as Risk Rating at your side via clicking on +Add column>See all column types>name the column as Risk Rating>select the calculated column type and add the formula as follows.

=IF(Likelihood="Highly Unlikely","Negligible",IF(Likelihood="Unlikely","Low",IF(Likelihood="Likely","Medium",IF(Likelihood="Highly likely","High",""))))

.

Result:

.

If you want to auto-color the background of the Risk Rating column, you can use the code as follows on Risk Rating.

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json","elmType": "div","debugMode": true,"txtContent": "@currentField","style": {"background-color": "=if([$Risk_x0020_Rating] == 'Negligible', '#4ed400', if([$Risk_x0020_Rating] == 'Low', '#00d4b4', if([$Risk_x0020_Rating] == 'Medium', '#f02bd2', if([$Risk_x0020_Rating] == 'High', '#d40004', ''))))"}****}

Result:

.

Best Regards,

Sincerely

Cliff | Microsoft Community Moderator

Answer 2

Hey Thanks Cliff,

ITs nearly what i was looking for and i thank you so far,

This is what the list will look like,

so basically i need something like if likelihood= highly unlikely AND consequence = Insignificant then the "rating" will auto populate to Negligible

This is my risk table for reference

Answer 3

####Update####

I have figured out the syntax for it to work however now, I am running into the 1000 character limit and wont work. Is there a workaround for it?

Answer 4

Dear Andrew,

Thanks for your confirmation.

Based on the screenshots, you may need to create multiple IFs with AND which may cause some error.

As a workaround, the best way is to use PowerApps to customize the form and use the Cascading Lists feature in the form.

Working with Cascading Lists in SharePoint and Power Apps | Microsoft Learn

If you have no PowerApps, you can use Microsoft Forms to create a form for users to select Likelihood and Consequences and then trigger a flow to search the Rating value from an Excel table as follows and populate the Rating value to the SharePoint Online list automatically with a Power Automate flow.

.

You can create the Excel table as follows.

.

You can create the flow as follows.

.

Filter Array: @and(equals(item()?['Likelihood'], outputs('Get_response_details')?['body/****']), equals(item()?['Consequence'], outputs('Get_response_details')?['body/****']))(replace the Microsoft Forms response value with the values in your environment)

Compose: items('Apply_to_each')?['Likelihood']

Compose2: items('Apply_to_each')?['Consequence']

Compose3: items('Apply_to_each')?['Rating']

Result:

.

If you have PowerApps and need help on the PowerApps way, welcome to post back with your updates and we'll help you further.

Thanks for your time and effort.

Sincerely

Cliff | Microsoft Community Moderator