InfoPath Dev

I'm building a relatively simple form that a customer will use for sales reports. This form is hosted using Forms Services in MOSS  - each salesman (about 50-100 persons) will have his own site with a form library. Each library uses a centrally managed Site Content Type (with fields published as columns) and each site is created from a template.

I have a field called 'Report Number'- in this field I want to generate, on opening the form, a unique ID number based on the user ID (windows login account name) and a consecutive number based on the forms existing in the library - eg: 'jonhil-12345'. The user ID I have already retrieved to populate another field so I can reuse that or use the defualt 'userName()' function.

The question is - how do I create the automatic number sequence - to start at 1 and increment within the existing library ?? 

(Note: the specific library cannot be referenced since this form template is a centrally managed Site Content Type and all the libraries will be set to use this - no forms are published directly to a library)

The traditional approach to this is to use a database to generate the next number. I don't know that SP has any ability to do this on its own.

Hi again Greg :)

SharePoint uses a column named 'ID' in all lists & libraries - this stores a numerical value for the list item. This *may* be enough for what I need. The question is then, how could I retrieve this from the list into InfoPath?? Do I need to create a separate webservice for this or is there one in SharePoint such as the http://server/_vti_bin/UserProfileService.asmx that can be used??

On the other hand...I just found this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1818142&SiteID=1

The problem with attempting to use the ID value is that it is not assigned until AFTER the form has been saved to SharePoint. As you want to assign it when the form is first created, this will be of no use to you.

The posting you refer to looks interesting, though I get nervous when I see the term "unsafe" thrown around in there.

Generating a GUID is always an option.

Yeah, I noticed that last comment about the GUID - shame there's no clue here on how to do that. Any pointers??

A GUID is easily generated from managed code (C# / VB.NET):

string uniqueId = System.Guid.NewGuid().ToString("N");

the "N" portion tells it to leave out all the dashes. If you want those, just use .ToString() instead.

I can give you my implementation of an Unique Identifier for an InfoPath 2007 form rendered by InfoPath Form Services in MOSS2007. This implementation does not generate the ID on opening the form, but on saving. I made this choice because the ID is retrieved based on the already existing forms. And in the time between opening the form and saving the form, another customer can already save another form. Allthough it requires you to reference the form which is not possible in your case, maybe you can get some idea's from it instead.

(Don't forget to update the XPath queries to match your datasources.)

1. The first step you should do is creating a Field "ReportNumber" of type Whole Number (integer).
2. The second step is creating a Field "DocumentName" of type Text (string).
3. Then publish your InfoPath form to a SharePoint Document Library (i.e. "Sales Reports") and promote the field "ReportNumber".
4. Create a new Data Connection named "Retrieve ReportNumber" to "Receive data" from a SharePoint Library. Fill in your site URL and select the Document Library "Sales Reports" and only check the Field "ReportNumber".
5. Create a new Data Connection named "Main submit" to "Submit data" to a SharePoint Library. Fill in your site URL and fill in as File Name: my:DocumentName. Select "Allow overwrite if file exists".
6. Go to Tools - Submit Options, select "Perform custom action using Rules" and create the following Rules:
• "Retrieve Last ReportNumber"
• Condition: DocumentName is blank
• Action: Query using a data connection: Retrieve ReportNumber
• "Set ReportNumber For First Request"
• Condition: Number of occurences of ReportNumber = 0 and DocumentName is blank
• Action: Set a field's value: ReportNumber = "1"
• "Set ReportNumber For Other"
• Condition: Number of occurences of ReportNumber != 0 and DocumentName is blank
• Action: Set a field's value: ReportNumber = xdMath:Max(xdXDocument:GetDOM("Retrieve ReportNumber")/dfs:myFields/dfs:dataFields/dfs:YOUR_FORM/@ReportNumber) + 1
• "Insert New Form"
• Condition: DocumentName is blank
• Action: Set a field's value: DocumentName = concat(my:gpContactSelector/my:Person/my:DisplayName, "-", my:Global/my:ReportNumber)
• Action: Submit using a data connection: "Main submit"
• "Update Current Form"
• Condition: Document is not blank
• Action: Submit using a data connection: "Main submit".

If you got any questions, let me know.

A slightly different alternative to what you are doing:

1. Submit your form to SharePoint.
2. Query SP for your form and get it's unique ID.
3. Store that unique ID into your form.
4. Resubmit your form over the original submit.

When you use the query data does it by default pull the report number from the last submitted form? Or is that specified in this bit of code?

• Condition: Number of occurences of ReportNumber != 0 and DocumentName is blank
• Action: Set a field's value: ReportNumber = xdMath:Max(xdXDocument:GetDOM("Retrieve ReportNumber")/dfs:myFields/dfs:dataFields/dfs:YOUR_FORM/@ReportNumber) + 1

That rule will indeed pull the report number from the last submitted form (if you have promoted the ReportNumber as a column). But as you can see at the condition, this is only when there is at last one report.

The rule "Set ReportNumber For First Request" is triggered when there are no reports and the ReportNumber should start at 1.

yeah that makes sense. also could you explain what this is doing?

"Condition: Number of occurences of ReportNumber = 0 and DocumentName is blank"

 I understand its for checking to see if any reportnumbers exist and if not it sets the value to one. But how would I set this in infopath. Should i just be pasting this in xpath?

Thanks. I am really new to this. 

This very cool approach .  But I must say I´m little bit confused about the condition in “Set ReportNumber For First Request”  because in my test I´ve not been able to meet this condition.  In the condition “ReportNumber  = 0” I´m not sure whether you are using the Field “ReportNumber” or the secondary data source “Retrieve ReportNumber” column “Report_Number” ?  Because if I query the SharePoint library, with no item in it, there is no result/count returned and there for never "0".  Could you/someone clear this to me.  I hope I´m making some sense ;)     

Yeah I did not qute understand that. He is not setting reportnumber equal to 0. He is actually checking for how many times reportnumber occurs in the list. If it is 0 then it is set to 1. Unfortunately I dont understand how to set that condition. If you figure that out please let me know.

You can set the condition to 'number of occurences' by doing the following:

Click 'Set Condition'

Choose 'Select a field or group' from first drop down

Browse to <DATASOURCE> -> datafields -> <FORMNAME> -> ReportNumber

Click on it, then in the bottom drop down where it says 'select' choose 'Number of Occurences of ReportNum'

Choose Ok, then choose is equal to 0 and the action to set the field in MYFIELDS to being 1

Jon

Thanks Jon for quick response.  This was just what I needed and now it´s working as expected.