Forum Discussion

Akshadaks's avatar
Akshadaks
Copper Contributor
Jul 18, 2023

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

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

developer
Document Library
Lists
Microsoft Lists
SharePoint Online
SharePoint Server
  • SvenSieverding's avatar
    SvenSieverding
    Jul 19, 2023

    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



  • SvenSieverding's avatar
    SvenSieverding
    Bronze Contributor
    Jul 19, 2023

    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



    • Akshadaks's avatar
      Akshadaks
      Copper Contributor
      Jul 21, 2023

      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

       

       

      • Akshadaks's avatar
        Akshadaks
        Copper Contributor
        Jul 21, 2023
        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.

Resources