PowerApps: Set SharePoint Lookup Field

In many solutions, we need to be able to set the values of fields via logic in our form, and we don’t always need the end user to pick from a drop-down box.  The most common example is the relational database and parent-child relationships.  When we build this type of thing in SharePoint, you would want the end user to have the master record on a form on the screen, and they should be able to quickly add child items to a sub-list (like repeating table style) on the same page.  A common example is an expense report with expense items in a sub-list.

I’ll be writing out the whole cascading drop-down thing in a separate post, but in this example, the end user has selected the name of a project from a gallery of projects, and when they submit their timesheet form, that has a lookup field to the associated project, we need to set the value of that selected project, to the “Project” lookup field.  Seems pretty simple, and you may be surprised, but currently it’s not that simple.

I’ve named my gallery ProjectGallery, and it shows items from a data source that is my SharePoint list called “Projects”.

In my timesheet form (using the form control in PowerApps), its data source is a SharePoint list.

image

Unlock this card in the advanced pane on the right.

Update 12/5/2017: Click to select the card.

Click to select the actual drop-down control as seen in the photo above. (don’t select the card, select the drop-down itself)

After we’re done configuring it, we can just hide the whole card.

What you’ll need, is the Title and ID of the project that you need to set in this lookup field.

In the Default property of the card, type this, using the name of your own gallery:

(do not copy/paste this text from my blog here, it doesn’t translate correctly into the PowerApp.  Just type it manually in your PowerApp)

{    ‘@odata.type’ : “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,

     Id : Value(ProjectGallery.Selected.ID),

     Value: ProjectGallery.Selected.Title

}

image

Yes, it’s ugly. Go ahead and set the Visible property to false for that Project card.

Now, when you submit a timesheet, the project you selected in a gallery will post in the timesheet form’s Project lookup field.

Here’s my full hour-long demo of how to build this, and how to build it with drop-down boxes instead of galleries:

