InfoPath Dev

Good day all,
As we've increased the number of users submitting requests using our IP filler form (2010) to our sharepoint site (2007), we're seeing duplicate IDs being created using the Max(ID)+1 method of generating an ID.  The form is the first one I had ever created and using the Max(ID)+1 seemed like a great option, but - of course - now that I'm more experienced with how IP and MOSS work together (and the more I read) I see that this ISN'T the greatest option. 

We have so much traffic now, and a super larger form, that there are times when it takes a bit longer than normal for the new form to submit.  While one form is in the process of submission to the sharepoint site, if another person clicks submit, they get the same list ID that the previous user got and... voila, dupe IDs.

I'm feeling a bit stuck on a means to fix this...since this issue didn't arise (of course) until we've gotten everyone on the form.  I need to ensure my "fix" doesn't cause a worse issue that dupe IDs now and then. 

My first question, is there possibly a different way to set up the Max(ID)+1 than how I currently have it set up to make it work more effectively?

Current set-up:  I have the query to the Sharepoint list for the last ID and the setting of the Request ID using Max(ID)+1 field running when the user clicks the submit button.

If there isn't a better means for set up, is there a way to change the name convention on new requests without causing current requests to use that new naming convention?  The only reason we're using the Max(ID)+1 was to add the ID to the name, but we'd rather have non-duplicate IDs and don't mind changing naming convention to something else.  However, I don't want the current 1000 requests out there to be recreated with new names since that would obviously be another issue.

Anyone have ideas??  Thank you!!
Jen

Check your list that you submit to  (On Submit Rule)  and compare these values of the New Form ID(one you set on new form) and the Max ID+1/uniqueId already on the list.
So if One is present/equal already there you can change your new Form Unique ID. +1 or so

 Then Submit! 

Also it might be helpful to concat Your MaxID+1 with different parts of the form that create a unique value.

Thanks jnava121,
Let me see if I am understanding what you suggested.

On submit, run a rule that compares the number to be set in RqstID field in the new form to see if matches any numbers already in that field in the the list.  If it doesn't, submit; if it does match, add another 1 to the ID.
Does that sound about right?

My only thought on it is that I currently use the Systematic ID from Sharepoint as the ID in the MaxID formula.  Sounds like I may have to use the RqstID as the ID in Max(ID) now otherwise I'd always be matching the next RqstID once the first one is off.  i.e. after the first "fix" I may have a system ID that is 101 and a RqstID that is 102.  On the next form, if still reading the system ID, it will try to make another RqstID 102, see it's there, create a 103 and then continue to just have to use that second formula.

Thoughts?
Jen

 First part sounds right, I'm guessing if 2 people open instances of the form but don't submit , when you grab the MAX Row ID it would be the same. Could cause some issues. Making the same number depending on your rules or filenames? That would be on Form Load but OnSubmit maybe grabbing the Max(ID) on submission would minimize the chances of simultaneous duplicate numbers.

Might have to test it some to make sure it doesnt increment away from the MaxID of the Row in SPList if thats what your goal is. Hope it helps!

 I would not grab the MAX(ID) until submit. Mainly because I can open a request, and I may decide it is not needed. so the user doesn't need the ID until the item is submitted. On submit, grab MAX(ID), set your field values, then submit form.

 

Another possibility is to do a double submit. I had a similar issue, On form load I would submit the form, invisible to user. Now I have an item with an ID and I can grab that ID. I was able to accomplish this, by auto generating my filename, I used a string that matched a date/time stamp. it is only the filename and didn't really matter. having a file name allows the form to be saved. then you can grab the ID from your DS where form filename matches sharepoint column filename

Thanks for the reply.  I currently don't run the Max(ID) until submission, but the problem is still occurring due to server response times and the number of users.  If one person's submission gets delayed, the next one being submitted (close to the same time) reads the same sharepoint ID for the formula.

I'd love to do the double submit, but we're currently using the ID in the file name.  Which gets to my second question in the original post... I would happily change that or look into another way to generate an ID, but it would cause all of my current items to generate a new file name and create duplicates.  Is there a way to make a change to how the file name submitted WITHOUT having that happen?

 your only other resort would be with code. You could query the SP list in SQL and store the ID in a secondary table. using something like this will allow you to lock the ID so it cant be used again. then with a SPROC you could query the second table to get your ID. No max(ID) any more. I have to say this is a much more complicated way of accomplishing this, but it will yield the results you are looking for.

 

You can try this: set a rule on submiting your form, compare laste update person(Main) on the form with last update person in SharePoint library(Secondary), if the two are not the same, bring user to a system confilct message page and direct user to submit again. Else submit the form successfully.

Another way around this without doing the comparison is:

1. Add a field to your form (let's call it "key") with default value of concat(userName(),"-",today())
2. Promote that "key" field and republish your form so it shows up in the library
3. Create a data connection to the library you are submitting to and make sure to include the new "key" field and the ID for the row (included by default)
4. Add a submit to your form (or use existing) and make sure it is set to overwrite existing
5. After the submit, add a rule with the following actions
• sets the query data for the "key" field equal to the key field
• query the data connection you created in step 3 above
• set the ID in your form equal to the ID of the returned ID data field
6. Submit your form again  

The problem with the comparison is that it doesn't prevent against duplicates completely because there can also be contention around the comparison.

The above approach is simple and fool-proof and doesn't result in duplicates.

 I have the same problem, but thankfully I have this guide to help me through.

Patrick,

 I employed this approach on my form, as I was having the same problem with duplicate file numbers when using the max(ID)+1 formula. I am not using a browser-enabled form. We were needing our "file numbers" to start at 8000, and after our multiple test form submissions before our form library went live, I ended up using the formula max(ID) + 7980 to come up with the right numbers. We were getting multiple submissions at the same time and thus our file numbers were duplicated.

I followed all the instructions above, but when I test it, it still assigns the same file number if forms are submitted at the exact same time.

Do you have any thoughts on what I could be doing wrong?

I'd very much appreciate any feedback.

Thank you!

Claudia

 the only true way to a void this is not to use max(id). I have needed this same scenario many time and these are the steps I take. I have a filename field. On form load I check if it is blank. if blank I set filename using this formula:

concat(substring(xdDate:Now(), 6, 2), "_", substring(xdDate:Now(), 9, 2), "_", substring(xdDate:Now(), 1, 4), "_T_", substring(xdDate:Now(), 12, 2), "_", substring(xdDate:Now(), 15, 2), "_", substring(xdDate:Now(), 18, 2))

this will always give you a unique filename

Onchange of filename I submit the form

query the Library (data source)

get the form id where Filename(promote column) matches Filename (form field)

then I set that value in the form to what ever u need it set. the save is invisible to the user and much better chance of getting ur unique ID