InfoPath Dev

Hello

 I was wondering if I could please ask for some help?   I have an infopath form that I have connected to a SQL database (a test database for now). The database contains two tables, the first table has the employee details in (Employee ID [PKey], Fname, Sname, DOB, Job Role) and the second table has details of any appraisals they have had completed (Employee ID [PKey, Appraisal ID, Objective 1, Objective 2 etc].  I have an infopath form which is using a query to help the user complete section one Employee details so that there can be no mistakes (i.e. two employee id numbers), this works fine and I can populate the document with the employees basic details. 

The second part of the form is supposed to remain blank so that the worker can complete a new appraisal form but two problems are occuring:

1. Section 2 is querying the second table and pulling all previous appraisal data through

2. Any changes is overwriting this instead of adding a new row of data into the appraisal table despite the appraisal ID and all other information aside from the Employee ID being different.

I have managed to overwrite but I am unable to simply insert a row into table 2, which is what I want to do. Everytime I get it to submit a new row in the appraisal table all of the keys change and a new employee ID is generated.

 I'm sorry I'm quite new to SQL databases - our HR system has had an upgrade and now sits on a SQL server so I am having to set the forms up differently. I was new to all of this anyway so to add in that to the mix has me at a bit of a loss

Any advice at all you can give me would be greatly appreciated

Andrea :)

P'S - I have a main connection setup and also a secondary "Employee" connection which is simply receiving data from the Employee Table. I have tried Master and Detail controls but this doesn't appear to work the way I need it to, each appraisal needs to be different so cannot show the worker their previous forms.

 Hi All

Does anybody have any suggestions :S 

Hi - so in your form's main data connection, you've connected the two tables, is that correct? Any chance you could share your form? You can attach a template under the options tab - I'd probably understand better if I could see it. :)

Hi Hillary

Many thanks for your response :)  I have indeed added the two tables to my main database connection.  I then tried adding a secondary connection to a sharepoint list to just query the employee table details to see if that would help... but it still queries the Appraisal Table no matter what I do.  The form attached is the one that I have been messing around with trying to connect to my test database with no luck. The original is submitting to a sharepoint site for now with no issues.

 Andrea

So - I'm confused by this:

I have managed to overwrite but I am unable to simply insert a row into table 2, which is what I want to do. Everytime I get it to submit a new row in the appraisal table all of the keys change and a new employee ID is generated.

When you open the form and query, then click "add new row" to the appraisal section, nothing happens?

Hi Hillary

I am quite new to this so I'm sorry if I am confusing you :)    I queried the persons employee details for the first table so that the form knows which person to attach the appraisal form to. However the form also pulls back all of the appraisal information that I had previously entered and I was hoping for a "fresh" new record to enter new appraisal data.  I understand that I could probably add a whole new record beneath the previous appraisal record but this is not what I want... I don't wish to see the previous appraisal data.  Otherwise this would make the query unsecure as anyone could run the query and find other workers appraisal information.

I did try it from scratch using the "new record" button but this clears the query that has been ran and instead on submit this produces a new appraisal record and a new employee record (giving the employee a second ID).  If I dont click new record and type over the appraisal form... this overwrites.

 Andrea :)

I don't think there is going to be a way to prevent the data from being returned. You could HIDE the previous appraisals - just add conditional formatting to the repeating section to hide it if it already has an ID (for example).

Thanks Hillary :)

 I will have a try with this and let you know how I get on.

Andrea

:D