InfoPath Dev

In my Infopath form I have a text box and a dropdown control that is populated by a SQL Server table.  In some cases none of the available values in the dropdown list will apply to the user.  In this case I would like the user to have the option to add a new record to the SQL table that populates the dropdown.  I would like to do this with code behind a button.  So, process would be: User looks at dropdown list. If the value is not listed user fills out the textbox and then clicks the button.  The button creates a new record in the SQL table using the textbox value.

Does anyone know how and if this is possible?  If my problem is too specific for the forum please suggest articles/other forums I could review.

Thanks.

Adding records from the form would best be handled in a stored procedure.  That way you can enforce your validation on the server and respond with a descriptive error if the record already exists or you have other constraints not met (e.g. name contains invalid characters, etc...). 

Here are two links on this topic:

http://www.infopathdev.com/blogs/jimmy/archive/2009/08/01/executing-a-stored-procedure-with-output-parameters.aspx

http://www.infopathdev.com/blogs/shiraz/archive/2007/12/21/how-to-work-with-stored-procedures-by-using-script-in-an-infopath-form.aspx

Thanks Jim.  Can stored procedures be run by Infopath form via the browser?

Ya, you can definitely create a Connection as we do for a normal dot net application.And pass values to sql table.

Hi Stacie,

If your SQL table already has constraints, stored procedures are a good way to go. Hilary has blogged this recently here: http://www.infopathdev.com/blogs/hilary/archive/2009/10/13/use-dbxl-submit-with-existing-sql-data.aspx

If your SQL table does not have any constraints, however, the best practice here is to not write any code and use a separate XML doctype to update the table and use a mapping in DBXL. Assuming your SQL table has <20 or so columns, it's easy to concatenate an XML together and submit it as a separate doctype. In DBXL, you can map the doctype to the SQL table. This will allow your form to update the list.

Good luck,

I reviewed the links provided and in their instructions it requires that I design the form from a SQL table.  I don't want to do this as I don't want to show any of the fields in this table on my form except for the dropdown list.  I can't use DBXL for this because I already have a Submit button on my form that is using DBXL to push the data to a different SQL table.  The purpose of this is merely to create a record in a SQL table if the value does not show in the dropdown list on the form.  Basically if the user does not see their value in the dropdown list they click the button and create a new record.  When the button is clicked the code should run the following stored procedure (the input parameters will be pulled from values user has keyed into the form)

 create PROCEDURE addnewproject @name nvarchar(50),@branch bigint, @owner bigint
AS
SET IMPLICIT_TRANSACTIONS off

INSERT INTO projects (name, status, branch_office, owner) values (@name,'3',@branch,@owner)

GO

Okay, so I've been Googling like crazy to figure out how to do this and it looks like that I cannot submit directly to SQL if my SQL table is not the main data source...correct me if I'm wrong on this. What I've read is that I must submit the data to a web service that works with the database.  I will already be using DBXL to submit the entire form to a database table so I'm not sure that I can use it again just to add a record to another table.  Any suggestions/thoughts???

 Please observe the forms in the attached ZIP.  Please add both forms as new doctypes in DBXL.  Use the doctype name ProjectForm for the ProjectForm.xsn file, and any name of your choosing for the other form.

• The ProjectForm will have a mapping to your Projects table in the database (please add a mapping for it)
• The other form will demonstrate how to submit forms to this doctype without submitting the main data source.  Please open this form from DBXL, fill in a few values, and click Create Project.  This should cause a form to be submitted to DBXL.

Now please open the SubmitProjects.xsn form in the InfoPath designer to see how it works.  There are several things to note here:

• The forms are being submitted using a Receive (instead of Submit) data connection to DBXL's SubmitDocument web method.  It's possible to use either type of data connection, but using a Receive connection offers the benefit of being able to set the arguments using rules (instead of binding them to fields), and easily accessing the result and errors returned from the webmethod.
• When setting up the SubmitProject data connection, I specified "ProjectForm" as the doctype name so it will always have this value by default, and unchecked the "Query data when the for is opened" option.
• I added the template.xml file from ProjectForm.xsn's source as a secondary XML data connection in SubmitProjects.xsn.  This allows you to store the project values in a location outside the main data source before they are submitted.
• I'm using a rule on the button that utilizes the concat() formula to build the XML to submit using the values in the ProjectsDom data source.  That full formula looks like this:

