{"id":4632,"date":"2019-02-15T23:28:49","date_gmt":"2019-02-16T04:28:49","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=4632"},"modified":"2019-02-12T23:32:00","modified_gmt":"2019-02-13T04:32:00","slug":"did-you-know-you-can-link-sharepoint-lists-2","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=4632","title":{"rendered":"Did you know \u2026 you can link SharePoint Lists?"},"content":{"rendered":"

There is a lot of nicely self-contained data \u2013 I track usage and membership stats to track adoption and predict future capacity needs, and a single list contains everything I need to know. But sometimes it\u2019s not possible to keep complete records in a single table \u2013 two different processes update the information, and there is a need to isolate access to the more confidential information. As an example, I have a process that updates employee e-mail addresses in PeopleSoft. I don\u2019t<\/em> have any reason to read employee evaluations or payroll information. Even where it is<\/em> possible, it can be inefficient. Data duplication can be eliminated by keeping the information in separate tables.<\/p>\n

In a relational database, this is called normalization. Instead of maintaining a single table with orders where the customer\u2019s name, address, contact person, phone number, etc are stored on every single order<\/em> \u2026 you create a table with order-specific data (what did they order, how many, how much did it cost) and include a single column for customer number. Another table has the customer number and their contact information. If the customer moves their office, you don\u2019t have to go through and update hundreds of orders \u2013 just go into the customer table and update their<\/em> record. All orders will use the new address. To retrieve an amalgamated record, use JOIN in the query (or create a view using a JOIN query).<\/p>\n

You can do something similar with SharePoint lists. As an example, I will use lists with customer contact information and a list with orders. First, we need a list with customer information. Add an index for the column that will be used to find the customer record \u2013 in this case, it is CustomerID. Click on \u201cIndexed columns\u201d.<\/p>\n

<\/p>\n

Click \u201cCreate a new index\u201d<\/p>\n

<\/p>\n

Select the column to index and click \u201cCreate\u201d<\/p>\n

<\/p>\n

You now have an index \u2013 something to speed up searching your list for CustomerID.<\/p>\n

<\/p>\n

We\u2019ll also need a list with customer orders. Create all of the columns that aren\u2019t<\/em> populated by lookup *or* the field on which the lookup occurs. Then Add lookup column using \u201cAdd Column\u201d and selecting \u201cMore\u201d<\/p>\n

<\/p>\n

Name it & select \u201clookup\u201d as the data type.<\/p>\n

<\/p>\n

Some of the column settings will depend on your particular use case. Requiring data: are there going to be orders where the site isn\u2019t known yet? Should the values be unique (in this case, no, since I hope my customers return and place a second order!).<\/p>\n

From the \u201cGet information from\u201d drop-down, select the table that stores the associated information. The page will reload; from the \u201cIn this column\u201d drop-down select the value you want to store in this lookup column \u2013 what information is being cross-referenced? Here, it\u2019s the customer ID number. Then check all of the columns you want to populate based on the lookup field. Here, I\u2019m pulling in the customer\u2019s name and mailing address details.<\/p>\n

<\/p>\n

I specifically configure this table\u2019s records not<\/em> to be deleted if the record gets pulled from the lookup table. Again, this will depend on your use case. Hit OK to create the column.<\/p>\n

<\/p>\n

When creating a new record, you won\u2019t even see the columns we\u2019ve checked above \u2013 there will be a drop-down where you can select the data on which the lists are correlated.<\/p>\n

<\/p>\n

When you save the record, the remaining columns display information from the customer information table.<\/p>\n

<\/p>\n

What if you don\u2019t want<\/em> a drop-down with ten thousand customer numbers? (A perfectly reasonable request \u2013 I\u2019ve loaded a few thousand customers in my list and there\u2019s no way I want to scroll through all<\/em> of those numbers and hope to get the right one).<\/p>\n

You can turn the drop-down list into a searchable drop-down by using PowerApps to customize the input form. Click the not-quite-a-hamburger menu between the list name and \u201cList\u201d and select \u201cSettings\u201d<\/p>\n

<\/p>\n

On the settings page, select \u201cForm settings\u201d<\/p>\n

<\/p>\n

Click the \u201cCustomize in PowerApps\u201d hyperlink. <\/p>\n

Wait a minute as the new app is built.<\/p>\n

<\/p>\n

In the right-hand tool bar, find the \u201cFields\u201d section and click \u201cEdit\u201d.<\/p>\n

<\/p>\n

On the menu that flies out to the left, click on \u201cAdd fields\u201d<\/p>\n

<\/p>\n

Select the fields for the input form and click \u201cAdd\u201d<\/p>\n

<\/p>\n

The drop-down box in PowerApps is searchable<\/p>\n

<\/p>\n

In the ribbon bar, select \u201cFile\u201d<\/p>\n

<\/p>\n

Click \u201cSave\u201d to save the app<\/p>\n

<\/p>\n

When the app has saved, click \u201cPublish to SharePoint\u201d<\/p>\n

<\/p>\n

You will see a warning that publishing the app to SharePoint makes it visible to anyone who uses your list. Click to continue publishing the app to SharePoint.<\/p>\n

<\/p>\n

Verify that the app is published successfully.<\/p>\n

<\/p>\n

Click \u201cBack to SharePoint\u201d to return to your list.<\/p>\n

<\/p>\n

In the list settings, verify that the custom form is selected for your list.<\/p>\n

<\/p>\n

On the data entry card, you will see that the drop-down is now searchable.<\/p>\n

<\/p>\n

When you type some text into the drop-down, you will see a filtered list of options.<\/p>\n

<\/p>\n

** This works provided the list against which the lookup is performed (in this example, my customer list) contains 5,000 or fewer records. If you see an error like this, then you\u2019ve exceeded 5,000 records in the lookup table.<\/p>\n

<\/p>\n

There are a few options \u2013 the generally recommendation from Microsoft is to break the list into multiple lists. This may mean breaking the data into different, unrelated lists. Or it may mean establishing a parent\/child list relationship (e.g. a customer list that has multiple entries per customer reflecting their various offices could be a parent list of customers and a list for each customer reflecting their sites). You may be able to use a view, filtered to a subset of records, for data entry. Writing a custom web input form and using the SharePoint REST API to read and write data<\/a> would allow you to populate the searchable drop-down with data retrieved from the lookup target (in this case my customer table) \u2013 as the column is indexed, the threshold should not be exceeded as you search.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

There is a lot of nicely self-contained data \u2013 I track usage and membership stats to track adoption and predict future capacity needs, and a single list contains everything I need to know. But sometimes it\u2019s not possible to keep complete records in a single table \u2013 two different processes update the information, and there …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[415,747],"class_list":["post-4632","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-sharepoint","tag-sharepoint-online"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4632","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4632"}],"version-history":[{"count":2,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4632\/revisions"}],"predecessor-version":[{"id":4662,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4632\/revisions\/4662"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4632"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4632"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4632"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}