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.

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, paste this, using the name of your own gallery:

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

     Id : 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:

 

14 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

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