Tag Archives: Lookup

PowerApps + SharePoint Drop Down Thoughts

This probably falls somewhere under app design, governance, and tips and tricks in the cross-over space between SharePoint and PowerApps.

When creating a list in SharePoint and needing a field with options in it, we need to decide between the relatively static ‘choice’ field with the options baked in to the control, and creating a Lookup control that pulls its options from another list. They both have their place. Choice fields tend to be a bit simpler while Lookups come with a little more overhead, but are good from a data perspective with consistency.

With the introduction of PowerApps and creating new design surfaces for your list data the Lookup option extends that consistency a bit further and may make you choose Lookups even more. When it comes to CRUD forms, both the Choice field and Lookup fields work great (way to go PowerApps and SharePoint teams!). Building other interfaces, however, may encourage SharePoint users to lean more on Lookups.

The Scenario

A default app created by the ‘Create an app’ option from SharePoint gives you a 3-page app that starts with a gallery view filtered by a Search control. I’d like to change the filtering of the page to be a drop down of a Status field. Two options we can use to accomplish this are to create a drop down with static options (like a the SharePoint Choice field) or to create a drop down connected to the same SharePoint list as the Lookup field.

image image

Option #1 – Static options

The initial reason I considered this was because I didn’t actually want to give users *all* of the options that are available in a choice field. Using a static options control allowed me to just display the options I wanted them to see. This is easily implemented using the method covered in my last post – Simple Drop Down Options – and using values that exactly match your list field values.

One possible upside to this approach is that you could use different text or drop down options that might make sense to users but *don’t* match your list values and then use formulas to match your drop down control values to your list values. For example in the above image I have a number attached to each option in my dropdown which some folks might prefer to leave off: “Paid” instead of “5 – Paid”.

The downside of this approach is I now have one more place where I’m required to maintain the list of values. If I update the Choice field in my SharePoint list I might also need to update the drop down control in PowerApps.

Option #2 – Lookup field options

This seems like how the drop down control was intended to be used. Rather than having to manually add values you can simply connect the control to an existing SharePoint list and have the exact same values as the SharePoint list column.

1. Add an additional data source to your PowerApp

2. Add the Drop Down control and set the Items value. By default it’s already set to a sample data set.
3. Change the Items property to the new data source and field to display and boom. Done.

4. Switch to the Advanced properties for the drop down control and set a default value for it.

5. Finally, change the gallery control to use the new drop down as a filter instead of the Search box (key change is highlighted).

From: SortByColumns(Filter(‘Sponsors 2018 April’, StartsWith(Company, TextSearchBox1_1.Text)), “Company”, If(SortDescending1, Descending, Ascending))

To: SortByColumns(Filter(‘Sponsors 2018 April’, (Dropdown1.Selected.Value = Status.Value)), “Company”, If(SortDescending1, Descending, Ascending))

NOTE: When using this approach It is important to understand how Delegation works with PowerApps and SharePoint in order to ensure complete and correct data is being surfaced in PowerApps. See references below for more information.

The most notable upside to this approach is that both the PowerApps drop down control and the SharePoint list column are pulling their options from the same data source and changes to that one list will update both the SharePoint list options AND the PowerApp.

Finally, as was mentioned in Option 1 – if you don’t want to make all the options available in the drop down control you can still accomplish this here by filtering the list like this:
Items = Filter(StatusOpts, StartsWith(Title,”2″) || StartsWith(Title,”3″) || StartsWith(Title,”4″) || StartsWith(Title,”5″))


The added benefits of using a separate list in SharePoint as a lookup source (Option #2 above) for SharePoint list data *and* PowerApps controls seems the better option for longer-term management.


JSLink – Filter on a Lookup Column with CSR

Lookup columns are both useful and a little odd in how they’re implemented in SharePoint list views. I wrote a bit about this in a previous post and showed one example on how to deal with Lookup column content. This post shows an alternative – and possibly more useful approach.

We’ll change the default link in the lookup field to a link that filters the current list by the lookup field value.  Yes, users can also use the filter built into the column header – this is just another way to implement it. I also think this is a bit more intuitive for users.

At the end of the day, this is just another example of custom link ‘building’ with CSR.
Sample File: CSR_LookupSelfFilter.js

Key lines:

With ‘Theme’ being the internal name of the Lookup field that we’re filtering on. Fairly straightforward.

The full list:

After clicking ‘Technics’:

Now we also need to add a ‘reset’ link to the web part so that users can get back to the default view. Otherwise, they could get stuck in a dead end after selecting a filter value. There are all kinds of ways to implement this, for simplicity’s sake we just updated the title text and link. 



Obviously you don’t *actually* have to change the text of the web part header – just the URL. Smile But again, it seems like better UX.

Hope this is useful.

JS Link and CSR Page

JSLink – Using the Lookup Column with CSR

This post covers another column type for use with CSR overrides. Lookup columns have what I consider an irritating default display characteristic in that they are displayed as a hyperlink to the lookup list item.


Where the link goes to:


The majority of the times I’ve seen a Lookup used, they are simply a list of items to select in a dropdown or radio buttons – like a category. In this case the hyperlink and displaying the form are useless (to me). Every so often the lookup list has a few other fields, like Description, etc. in that case the link can be a bit more useful, but it still seems to be more of a distraction from a user experience (UX) perspective.

What I’d like to see is the Lookup value – the same text – displayed without the hyperlink. I can get this value using the following:


Or, more generically:


You can also access two other properties of the item. The properties are: isSecretFieldValue, lookupId, and lookupValue. And no, right now I have no idea what the heck ‘isSecretFieldValue’ is other than a Boolean value. Smile 

Note: OK. I looked it up. There’s one post under ‘isSecretFieldValue’ here – and if set to ‘True’ it apparently hides the values of the Created By and Modified By values in the form. Huh.

Back to our Lookup column.

As with several other column types, the Lookup column can contain more than one item and is stored as an array. As with those other column types you can also check how many items are contained in the column value by checking the length of the array:


Once an override is put into place using this approach, the list view now looks something like this:


Definitely cleaner.

Sample code for this example can be found here.