Topic
This How-To will cover how to reference the previous item on a list in a new item using a Nintex Form.
Scenarios for this use may be that you have a running balance that needs to be updated with each new item and you want the previous balance shown on the new item form. It may be possible to run a workflow after each item is submitted to update a list item somewhere where the form can reference but doing it by just using a form eliminates the need for another location to store the required metadata and a workflow.
Scenarios for this use may be that you have a running balance that needs to be updated with each new item and you want the previous balance shown on the new item form. It may be possible to run a workflow after each item is submitted to update a list item somewhere where the form can reference but doing it by just using a form eliminates the need for another location to store the required metadata and a workflow.
Instructions
Add the following formula to a calculated value control on your form. (type this in and not cut and paste)
lookup("List","ID",max(lookup("List","Item Child Count","0","ID",true)),"ColumnYouWantOnPreviousItem")
When a new item is added to a list the Item ID value increases so it is fair to say that the highest ID will be the last item added to the list.
This formula looks up every item ID. To retrieve all items I needed a column that had the same value on every item so I used the Item Child Count column which is always 0 unless you use this column for its intended purposes. If you do you could use the Version column if you are not using versioning. If all else fails you could add your own column and have the form update it with a form variable and use static text.
After all Item ID's have been retrieved the max() runtime function looks for the highest number.
The next Lookup() then looks up the Item ID using the highest ID number retrieved which will be from the last item submitted and returns the column you need.
lookup("List","ID",max(lookup("List","Item Child Count","0","ID",true)),"ColumnYouWantOnPreviousItem")
When a new item is added to a list the Item ID value increases so it is fair to say that the highest ID will be the last item added to the list.
This formula looks up every item ID. To retrieve all items I needed a column that had the same value on every item so I used the Item Child Count column which is always 0 unless you use this column for its intended purposes. If you do you could use the Version column if you are not using versioning. If all else fails you could add your own column and have the form update it with a form variable and use static text.
After all Item ID's have been retrieved the max() runtime function looks for the highest number.
The next Lookup() then looks up the Item ID using the highest ID number retrieved which will be from the last item submitted and returns the column you need.
Additional Information
The Lookup() runtime function has a limitation of 1000 entries that it can store in the array.
This formula may not work correctly if the list exceeds 1000 items.
There are many ways to achieve any solution so there may be other ways to achieve this one but this is a good start.
This formula may not work correctly if the list exceeds 1000 items.
There are many ways to achieve any solution so there may be other ways to achieve this one but this is a good start.