Workflow to copy SharePoint Calculated Column to Line of Text

[S_D_Andrews]

Hi all,

I have a SharePoint list that has two multiple choice columns.  As I want to keep this list under the 5k item count I have set a Power Automate to copy list items into an Excel list.  I can copy all fields within SharePoint list items into Excel apart from Multiple Choice fields.   Single lines of text, currency and all that move just fine.

They appear to cause a loop in the Flow which I can't resolve.  Does anyone know how I might convert multiple choice columns into a single line of text that I can then export to Excel via Flow.  

many thanks

Steven

[v-bacao-msft]

Hi @S_D_Andrews ,

Please refer to the following method to handle these two columns.

We need to create two Array variables to process them separately and store the value of the field.

You can insert the output of the Join action in the excel table, and you need to clear the array variables after each loop.

You can use the same method to process another field.

Image reference:

Spoiler

Hope this helps.

Best Regards,

[S_D_Andrews]

@v-bacao-msft 

Thanks a lot for this.  Looks a little complex at first glance but I'll spin it up with a test site and will let you know how I go.  I may have some more questions and I'll of course mark this up as the appropriate solution once I've understood and implemented it.

Thanks again
Steven

[S_D_Andrews]

One question thus far.  How do I clear down the array variables after each loop?  I'm not seeing anything that'll do the job in the variable options or the control options?

[v-bacao-msft]

set the array variable to an empty array [].

[S_D_Andrews]

@v-bacao-msft 

Ok, so, I've given this a go on a test site / Flow and have failed to get it working.  Any insight  that can be provided would be appreciated.   

So, first some information on my test set-up. I'm using a Modern SharePoint Online form that has the following fields: -

• Title (mandatory single line of text)
• Pick your colour (choice field: allows multiple options to be selected)
• Pick a number (choice field: allows multiple options to be selected) Ready to Archive (Choice field: single choice allowed)

I have a spreadsheet with a table in it that, upon the Ready to Archive field being set to Yes, will receive from Flow any item from SharePoint before it's deleted.   It's the workflow that I'm having problems with.  I've tried to copy the suggested solution above but I've likely got an error here that I can't quite see or work out.  Details on my error will be provided below the screenshots of my workflow configuration. 

The error that I get occurs on the application of the "ready to archive" field and reports "ActionFailed. An action failed. No dependent actions succeeded"

Any ideas?

Many thanks
Steven

[v-bacao-msft]

Hi @S_D_Andrews ,

First of all, if you want to use Choice column as a filter to filter items, you need to use Choice value (Ready to Archive Value).

In addition, although the error message appears in the Condition, in fact, the execution of the action in the corresponding branch has an error. Please check if there is an action that fails in the If yes branch. You can try to solve this error based on the error message or show them to us.

Best Regards,