Difficulty with Infopath 2007 submitting and receiving data from SQL Database. - InfoPath Dev
in

InfoPath Dev

Having trouble finding a blog or post that answers your question? Check out our Custom Search Page

Difficulty with Infopath 2007 submitting and receiving data from SQL Database.

Last post 10-01-2014 08:06 AM by AndreaARCH. 7 replies.
Page 1 of 1 (8 items)
Sort Posts: Previous Next
  • 09-22-2014 05:31 AM

    Difficulty with Infopath 2007 submitting and receiving data from SQL Database.

    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.

  • 09-30-2014 02:02 AM In reply to

    Re: Difficulty with Infopath 2007 submitting and receiving data from SQL Database.

     Hi All

     

    Does anybody have any suggestions :S 

  • 09-30-2014 07:56 AM In reply to

    Re: Difficulty with Infopath 2007 submitting and receiving data from SQL Database.

    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. :)

    Hilary Stoupa

  • 09-30-2014 08:05 AM In reply to

    Re: Difficulty with Infopath 2007 submitting and receiving data from SQL Database.

    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

  • 09-30-2014 09:43 AM In reply to

    Re: Difficulty with Infopath 2007 submitting and receiving data from SQL Database.

    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?

    Hilary Stoupa

  • 10-01-2014 02:02 AM In reply to

    Re: Difficulty with Infopath 2007 submitting and receiving data from SQL Database.

    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 :)

  • 10-01-2014 07:56 AM In reply to

    Re: Difficulty with Infopath 2007 submitting and receiving data from SQL Database.

    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).

    Hilary Stoupa

  • 10-01-2014 08:06 AM In reply to

    Re: Difficulty with Infopath 2007 submitting and receiving data from SQL Database.

    Thanks Hillary :)

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

    Andrea

    :D

Page 1 of 1 (8 items)
Copyright © 2003-2017 Qdabra Software. All rights reserved.
View our Terms of Use.