Sharepoint column value (status) based on other multiple column values

[Akshadaks]

Dear Team,

I have a very basic knowledge of SharePoint and therefore requesting for help for the below scenario.

I want to create a column on a sharepoint list to show the status (Approved, Rejected) based on the values from 11 other columns that are (Yes/No).

If all 11 column values are "Yes" then status to show result "Approved" and if any value in those 11 columns in "No" then status to show result "Rejected"

Please let me know how I can achieve the above with possible syntax.

Request for assistance.

Regards

[#2 · Accepted solution]

Hi @Akshadaks,

you can use a calculated column for that.

In this example I have a list with three Yes/No columns: YNField1, YNField2 and YNField3

Now press "+Add Column", select "See all column types" and press "Next"

Name your new column "Status" and select "Calculated (calculation based on other columns)"

Now enter this formula into the Formula field and press the "OK" Button on the lower left side.

=IF(
	AND(
		[YNField1]=TRUE,
		[YNField2]=TRUE,
		[YNField3]=TRUE
		),
	"Approved",
	"Rejected"
	)

Now your list should look like this

The new status column will display "Rejected" unless all of the "YNFields" are true.

Best Regards,
Sven

[#3]

Hi @SvenSieverding ,

Thanks for taking time and replying to the issue.

I will give it a try with your method which seems way better than what I did already. 🙂

I used the below code in the Format Column section (screenshot below). 

"txtContent": "=if([$Column1] == 'No', || [$Column2] == 'No', || [$Column3] == 'No', || [$Column4] == 'No', || [$Column5] == 'No', || [$Column6] == 'No', || [$Column7] == 'No', || [$Column8] == 'No', || [$Column9] == 'No', || [$Column10] == 'No', || [$Column11] == 'No', || [$Column12] == 'No', || [$Column13] == 'No', || [$Column14] == 'No','Rework', 'Approved')"

"txtContent": "=if([$Column1] == 'No', || [$Column2] == 'No', || [$Column3] == 'No', || [$Column4] == 'No', || [$Column5] == 'No', || [$Column6] == 'No', || [$Column7] == 'No', || [$Column8] == 'No', || [$Column9] == 'No', || [$Column10] == 'No', || [$Column11] == 'No', || [$Column12] == 'No', || [$Column13] == 'No', || [$Column14] == 'No','Rework', 'Approved')"

However, because of this I was not able to populate the result for the new entry into and email (below screenshot for reference). As the container is applied by Power Automate automatically to entire records. Not sure why, is it because the "Result" column is created in Sharepoint and other columns are populated through Microsoft Forms?

Not sure if I explained it clearly but let me know if it can be achieved some how.

What I want is basically, based on the 14 (Yes/No) columns a "Result" column to show particular status and then trigger an email (only for the new/modified entry) including "Result" field to be sent to the user.

Your help and support is greatly appreciate.

Regards

[#4]

Also with the code that I used, I am only able to use 2 types of statuses, but wondering if more than 2 status (results) could be achieved based on different combinations of selects.

e.g.
When
Column1 = Yes
Column2 = No
Column3 = Yes
Column4 = Yes
Status (Result) = Approved (Conditionally)

AND
When
Column1 = Yes
Column2 = No
Column3 = Yes
Column4 = No
Status (Result) = Rewrok required

Hope that clarifies.

[#5]

Hi @SvenSieverding,

I tried your method and its giving the below error.

Regards