Workflow; Use previous document library item to populate field

Question

This one has stumped me indefinitely... so I'm coming to the great guru's of the confusing concepts.

CASE:

I have an InfoPath form which is connected to a document library. Once the form is submitted, it will create a new form (like usual), and on create it initiates a WorkFlow which will turn into a huge complicated spaghetti bowl. However, the only thing I want is to increment an ID number of sorts (We'll call it f-number). It must be formatted with three digits (E.g. 042, 120, 002, etc.).

So, the steps:

1. The f-number must be incremented from the previous item
2. Formatted as a three-digit number
3. Automatically populated inside the current item

ATTEMPTS:

I have created two columns inside the document library; the first is the calculated value of f-number + 1 (calc-f-number). The second is the formatted text (Using the TEXT([COLUMN], "000")) as three-digits(formatted-f-number). Then I created a separate custom list called AutoID with a column called prev-id-number. Inside is a single item with the ID number of the previous item. During the workflow, it is SUPPOSED to: (This is formatted like the WorkFlow for ease of translation)

• Set f-number inside Current Item based on:
  • formatted-f-number column inside Document Library, by the Document Library ID equal to:
    • the prev-id-number inside AutoID custom list that has ID equal to 1

Then set the AutoID to the ID of the Current Item.

For some reason, when I try to select prev-id-number, it will only let me select ID, UI Version, URL Path, Item Type, Level and Path. Perhaps there is a MUCH MUCH simpler way to do this, but through the tiny bit of information research churned up this seemed like the only course of action.

QUESTION:

First and foremost, is there an easier way to do this?

Or how can I select prev-id-number? Could it be based on actual column TYPES? If so, why does WorkFlow only allow you to select certain column types?

Answer 1

Christopher,

You can do the auto-increment in the InfoPath form during submission. Create a data source to the list that the form is published to and only lookup your incrementing column (f-column) and query at form load. Then during submission, set a field (f-field promoted to f-column) with the following rules:

Rule 1:

• Condition

  1 + number(xdMath:Max(xdxDocument:GetDOM("[data source]")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:F-Column)) > 0

  AND

  F-Field does not match pattern Custom Pattern: 0

• Actions

  F-Field =

  1 + number(max(F-Column))

Rule 2:

• Condition

  F-Field is blank

• Actions

  F-Field =

  number(1)

I am unsure of how the formatting should be set up. The problem is that f-column needs to be set up as a number column and I'm fairly certain that any number you put in there will only display significant numbers (no leading 0's). If I find more information on the formatting side of this issue, I will update my post.

Answer 2

Could you just use the workflow to match the ID without using the calculated column.

In the workflow do something like

1. Get previous item (SharePoint) id number by doing performing a calculation action of "Calculate 'CurrentItem:ID' minus 1 (Output to Variable:PrevID)
2. The create new DocID "Calculate 'Get Source - This is a defined lookup, look at the same list choose the DocID for the item and then in Find the List Item choose the Item ID and match the value to PrevID determined in Step one, this will get you the previous DocID' plus 1 (Output to Variable:NewDocID)
3. Set DocID to Variable:NewDocID

I wish I had more time to detail this out some more, but let me know if this makes sense.

Answer 3

I would need to think more for a better solution, but a way to get it to work...

Create a separate list and the only thing in it is Title set to 1, and a lastID column that gets continuously updated to the most recently added value.

All you have to do is search through that separate list where Title = 1, and you will have the previous ID. And of course every time you get the previous ID, you update it.