InfoPath Dev

hi, i m new to infoPath. I am using infoPath 2003,

I have two tables, one is "customer" with fileds, cus_Id, cus_First_Name, cus_Last_Name, cus_Sex, cus_Dob, cus_Ethnicity

and the other is, "questionare" with fields, qs_Id, qs_Smoke, qs_Dring, qs_Drug, cus_Id

now all i want is,

1. user can search the customer by his Id, first name, or by last name.

after searching there shud b some machanism to fill the questionare, if it is the first time, a blank row shud b displayed for questionare, and if there is some data exists against searched customer, it shud displayed.

after filling up the questionare, user shud be able to submit the questionare.

there shud b two views, one is for searching containg search results and a button, 'Ans. Questions', wen user clicks on this button , the next view shud display, and he can give the answers and submit the form.

search process is done, but the next step is causing headache...!!

Plz help me,

thx.

Hi TheKing,

It sounds like you have developed a database-backed form. Many people do this and experience numerous problems:

• When form's data source is extended , new fields cannot be mapped into database
• Database changes invalidate the form and require you run "Convert Main Data source". Old XML data may no longer open in your form.
• Accessing SQL directly across the Internet will require VPN and passwords. Maintaining and managing access to SQL will require workl
• Not all data types in InfoPath roundtrip to SQL (for example, images and rich text)
• Filtered search cannot be extended to use a number of data fields or diffferent comparison operators (for example, give me all rows with submission dates later than January 1st, 2009

Because of these limitations, the best practice is to use a Web service in between the InfoPath form and the SQL database. In addition, we recommend creating your form using XML data source, not the Web Service or SQL database parameters. That will allow you maximal flexibility to change your form and extend your database as requirements change down the road - and trust me, you will want to change your schema down the road. XML data types are richer than SQL and the XML data can be stored separate from your SQL tables to allow for continuing submission during times when you change the database or form. Create your survey form from scratch (blank) in InfoPath 2003 to look the way you want.

Next, hook it up to SQL using a Web service. We provide a free trial of our Web Service here: http://www.infopathdev.com/files/folders/core_documents/entry38256.aspx. You will need to install the Web service on a server that is running IIS. If you are using SharePoint, the easiest way to do this is to install it on your SharePoint server. If not, you can install it on any server, but installing it on the same server where you have SQL will simplify your installation. Of course, for long-term deployment, you'll likely want them on different servers, so this is merely a suggestion to accelerator your solution development.

Once you have the Web service installed, map the XML to your pre-existing SQL tables (see our documentation in the previous link for how to do this). Add a column to your SQL tables that identifies when data was created with InfoPath. You should do this so you can determine when to load the data from SQL vs. XML. The Web service stores newly submitted data (or edited old data) using XML files. This gives you better data fidelity and allows you to add rich content controls such as images or rich text without having problems round-tripping to and from SQL.

To search and load data from your database (that was not created/previously edited with InfoPath), you'll want to provide a separate view (as you suggest) that queries the DB. You can build smart queries that query your DB filtering with data fields in your form. Download the Query Builder tool and watch our video on how to do this. Once the user finds the correct data, you can give them a separate button to switch to a view that allows them to fill out separate data. The database mapping in the Web service is flexible and allows you to map your data to arbitrarily deep child tables.

If we were doing this work, I estimate it would take between 2 and 8 hours for the initial prototype.

Have fun!