InfoPath Dev

I have a browser compatible InfoPath 2007 form that is based on a SQL Server Data Connection.  The form submits the data back to the database via a web service.  The form itself is then submitted to a SharePoint Library.  The form works just fine when opened in either the InfoPath Client or the browser.  I can populate the fields and submit the data to the database and the form to the SharePoint library.  The form has both required and optional fields.  The problem is if the form was submitted to the sharepoint library from the browser and i open that form again from the SharePoint library, the optional fields that were NOT populated are disabled.  The problem is I can not go back into the form and edit the fields so i can submit it again.

The submit process is done through rules on a button i added to the form.  When the button is clicked...

I set some fields' values

I submit the data to a web service

I query the data using the web service to get the ID of the record that was just added to the database. (The database uses an auto-numbering field as the PK and i need to get this ID back to the form.)

I set that ID value to the query field on the main connection and then query the main connection to get all the data records with their proper IDs back into the form.

I then submit the form to a sharepoint library.

When i open that submitted form from the sharepoint library, any field that was blank is disabled.  Any field that had data can be edited.

This not a problem if the form was opened in the InfoPath client.

Any ideas?

So when you open the submitted form using InfoPath, you can edit the fields that were blank on submit?

I can only edit the fields that were blank on submit if the form was submitted using InfoPath.  If the form was submitted using the browser, I can not edit the fields that were blank on submit even if the form is opened in InfoPath.

I believe this has something to do with the main connection query returning nulls for those fields.  It's like the browser disregards those fields as being part of the data source.  I compared a form xml file that was submitted through InfoPath to one that was submitted through the browser and found that the one through the browser was missing the fields that were blank.

Thanks for your help on this.

The XML schema generated for Secondary data source when you connect to database using Web services does not contain the fields which are empty.

To achieve this recieve data in all columns of your secondary datasource. Do not keep any field empty when you are recieving data, try recieving garbage values but recieve some data. This will make a proper XML schema and will solve your problem

Thanks for the reply Sourav, however, the issue is with my main data source which retrieves data from a sql server database connection.  I originally designed the forms using a webservice that returned a dataset, but what that did was make the optional fields required in the form if the fields were a non-string data type and I didn't want to have values in optional fields unless the user put them there.

Is there a way to edit the sql statement for the main data source so that it converts the NULLs to empty strings?  Any other ideas?

 Thanks.

 Why dont you use ISNULL in your SQL queries to set some values if it is null.

The other way is to if the value is null from database send a known character to the form. Then put a rule to make the field blank again if the value is ur known character but this time as you are dealing with the data on your form the field will be editable.

Sigh. This is why we created our Web service which is not tied to SQL data types. It submits XML to the DB and use that for 100% fidelity. Loose coupling is submitting XML to Web service as a string that is mapped to SQL by the Web service. This loose coupling works because InfoPath does the XML validation. Anyway, I'm sure you didn't really want to hear this ... but, just giving you some history. Next time you create a form consider using our free trial Web service - it'll save you a lot of money.

Now that you have created your form using the hardcoded types, it's difficult to go back to a more loosely coupled model. You could do a rule on load to modify the XML in your form. You could also have some kind of SQL trigger to do it. Still thinking ...