Count list items fitting a specific criteria and set a field value

  • 29 March 2019
  • 5 replies
  • 5 views
T
Badge +1

Hello community,

even I am working with NWF for quite a while this is my first public request. Hopefully someone could help.

 

I have a SharePoint (on premise) which got a list of projects. Each project has an ID which is based on the year it started (P-StartDate) and a consecutive number: e.g. 18-001, 18-002, 18-003, ... 19-043. Actually it is entered manually but I like a workflow to do this. As far as I know (but I am a "forced-to-develop-myself-but-not-a-fully-skilled-developer"), I could use a collection to count items. The idea I have is to get the workflow to count all existing items with the same year (identified by the first two digits of the existing ProjectNumbers), save that count to a variable, simply add "1" to it and set the field value "ProjectNumber" to the new build string.

 

Any of you who can explain to me how to configure that? Would really appreciate that. Thank you!

 

Greetz,

TAA

5 replies

C
Badge +5

Hi @taa ,

We have a workflow that does exactly this. Here is how we have done it:

1351iBB4145CC9EB7BE28.png

 

It isn't in the screenshot, but since this workflow runs multiple times on the same item, it first checks to see if the number field is empty. If it is not empty, it skips everything in the screenshot.

In "Get Last Number", this is a query to a reference list with only one item that does nothing other than store the last numbers used. It has two columns, one for Year and one for Number. Each of these columns is used to set a corresponding workflow variable, within the Query List action.

Build Current Year is a Build String action with this formula: fn-FormatDate({Common:CurrentDate},"yyyy")

"If new year" compares the year variables. If it is the first item of a new calendar year, it follows the yes path. The sequential number is changed to 1, and the reference list is updated. The ID number is set using Build String with the following formula: fn-FormatDate({Common:CurrentDate},"yy")-0001

When "If new year" is No, it adds 1 to the variable holding the last number used, which was retrieved from the other list in "Get Last Number".

In "Update PDCR Num List", the other list is updated with the number that is now going to be used.

The build string action has this formula: fn-Length({TextStart}{WorkflowVariable:numNewCRNum}{TextEnd}). It finds how many digits are in the number. This is then converted from text to number in "Convert value".

"Switch" evaluates how many digits are in the number and prepends the appropriate number of zeroes.

Finally, the list item the workflow is running on is updated with its unique YY-#### number.

C
Badge +5

To do your original idea, you would set a variable to the last two digits of the current year, then use a Query List action where it filters on "Begins With" (your variable), instead of querying a separate list. You would then not need the actions where the separate list is updated. The action to use to find out how many list items begin with the current year would then be Collection Action, for the variable you stored the result of your Query List in, select Count, and store the number in an integer variable. You'd then want a similar Switch setup to what I posted previously.

Post if you have any questions :)

R
Badge +2

This is a similar solution that i've used before.  Instead of a switch, you could also use the padLeft function in a build string action to appending leading zeros.  set your desired length to 4 and it will take care of the switch for you in one action.

C
Badge +5
Much more elegant. Thank you @rjimenez89!
T
Badge +1

Thx! I'll give that a try!

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings