Stump the Panel: SharePoint Q&A

Ok, that seems simple enough. One option is to just have a dropdown with a list of project identifiers that you could choose from on each new item and use a calulation to pre-pend it to the final identifier column (or a text field where it can be manually entered), or as you suggested, just keep them in different libraries.



The reason I was asking was just to see if we could leverage the built-in versioning capabilities, but if its too much to switch to numbers, then you can simply just group by the automatically incremented number field to see the different versions (this would mean that each new version [A, B, C, etc] would be a new document added).

So, what it appears that you need is just a way in which to have an auto-incremented field that you could then concat with the fixed project identifier and version letter...sound about right?

- Dessie

Exactly!

Ok, I'm going to play with the existing solution I have for creating the auto-incrementing number and see how I can apply that to a document library with your specifics. I'll let you know once I get something working, and you'll see it as a new article posted on the main site.

- Dessie

That's fantastic! Thank you.

I look forward to seeing what you come up with. I've found solutions here and there but most deal with the ID number - useful, but I could run into problems if I mess up and need to delete something, or have other documents in the library besides official design docs. This would create holes in the numbering scheme.

Thanks again!
Best,
Emily

Ok, couple more questions...

Is this a library that you'll be managing yourself, as in: are you the only one adding items to it, or would others be as well?
Do you need approval on the documents added? Or do you normally just upload them and call it good?

What is your preference on workflow initiation? I ask this one because of the following gotcha's:

