4

I have a SharePoint online list that will be populated with data from a Form (MS Forms) via Power Automate. I have some choice (single select, multi-select and yes/no) fields in the form, but before I build my list and import Flows I want to make sure I'm not getting myself into a dead end.

For example, I know that I can't reference a choice field in a lookup in SharePoint. So, if I want to do any lookups, I need the data to be imported as a single line of text, or create a calculated field in the background (which is a bit of a pain).

I also know that if I want the field to be editable once it's in the list, I won't be able to retain that structured data integrity if I use a text field instead of a choice field, so if users are going to be editing the data, it's important that it's a choice column in SharePoint.

Are there other "gotchas" that I should keep in mind, especially in terms of either conditional formatting (JSON) or being used as a trigger in a Power Automate flow?

Improve this question

1 Answer 1

Reset to default
6

You are correct that you can't use a choice field in a lookup (but you can reference a choice field in a calculated column which can be used in lookups). However, there are some gotchas when it comes to lookups and formatting (specifically when using additional columns or when referencing in forms). See this video for more details: Using field lookups with list formatting

Using Forms with Flow to SharePoint list with choice columns is no problem (though I'd probably make the list field be single line of text since the choices are in Forms, but totally agree that matching the list of options will be needed if users are editing items in the list directly).

There are no gotchas that come to mind for either formatting or Power Automate for the single choice column. These will be treated nearly identically to single line of text columns. There can be some annoyances when working in Power Apps (choice fields have to be accessed using a sub property of Value).

However, there are a few complications with multi-value choice fields. Flow will take care of most of these (unless using Http actions to talk to the SharePoint API directly). In formatting, you'll have to use the forEach pattern to work with the values individually or you can use the join operator to work with all values as text.

Improve this answer
1
  • 2
    That helps! It seems that the simplest solution in terms of formatting and integration is to use single-line text columns in SharePoint. Single-choice fields are almost as versatile, but may require some workarounds in Power Apps. Multi-choice fields are more difficult to work with, and should only be used if the value gained from editing them within SharePoint will offset that extra complexity. In my case, then, I'll trade off a little editing ease to gain simplicity in the back end with single-line text when importing my choice fields from Forms into SharePoint. Thanks for the clear help.
    – Darryl
    Commented Nov 8, 2021 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.