Managing a SharePoint MultiChoice Field in a PowerApps List Form

Wow. This was meant to be easy. It wasn’t. Well, the solution is easy but getting to it was rather time-consuming.

Background

I have a SharePoint list with a choice column called Administration Type. Not all our choice values are always valid choices to be selected. Some of them expire over time (such as a Project ID or a Programme). In these cases we don’t want users to be able to select expired values for new items, but we do need them to be able to retain their expired values if it is edited later on.

We would like to be able to set Effective From and Effective To dates on our metadata values so we know when they are “Active”.

We also want to use the chosen items in search refiners for a good drill down User Interface.

We also wanted to make the maintenance of our lookup values easy for business users and them editing list settings

Possible Approaches

Lets look at the common approaches:

A Choice Field — Won’t work, because ALL choices are always available to be selected. Users don’t know what is active and what isn’t. Also, this only allows the selection of one choice. And this is hard for business users or workflow to keep up to date.

Multiple Choice Field — Won’t work for mostly the same reason as the choice Field. If you are wondering then a MultiChoice Field is a completely different field type than a choice field with different options. Multiple Choice is not simply a setting of a Choice Field.

Managed metadata column — Won’t work. Whilst we can disable expired Terms this won’t allow us to be able to select them for content produced at the time it was valid. Managed Metadata also has some remaining incompatibilities that are frustrating. I love Managed Metadata, but not right in this scenario. And this is hard for business users or workflow to keep up to date.

Lookup List — Won’t work. This will always show all options including expired ones. In fact, there is a way to use a calculated field to show a filtered set of the items but this won’t allow us to show “All current items, including ones already in use on this item”.

Single / Multi Line of Text — Won’t work. We could store our multiple choices in a Single Line of Text or a Multiple lines of text field with a separator, we could even use Custom Field Formatters to render them as separate items in the list views. BUT we can’t use them and break them break out for search refiners. Damn. This one comes close.

The Approach I took

So, all the standard SharePoint ways of doing metadata don’t allow for this specific set of requirements. Its time to get creative. What I propose to do is to use a SharePoint list to hold our lookup values. Then use a Power Apps SharePoint List Form to make changes to the Edit Properties experience and then have that write back to a MultiChoice Field. We won’t put the choice values in the MultiChoice Field, but leave it blank and allow Fill In options so we can really put in whatever we want.

A list of lookup values

First. I want a SharePoint List of lookup values. I will have multiple content types for the different lookup values that I want to store.

Metadata Field Settings

This is the actual field that will contain our metadata values in our Document Library. Note that there are no Choice options here (other than Select…)

Now, we just need to connect the two together.

The Power App List Form application

This section outlines the Power App that we will use to give the use the choices that are relevant and then how to write them back to SharePoint. Firstly, to create the Power Apps list forms you click this button on a Document Library:

This will fire up Power Apps and automatically connect your app to your SharePoint list using a component called SharePointIntegration. It will also build your basic form to start.

Step 0: Connections

General is our Document Library and Lookup Values is our SharePoint list of lookup values.

We also have two data structures for our solution to work. We create a Table variable for our lookup item values: dsAdministrationTypes and then a collection to store the choices that the user has selected: AdminTypesUpdate.

Step 1: App OnStart

In this stage we need to setup the Variables and Collections in the Power App that we will use for display in our ComboBox and also for us to send back to SharePoint to update the column value.

First we will filter our Lookup Values list and just get the items that match a particular Content Type ID. I would use name here, but that always seems to be blank. This structure is directly usable in our Combo box so we don’t need to change it in any way.

Set(dsAdministrationTypes, 
    Filter('Lookup Values', 
        "0x0100083E0748DBE3D943BB86D02DB0669D2F09" in 'Content type'.Id
    )
);

Next we need to load up our AdminTypesUpdate collection with the current values of the list item / Document that we are going to be editing. We call Clear() to reset / initialise the collection. Then, we do a few things. This is slightly over complicated because of my requirements for Active and Archive lookup values.

 Clear(AdminTypesUpdate);

ForAll(
    Filter(
        Filter(dsAdministrationTypes,
            Or(
                Title in SharePointIntegration.Selected.'Administration Type'.Value,
                And(
                    'Effective From'<=Today(),
                    Or('Effective To'>=Today(), IsBlank('Effective To'))
                )
            )
        ), Title in SharePointIntegration.Selected.'Administration Type'.Value
    ),
    Collect(AdminTypesUpdate,
        {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Id: CountRows(AdminTypesUpdate),
            Value: Title
        }
    )
); 

Lets break this down a bit.

Our first Filter() will select all the lookup items where the value is in the Administration Type field of the selected item that we are editing OR is an active lookup item, i.e. Effective From is less than or equal to Today and Effective To is greater than today or Is Blank. This is all the lookup values the item COULD be. We could have loaded this into a collection to reuse elsewhere, but I didn’t.

 Clear(AdminTypesUpdate);

