Create a calculated column in Share Point list using another column

[AJ_vizMan]

Hello Power platform community member,

hope you are doing well.

Need your help in a scenario with below details :

Scenario - I am creating PowerApps to collect feedback from HR community. There are certain columns in feedback form which contain feedback on multiple line basis. And i am concatenating the multiple column values in SharePoint list column (Multiple line of text column).

You can see, 'RepeatedSection' column contain 2 lines in a feedback starting with 0 and than another feedback with serial number 1. Each line has status value in it like for serial no. 0, status is 'In Progress' and for 1 it is 'Closed'

 Requirement : Need to create a calculated column to define status of feedback i.e. if any of line has status = 'In Progress' than feedback status should remain 'Open' else 'Closed'. So basically we need to get status value from each line and check it to ensure whether item should remain 'open' or 'Closed'

My Approach : I Tried to create 'Calculated' column using below formula -

IF(ISNUMBER(SEARCH("In Progress",[RepeatedSection])),"Open","Closed")

However, it throws an error saying data type of 'RepeatedSection' column is not supported.

Could you please help me here. @RandyHayes 

[RandyHayes]

@AJ_vizMan 

You're referring to setting up a calculated column in SharePoint.  This is more of a PowerApps forum...so, the error you see is correct.  The SharePoint calculated columns cannot use a multi-line text column in it.  

You could do this in PowerApps on a record by record basis, but it seems you need to do this at the list level.

I would reinvestigate your data structure and consider using a yes/no, integer or text column to represent the status instead of having it in free-form text.

[AJ_vizMan]

Thank you @RandyHayes  - I followed below approach -->
1. using 'Update Context' - stored value of Status column (it is only required for calculation) --
UpdateContext(
{
StatusCombined: Concat(
galRepeattingTableNewScreen.AllItems,
Concatenate(
drpStatusNewScreen.Selected.Result,
";"))});

2.  'StatusCombined' value is written in 'Single line text' column in share Point list

3. Created a calculated column in SP list with below formula :

=IF(LEN(StatusCombined)=0,"",IF(ISNUMBER(FIND("In Progress",StatusCombined,1)),"In Progress","Closed"))

It worked perfectly fine. Thank you