Adding Calculated and Lookup Columns to Your Custom SharePoint 2016 App

Calculated columns are especially powerful for automatically generating data in SharePoint 2016. Don’t be intimidated — the web is full of great formula examples for SharePoint calculated columns. Some common uses include

• Adding days to a Date column to calculate an Expired or Due Date column
• Adding Number or Currency columns to get a total
• Using the Me function to automatically add the username to a field

Creating a calculated column

To create a calculated column, follow these steps:

1. Select the Calculated column type in the Name and Type options in the Create Column dialog box. The Additional Column Settings area changes to support entering a calculation and specifying column options.
   Column settings for a calculated column.
2. Type your formula using the proper syntax in the Formula text box. If you’re basing your calculation on another column in the app, you can reference that column using the square brackets reference syntax. For example, to calculate a Shipping Deadline value, add five days to the Order Date value in another column by entering [Order Date>+5 in the Formula text box.
3. Select the proper data type for the returned value and other data type property options, if available, from the Additional Column Settings section of the page. Not all return values are of the same data type as the input columns. For example, if you subtract one date from another, your returned value is a number (the number of days’ difference between the two dates). Other examples include
   • Adding the current username to a field. Simply type the constant [Me> in the Formula text box.
   • Using today as a date in a calculation to create a new date by entering [Today>+7 in the Formula text box.

Using a lookup column

Maintaining all your options in a Choice field can be cumbersome and prone to error. SharePoint uses a similar model to relational databases by separating the lookup information from the transaction app. Think of all the lookup data that could be maintained in separate apps. For example, computer hardware inventory lookup apps could include hardware type, maintenance contract, and department location. These apps can be maintained independently of the transaction app — the inventory itself.

For example, you could create a Customer custom app with a single field — Title — and populate it with the names of customers. Then build an Order app (to track orders that customers place). Customer is a column in the Order app. Rather than build a Choice field, you could use the Lookup data type to connect to the Customer app and use the Title field as data for the Customer column in the Order app.

The end result is that all your customers are separated from the orders. If a customer needs to change his or her name (maybe she got married, or his name was misspelled) you can just change the name in the Customer app instead of in every entry in the Order app.

You can also add other columns from the lookup app to the drop-down list to help users select the proper choice. When a user selects the value from the drop-down list, values for the additional columns also display. The example below shows a scenario that uses a lookup column to display a customer’s sales territory. The customer’s name and sales territory are stored in one app and displayed in another app using a lookup column. You can also see the use of inline editing in a Web Part.

For users familiar with databases and referential integrity, SharePoint 2016 includes additional options to support this implementation. Lookup columns can also be used to create a chain of joined apps that can be used to query and display values from additional columns.