No-code solution to lookup previous item in a list
Description
This article describes how to pull data from the previous item of a list while working on the existing (or new) item. This no-code solution works in SharePoint 2010, 2013 and Office 365 SharePoint Online using SharePoint designer workflow.
Setting the scene
Consider a scenario where a list has been created with columns chosen as shown below. Somehow you need to keep a running count of the sum of the total number of planned and required hours spent and that count needs to be refreshed every time a new item is created. What this means is that when a new item is created we need to look up the 'Sum of Hours Planned' and 'Sum of Hours Required' of the previous list item and add it to the existing list item.
Below is also a screenshot of the columns of a list
https://l7tkda.dm2301.livefilestore.com/y2pOHj2ICsVGnzLSMejrXv3gqjd_z3cZEuImNes5a6P1HFbzBLYQX547fStwcfWevyE69jh8ZNXWfppb0rIE_XqJitdoTFXHkVf5dYTBd1pAqA/picture.png?psid=1
Solution
Below is a screenshot of the entire workflow. In a nutshell, we are using the list item's ID to find the columns of the previous item, add those values to a new item column and then do the calculation. For the sake of this explanation, the workflow is two separate steps. This is not a requirement.
https://l7tkda.dm2301.livefilestore.com/y2p8IpL5LXyxhiYSfDLU9IZQl_gFDrrSRxXacQS4fKtSinThs8Qnf-DmBgpRrEA7kSrAInN4V9ZxjdoI5enbtHgOT9rVEr2w4ebny9YE0GVPo8/picture2.png?psid=1
Get data from the previous item
By design the list item number increase in a sequential order hence we can assign a temporary number column called 'Current ID' set the current Id of the list item and then find the ID number of the previous list item. Below is a screenshot of the three actions.
https://l7tkda.dm2301.livefilestore.com/y2pvUJ-u4GgcPqfijGEUdqQgVHyDNcWumfp4bJUbG67kygkgGZA1MPH4kC7PSM_27tQ1gKDQNmV5NX4EG__PN4Omm84uiF2n06HKmcK6jujsQY/picture3.png?psid=1
Below is the most critical action and can also get confusing. We have added extra screenshots here. We are going to use the 'Set Field in Current Item' action. Below is what the action looks like when added and without any setup.
https://l7tkda.dm2301.livefilestore.com/y2pS3MYa66r6vTFW5DGI5ZQMJAhNbum-3jMRtQojpkvRTOPQcDyMswiTXlJgKgA20eOY2Pk41MO0845y-335CArGvL-rjCzoqKMd6zo0Gd2evA/picture4.png?psid=1
Click on field and select 'Previous Sum Of Hours Planned' Below is a screenshot
https://l7tkda.dm2301.livefilestore.com/y2pgpY_kbp2M_0VtFicGO55dbNO4bLoGQacSsMelwbcJddU69EHKYIpPJ8FYo4uBH78wuPvb92LSq0QxwLuxUrQm67kB8JuuywXguJQzM-X3c4/picture5.png?psid=1
Click on Value and then click on fx. The fx doesn't show up until Value is clicked. Below is a screenshot
https://l7tkda.dm2301.livefilestore.com/y2pVrbhKN249r_g_-XJ0YkpCC9fImBPaW-3z-TYGC-7UQ2xx-5zqvbdvWLfReBUriRom37U-sE-VUFVRmiYchTnoBXQNxahbDTQkGAZayxUqBk/picture6.png?psid=1
By default Current Item is the Data Source. Change that to Current List
https://l7tkda.dm2301.livefilestore.com/y2pHdSRDWAd8rnb9_-bJY0XEwdYp4TqQ92WGRKsL5antWqBi2CEU1H74YoAE2JdwhDrcuKceqIUWsIbBAQ8RYdLDL4UDCXS0FrBU2YzMRrmq3w/picture7.png?psid=1
You should now see more options as shown below.
https://l7tkda.dm2301.livefilestore.com/y2poPUvanLazPaHK3lHLPOUTsR9x_EzVYiFqzUBTA0iR1q5BNJ6z6KzQNnCiza61BZXv5KVSVOipgZ5OL59sDfBDiUegdW1gJ1t0-j82n_TP2E/picture8.png?psid=1
Populate all the fields as shown below. Let's pause here to understand what and why we choosing these settings. The data source i.e. the list is the same so no worries there. We do want to get the 'Sum Of Hours Planned' from the previous list item and we are going to use the item ID of the previous list item as a reference. This is why we had to do the calculation above.
https://l7tkda.dm2301.livefilestore.com/y2py4asOmuTVlfZloXdgNKTYlIPoLdlWL-y_k9KP9LhOzXTe_lEVm1JpAo8hvBv7_aaGGMqptfgw_m2CYDaNpstD4C0X0taY06VHTa2wQOMXX0/picture9.png?psid=1
Below is what the action will look like
https://l7tkda.dm2301.livefilestore.com/y2pb8kV7nUL8Bkb5zd9oBOWHNx-dHeZgP9Y-OP15VHsT_5U7beaMrc4-_HRYIvvBAyXqFche-maSi1dZYKBR7eS2WT1FwoJTD7N0M5zrPv-REc/picture10.png?psid=1
Do the same for the Sum of Hours Required.
Add previous and current items
This section is pretty straightforward. First, we are taking the new Hours Planned we just received, adding it to the Previous Sum Of Hours Planned and saving it to a variable.
https://l7tkda.dm2301.livefilestore.com/y2pgJHGB17xUvMWq00q5A4bV98n_hem_aFGdJ0V3RDBEIi3ndo8kMdFUVFOTUE_S3fgIe3mYhxc42-98Wclo1dDVw8nVqY53d17CYT4X_DImbk/picture11.png?psid=1
Second, we are setting the Sum of Hours Planned and Sum of Hours Required using the two variables we created above.
https://l7tkda.dm2301.livefilestore.com/y2pXVP7oFKe25yWPjyxQtRjod_decAa_4YI819bIPu-6k1IBHKa5RKHEPt4M0gfFLekIlGwixOOAAnbOOoIZg3QoRP1MIguyZROIWU84EN15D4/picture12.png?psid=1
Example
I have created a video which walks you through the workflow and then adding items the list and sees how the workflow does the calculation.
In this example the I have used Office 365 with SharePoint Designer 2013, however, the workflow is fully functional in SharePoint 2010 and 2013 as well. Due to prior testing, the first list item's ID number is, however, the workflow works just as well when the new item ID is 1.
(Click Here to view higher resolution)
Conclusion
This very same solution can be built using Visual Studio or JQuery, however, those will require coding. What I have described here is a simple, no-code solution that can be achieved using either SharePoint designer 2010 or 2013.