locked
Infopath Submit to only 1 Table RRS feed

  • Question

  • Hello All - I am trying to work through an issue submitting information to an Access (2007) Database using an Infopath (2007) form (which form I did design based on my Access database).  Here is what I have done so far:

    I created my database with 3 tables:

    • Std_Info that contains basic student demographic information (Primary key is Student_ID)
    • NSM_Info that contains multiple rows of commentary for each student ID (dual primary key of Student_ID & Comment).  This table is set up as a child of the Std_Info table.
    • Tracking that is designed to capture who is using the form I have created, with a time_stamp and the Student_ID they searched for.  (dual primary key of username & timestamp, as users are allowed to use the form multiple times for the same Student_ID)  This table is also set up as a child of the Std_Info table.
    • I have built relationships for all three tables using the Student_ID as the common connection (with the Student_ID from the Std_Info table as the parent to both Student_ID's in the other two tables).

    I want my form to allow the user to Query for a specific Student_ID, that will display the Information from both the Std_Info table and the NSM_Info table, but that will also write to the Tracking table based on the Student_ID, the username, and the now() function.  I have tried setting this up two different ways, both of which have failed:

    1. I have created the main connection to include all three tables.  I am able to query successfully based on Student_ID, and I am also able to set the field's values for the 3 fields in my Tracking table.  However, when I try to submit, I receive the error: "Empty row cannot be inserted. Row must have at least one column value set."  This seems that Infopath is trying to add a new row to EVERY table in my database, whereas I only want it to add a new row to my Tracking table.
    2. My second approach was to create the main connection using ONLY the Tracking table.  I then built a secondary data connection to receive data from the other two tables in my database.  In this approach, I am successfully able to add rows to my Tracking table, but I am not able to filter on the secondary query (i.e. Infopath pulls ALL records in my Std_Info table and my NSM_Info table). 

    Ultimately what I am looking for is to either edit my main connection so that it only tries to add a new row to one of the three tables, or edit my secondary connection so that it only pulls the information for 1 Student_ID at a time (based on a user input).  I have been searching for days with no success so I am really hoping someone will be able to point me in the right direction (preferrably without having to use a Web Service or custom code - though if that is the only way, please let me know).

    Thanks

    Monday, April 9, 2012 8:08 PM

Answers

  • Hi Dustin_Maronet,

    There is some limitation in InfoPath, Please check these.

    • There must be a base column for every column that is used in the form's query.
    • A table column may not appear multiple times in the entire query.
    • A primary key, unique constraint, or unique index must be available for every table in a SELECT clause that's used in the form's query.
    • A table cannot be included multiple times in the form's query.
    • Relationships between parent and child tables must include all primary key columns from the parent table.
    • There can only be one base table for all columns in a SELECT clause that's used in the form's query.

    You can refer to the following link.

    http://msdn.microsoft.com/en-us/library/bb251005(v=office.12).aspx

    Thanks,

    Jack


    Thursday, April 12, 2012 2:52 AM
    Moderator

All replies

  • As an update, I have taken my second approach above (main connection just to Tracking Table, secondary connection to the other tables).  I added custom fields to my main data connection (one to match each of the fields from the other two tables in my secondary connection).  I then built custom rules to set those fields values.  This works in part, however it does not allow the fields to repeat.  I have set the properties to allow it to repeat, and have added repeating tables, but it still only shows one line of data (whereas I know there are multiple lines of data to display).  So this did not work either. 

    Any ideas or suggestions would be appreciated.

    Tuesday, April 10, 2012 6:39 PM
  • Hi Dustin_Maronet,

    There is some limitation in InfoPath, Please check these.

    • There must be a base column for every column that is used in the form's query.
    • A table column may not appear multiple times in the entire query.
    • A primary key, unique constraint, or unique index must be available for every table in a SELECT clause that's used in the form's query.
    • A table cannot be included multiple times in the form's query.
    • Relationships between parent and child tables must include all primary key columns from the parent table.
    • There can only be one base table for all columns in a SELECT clause that's used in the form's query.

    You can refer to the following link.

    http://msdn.microsoft.com/en-us/library/bb251005(v=office.12).aspx

    Thanks,

    Jack


    Thursday, April 12, 2012 2:52 AM
    Moderator