24 comments

  • Good stuff, but I have a question. If you’re creating a child item based on the parent using PowerApps, is there a need for the lookup column? You can look the two lists together for reporting simply by having a text column that holds the value/id of the parent item in it couldn’t you?

    The real issue I found in PowerApps was when creating a parent item and then a child item in the same form (e.g. you create a new order and then enter order details as part of that app in a different form). Because the parent hasn’t yet been created in SharePoint there is no ID assigned to it in the list, so therefore I had to generate a GUID that could be automatically assigned to both the parent and child on creation of each.

    The parent/child relationship is such a common requirement it would be great if Microsoft could make this a really easy function to implement.

    Like

    • You’re right. But, there are a couple of reasons you’d need to know how to do this. You may have existing lists that already have all of their relationships built upon lookup fields, and you’re creating a new PowerApp using those lists. Another reason would be if you ever need to interact with the data from within SharePoint, and have the lookup actually be a lookup. So, it would be more useful in both PowerApps and SharePoint. So, with both of those in mind, it was a show stopper for me, not being able to set those types of field values.

      Like

  • Pingback: TFS/SharePoint Integration; Co-authoring SharePoint Online Documents; Enterprise Cloud Computing Security

  • Pingback: Office 365 August 2017 Edition | Alpesh Nakar

  • Thanks Laura, by the way, how do you do this with a choice field?

    Like

  • This doesn’t work for me. When I set the lookup default to a value like this:

    {
    ‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
    Id:BrowseGallery1.Selected.ID,
    Value:BrowseGallery1.Selected.Title
    }

    then when I look at the app preview, the lookup is filled in with a default value. But when I open the app through Dynamics365, the combo box remains empty.

    Like

  • Thanks! Had this challenge in my own app … worked like a charm! 🙂

    Like

  • This has been great but has suddenly stopped working; it works the first time but then won’t repeat the action moments later. Do you have ideas why this could be? Can’t see any update that would make it do this?

    Like

    • Same with me. It works the first time you open the app and create an item, then the lookup never gets populated again.

      Like

    • I worked out where I went wrong. If you place the following code in the Default section of the ENTIRE Datacard, rather then on the dropdown field of the datacard, it works.

      {
      ‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
      Id:BrowseGallery1.Selected.ID,
      Value:BrowseGallery1.Selected.Title
      }

      This method also removes the #MIcrosoft.ASSURE nonsense from the field, thus allowing users can change from the default dropdown option to any other option if they need to.

      Like

    • Thanks, have tried that but am still having the same issue unfortunately. Still working ok for you?

      Like

  • Hi Chris. Yes. It is still working fine. Perhaps try deleting the custom card and adding it on again. And then only apply the code to the Datacard rather then the drop down box.

    Like

    • Thanks Hayden but that still won’t work. Its brought our project to a stop as the relations are very important within our app.

      Like

  • I just smacked my hand on the ceiling fan jumping for joy after the first record was saved to my list using this solution. I’ve spent the last tree days looking for this. THANK YOU !! Mine is unique where I have a Gallery that opens a parent form. From that parent form you open the child form (Apartment > Inspection Items). This solution allowed me to save the cascading drop down id/value pairs AND the Apartment id/value pair using variables. Thank you so much for taking the time to share your discoveries!

    Like

  • This was great! But now I’m on trying to solve the next riddle and that is how do I do a GroupBy with a LookUp column as the column to Group By? LookUp columns aren’t listed as one of the columns to choose from, so does anyone know how to do it?

    Like

  • Hi,
    I’ve thought this was the answer finally! But no, I have followed the procedure and get the #Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference showing up in the dropdown text box but when I submit the form is accepted but the entry in the SharePoint list shows the new record but with a Field Required error message in the lookup field.
    I have tried with Hayden’s suggestion but that didn’t work either.
    So near yet so far. Would be feeling like Rob if someone could sort this for me.
    Thanks
    Phil

    Like

  • how do you reset the gallery? what is the code on the back bottom

    Like

  • ho boy … phiip I know how you feel … so near but yet so far. I’ve been trying to get this working for hours and hours and hours now, with no luck. When I submit the form, after a few seconds I get the error message “The requested operation is invalid. Server Response: A value must be provided for the item.” followed by a clientRequestId value with a long string of numbers.

    This is the code I inserted into the dropdown list inside the datacard: {‘@odata.type’: “#Microsoft.Azure.Connectors.Sharepoint.SPListExpandedReference” , Id : glyCustomerStaff.Selected.ID, Value: glyCustomerStaff.Selected.FullName}

    Any more ideas would be much appreciated!

    Like

  • I have tried your solution and can get the detail record to submit – but ONLY if I click on the pick list, get a dropdown and pick one, and then submit. It does correctly submit a Lookup record, but the list isn’t restricted to only the item selected in the gallery, and won’t submit until I pick one. Can you help? These reference a master – detail list, with the detail list having a look-up column to the Title field in the master list.
    Here is my code inserted into the default value of the dropdown list:

    { ‘@odata.type’ : “#microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”, ID: Value(Gallery1.Selected.ID), Value: Gallery1.Selected.Title}

    Thanks for your help!!

    Like

  • Does anyone know if something changed in PowerApps as far as setting Lookup Value’s go? This functionality was working previously but as of a few days ago it doesn’t. It looks like it is setting the default value correctly on the app, however, the item isn’t getting added to the list and I get an error in the editor that “A value must be provided”.

    Like

    • I am having the same issue. When trying to customize a form, the lookup field (defined within SharePoint) does not appear to be a LookUp control within Powerapps (it was a few weeks ago though). Instead it appears to be a Choice control. Has anyone figured out a work around or solution to this?

      Like

  • Pingback: PowerApps: Setting a SharePoint List Lookup Column | Marc D Anderson's Blog

Leave a reply or question

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s