InfoPath Dev

Hi,

I currently have an InfoPath 2007 form that is being used to retrieve and update records from a MS SQL Server 2003 database. These records are then being filted based on the current user that is logged in. One table, referred to as CDR (Call Detail Records), has a line item for each call that the user has made. These values are then displayed in the Infopath 2007 form as a repeating section. The form itself seems to work well with the table, and I am able to submit the form and information not only to the SQL Server, but also to a MOSS 2007 hosting enivonrment.

The problem that I've run into, however, is that the values that are being stored within the InfoPath form (the values returned by the origional SQL query) are not staying in the form when it is submitted to the MOSS Form Library. Essentially a saved form is opened and all of the values are blank. What is the best way to store the values that are located within the repeating table populated by the query? I have looked into enabling form data being available in offline mode, but that does not appear to display the queried values. I have also looked into the post here and have attempted to modify my form's custom code to the code below. The data connection "SQL Submit" contains my SQL data connection, while the repeating table is stored in the form. The following code is using Java Script.

function CTRL34_7::OnClick(eventObj)
{

XDocument.GetDOM("SQL Submit").setProperty("SelectionNamespaces", 'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-10-20T16:02:22"');
XDocument.DOM.setProperty("SelectionNamespaces", 'xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-10-20T16:02:22"');

// Get a copy of the main DOM node.
var clone = XDocument.DOM.selectSingleNode("/dfs:myFields/my:CDR").cloneNode(true);

// Clear the main DOM nodes.
XDocument.DOM.selectNodes("/dfs:myFields/my:CDR").removeAll();

// Copy nodes from 2DS to main DOM.
var nodes = XDocument.GetDOM("SQL Submit").selectNodes("/dfs:myFields/dfs:dataFields/d:CDR"); // Note: need to supply the correct namespace per element in this XPath.
while(node = nodes.nextNode())
{
 clone.selectSingleNode("/dfs:myFields/dfs:dataFields/d:CDR/@PHONENUMBER").text = node.selectSingleNode("/dfs:myFields/my:CDR/my:Phone").text;
 clone.selectSingleNode("/dfs:myFields/dfs:dataFields/d:CDR/@CALLDATETIME").text = node.selectSingleNode("/dfs:myFields/my:CDR/my:CallTime").text;
 clone.selectSingleNode("/dfs:myFields/dfs:dataFields/d:CDR/@CALLEDNUMBER").text = node.selectSingleNode("/dfs:myFields/my:CDR/my:CalledNumber").text;
 clone.selectSingleNode("/dfs:myFields/dfs:dataFields/d:CDR/@DESTINATIONCITY").text = node.selectSingleNode("/dfs:myFields/my:CDR/my:CalledCity").text;
 clone.selectSingleNode("/dfs:myFields/dfs:dataFields/d:CDR/@DESTINATIONSTATE").text = node.selectSingleNode("/dfs:myFields/my:CDR/my:CalledState").text;
 clone.selectSingleNode("/dfs:myFields/dfs:dataFields/d:CDR/@DURATION").text = node.selectSingleNode("/dfs:myFields/my:CDR/my:CallDuration").text;
 clone.selectSingleNode("/dfs:myFields/dfs:dataFields/d:CDR/@AMOUNT").text = node.selectSingleNode("/dfs:myFields/my:CDR/my:CallAmount").text;
 clone.selectSingleNode("/dfs:myFields/dfs:dataFields/d:CDR/@PERSONAL").text = node.selectSingleNode("/dfs:myFields/my:CDR/my:CallPersonal").text;
 clone.selectSingleNode("/dfs:myFields/dfs:dataFields/d:CDR/@CERTIFIED").text = node.selectSingleNode("/dfs:myFields/my:CDR/my:CallCertified").text;

 XDocument.DOM.selectSingleNode("/dfs:myFields/my:CDR").appendChild(clone.cloneNode(true));

}

}

 Although the form does not render any explicit errors, the specified repeating table that is cloned has no information stored in it...I'm not sure if this is the best way to keep the SQL values or not or if I'm missing something all together.

Any help or guidence that could be given would be greatly appreciated. Unfortunetly, I am unable to install any additional plugins, addins, or any other software to the environment where this will be deployed.

 Thanks in advance,

 John

No other response, so I'll hazard an attempt at helping, but what I describe might not fit your template use exactly - I haven't in particular worked with Sharepoint and I use Infopath 2003.

When I've had forms that connect direct to a database the fields are bound to the database and the form itself doesn't 'store' the data - it only retrieves/displays/submits it.
Saving the form as an XML file doesn't actually have the data in it.

I've noted though that you can still add fields to the form template directly via the data-source panel.
These fields will have the my: namespace and will actually be saved with the form when you save it as an xml.

I am hoping that this will mean when you send it to SharePoint these fields will also go with the form.

You will still have to populate them, you can use a similar approach to what you have done, but populate the my: fields instead of cloned dfs: ones.