SQL Database Integration

Re: A many to one relationship might exist between two tables in the form

Patrick Halstead — Thu, 24 Jan 2008 01:12:22 GMT

Hi Steve,

Check out the following link: http://blogs.msdn.com/infopath/archive/2006/08/29/730062.aspx. In general, our Database Accelerator Web Service will simplify your form significantly since it will abstract the XML to SQL DB mapping and allow you to define the mapping separately without having to worry about redesigning your form every time the DB changes. Let me know if you want to talk again or if I can show you another demo.

Re: A many to one relationship might exist between two tables in the form

m181354 — Wed, 23 Jan 2008 17:52:28 GMT

LaborSummary

Autonumber(Primary Key)

xxx

LaborSummaryCommentsLabor

Autonumber(Primary Key)

CA_L

Comments

I have a CA to CA_L relationship setup between the two tables...which will be a one to many.

Re: A many to one relationship might exist between two tables in the form

Clay Fox — Wed, 23 Jan 2008 17:38:22 GMT

This would be refering to the relationships between the tables.

Are you using Primary Keys for each table?

What is your layout?

Re: A many to one relationship might exist between two tables in the form

m181354 — Wed, 23 Jan 2008 17:20:57 GMT

I setup my tables and relationships like stated above but get the following error when I create the Data Connection:

A many to one relationship might exist between two tables in the form

What am I doing wrong?

Re: 1QNAN Error

m181354 — Wed, 23 Jan 2008 16:09:39 GMT

My field in Access is setup as a type "double" with 4 decimal places. When I perform a "sum" function on that column I get the 1QNAN error anytime I have a number after the decimal point such as 0.0025. The sum works fine if all the columns something like 0.0000 or 1.2500. The total column is setup a decimal. Does anyone know why?

Re: SQL Database Question

Clay Fox — Tue, 22 Jan 2008 18:36:17 GMT

I went through this in another thread recently.

First you need some data in Access before running the data connection wizard. It also should be formatted in Access so it is not rounding but displaying the decimal places. Then it should be a float field in InfoPath and you can set control format to the correct number of decimal places.

Re: SQL Database Question

Clay Fox — Tue, 22 Jan 2008 18:33:53 GMT

When a new record is added on the main table, InfoPath will default a new record for each child. The user can complete these, ignore these, or add more than one. That is why the records need an ID as well as a pointer to the parent, to enable multiple children. A new record would need the pointer set to submit properly but it would be blank initially. Child tables cannot be created on their own without an associated parent.

When a record is queried the related children will be returned as well.

Update your table as I laid out and then create a new form and I think you will see as you experiment with it.

Re: SQL Database Question

m181354 — Tue, 22 Jan 2008 18:33:41 GMT

I have a another quick question...I am summing a field set as Decimal with 4 places such a 0.0000. Some records have 0.0000 and others have numbers such as 812.1677. I get 1#QNAN sometimes even though there are no blanks in the field. Do you know why?

Re: SQL Database Question

m181354 — Tue, 22 Jan 2008 18:25:36 GMT

So the child tables do not have to have a record associated with a record on the parent table to start? I want a one to may relationship where there are no comments to start and users can add as many comments as they want to per Control Account.

Re: SQL Database Question

Clay Fox — Tue, 22 Jan 2008 18:15:56 GMT

That may be the issue.

InfoPath typically wants a one to many relationship, multiple children per parent or at least the capability to handle this. I would try adding a Primary key which is unique to each child and see if that works.

Re: SQL Database Question

m181354 — Tue, 22 Jan 2008 18:09:59 GMT

This may be my problem...I am using the ID and MainID as the same key on the child table...for the table and for the link to the parent table.

ID Name Decription etc

1 Test Testing x

2 Test2 Test Again x

Then child

MainID Name Comment

2 TestNote Testing is fun

Re: SQL Database Question

Clay Fox — Tue, 22 Jan 2008 17:33:17 GMT

Typically you would configure each table with a primary key so that each record has a unique ID. Child tables would have an extra column/field which pointed to the parent record in the parent table.

So main

ID Name Decription etc

1 Test Testing x

2 Test2 Test Again x

Then child

ID MainID Name Comment

1 2 TestNote Testing is fun

You may already have this, I just wanted to be clear that your relationships are right. Also it should ask you for this relationship in the wizard. Are you supplying the ID numbers in the form?

Re: SQL Database Question

m181354 — Tue, 22 Jan 2008 17:26:35 GMT

I am using an autonum to create a key on a new record for me on all tables. I have a autonum field records 1 to 3283 on all three tables. My main table have all fields with data in them. My two child tables have the same autonum field filled in 1 to 3283 and also a Control Account Number field.

It looks like I did everything right. Do the child tables need to have the same key(1-3283) as on the parent table. When they did not, they were not editable on the form.

I will try creating a new form to see if I can get it to work.

Re: SQL Database Question

Clay Fox — Tue, 22 Jan 2008 16:59:46 GMT

Alright.

First I would try adding a new view and drag your whole data node onto it as a sectio with controls so you can see everything.

Preview and see if you can query and submit all three tables. One thing do you have any data in your tables? Often I have an issue with the data connection if the table has no records (1 blank). You might try putting some test data in the first row and then go through the data connection wizard if this has been the case.

Lastly, create a new form from scratch real quick and do the default drag of the query fields and data fields and preview that and see if you get full functionality on all of your tables.

Re: SQL Database Question

m181354 — Tue, 22 Jan 2008 16:51:02 GMT

I have done that, my Data Connection looks like this...

- LaborSummay.dbo

- LaborSummayCommentsLabor.dbo

- LaborSummayCommentsNonLabor.dbo

Autonumber is the primary key for all the tables and I have setup a relationship between the first table listed and the two below it.