InfoPath Dev

I have a sharepoint 2010 list that contains strings that were concatenated with numbers. The ID column was used (I didn't develop this) and now there are issues since the ID Column somehow changed when the site was migrated, so it needs to be replaced with something else. Is there a way using a formula that I can query sharepoint and somehow pull the largest number from a string on a list? I have a translate function working, but it doesn't seem to be able to find the max one, it just translates the first item on the list.

Hey Bro-grammer,

This questions comes up a lot and there are numerous posts on using the max(ID) xpath function, but it doesn't work well when there are permission restrictions on views or people submitting at the same time.

If you are using an InfoPath form, I recommend:

1. Create a field in your form called "key" and make it a promoted property (file->form info->promoted props)
2. Publish your form to the library so that it gets the new promoted prop
3. Add a retrieve data connection to your form that queries the SharePoint library where it is being submitted, call this "FormQuery"; make sure you include the "key" column
4. Create a rule on your submit button called "SubmitWithUniqueID" that has an initial action that sets the value of key equal to the xpath now() function (e.g. 2013-07-20T17:08)
5. Add an action to the submit button after that to submit the form to your library (if you don't already have one)
6. Add another action that sets the key field in the secondary FormQuery data source's query parameters to the value of key in the main data source
7. Add another action that queries the FormQuery data connection - this will return the form info you just submitted
8. Add another action that sets the ID in your main data source equal to the ID column returned from the data section of your FormQuery secondary data source - that's the current max ID
9. Add another action that submits your form with this new ID set
10. Close your form

That's it.

I just wanted to share this tidbit to help others avoid my mistake - this doesn't work if you are querying a default view that is grouped.  It took me a couple hours to realize why I was returning the same ID for every item added.  I erroneously got the bright idea  to group my default view (in this case, All Documents)....it broke the query for the next ID.

HTH

Thanks HTH for posting and sorry to hear that you had to wade through that swamp.

When setting up the query on SharePoint 2010, there is a checkbox for returning just the active form only (see picture below). I wonder if that provides a workaround.

Another idea is to simply use DBXL's Query SharePoint command, which can be passed a view. It's much faster than ShP anyway and handles more complex filtering.

 

Thanks, I tried your suggestion, with default view grouped and it still fails to find next highest ID,

Update: Trying this actually created a problem for me which I didn't realize when I first posted this remark. It caused the step: '@key = key' to fail with error message: "parameter not applied". I thought I'd done something wrong and didn't correlate that the problem started when I ticked that checkbox you suggested until this morning. I left it checked after my test thinking it harmless (was too focus on the grouping of SharePoint view being the cause).  Now that I've  deselected "Include data for the active form only" rules are able to retreive @ID (aka @Key) again. Phew.

http://www.youtube.com/watch?v=LcxYwwIL5zQ

UPDATE: I ran into an issue with step 8 of Patrick's steps.  I recently realized this numbering was not working correctly in a form I created when I tried to replicate these steps in a new form. My former form numbering appeared to be working only because the default view is being sorted by ID in descending order.  According to Patrick, this method is supposed to select the active form being submitted which naturally would be the next max ID (but it should not pick the physical top ID in the list) and therefore it shouldn't matter what the View's sort order is.  To correct this, a clarification should be inserted after step 8:

"8.      Add another action that sets the ID in your main data source equal to the ID column returned from the data section of your FormQuery secondary data source - that's the current max ID. "

Clarification:

8.1  Filter on @ID from secondary source FormQuery.  Step 8 Add Action should look similar to this:  Set a field’s value: LibID = @ID[@key = key]

p.s. Maybe that's what you mean in step 8, but it obviously was not clear to me so it may not be clear to others. HTH.