Combine lookup function with formula: calculated value plus 1

  • 26 April 2016
  • 3 replies
  • 3 views

Badge +7

The problem:

In a list titled "PHB lijst" every newly created item needs to be registered with an index number. This index number is based on the index number of the last item that was added to the list increased by 1. Would it be possible to lookup the last item that was added to this list and then calculate this value plus 1?

The solution so far - for lack of having found a solution in Nintex Forms:

When adding a new item to the list "PHB lijst", the index number is added on the form to the field named "Nummer" as a lookup (calculated value control) from another list. This list is named "Index" and data for this list consists of the continuously updated index number that is being generated in a workflow.

This workflow is added to the list "PHB lijst" and consists of the actions math operation and update item.

The math operation does a list lookup for the current item of the list "PHB lijst", for the field named "Nummer" (index number), plus: value 1, stored in a variable named "num_Volgnummer" (index number).

Update item writes this variable to the field "Volgnummer" (index number) on the list "Index".

Index List:

ListIndex.jpg

Math Operation:

MathOperation.JPG

Update Item:

UpdateItem.JPG

This solution works just fine but I was wondering if I might have overlooked simpler functionality available in Nintex Forms? So that no additional list and workflow are needed?

Kind regards,

Yvette


3 replies

Badge +7

Hi Yvette,

Every SharePoint list creates a unique ID per item or document added.  Have you considered using this?  I know these fields are a bit hidden, but I can send some examples of how to add it to your list.   The workflow will in any case be able to pick up this value whether you show it or not.

The only drawback is that you can't control this number but you can probably play around with a calculated field to use this number as part of an equation, i.e. to get ID + 25.  It is a sequential number which seems to be what you are trying to achieve.

Let me know if this is not the case or if I need to explain in more detail.

Badge +7

Hi Francois,

Thank you for your reply!

I probably should have included this in my post but using item ID is not possible because the list is composed from different lists and then migrated from SharePoint 2010 to SP 2013. There are currently 37135 items already.

You are right in saying that the index number is sequential.

Kind regards,

Yvette

Badge +7

Hi Yvette,

If that is the case, I had to build something similar and each item in your PHBJ Lijst was a department for me with their own number range, i.e.

Dept          Number

Finance      123

HR                2,343

Projects     323

etc.  and I would follow the same principal.  I eventually manage to move some of the departments over to Unique ID.  What I picked up with my solution was that sometimes the workflows got stuck and I'm not sure if it is almost like a table lock happening. So I just add the commit pending changes.  I would like to think that solved that issue for me :-)  might have been something else as well.

I don't know of an easier way to achieve what you are trying to achieve.

Reply