InfoPath Dev

Hi folks

I'm virtually a complete newbie to Infopath and I've been assigned the task of producing a POC (proof-of-concept) for a customer who is interested in exploiting Infopath. However I'm really struggling to grasp the concept of repeating tables, and how your database needs to be structured to achieve what I want.

Basically I need to create a simple form which allows the user to query any one of 3 fields and display the results if a match is found. If not I need the user to be able to enter these 3 fields, and then enter data against them. The rough layout would be like the one below

--- Query section

ID Number ...

Surname ...

Forename ...

--- Data input section

ID Number ...

Surname ...

Forename ...

Date - Notes - Username   <-- This bit needs to repeat to allow multiple entries to be created against each ID / Surname / Forename combination

I've modelled the database so that there is a main table that stores the ID number, surname and forename. This is then keyed by ID number to a secondary table where the Date, Notes, and Username columns are stored - essentially a one to many relationship.

The problem is that I can't get the repeating bit to work at all. I've tried using a Master / Detail control but this didn't work and I kept getting errors that empty rows could not be stored. I've had situations where the repeating table didn't add rows when you click the Insert link, and I've also had situations where I can't add repeating sections because it claims that the data doesn't repeat.

Any guidance on this would be greatly appreciated.

Kind regards.

Hi AGInfoPathDeveloper and welcome!

The database must have more than one row in it when you set up the data connection. If it doesn't it won't show repeating. That's probably the quickest fix. Just make sure you have >2 rows in the database when you set up the form initially.

In general, creating forms directly from a DB is a bad idea for the following reasons:

• schema changes are more difficult to maintain - old data must be migrated, and changes will interrupt submission process as you republish your form and fix up your DB. In other words, tight coupling is bad because it's brittle and easy to break
• schema is more limited - you are restricted to SQL datatypes (no images, rich text, etc.). if you add nodes to the main data source they won't be saved to SQL (you have to save them separately and then you have migration issues when the schema changes)
• schema structure is limited by database. SQL is a relational DB. InfoPath XML is hierarchical. Guess which one can easily represent more data structures? XML, not SQL. XML is a superset of SQL. The schema that the DB returns is based on how your DB is created. You can't change the structure after you set up the DB for the first time without going to SQL and changing it. You are limited.
• queries are limited - you can't change your queries easily once you have created the form

For these reasons and others, we recommend that you use a Web Service to query and submit data to a database.

Qdabra Software pays for this free forum. We sell very inexpensive Web Services to help us pay for this site. It's like Girl Scout cookies but while they taste good, they're less fattening and we don't force them upon you. :) You can try a free trial of our DBXL Web Service to see how easy it is to create queries and submits.

Migrating your form from a Database-backed schema to a more flexible XML schema is best done before you deploy it. It will take a lot of time to do it after you have thousands of XMLs in the database-backed schema format.

Good luck with your POC!

Hi Patrick and thanks for the welcome. :)

I couldn't believe it when you said the number of rows in the database can directly influence which parts of the form repeat! I can see why but it's certainly something I'd never have thought of. I'll give that a go and let you know.

As for the web server etc, that is beyond the scope of this project for the moment. I know that linking to an Access database is basically rubbish, but with this simply being a POC we don't have the time or resources allocated to set up a full Infopath system complete with web services. Believe me I'd have done it this way if this had been a full project but it's a simple project with a simple purpose. Our clients are wanting to experiment with different devices (tablets, PDAs, mobiles, laptops), and the different ways of inputting data such as keyboard if available, mouse, stylus and finger. As this POC is due to be installed on many devices with no access to the outside world (not even access to a network), we needed a 'local' solution and believed that this was the only way to go. The system also needed to be quickly 'reversible' because you know what people can be like with respect to breaking things. We've got a batch file that will copy the original database back to the location identified by the DSN, and will also copy the form to this location too. Finally we've configured a DSN to the database so that the form isn't physically bound to it, which allows us to move the database around without upsetting the form template.

As I mentioned earlier I'll let you know how I get on - many thanks for the advice.

Andy.