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

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:

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)

Advertisements

Using SPFilterDropdown to Reorder Options in a Dropdown on a SharePoint Form

Many times when you have a dropdown column in your lists, you’d like to show the values sorted in a different order than alphabetically. Sure, you can use a Choice column and order the choices in any way you’d like. However, in the cases where you want the dopdown to be based on a Site Column which is a Lookup into a list, you can’t specify the sort order. I prefer using a Lookup column in almost all cases because it means that an admin (or any user with permission, really) can update the values in a list context rather than a column settings context. We also often end up storing other information about the values in the list, which we can’t do with a Choice column.

Here’s an example. I have a Site Column called Recommendation. It’s a pretty common idea: what’s our view on a particular stock or bond? We may want to recommend to our clients that the stock should be a Strong Buy, Buy, Hold, or Sell. If we add those values to a list as the source for a Lookup column, they will be ordered like this: [Buy, Hold, Sell, Strong Buy]. That’s not how we logically think of things, so it’s not great.

SPServices to the rescue! If we add a column to the source list which contains the sort order we want, we can make things look the way we need them to work. In my list below, I have a column called, logically enough, SortOrder. Note that it contains values which will sort the recommendation options the way we want.

Recommendations Datasheet View

Recommendations Datasheet View

Then, in the forms where I use the Recommendation column, I can add a call to the SPFilterDropdown function which takes care of things for me. No, the function name really doesn’t indicate that it could help with this – I built it to let you filter the options in a dropdown based on a column in the source list – but there’s a little trick you can use. If you specify a condition in your CAMLquery which always evaluates to true and specify a relationshipListSortColumn, the function will sort the values the way you want.

For my example above, I specify SortOrder as the relationshipListSortColumn, and set the CAMLQuery to give me all items where the Title does not equal an empty string. That will return all of the items, they will be sorted by SortOrder, and the dropdown will look just the way we want it to.

Recommendation Dropdown

Recommendation Dropdown

// If the Recommendation column exists, sort it as indicated in the source list
$().SPServices.SPFilterDropdown({
 relationshipWebURL: "/", // The source list for the Recommendation site Column is in the root site
 relationshipList: "Recommendations",
 relationshipListColumn: "Title",
 relationshipListSortColumn: "SortOrder",
 columnName: "Recommendation",
 CAMLQuery: "<Neq><FieldRef Name='Title' /><Value Type='Text'></Value></Neq>", // Get all values by specifying a non-blank Title
 debug: true // Debug mode on while we're developing
});

Filtering on a Value in a Multi-Select Lookup Column

A great way to manage the values for a Site Column in SharePoint is to put the values into a list and then use the Lookup column type to grab the values.  When you allow multiple selections in the lookup column, what is stored looks something like this: 

value1;value2;value3;value4

If you would like to then check in a DVWP to see if a value was selected, it’s not as simple as you’d like.  If your values are truly unique and cannot be nested (e.g., School and Schoolhouse both contain the string School), then you can use contains.  Otherwise, you’ll want to tighten things up a bit by doing some fancier matching.

The possible ways that we can have a match are:

  • The multi-select string matches the value exactly.  This is the case when there has been only one selection made, and it is the one that we want.
  • The multi-select string begins with our value followed by a semicolon (value1;)
  • The multi-select string has the target value in the middle, therefore surrounded by semicolons (;value2;)
  • The multi-select column contains the value at the end, preceded by a semi-colon (;value4)

To test for each of these conditions, you just need a little logic in your XSL.  Here’s an example that works when you want to see if MultiSelectColumn contains DesiredValue.

<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row[
  @MultiSelectColumn= $DesiredValue or
  starts-with(@MultiSelectColumn, concat($DesiredValue, ';')) or
  contains(@MultiSelectColumn, concat(';', $DesiredValue, ';')) or
  substring(@MultiSelectColumn, string-length(@MultiSelectColumn) -
   string-length($DesiredValue), string-length($DesiredValue) + 1) =
   concat(';', $DesiredValue)
 ]"/>

Depending how you will need to do the test, consider creating a template that you can pass the multi-select string and the desired value, passing back the results of the test.  You can store this template in a separate file and then use the template in many DVWPs by including it with xsl:import.

Technorati tags: , ,

Using the ID Value for a Lookup Column

Lookup columns in SharePoint can be very useful as a way to standardize input values.  One of the drawbacks of Lookup columns is that they violate the Rules of Data Normalization (see my previous post entitled “SharePoint Is Not a Database”).  Specifically, by storing the text value of the lookup rather than the ID, we violate Rule 1: Eliminate Repeating Groups – Make a separate table for each set of related attributes, and give each table a primary key.  While the key exists in the lookup location, the actual value is stored in the item and is generally what folks use in their Data View Web Parts (DVWPs).

There’s a useful post over at the Microsoft SharePoint Designer Team Blog by Greg Chan, Program Manager on the SharePoint Designer team, entitled SharePoint Conference – Building a SharePoint Designer Mashup (Part 1) that shows how to use the ID of the lookup value in DVWPs rather than the text so that you can at least try to play by the rules.  (The post is really about some demos that he did at a Seattle SharePoint Conference, but the trick is embedded in it.)

Lookup columns actually do store the ID of the lookup value (the stored value is of the form ID;#text, e.g., ‘4;#Supplier Name 4’ or ‘1;#Betty’s Golf Shack’), but the ID is normally not exposed.  By using the LookupId attribute in your CAML, you can get at the lookup ID.  You can read Greg’s post for the full details, but here are the key bits.

In your selectcommand (where the CAML is stored) in your DVWP, add the attribute/value pair “LookupId=’TRUE’”.  So, from Greg’s example, to get the Supplier’s ID rather than the text value, your selectcommand needs to change from this:

selectcommand="<View><Query><Where><Eq><FieldRef Name=&quot;Supplier&quot;/><Value Type=&quot;Text&quot;>{SupplierID}</Value></Eq></Where></Query></View>"

to this:

selectcommand="<View><Query><Where><Eq><FieldRef LookupId='TRUE' Name=&quot;Supplier&quot;/><Value Type=&quot;Text&quot;>{SupplierID}</Value></Eq></Where></Query></View>"