InfoPath Dev

I have created a integer field in my form called SortOrder that uses the 4-digit year date part + 3-digit number, Ex: 2013000, 2013001,2013002 etc. (I will actually display it in the library in another field like this: 2013-000, 2013-001 etc).

Here's how my sequential numbering works:

I have a field called SortOrder. If there are no items in the library SortOrder will become YEAR000 (or 2013000, 2013001, 2013002...), then the next item will become max(@SortOrder) +1 as long as the library is not empty.  If all items were deleted from the library, the numbering would start over OR if there are existing items in the library BUT it's a NEW YEAR,  the numbering will start over by multiplying the 4-digit year * 1000.

On form load rules there is a Year date check: if [substring-before(today(), "-") - substring(max(@SortOrder), 1,4)) = 0], then [max(@SortOrder) + 1];  if Year date check = 1, then [substring-before(today(), "-") * 1000]

 This works when the same person is always creating the form or is in Design mode on local machine, but it randomly works when others create the form from the library directly.  For example, if library is empty user1 creates a form 1st, it starts with 000, if user 2 creates a form next (2nd), it may start with 000 or 001,  if user1 creates their 2nd form or is now the 3rd item added to the library, it may start with 001.

 I am setting permission by item on the library using a SPD workflow but I removed the workflow (i.e. permissions) on the library to rule out that being the issue and it still behaved this way.

 Is there something obvious I am missing with the numbering?

 I am not using any special qRules or DBXL - it's a fairly simple form other than there are 20 views.

Update: the solution is to remove the item level permissions.  I need the item level permissions too, so I will have to come up with a way to do both but for now this post is done.  Thank you to myself for the answer.

Hi Doreen,

If you have a workflow on the library, you need to make sure to add a condition to the Impersonate Step that checks the status of the form and when the form does the first of two submits, make sure that status is not set to a value that will run the workflow.

Here's an updated post with your 10 steps to enlightenment.

http://www.infopathdev.com/forums/p/26341/91512.aspx#91512

You're on the road to Lhasa but watch out for the toxic nuclear waste provided by the ROC.

:)

I am using SPD 2007 - I don't see an "Impersonate" step?  Is this in a later version?  I can see how this could be very useful in my case.

I was also thinking if I could put SharePoint column level permission on the one column the form  needs to get max(number) from,  that might be a way around this. 

In the meantime I am promoting the number field as "editable" by the admin user so they can manually set the next number when a user without permission causes the number to duplicate....not ideal but until perfection is acheived, my solution. 

p.s. what do you mean by this: "You're on the road to Lhasa but watch out for the toxic nuclear waste provided by the ROC."

:(

Secondly, I tried creating another library, with open permissions, and promoted just the field in the form template that I want to query and the user of the current form still cannot access all items in that new library.

you don't care about permissions on your library because you won't be using max(ID). you are submitting, querying the item submitted, concatenating your ID with the ID returned, and submitting again, so the user only has to have access to the form she is submitting and the workflow shouldn't take those permissions away until after the second submit

I understand your idea with the ID, I have it working but that does not satisfy the requirement for the form. 

I have to retrieve my own unique number using max (number) and add 1 to it.  

The technique I showed in last Thursday's webinar will return the current ID without using max (@ID) and it is bullet proof and best practice.

The popularized max (@ID) technique is flawed for the following reasons:

• Simultaneous submits create forms with same ID
• Doesn’t work for views with permissions (just ask Doreen)
• May require that the entire list/library be queried -> not performant!

So, please stop saying max (@ID). It's just bad and has no redeeming value. :)