Choices columns – manage in SharePoint by Admin, but use Text field for Power Apps
If you have read some of this site’s blogs, you may deduce that 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.
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
All that is need then is the Update of the Data Card (using a Combo Box called cbAnimals)
cbAnimals.Selected.Animalsto write back to a Text field.
As mentioned, maintenance of this does not require even SharePoint design privileges, just Contribute on the SharePoint list.
5 Comments
Power Apps development company
Wonderful blog & good post.Its really helpful for me,
kjrack
Genius – I am going to start using this everywhere.
Darren
Warren, using this method, what do I set the DefaultSelectedItems on cbAnimals to?
And is there a reason you chose your data structure in that list rather than a simple two column list where the first column has the name (Animal) and the second column has the Value?
What I’m suggesting results in repeating rows but should result in no delegation issues at all because I can test for Name = “Animal” which is delegable.
Warren Belz
The DefaultSelectedItems (assuming you are reading from a Text column) would be {Animals: ThisItem.YourColumnName}.
The structure is to simply have one list covering all of the drop-down choices in the App and the ability for someone to manage this without needing design privilages in either SharePoint or Power Apps.
Also adding here I avoid complex field types such as lookup and single choice columns due to the complexity and limitations they bring and use Single Line of Text wherever possible.
Darren Carlson
I have successfully implemented this and am slowly migrating all of my prior Choice columns decisions/designs to this method but I have a logic related question to managing the from an app and maybe I’ll figure out the answer to my question by figuring out how to ask the question correctly in writing.
Bear with me. I may answer my own question and you could leave it here for others to learn from.
The question is this:
If I’m adding a value to the list, how to I figure out which record to modify since every record has a value in record 1 but then may not in record 2 and I just answered my own question.
If I need to insert a new ‘color’ in your example, I need to grab all records that have values in any of the columns, sort by ID, and then pick the first record that does not have a value in the column I’m concerned with.
And if I was adding vegetable, I’d determine there is now record returned in the first filter with an empty vegetable column and i’d need to add a brand new record.
Thanks for reading.