Main Table Has Multiple Entries

[Alex Anderson]

Hello Everyone,

I'm using Infopath 2007 and my backend is SQL 2005. I'm creating a vacation
leave form and in my form I have a repeating table which contains the dates,
hours, type of leave, etc... The other section of my form has Employee
number, first name, last name, and department. I don't have any issues until
I insert other record in the repeating table. I made this option because
there are times when people like to make multiple dates of leave on the same
form. I fill out the form and add my multiple enteries in the repeating
table and when I go to submit, I get an error stating Employee number does
not accept nulls.

This is true since in the database I made it so nulls are not allowed. For
troubleshooting purposes, I allow nulls and the form works however under my
primary table "Slips" there are multiple slips. I wouldn't really mind this
but the second entry in the Slips table is all blank. I've tried playing
with Master/Detail which did show some promise but that didn't work either.
Is there code I need to make it so Slips has only one record but the form
allows multiple entries when selecting multiple dates of leave? Below is how
my database is setup in SQL 2005

Slips:
SLIP_NUM_ID (Primary Key) - One-to-One relationship to leave_Record Table
EMP_NUM (Unique Index Key) - One-to-many relationship to Leave_Record table
EMP_FIRST_NAME
EMP_LAST_NAME
DEPARTMENT

Leave_Record:
SLIP_NUM_ID (Primary Key) - One-to-one relationship to Slips table
EMP_NUM (Not unique - Index key) - Many-to-one relationship to Slips table
LEAVE_TYPE
LEAVE_HOURS
LEAVE_DATES_FROM
LEAVE_DATES_TO
LEAVE_TIME_FROM
LEAVE_TIME_TO
DATE_REQUESTED

Thanks for your help!
Alex Anderson

 

[Hilary Stoupa]

Hi, Alex:

How did you set up your data connection in InfoPath? Are both tables in your
main connection with the relationship defined?

 

[Alex Anderson]

Hilary,

The relationship in infopath is the basic one I set up while creating the
main connection. Both SLIP_NUM_ID in both tables are related going through
Infopath's connection wizard. Is there a way to change the type of
relationship in Infopath?

 

[Hilary Stoupa]

I guess what I am thinking is since you have a one to many (correct me if
I've misunderstood anything) between Slips and Leave, you may have a problem
with the way your data source is set up. Under data connections, select your
main connection and select modify. On the Slips table, make sure the check
box under multiple records is not selected, and if there isn't a way to check
the relationship between the tables (I can't remember if there is...), you
may wish to re-add the Leave table.

Oh -- wait, I just looked at your schema below -- wouldn't the Slip Id
Number be a foreign key relationship from the leave table? Did you define the
relationship in SQL?

 

[Alex Anderson]

Hilary,

I definded my relationships in SQL 2005 (I thought I had too) and when I
created my main connection.

 

[Alex Anderson]

Hilary,

I took a look at the relationship in SQL, and the SLIP_NUM_ID in
Leave_Record table is the foreign key and the primary is in the Slips table.

 

[Hilary Stoupa]

Cool, good, and on the slips table in the InfoPath data connection, the
Multiple records check box is not selected? Or, you can just look in the data
source task pane, make sure the group associated with the slips table isn't
repeating....

 

[Alex Anderson]

Hilary,

Now maybe that's where my problem is. In Infopath, which table should I
select first, Slips or Leave_Record? Right now, I have Slips first, then I
selected Leave_Record as second. How should this be set up?

 

[Hilary Stoupa]

Well, Slips is your main table, so when your data connection is created, you
would select it first. Then you can Add Table, add your Leave table, and
create the relationship between the two by associating the Slip Id column.
Then, select your Slips table again, and Modify it, and deselect the Multiple
records checkbox.

Basically, if you were to drag the folder from the Data Source containing
the datafields onto your IP view, you would expect to see an ordinary section
(with the Slip info) and a repeating seciton (with the Leave info) ...

I really can't think of another reason you would be having this issue, if it
isn't the data connection or the table set up in SQL....

 

[Alex Anderson]

Hilary,

Once I deselected "allow multiple records" on Slips, and when I modify table

 

[Hilary Stoupa]

I can't replicate that -- I just tried. I created two tables, one with a
primary key, the second with a foreign relationship to the first table on
that primary key. I was able to connect to the first table and add the
second, then deselect Multiple Records on the first. I also tried deselecting
Multiple Records prior to adding the second table -- in no case did it grey
out. So, you've got something kind of odd going on somewhere. Have you tried
creating a new (simple) form from the database? Maybe give that a shot, see
if the problem still exists in a new form. If it does, it is probably
something on the db side, not the IP side... if it doesn't, it is probably
something in your form...

 

[Alex Anderson]

Hilary,

The reason the Multiple Records option was greyed out was because of my
database design. I had a one-to-one relationship. Once I changed the
relationship to one-to-many I was able to deselect Multiple Records on Slips
and select Multiple Records on Leave_Record. The only caveat doing this was
that I had to remove my one of my already existing relationships for the
EMP_NUM record. I wasn't able to enter duplicate records on Slips since I
put a unique index key on that column. Basically, I have to reference the
Slip table to see who issued the request. I'm not that familiar with SQL
2005, and I'm pretty sure there's a way to accomplish what I'm trying but at
this point, I don't mind referencing. Thanks again for your undivided help!

THank you
Alex Anderson