Skip to main content

Notifications

Choices columns - manage in SharePoint by Admin, but use Text field for Power Apps

Some of you who have read my posts will know I have an aversion to complex column types for data fields in Power Apps unless there is a good and necessary reason to use them. On the lesser end of this scale, Choice columns cannot be used to Sort by in a Delegable manner and still in general add a level of complexity that may not be needed if you are only using single choices.
You can of course "hard code" them in Power Apps Drop-down /Combo Box controls, but any changes require design privileges in Power Apps, not something you always want.

 

So how do you write back to the desirable Text column, while still allowing some management of the Choices without Developer involvement ?

I recently tested the process described below on a new field production app and it worked very well. This test example has a "master" SharePoint list called ChoiceStore as per the image below and you can see five different lists of choices of varying numbers. 

WarrenBelz_2-1662943729571.png

 

At App OnStart, I create a Global Record Variable gblChoices as below (the With() statement is only to avoid the Delegation warning) and set each field in the Variable to a sorted list of the relevant column where a value is present. You could also use a Collection here.

With(
 {wChoices: ChoiceStore},
 Set(
 gblChoices,
 {
 Animals: Sort(
 Filter(
 wChoices.Animals,
 !IsBlank(Animals)
 ).Animals,
 Animals
 ),
 Birds: Sort(
 Filter(
 wChoices.Birds,
 !IsBlank(Birds)
 ).Birds,
 Birds
 ),
 Fruit: Sort(
 Filter(
 wChoices.Fruit,
 !IsBlank(Fruit)
 ).Fruit,
 Fruit
 ),
 Vegetables: Sort(
 Filter(
 wChoices.Vegetables,
 !IsBlank(Vegetables)
 ).Vegetables,
 Vegetables
 ),
 Colours: Sort(
 Filter(
 wChoices.Colours,
 !IsBlank(Colours)
 ).Colours,
 Colours
 )
 }
 )
)

The Items of the Combo Box / Drop-down is then simply (example)<

gblChoices.Animals

 

WarrenBelz_1-1662943694586.png

All that is need then is the Update of the Data Card (using a Combo Box called cbAnimals)

cbAnimals.Selected.Animals

to write back to a Text field.

 

As mentioned, maintenance of this does not require even SharePoint design privileges, just Contribute on the SharePoint list.

Comments

*This post is locked for comments

  • WarrenBelz Profile Picture WarrenBelz 145,422
    Posted at
    Choices columns - manage in SharePoint by Admin, but use Text field for Power Apps

    For multiple choice fields yes - single choices are counter-productive as explained in the blog

  • KBeale Profile Picture KBeale 312
    Posted at
    Choices columns - manage in SharePoint by Admin, but use Text field for Power Apps

    I love this choice store approach, and the named formulas do work @sb_teal . Is there any reason that I couldn't use this concept, but just convert a text field to Approved Choices and use the column in the choice store list? @WarrenBelz  Thanks so much! 

  • WarrenBelz Profile Picture WarrenBelz 145,422
    Posted at
    Choices columns - manage in SharePoint by Admin, but use Text field for Power Apps

    @sb_teal ,

    Probably, but I have not tried it. You might test and report.

  • sb_teal Profile Picture sb_teal 128
    Posted at
    Choices columns - manage in SharePoint by Admin, but use Text field for Power Apps

    @WarrenBelz Could this be combined with the recently announced Named Formulas?  So, for example, instead of your set statements used in App.OnStart, in App.Formulas it might be something like

     

    Animals = Sort(
     Filter(
     ChoiceStore.Animals,
     !IsBlank(Animals)
     ).Animals,
     Animals
     )

     

  • WarrenBelz Profile Picture WarrenBelz 145,422
    Posted at
    Choices columns - manage in SharePoint by Admin, but use Text field for Power Apps

    This is only about the choices available in the drop-down - as long as your Default / DefaultSelectedItems and Data Card Update are correct, it will work like any other choice set.

  • Erol_Strat-Tek Profile Picture Erol_Strat-Tek
    Posted at
    Choices columns - manage in SharePoint by Admin, but use Text field for Power Apps

    Thank you for sharing this approach! Will the Code work as is If the Field was already populated with some Value, and later is opened with a Form in Edit Mode, where the User would like to change the previously Stored Value from say "Cat" to "Dog"?  Or is a bit of tweaking needed?