Home » Visual StudioRSS

Auto generate a number in a text box in Infopath Form

Hello All,

I have a requirement of generating the auto number in one of the InfoPath form field(text box).I am designing this infopath form for Change Request in the organization.Can anyone help me  in doing it.

I appreciate your response.

 

Thank you,

Smith

 

14 Answers Found

 

Answer 1

Smith,

You can create a sequence, Query the list and get the number  in the field:

using (SPSite site = new SPSite(siteUrl))
                    {
                        using (SPWeb web = site.OpenWeb())
                        {
                            // Get all the entries in the Ticket List
                            SPWeb oWeb = SPContext.Current.Web;
                            SPList oList = oWeb.Lists[listName];
                            SPQuery regQuery = new SPQuery();
                            regQuery.Query = "<Query><OrderBy><FieldRef Name='Title' /></OrderBy></Query>";
                            items = oList.GetItems(regQuery);
                        }
                    }

Now you would have the list elements in datatable items.

Get the last record so that you have the latest number.

Add +1 to the number and you would get the next number.

Set the textbox value in InfoPath VSTA:

GetCurrentXPathNav("<XPath>").SetValue(selectedListItems.ToString());

 public XPathNavigator GetCurrentXPathNav(String XPath)
        {
            XPathNavigator DataSource = MainDataSource.CreateNavigator();
            XPathNavigator XPathNav = DataSource.SelectSingleNode(XPath, NamespaceManager);
            return XPathNav;
        }

Pradeep

 

Answer 2

Hello Pradeep,

Thank you for your response.I am new bie in infopath  forms.Can you say me where to write this code.Do i need to write this in the Submit button of my infopath form.

I appreciate your time and help.

Thank you,

Smith

 

Answer 3

Smith,

You have to configure your server:

http://www.summsoft.com/blogs/garyvsta/archive/2006/07/26/Finding-and-Using-VSTA-in-InfoPath-2007.aspx

Then follow:

http://msdn.microsoft.com/en-us/library/bb251747.aspx

or

In the InfoPath: Tools> Form Options> Programming > set the programming language.

Then

Tools > Programming > Loading Event.

This will open VS.

Pradeep

 

Answer 4

I don't consider it wise to tell a newbie to immediately add code to their form.  There are non-code methods for doing many things, and there are likely multiple ways to go about creating unique IDs without code.  I just don't think it's good to immeidately go to a code solution and continue to propose that after the person says they are a newbie and don't know where to put the code - it is not a good way to give advice, imo.  He likely doesn't even have Visual Studio installed.

Smith, in my opinion, this is very easy to do without code as long as your ID does not have to be in the filename.  If that's the case, then here are the simple steps:

1) First, incorporate my Auto-Generating Filenames method.  Make sure that when you publish your template with this method that you promote the strFilename field so that it's saved into the form  library.

2) Next, create a new data connection to Receive from your Change Request form library (the same one where the form originates).  In this data connection, choose the ID and strFilename fields for retreiving.  Set the data connection NOT to automatically retrieve - we will only do it on demand during the initial Submit.

3) In your custom (non-code) submit button within the submit rule that you created as part of my blog, add a few actions.  Before the Close action but after the Submit action, add an action that queries the DC from step #2.  Then, add an action that sets your custom ID field to the ID of your DC from #2 but filtered where the strFilename field of the DC (SharePoint) is equal to the strFilename of the main data source (form).  After that, add another Submit.  The effect of the above actions will be that you submit the form with an auto-generated filename, then the form queries the form library, the returned data set includes the form you just submitted and includes the unique ID of that form,  you set the custom ID field of your form to the ID of the form you just submitted, and then you re-submit so that your CustomID field is populated, and then it closes.  If you want your custom ID to be something special like 1000-01, then you just need to use a concatenate formula in the action where you set the custom ID field.

 

Answer 5

When new item is added to SharePoint List or Library, then SharePoint creates unique ID number  and insert it to column named “ID”. It means SharePoint does generating  number automatically for you. Therefore, we can use this “ID” column in List or Library.

First, Create a new data connection as receive data from your SharePoint form  Library.

-          Select “ID” field in Data Connection Wizard. When query occurs, ID field value in Form Library will be returned to InfoPath form.

Second, let’s use your data connection

-          Go to Tools à Form options à Open and Save à Click Rules in Open behavior

