Microsoft Forms Likert Responses to SharePoint Columns

[tictag]

In a Microsoft Form I need to choose from many attributes ('Statements') to assign to a limited number of individual people ('Choices'), for example 'Shoe Size of Person 1', 'Shoe Size of Person 2' etc. For this I am using a Likert control. Note: Simple Text/Number fields are not an option because of ... reasons.

My flow triggers on form submission and transfers form data to a SharePoint list (basic template), nothing special.

Transferring the choice response to a Likert statement to a SharePoint columns is easy enough, but I need this data to be 'flipped' - I need the chosen attribute ('Statement') to be displayed against the person ('Choice'), the exact opposite of the default behaviour. The UID of the statement is known, together with the choice made in the Get response details output.

It sounds simple enough but I've been hemorrhaging brain matter on this now for nearly a week! 🤯 I am in need of a bigger brain! 🧠

My guess at a solution would be to use JSON data mapping techniques (e.g. variables, arrays, compose etc) to store the correct data inside a variable, then use that variable to populate the SharePoint column in the Create item step.

In theory there should be only one attribute ('Statement') chosen for each person ('Choice'), for example:  "The Shoe Size of Person 1 is UK12", "The Shoe Size of Person 2 is UK6" etc. As I cannot technically restrict this, any advice on exception handling would be useful.

Any help / guidance would be warmly appreciated.

David.

[v-xiaochen-msft · Accepted solution]

Hi @tictag ,

You could create a list to store the relationship between the statement field and its value.

I did a sample for you.

[
@{split(slice(string(outputs('Get_response_details')?['body']),0,indexOf(string(outputs('Get_response_details')?['body']),'Person 1')),'"')?[add(length(split(slice(string(outputs('Get_response_details')?['body']),0,indexOf(string(outputs('Get_response_details')?['body']),'Person 1')),'"')),-3)]},
@{split(slice(string(outputs('Get_response_details')?['body']),0,indexOf(string(outputs('Get_response_details')?['body']),'Person 2')),'"')?[add(length(split(slice(string(outputs('Get_response_details')?['body']),0,indexOf(string(outputs('Get_response_details')?['body']),'Person 2')),'"')),-3)]},
@{split(slice(string(outputs('Get_response_details')?['body']),0,indexOf(string(outputs('Get_response_details')?['body']),'Person 3')),'"')?[add(length(split(slice(string(outputs('Get_response_details')?['body']),0,indexOf(string(outputs('Get_response_details')?['body']),'Person 3')),'"')),-3)]},
@{split(slice(string(outputs('Get_response_details')?['body']),0,indexOf(string(outputs('Get_response_details')?['body']),'Person 4')),'"')?[add(length(split(slice(string(outputs('Get_response_details')?['body']),0,indexOf(string(outputs('Get_response_details')?['body']),'Person 4')),'"')),-3)]},
@{split(slice(string(outputs('Get_response_details')?['body']),0,indexOf(string(outputs('Get_response_details')?['body']),'Person 5')),'"')?[add(length(split(slice(string(outputs('Get_response_details')?['body']),0,indexOf(string(outputs('Get_response_details')?['body']),'Person 5')),'"')),-3)]}
]

@{outputs('Get_items')?['body/value']?[0]?['Test']}

Result:

Best Regards,

Wearsky