On Item Creation:
In a list, when you create a new item, you're filling in all of the needed fields at the time of creation, whereas in a document library, this is accomplished after the document has been uploaded. Because of this, having the workflow initiate on item creation will not work correctly because it will be looking for the existance of specific metadata (columns populated with information) that will be added after the document is loaded. "Pause" actions could be added to the workflow to help accomodate this, but they're unreliable sometimes and may or may not work as intended (known problem - even after updates and SP'sthat have supposedly fixed the issue).

On Item change:
The logic of this particular workflow has a few item updates in its logic, so initiating the workflow on item change will make it error out because it will be calling the workflow again on each update (endless looping scenario).

Manually start:
So far, this seems to be the best and only option because in order for the workflow to work without errors, all pertinent fields must be filled in before the workflow can run...and as in the first scenario of initiation on item creation, none of these will be populated until after the document has been loaded. This in itself may be enough for this process to not work out for your specific needs since part of the idea here is to be able to move you away from as much manual operation as possible...so its up to you.

To give you an understanding of why "Manual" initiation of the workflow will be needed, here's a breakdown of the solution I was able to come up with:

We have a "Master" list which contains a single item - the "Next Available Number", which we will be using as our auto incrementing number for our Project ID.
In our document library, we have a "New Project" field (lookup) that points to the "Next Available Number" and will be used in the "xxxx" portion of our final Project ID. We also have a "Auto Number" field that will be updated with the "Next Available Number" via the Workflow - this is neccessary because we'll be deleting the existing item on the "Master" list and replacing it with a new "Next Available Number" item after we've used the current number to build our Project ID...and, in order to match up the existing "Next Available Number" item on the Master list with what we want to delete, we'll be comparing its item ID (SharePoint generated) with that of the current item ID that we're creating in the document library. It gets a bit confusing when trying to explain how it works, but the walkthrough I'll be posting as an article later will better illustrate it with screenshots :)

Also, in the document library, we have the "xx.xx" portion of the final Project ID as a choice column containing all of the values we'd need, and another choice column for the "A, B, C..." version values.

Next, we have our calculated column which brings together the "Project Code" ("xx.xx"), "Auto Number" ("Next Available Number"), and "Version Code" ("A, B, C...") and concats them together as the final Project ID ("01.01.1234.A").

We also have an "Existing Project" field (lookup) that is connected to our current library and is used with the "Version Code" values in order to match up any projects that are of any type other than "A" and not increment the "Next Available Number" item on the Master list (no need to increment it if the document is just a "B" or "C" of an existing project - instead, it will just build a Project ID containing the version and add it to a "Grouped" view of all related projects).

The workflow itself checks to see what the version is (IF "A" do this, else IF not "A" do that), then gets the "Next Available Number", increments it if needed, creates a new item on the Master list (will always do this, even if its not incremented - this insures that the next document added will always be able to match up with the system ID between itself and the Master list), then deletes the old one (again this step will always occur in order to keep the ID's matching for comparison in the workflow steps).

The last piece is the update that occurs on the new document added to the library (or rather, the last if its version is "A", and first if its version is not "A"). This update will take the value from the lookup and write it to the "Auto Number" field because after we've deleted the corresponding item on the Master list, the value is removed from the lookup column (we actually capture it at the beginning of the workflow and assign its value to a variable so we still have it after the deletion takes place).

After the workflow completes, the Project ID is built and displayed and we either have a new item added with what was the last "Next Available Number" value, or if adding a new version, the Project ID reflects the new version letter and contains the rest of what made up the previous version's ID ("01.01.1002.A" remains, and we add a new document with the ID of "01.01.1002.B").

In the view, I'm grouping by "Auto Number" in order to group together all versions - and the "Auto Number" field is the only one that is common between them (although a new column could be created that concated the "Project Code" and "Auto Number" [xx.xxxx] and grouped by that, but thats a simple addition if needed that doesn't affect any of the above functionality).

So, after this lengthy explanation, does this sound like something that would work? Even if the workflow has to be initiated manually? (haven't figured out a way around that given the requirments of having both an auto-number, and the ability to re-use existing numbers for versioning).

Let me know what you think...

- Dessie

Hi Dessie,
This sounds GREAT! I agree with you that starting this workflow manually is the best option.

Although I will be the primary uploader, part of the goal of this project is to remove myself as the bottleneck in the system and create more transparency. A manual start (a figurative "assign number" button, if you will) will allow for the inevitable oops moments as more people become involved in the drafting process.



Approval workflows are one of the reasons I think SP lends itself well to our system. The current practice is to manually fill out a form, print it out, chase signatories around with form, lose/spill coffee on/incorrectly sign form, and then file it somewhere eventually. The documents themselves are "approved" simply by my moving them to a different, restricted folder. I am thrilled to be able to have this information travel with the documents so we can report on them in a more efficient, informative manner.

I can't wait to see this. I've already started playing with several of the pieces you describe but look forward to seeing it done in the correct order.

One small problem - I forgot that the different doc types are numbered a little differently. For example, Parts are 1xxx, Tools are 2xxx, Reports are 3xxx, etc. This could probably just be generated through a choice column and added on later, correct? Or, could we generate lists for each series and simply point the workflow to the list depending on the user's choice of type? Sorry to throw that in so late, hope it doesn't cause too much of a problem.

Best,
Emily

PS: By approval workflows I meant a way to make them "Final"; straight uploading at the beginning is fine.

The reason I was asking about who be initiating the workflow is because of the design of the solution...and its breaking points.
The "Master" list needs to be updated by the workflow, and since workflows run under the context of the user who initiated it, then whoever would need to be able to run it would need to have contribute access to the "Master" list. And, since the ID's need be able to match for creation and deletion, if someone were to go in and start adding/deleting things in the "Master" list directly, it would cause the workflow to fail because the ID's would then be out of sync.

So, its something to consider and be prepared for if others need to be able to run this solution aside from yourself. Fixing it if it breaks is not a huge deal, just involves creating and deleting as many "junk" records as needed in order to bring the ID's back in sync.

On the "Approval" question, it wasn't meant as you needing to get approval literally before you can upload something, rather I meant that when you upload a document, did you want it to be in a "Pending" state prior to initiating the workflow or would you be uploading documents that were "Live" versions from the get go. By placing them in a "Pending" state and having the workflow first check its "Approval Status", it would make sure that only the "Approved" documents would get an Auto-Number assigned and go through the final "Project ID" building phase. It was more of a "checks and balances" thing...so, if its not needed we can skip that part.

On the different doc types needing different numbering schemes, you could make the "Auto Number" 3-digits and provide (in a choice column) a field that you can select the type from...then add it to the concatenation in the final steps. I was just using the 4-digit numbering scheme since that's what you had originally detailed, but if the first number of the 4-digit scheme will differentiate the types, then a 3-digit auto-number would work also.

- Dessie

Emily,
I'm in the process of writing up the article series. I'll post a link here once its published.

Didn't want you to think I had forgotten about you :)

- Dessie

Thanks for checking in! I hadn't lost faith :-) And I have no shortage of work to keep me busy in the meantime.
Emily