SOLVED

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

Copper Contributor

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

4 Replies
best response confirmed by Juan Carlos González Martín (MVP)
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

SvenSieverding_0-1689745995107.png

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

SvenSieverding_1-1689746040621.png

 

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

SvenSieverding_2-1689746090638.png

 


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"
	)

SvenSieverding_3-1689746239170.png


Now your list should look like this

SvenSieverding_4-1689746306215.png

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

Best Regards,
Sven



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')"

 

Akshadaks_0-1689902495942.png

 

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?

 

Akshadaks_1-1689902665665.png

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

 

 

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.

Hi @SvenSieverding,

 

I tried your method and its giving the below error.

 

Akshadaks_0-1689904358267.png

 

Regards

1 best response

Accepted Solutions
best response confirmed by Juan Carlos González Martín (MVP)
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

SvenSieverding_0-1689745995107.png

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

SvenSieverding_1-1689746040621.png

 

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

SvenSieverding_2-1689746090638.png

 


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"
	)

SvenSieverding_3-1689746239170.png


Now your list should look like this

SvenSieverding_4-1689746306215.png

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

Best Regards,
Sven



View solution in original post