ForAll(
    Filter(
        Filter(dsAdministrationTypes,
            Or(
                Title in SharePointIntegration.Selected.'Administration Type'.Value,
                And(
                    'Effective From'<=Today(),
                    Or('Effective To'>=Today(), IsBlank('Effective To'))
                )
            )
        ), Title in SharePointIntegration.Selected.'Administration Type'.Value
    ),
    Collect(AdminTypesUpdate,
        {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Id: CountRows(AdminTypesUpdate),
            Value: Title
        }
    )
); 

The second Filter() then chooses all those items from the first filter that are currently selected on the item we are editing. We might be able to loose the first filter, but this might help a situation where we have multiple items with the same time, but different Effective From and Effective To dates (duplicates), but I think this solution might still be susceptible to some issues there.

 Clear(AdminTypesUpdate);

ForAll(
    Filter(
        Filter(dsAdministrationTypes,
            Or(
                Title in SharePointIntegration.Selected.'Administration Type'.Value,
                And(
                    'Effective From'<=Today(),
                    Or('Effective To'>=Today(), IsBlank('Effective To'))
                )
            )
        ), Title in SharePointIntegration.Selected.'Administration Type'.Value
    ),
    Collect(AdminTypesUpdate,
        {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Id: CountRows(AdminTypesUpdate),
            Value: Title
        }
    )
); 

Now, for all the current items that we have found we can add an item to our AdminTypesUpdate collection. This is the content that gets sent back to SharePoint.

 Clear(AdminTypesUpdate);

ForAll(
    Filter(
        Filter(dsAdministrationTypes,
            Or(
                Title in SharePointIntegration.Selected.'Administration Type'.Value,
                And(
                    'Effective From'<=Today(),
                    Or('Effective To'>=Today(), IsBlank('Effective To'))
                )
            )
        ), Title in SharePointIntegration.Selected.'Administration Type'.Value
    ),
    Collect(AdminTypesUpdate,
        {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Id: CountRows(AdminTypesUpdate),
            Value: Title
        }
    )
); 

Out of the box, the Power App will try to send back a comma separated list of our chosen values:

“Administration Type 1, Adminsitration Type 2, Adminstration Type 4”

And SharePoint won’t accept this.

We need to send THIS structure back to SharePoint for each item that is selected:

 {
  '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
  Id: {{Iterator}},
  Value: {{Lookup Value}}
} 

Now. we have an initial collection of the choice values that were passed in from the item and we won’t write back a blank field value, losing our initial metadata!

Step 2: SharePoint Form Data Card

Now, we can customise the Power App Data Card for this Field by setting the following properties:

Property: Update — this is the data that gets sent back to SharePoint.

AdminTypesUpdate

Step 3: Data Card Combo Box

Next, we need to make changes to the ComboBox in the data card by setting the following properties:

Property: Items — This is all the items that the user can choose from. This is actually exactly the same as the first filter that we used earlier to get the list of ALL choices this field could take. That is all initially selected choices and all currently active choices. We could have made this a collection to make it easier to read and reusable. But I didn’t.

Filter(dsAdministrationTypes,
    Or(
        Title in 'Administration Type'.Value,
        And(
            'Effective From'<=Today(),
            Or('Effective To'>=Today(), IsBlank('Effective To'))
        )
    )
)

Property: DefaultSelectedItems — These are the items that are already set on the item when we come to edit it. This is the same as the second filter that we used earlier to get the list of ALL current choices for this fields initial selected state.

Filter(
    Filter(dsAdministrationTypes,
        Or(
            Title in 'Administration Type'.Value,
            And(
                'Effective From'<=Today(),
                Or('Effective To'>=Today(), IsBlank('Effective To'))
            )
        )
    ), Title in ThisItem.'Administration Type'.Value
)

Property: DisplayFields — This just tells our control which attribute of our lookup item to show to the user. In this case we just want to show the Title, which maps directly to the Title field in the SharePoint list we use to hold the lookup items.

["Title"]

Property: OnChange — lastly, as the user makes changes to the selection of metadata options we need to rebuild the collection to send back to SharePoint. I am sure we could just manipulate the collection by removing items and adding items, but Clearing it and rebuilding it is just easier. This is nearly the same code that we used earlier to build the initial collection, but this time we are loading it from the DataCardValue3.Selected Items component, not the SharePointIntegration.Selected object.

Clear(AdminTypesUpdate);

ForAll(
    DataCardValue3.SelectedItems,
    Collect(AdminTypesUpdate,
        {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Id: CountRows(AdminTypesUpdate),
            Value: Title
        }
    )
);

And that is really all there is to it!

Save, publish to SharePoint and test and let me know if this works out for you.

Mark – Managing a SharePoint MultiChoice Field in a PowerApps List Form

Managing a SharePoint MultiChoice Field in a PowerApps List Form