InfoPath Dev

 Okay! where to start. This is driving me frigging bonkers! I am pretty new to Infopath and so far every item I have found relating to this problem has been written by some dork that loves to lay on the geek speek just to sound smart. Unfortunately it goes right over my head and i need a clear concise answer to my problem.

I have designed a form which includes option buttons, text fields, rich text fields and repeating sections containing the afore mentioned. I am trying to hook this form to a sql database on my windows 2003 server (2005 sql express running)

 My problem is that what I create a new form using the Design a form template > Database option. When I drag the fields across from the datafields (data source) it immediately sticks the field on the page as a text box and shoves it inside a repeating section box. When I try to move the text box out of the repeating section it immediately says (control cannot store data correctly).

 I also need to know what i should set the datatypes to on sql for each of the individual field types listed above and how i should be laying out the fields in sql server (in particular - how the hell do I setup parent/child fields using  Microsoft SQL Server Management Studio.

 Any help would be appreciated as would it being simple aswell.

cheers

Martin

Hi, Martin. Welcome to the forum.

The reason IP wants to put the controls into a repeating section is because your data structure is repeating in nature. That is -- you've connected your form to a database table. That table can have one to umpteen rows. InfoPath will always assume it has umpteen.

I'm not sure if you are using InfoPath 2003 or 2007, and I don't recall the IP 2003 options very well, so please forgive me if this doesn't apply to your situation, but in IP 2007, when we walk through the wizard to create a new IP form off a database, on the screen where you select your database, after you select your database, you can select the table in the window that says Data source structure and then click the button to the right that says Modify Table. When you click that button, the title of the dialog box is Sort Order and most of the stuff in there is about sorting your records. However, at the bottom is a little checkbox about allowing multiple records. This is selected by default. De-select it, and be aware that if you walk through the data connection wizard again, IP will generally reselect it for you!

Now your highest level data fields group that starts with "d:" will not be repeating.

To get the type of control you want, you can right click the text box and select 'Change To' or in the data source, right click and see the controls available.

To create a relationship between two tables in SQL, look at the table in design mode and right click on the open window and select relationships....For a tutorial, go about halfway down this page and look for Adding a Foreign Key Using SQL Server Management Studio. You'll have to define the relationship in InfoPath too, and this from Office Online should get you moving in the right direction.

Hope this helps!

Hilary! you are the first person who has actually made complete sense. Ill go give this a try and get back too you. cheers......it is infopath 2007 by the way.

cheers

martin

 Hi Again,

 I am having a little problem here

I obvoiusly dont want things like customer name, address 1st line, address second line etc  to be in a repeating section (as i described above) but i do have 1 or two repeating sections later on in the form for adding telephone numbers etc. When i switch off "allowing multiple records" in the parent table i cant switch it on on any of the child tables so i assume therefore I cant use the repeating section for the child table. How do i get round this tebockle? or will it let me post multiple rows to the child table even though this is switched off?

 cheers in advance.

Martin

Actually, you should be able to set the child table to have multiple rows.... especially if the relationship between the tables has been defined correctly on the server side....

With the parent table selected in the data source wizard (in the window that says Data source structure) after setting the parent table to not have multiple records, you should be able to select the Add Table button. You'll have a chance to define your relationships (column a in table parent should be equal to column b in table child, or whatever your data structure looks like). When I walked through this a second ago, to make sure I was talking sense, my parent table shows as a plain folder in the Data source structure window, and my child table has the little blue arrow flag on it, to let me know it repeats....

 hi there hilary. its just not working. The tick box is disabled on child tables and every time i try to map a repeating section to one of the child folders it comes up with "a repeating section must be bound to a field or group that repeats"

 ok.......i just tried something. I removed the tick box as you told me too and it was all or nothing. All of the tables as repeating or none. However i went back to sql and removed the primary key from one of the child fields and what do you know it became a repeating group while the parent was a non repeating. bingo! i thought to myself but now when i click next it says:

submit status: Not allowed: one or more of the tables in the data source do not have a primary key, unique contraint or unique index.

All tables in SQL will need a primary key. What are the relationships between your tables? How did you define those relationships in InfoPath when you added the child table to the parent?