AMFTF: "Lookup" versus "Single line of text"

[Rick72]

Recently, I was working on a SharePoint on-premises to SharePoint online migration project. A well-known migration tool was used. Testing showed some remarkable results (issues). The source of the issues were columns of type "Lookup". Items in the corresponding lookup list were created by the migration tool but with different ID’s resulting in strange values in the primary lists. This raised the question to me, why should we use a column of type "Lookup" at all when using a canvas app which uses SharePoint online for data storage? This question made me write this blog.

A column of type "Lookup" has some special features:

1. Custom permissions can be set on the lookup list making it possible to delegate the administration of the values to a group of users.
2. A change of an item in the lookup list updates the value of the related primary list items.
3. A deletion of an item in the lookup list removes the value from the related primary list items.
4. Additional columns in de lookup list can be shown in views (SharePoint) of the primary list.
5. A column of type "Lookup" support multiple values.
6. A lookup list value is clickable (SharePoint) showing the lookup item and all columns/values in a pop-up window.

These features are described in more detail below compared to using a column of type "Single line of text".

Feature 1

This feature is still possible when using a column of type "Single line of text". PowerApps connects to the lookup list with custom permissions and this lookup list is used in controls like "Drop down" or "Combo box".

Feature 2

This is a powerful feature but not always useful/wanted. It changes all related items including in-place archived items and items in multiple primary lists when used.

This is a powerful feature but not always useful/wanted. When using a column of type "Single line of text", a script can be made to update all values (useful for bulk updates). This script can also be created to not update the version (CSOM & SystemUpdate). Updating the version when changing data could be a requirement though. A requirement which can easily be overlooked.t changes all related items including in-place archived items and items in multiple primary lists when used.

Feature 3

This is a dangerous feature and one of the main reasons for me not to use a column of type "Lookup" (unless really necessary). "Restrict delete" (SharePoint) can be used to minimize the impact but this setting is often overlooked and has it drawbacks.

When values need to be deleted in bulk, a script can be created. Using a script makes it possible to use complex criteria when a value must be deleted from an item. Something not possible when using a column of type "Lookup".

Feature 4

This is unique, but one can ask her-/himself if this is really an issue because a canvas app is used. And if this is an issue, this can also be solved by using a second, third, … column containing values from the lookup list. Yes, this makes the canvas app a bit more complex related to the creation/update mechanism but has the additional advantage that real values are available in the primary list. Have you ever tried to make a calculated column based on a additional column from a lookup list?

Feature 5

This functionality can be created when using a column of type "Single line of text" though it is not that easy. Perhaps in a future blog I will write about this.

Feature 6

This functionality can easily be created when using a column of type "Single line of text". A connection to the lookup list must be made. A button next to the dropdown field can be used to show a pop-up window with extra details from the lookup item. One could even shown a pop-up window directly after making a selection/change.

Other considerations

A canvas app makes it possible to use multiple fields for a lookup (cascading dropdown). This is not a feature of a column of type "Lookup".

When deploying a solution for the first time, the values in the lookup list will be up-to-date but what are your options when you want to disable/hide a value in the lookup list? Well, there are none. Deleting an item in the lookup list is the only option but this will delete the value from the related primary list items too. A real drawback imo.

Conclusions

I avoid the usage of columns of type "Lookup" unless really needed. This column type seems useful at first sight but can be a pain for migration and archiving scenarios. Using columns of "Single line of text" will be much easier for these scenarios. Scripting is needed for bulk updates/deletes though, which adds extra knowledge to the persons maintaing the solutions.