Related SQL Tables

[Eamon]

Hi,

I am trying to submit to an SQL Database with 2 tables - one customers and
one tickets. Each cusomter can have zero or more tickets. This logic is
fine in SQL server as I can add a new customer with no related tickets......

However.... in Infopath I have created a main data source with both tables.
The relationship is correct ie a FK for customers in tickets table.

When create a new customer in Infopath a related ticket is automatically
created????

Any clues or thoughts would be massively appreciated

 

[INTP56]

I'm new to InfoPath, just got involved this month, so consider the source.
But I think this has to do with how InfoPath handles data in the Main
connection.

Because you included both tables in the main connection, InfoPath submits to
the whole connection, not just the table (Customer) where new data appeared.
Note, I think this has nothing to do with what's on the form, but how it's
specified in the connection. So InfoPath inserts a record into Tickets with
blank data.

I noticed a similar problem when specifying a default value in the SQL table
definition. When I insert a record from InfoPath, and the column wasn't even
on the form, InfoPath put in a zero length string. The only way(that I found)
to get the default to "work" was to not include it in the Connection
information (I unchecked the box for that field)

You could also put a constraint on your ticket table, say by adding a check
constraint in the table definition that requires the LEN(ColumnName) > 0.
Then, when you insert a new customer, you will get an error message telling
you the submit failed, even though that constraint is in the ticket table,
not the customer table.

As I understand it, InfoPath will insert something into every field
specified in the connection (except Identity Fields, it seems to know about
them) That means if you have a computed column, InfoPath will try to insert
something into it and fail, telling you it can't insert data into a computed
column or UNION.

As I understand it, to add a customer without adding a ticket record, you
need another form that only includes the Customer table. Then, have another
form that assigns tickets to a customer (that must already be in the
database).

Hope this helps,

Bob

 

[Eamon]

Thanks......that is really helpful.

You are correct one way around this was to create a connection with a single
table.

The other discovery I made was that when in a repeating table/section
properties there is a default values option. Despite any sql relationship
this can stipulate that the child table is also populated. If you unselect
all the fields form the child table - it works a treat and as expected.

Eamon

 

[INTP56]

Thanks, your findings about the default values will help me too.

Bob