PowerApps Tip: Setting a SharePoint List Lookup Column

PowerApps is really cool, and I really mean it. Unfortunately, it’s been moving forward so fast that it’s really hard to find clear information about how to do things. Examples you may find from just a few months ago may not look at all like what you see on your screen. (This post may be obsolete by the time I finish writing it!) Because of this, seemingly simple things can become a time consuming headache.

Here’s how I solved one of my recent headaches. There are lots of piece parts about this out there, but I haven’t found a soup to nuts description of how to do it.

I have a simple demo list called Beach Inspections – which are nice to think about in February in New England! It’s a simple list, and for this discussion, we’re only going to care about one column: Country. Country is a lookup column into the Countries list’s Title, which contains all the world’s countries. When we create a new beach inspection, we’ll usually want to preset the Country based on what we already know, whether it be the user or the beach name or whatever; it doesn’t really matter what. The challenge is in setting the value of the lookup field in PowerApps.

Let’s look at this from the initial setup of the PowerApp for the list. When you first click on Customize forms, you’ll see a bunch of screens and will arrive on one which looks something like this:

PowerApps generates a pretty decent form for you right off the bat – in theory, it may be what you need, but I expect in most cases we will edit this to make it our own.

To set things up to preset the Country lookup column, click on SharePointForm1 in the left panel (1) to display the forms properties in the right panel. You’ll see the data connection on in the right panel (2) at the top.

With the Data panel open, you can see the available columns from the data connection (your list – my Beach Inspections list). Many of them will already be checked, meaning they are included in the default form.

Next to the Country column, there’s a little icon showing what type of control is rendered on the form. When you click it, you’ll see the options below.

By default – even though the column is a Lookup column – the Edit selection control is set. This is the part I missed in all of the posts I found. Laura Rogers post (in the References section below) was the closest, but this part was omitted. You need to switch to the Edit lookup control to make things work.

Now, to set the value in the field, you can add a JSON object to the filed. In my case, I’m keeping it simple. I’m setting the default value to the United States with the JSON below:

{
  '@odata.type' : "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
  Id: 236,
  Value: "United States"
}

This works because the item for United States in the Countries list has an ID of 236. If you’re familiar with the way lookup columns are stored in lists, this should make a lot of sense to you. In my Beach Inspections list, the Country value will be stored as:

236;#United States

I know, I’m being very USA-centric. I could just have easily used:

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

as the default, because the item in the Countries list for Andorra has an ID of 6.

Of course, the values for Id and Value could be provided by values in other fields, a calculation, or many other things. Again, I’m trying to keep things simple here.

One problem we see right away is that the field looks weird in PowerApps when we add this JSON:

That’s because PowerApps by default displays the value for ‘@odata.type’. I’m going to go out on a limb here and guess that no one will ever want to see that value in their form. The way to fix it is to change the setting in Value1 below the Default setting for the field.

You can decide to display the Value or the Id you’ve provided in the JSON. Generally speaking, I’m guessing you’ll want to show the Value, so select it from the dropdown.

Now, when I create a new item in my Beach Inspections list using the PowerApps form, I see this:

The default for Country is set to United States, but I can decide to change it if I’m inspecting all the beautiful beaches in Andorra. (Hint: Andorra is a land-locked country, so there’s very little work for us beach inspectors.)

References

PowerApps: Set SharePoint Lookup Field by Laura Rogers (@wonderlaura)

Similar Posts

14 Comments

  1. Hey Marc, thanks. Iv’e been playing with PowerApps a little too. I agree about the need for the need for a little maturity and documentation. It does look promising though.

  2. When you click the dropdown which is now defaulted to “United States” do you see a list of the country names, or the Id numbers for the countries? I cannot seem to get the lookup to reach past the Id and get to the value to display in the dropdown.

  3. Nothing works the way you describe….I guess it’s not your fault, it’s all about PowerSUCKS!

    1. @Makis:

      I’m sorry to hear you haven’t had success with this; it’s been working for me. I think you find once you’re more up to speed with PowerApps that you can accomplish a great deal.

      M.

  4. Never mind! Looks like I’ve got it working. Thanks for the great article that pointed me in the right direction!

  5. Hi

    In my scenario,

    I have

    Sharepoint list 1: Title,Product

    sharepoint list 2: Title,MyProducts

    Here Both Title columns are having different data.
    And both Product and MyProducts Columns are lookups with different names and having same data.
    In My Application, Dropdown1 has Title column form Sharepoint list 1
    Dropdown 2 has Title column from Sharepoint List 2
    Now I need to filter Dropdown2 Title values based on Dropdown1 Title values using similar data from Products and MyProducts lookup columns from two sharepoint list
    Can anyone please help me with getting these values into PowerApp form.

    Thanks in advance.

  6. Hi, I have 2 options “/sites” and “/teams/”. I want these 2 to display different strings. By using the above solution i was able to change the default value to display the string I want, but whenever I click the dropdown it still displays /sites/ and /teams/. Any ideas on how to fix this?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.