So I got a request from a customer who uses SharePoint 2013 that they need to create an identifier that looks something like this: FY15 XXXX
the XXXX is the identity number that starts from 1 at the new Fiscal year (the client's fiscal year starts in October).
This is an interesting problem that everyone encounters. So I gave it a thought and came up with a solution that removes the concurrency concerns and other factors that could cause issues.
Here is the solution that's been tested and is working:
first create the column called:
ok. now the fun part of the exercise. Creating a workflow on the list.
I created two stages: Main and Sleep.
Main:
Sleep:
the XXXX is the identity number that starts from 1 at the new Fiscal year (the client's fiscal year starts in October).
This is an interesting problem that everyone encounters. So I gave it a thought and came up with a solution that removes the concurrency concerns and other factors that could cause issues.
Here is the solution that's been tested and is working:
first create the column called:
- FiscalYear
- number
- Default:
- calculated value: =IF(MONTH(Today)>=10,YEAR(Today),YEAR(Today)-1)
- This calculation is based on the fiscal year starting in October
- IdentifierID
- string or number: I used String so that it is easier to append to my real id
- Please note that the images have been modified so if you see UniqueID or UniqueID0, that was the original name of the field IdentifierID.
ok. now the fun part of the exercise. Creating a workflow on the list.
I created two stages: Main and Sleep.
- Main: does all the work, like call web service and calculate the ID
- Sleep: pauses for a minute for other items created previous to the current item to get their IdentifierID
Main:
- variable to sleep = No
- build the requestheader dictionary
- call web service
- https://MySharePointSite/_api/web/lists/getbytitle('NameOfList')/items?$top=1&$select=IdentifierID&$filter=(ID lt [%Current Item:ID%] and FiscalYear eq [%Current Item:FiscalYear%])&$orderby=ID desc
- the key is here:
- get the top 1 item
- filter by ID less than currentItemID and FiscalYear equals CurrentItemFiscalYear
- Order by ID desc
- get results into listitems dictionary
- get count of items in listitems dictionary
- this is so that I know if it is the first item in the new Fiscal Year
- then get the IdentifierID
- Process the IdentifierID
- go to End of Workflow
Sleep:
- Pause for a minute for other workflow instances on previous items to complete
- go back to main
