PowerApps: Multi value lookups SharePoint in Datacards

Posted on by Django Lohn

Why?

The SharePoint lookup column that allows multiple values to be selected. Nice idea from a SharePoint point of view, but when used in a PowerApps datacard that you want to have default values from a combobox control this may be tricky

What?

An explanation of how you can get multi value lookups from SharePoint to work in a PowerApp that has multiple screens.

How?

In my previous post the single value lookups were clarified: https://knowhere365.space/powerapps-single-value-lookups-sharepoint-in-datacards/ so please check that one first for the context.

Now we want to expand the ListRequests with combox control in Screen 1. In my example I call this combobox: ComboBoxRequestFormats:

Combobox control linked to SharePoint List and SelectMultiple property is set to true

The values of this dropdown need to come from a source SharePoint list so the business can manage the options themselves in this list: SourceListRequestFormats. We created a lookup column with the Allow multiple values enabled so the lists are linked: LookUpFormatType. For a best practice (and easy PowerApps management when building), you can use a different column for the lookup instead of the Title column: imagine you have multiple lookups then you need to make sure you are using the correct Title column in the correct syntax.

For submitting the data to your SharePoint list we want to use the Forms Control in Edit mode that is placed in the last screen.

1) The trick is to get every selected item in the Combobox control in a collection so we can use alle data of every selected item. We can achieve this with a ForAll function that we can place in a moment where you change screen or just before you submit the form:

ForAll(
    ComboBoxRequestFormats.SelectedItems,
    Collect(
        COLformatComboboxSelected,
        {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Id: ID,
            Value: ComboBoxRequestFormats.Selected.Title
        }
    )
);
SubmitForm(FormWithDatacard);
Clear(COLformatComboboxSelected)

The ForAll function puts all the selected items of the control ComboBoxRequestFormats in a temporary new collection COLformatComboboxSelected. In this collection we define three columns to be used in the Datacard of the SharePiont list in next steps:
– @odata.type = a default string for this type of SharePoint columns
– Id = the ID from the SharePoint list
– Value = the property of the Combobox matching the selected column chosen in the multi-select lookup
(very important to change this if you are not using the Title column in the lookup column!)

2) Then we can use the new collection COLformatComboboxSelected to populate the the Default property of the LookUpFormatType Datacard:

COLformatComboboxSelected.Value

3) We also need to change the Update property of the same LookUpFormatType Datacard to submit the data to the SharePoint list preventing it from being blank:

COLformatComboboxSelected

It is important to put the formula of step 1 on the right place to make sure the latest selected items of the ComboBoxRequestFormats will be saved to the list 💪👍

9 thoughts on “PowerApps: Multi value lookups SharePoint in Datacards”

  1. I am able to patch but the values are not being displayed when sharepoint list is opened. Also when trying to use the COLformatComboboxSelected in default it is not recognized.

    Reply

  2. Quick question, I have a combo box which is combobox.selecteditems ( it’s staff ID)
    And I have another label trying to do lookup( source, I’d = combobox.selecteditems,staffName)

    Trying to get mutiple staff name

    Reply

  3. Thanks for your help. Ia am trying to do the same using Data verse instead of a SharePoint list, but it’s not working.

    Reply

    1. Hi Emile, Dataverse is another ball game 😁.
      I would expect it to be easier though… if you are using a Form Control that is linked to the Dataverse Table you should see how it is setup by default?

      Otherwise I really recommend to ask your question in the forum: https://powerusers.microsoft.com/t5/Get-Help-with-Power-Apps/ct-p/PA_General
      Feel free to mention me if there is no one responding but my experience is that multiple experts will help you very fast 👍.

      Reply

  4. Hi there, I attempted your provided solution but it does not work. I have a similar setup where the Sharepoint List column is of “LookUp” data type and it allows for multi selection. I get errors on both step 2 and 3 where both Default and Update shows “Expected Record Value”. Can you advise?

    Reply

    1. Hi 1111,
      This is a very specific use case where you are using the Form Control of a SharePoint List and you want to get data from a combox (outside the Form Control) to be saved into SharePoint. You are using a combobox to get the info into the COLformatComboboxSelected collection?
      Step 2 and Step 3 is on the properties of the Datacard. I will try to add some clarification.
      Otherwise I recommend to add a post to the Power Apps Community: https://powerusers.microsoft.com/t5/Building-Power-Apps/bd-p/PowerAppsForum1. Here you can add screenshots and get help from other epxerts as well. Feel free to mention me 👍

      Reply

  5. In your ForAll, ID doesn’t resolve? Don’t you need the ID of SelectedItems? ID is saving as ThisItem.ID. Can you clarify?

    Reply

    1. I added some clarification.
      The ID being collected in the temporary collection, is the ID of the SharePoint item that is selected in de combobox. So in the data source (items property) of the combbox you are using the SharePoint list with selectable options and a SharePoint list always has an ID column.

      Reply

Leave a comment