concat('<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:ProjectsForm:-myXSD-2010-02-27T08-03-19" href="manifest.xsf" solutionVersion="1.0.0.3" productVersion="12.0.0" PIVersion="1.0.0.0" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-02-27T08:03:19" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003"><my:name><![CDATA[', xdXDocument:GetDOM("ProjectsDom")/ns1:myFields/ns1:name, "]]></my:name><my:status>", xdXDocument:GetDOM("ProjectsDom")/ns1:myFields/ns1:status, "</my:status><my:branch_office>", xdXDocument:GetDOM("ProjectsDom")/ns1:myFields/ns1:branch_office, "</my:branch_office><my:owner>", xdXDocument:GetDOM("ProjectsDom")/ns1:myFields/ns1:owner, "</my:owner></my:myFields>")

Though it may look intimidating, all I've done here is take the XML from the template.xml file of ProjectForm.xsn's source, removed all the line breaks, and split it up in the location where the values go.  The XML is surrounded by single quotes to represent it as string values, and this is interspersed with XPath to ProjectsDom's values (I added these using the Insert Field or Group... feature).  Notice that I've also removed all of the xsi:nil="true" attributes, and added <![CDATA[ and ]]> before and after the location where the name value goes, to prevent any problems with XML symbols that might be present in the name.

Please give this a try, and let me know if you have any further questions.

Thanks Jimmy.

Can you explain how I do this part?

"I added the template.xml file from ProjectForm.xsn's source as a secondary XML data connection in SubmitProjects.xsn.  This allows you to store the project values in a location outside the main data source before they are submitted."

 Open up ProjectForm.xsn in design mode, and from the File menu select Save as source files... and choose a directory for the extracted source files.

Then in your main form, go to the Add Data Connection dialog, select the option to add an XML secondary data source and select the template.xml from the directory where you extracted ProjectForm.xsn's source.  Use all the default options "Store the file as a resource, query on open" but give the data source a more meaningful name than just "template."

Hey Jimmy,

Have a couple more questions for you.

 "I'm using a rule on the button that utilizes the concat() formula to build the XML to submit using the values in the ProjectsDom data source.  That full formula looks like this:"

I looked at the rules in your button and don't see where you added the concat() formula.

Also, on my main form, the fields that are collecting the Name, Branch and Owner are located in the Main source, do I need to pull the fields from the secondary source? If so, how do I handle the fields for my Branch and Owner as they are dropdown lists that are being populated by external data?  I've attached my forms so that you can see what I mean.  In my Job Setup Form, the Job name textbox value will populate the project name, Owner Name dropdown list value will populate the owner and the Branch Office dropdown will populate the branch field.

 Thanks so much for your help on this!

 Hello Stacie,

 I'm using the concat() formula in the second action of the Set Values rule on the button (see attached image).

If all of the requisite values are already in your main data source, there isn't even any need to create a secondary data source to hold them.  You can just set up the concat() formula to pull the field values from the main data source.  In this situation, you would hardcode the Status value of 3 into the concat() function and omit the rule that sets it in the secondary data source.  The resulting concat() formula would look like this:

 concat('<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:ProjectsForm:-myXSD-2010-02-27T08-03-19" href="manifest.xsf" solutionVersion="1.0.0.3" productVersion="12.0.0" PIVersion="1.0.0.0" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-02-27T08:03:19" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003"><my:name><![CDATA[', "ADD THE FIELD FOR YOUR PROJECT NAME HERE", "]]></my:name><my:status>3</my:status><my:branch_office>", ../my:Job_Information/my:ddl_branchoffice, "</my:branch_office><my:owner>", ../my:Job_POCs/my:ddl_ownername, "</my:owner></my:myFields>")

I wasn't able to find a field in your form that would seem to be a place for the user to type in a master project name, so that's marked in red with a placeholder in red above.  You would need to replace this (quotes and all) with whatever field you're using for the project name.

 I'm also uploading a copy of your form with the changes in it.  Remember to update the concat() formula to include your project name.

Thanks Jimmy.  So if I'm not using the secondary source (ProjectForms.xsn) How do I do the mapping in DBXL?  Do I use Job Setup Form twice?

 You should add the ProjectForm.xsn form as a doctype in DBXL, and map that to your project table, but you don't need to add or use its template.xml file as a secondary source in your Job Setup Form (the data source called CreateProject in your form right now). 

Your rules build the XML to submit and submit them using the SubmitDocument-Projects data connection.

 Does that make sense?