-          Add action (Set a field’s value) à Put target field in Field à Click function button in Value à Click “Insert Function..” à Select “max” in Math category à You will have a string “max(double click to insert field)” in Formula field à double click underlined text à change  your data source to Secondary data source which you just created à select ID and click “OK”. à Add “+1” à your Formula field will be look like “max(@ID) + 1”. à Click ok.

 

Max(@ID) will return biggest ID value in Library and +1 will increment biggest ID value by 1.

 


??? - http://blog.naver.com/lightyea/
 

Answer 6

Hello Pradeep,

I know how to configure and start VSTA.So i got to know from your reply that i need to write it in Loading event.Thank you.

Thank you Clyaton too.I am looking into the information you have posted me.

Smith.

 

Answer 7

SPSite mySite = SPContext.Current.Site;
SPWeb myWeb = mySite.OpenWeb();


#region Get ID
SPSecurity.RunWithElevatedPrivileges(delegate()
{
	using (SPSite oSite = new SPSite(mySite.ID))
	{
 		using (SPWeb oWeb = oSite.OpenWeb(myWeb.ID))
     {
			SPQuery oQuery = new SPQuery();
       oQuery.RowLimit = 30;
       SPList oList = oWeb.Lists["yourListName"];
       SPListItemCollection collListItems = oList.GetItems(oQuery);

       oWeb.AllowUnsafeUpdates = true;
       oListItem["YourColumName"] = Convert.ToString(Convert.ToInt32(oListItem["YourColumName"].ToString()) + 1);
       oListItem.Update();
       oWeb.AllowUnsafeUpdates = false;
       setNodeValue("/my:myFields/my:YourTargetNode", oListItem["Number"].ToString());
		}
	}
});
#endregion

#region Set node value
public void setNodeValue(string path, string value)
{
   XPathNavigator root, infopathNode;
   root = this.MainDataSource.CreateNavigator();

   try
   {
    infopathNode = root.SelectSingleNode(path, this.NamespaceManager);
    if (infopathNode.MoveToAttribute("nil", "http://www.w3.org/2001/XMLSchema-instance"))
     infopathNode.DeleteSelf();

    infopathNode.SetValue(value);
   }
   catch { }
}
#endregion

Clayton and I provide your how to do it without code.

If you like to use code, then refer mine or Pradeep. Use setNodeValue() to set not value after you create/get uniqe number


??? - http://blog.naver.com/lightyea/
 

Answer 8

Sangha, that method requires that the default view has the item with the most recent ID, which also requires that the most recent item is never deleted.  My method cannot get the wrong ID.
 

Answer 9

Thank you Clayton, that’s good point.

My approach needs at least one item in List/Library


??? - http://blog.naver.com/lightyea/
 

Answer 10

Sangha / Clayton,

We can always set a condition that if the list is empty, set the default value whatever is required.

Pradeep

 

Answer 11

Another problem is that if you do it on form  Load, someone else may have submitted a form before you click Submit on yours, so you always need to run queries like this INSIDE your submit button.  The query to get Max(ID) must occur milliseconds before the submit action, so I recommend never doing it on Form Load.

One item in the library is not enough either.  It has to be the MOST RECENT ID, otherwise you get an ID that has already been used.  The default view may not show all items, it may not show the most recent item, and the most recent item may have been deleted.  It's best to submit, retrieve the ID of the form you submitted, then use that ID.  Otherwise, it's a risk.

 

Answer 12

That doesn't work either, because the default view may be empty, but that doesn't mean the list or library is empty.  Even if it is empty, it may not be a brand new list.  Deleting items doesn't restart the sequential ID numbering, which is again why you must get the ID of the current item by submitting it first and then retrieving the ID.  This works wonderfully unless there is a requirement  to put this ID in the filename, which I recommend not doing.
 

Answer 13

To assign a unique number  to form, I think that can be managed by creating a workflow . Try below steps. Would be easy users familar with creating simple WF using SPD.

Insert a Texbox in your infopath  form, "formNumber" and apply read only.  While publishing the form  ensure this field is made available to your list and  mark this field is allowed for edit.

- Create a New WF. Attach this worlflow to your "List"

- Set WF start option as "Automatically start when new items is created"

- Create an Action  "Set Field in Current Item".    Cilck on "field" and select "formNumber".  click on "value" then click on "fx" button.  select "Current Item" as  source and select "ID" as Filed.

Hope this will help

 

 

Answer 14

All that does is duplicate the built-in ID in a different field.  You don't need to do that when the ID is already created by SharePoint and stored in the form  library.  You can simply retrieve that ID into the form the moment it is submitted without the need for a workflow.
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter