1. Flow of the Week
  2. Walkthroughs

Advanced | Flow of the Week: When a new item is created, automatically populate a SharePoint lookup column calculated field

, Senior Software Engineer, 13. juni 2018

This post will show how to automatically calculate and update a column in a SharePoint list when an item is created or modified.

It's common practice in SharePoint to use lookup columns to manage relationships between lists. Sometimes the list also needs to maintain a calculated field based on lookup column values selected, it could be as simple as sum of another column values in lookup column list or a complex formula. In this post we look at a simple sum of values from lookup column. Consider following two SharePoint lists that are used to manage the turbine energy distribution at Cronus energy.

 

SharePoint Lists

 

Turbines List

This list contains different turbines available and corresponding maximum output capacity.

 

Turbine Energy Distribution List

This list contains entries for weekly energy distribution by selecting a number of turbines from the Turbines list. One of the list column 'Turbines' is a lookup column into Turbines list which allow multi-selection of turbines.

Details of lookup column configuration is shown below. The ‘Turbines’ column in ‘Total Energy Distribution’ list points  to column ‘Turbines Name’ from ‘Turbines’ list. Also notice that ‘Allow multiple values’ checkbox is checked to allow selection of multiple turbines and ID  field is checked in additional fields. The ID field allows us to look up the associated list item in the ‘Turbines’ list.

Given above lists the goal is to automatically calculate the 'Total energy' column value in "Turbine Energy Distribution" list for the all of the turbines selected whenever a new item is added or existing item is updated.

 

Creating the Flow

The flow algorithm on a high level : Flow runs when an item is created or modified and then loops through the turbines selected in the lookup column to calculate the sum of the output energy capacity and finally updates the list to populate the ‘Total energy’ column with the calculated value.

 

Below are steps to implement the flow to achieve above goal.

  • First add "When a new item is created or updated" trigger. This trigger fires whenever an item is modified or updated so that flow can handle the calculation in both cases and keep the Total energy value for any changes to selected Turbines.

  • We need an integer variable to store the total energy value as we loop through all of the turbines list. Create "Initialize Variable" action" and Name the variable as "Total Energy Sum". Select the "Type" to be an integer to store the energy capacity and set the initial value to 0.

  • Next step we need to loop through all turbines to calculate the sum of the energy capacity for the selected turbines. Click on "New Step" and select "Add an apply to each" under "… More".
  • Click into  ‘Select an output from the previous steps’ textbox and select "Turbines.ID" token from the Dynamics content. This is an array field that contains the Turbine Id's from the "Turbines" list through lookup column relationship.

  • Next step add "Get Item" to find the energy capacity for each turbine from the "Turbines" list. Configure the "Get Item" action with "Turbines" list and "Turbines:ID id" token which points to a specific turbine in the list.

  • Next step is to convert the turbine output capacity into an integer value and add it to the "Total Energy Sum" variable. Add "Increment variable" action, select variable name to be "Total Energy Sum" and use expression builder to convert "Max output Capacity" to an integer value. Select "Value" textbox and then click on "Expression" tab in the token picker. Enter function name int() and switch tab to "Dynamic content" and select token "Max Output Capacity" to set it as a parameter to int() function. Raw function expression should look like  "int(body('Get_item')?['Max_x0020_Output_x0020_Capacity'])". Click on "OK" button on the token picker to update the value field with the expression.

            We are done with logic to calculate the total energy inside the loop. Apply-each loop goes through each turbine and increments the "Total Energy Sum" variable with "Max output capacity" for each turbine.

  • Next task is to update the "Turbine Energy Distribution" list with "Total Energy Sum" calculated in the above 'apply-each" loop. Let's add a condition and update the list item only when the total energy calculated doesn’t match with existing item value. This is important since the update item will trigger the same flow and could lead to infinite loop without the condition. Add following condition action, select "Total Energy Sum" from variables, select "is not equal to" from the drop-down and select integer value for the "Total Energy" from trigger outputs using int() function from expression builder.

  • Select 'Add an action" in "If yes" from the condition block and add "Update Item" action. Select the value for 'Site Address' and select 'Total Energy Distribution' for 'List Name'. Configure the required fields with tokens <ID> and <Week>.  Select Total Energy column and pick "Total Energy Sum" token from the "Variables" section in the token picker (If the variables section is empty, please select 'see more' on the top right to see all of the tokens).

  

After adding all of the steps overall Flow looks like below.

 

Testing the Flow

Go to SharePoint and add a new item to "Turbine Energy Distribution" list and select 4 turbines. Please note that "Total Energy" column would empty after creating the item, this column gets populated with the sum of the capacity of the turbines selected after Flow run.

 

Wait for few minutes and refresh the list item. As expected "Total Energy" field Is updated with sum of the capacity of the selected turbines in the list.

You can also verify that Flow run succeeded and "Update Item" in the run history would show "Total energy" column is updated with value 90 which is sum of output capacity for the selected turbines.

This time update the list item and add one more turbine "Peace River – 004" and wait for the flow to run. Total Energy is automatically updated to 110 reflecting the sum of all the of turbines selected.

 

Conclusion

In this blog post we learned how to use a loop to iterate through the list of items in a lookup column list, using "initialize variable" and "increment variable" actions to maintain the calculated value in a loop,

using lookup columns to automatically update the calculated fields using the flow. You can download the flow created from here. If you have any questions please leave us comments below