InfoPath Dev

I have several rows in a table on my form.  A header section of the form displays an employees information.  I want to insert each row into a database one row at a time along with identifying information (about an employee). This means resubmitting the one-time employee information along with each row multiple times.  The result might be a table in the database containing 85 rows for one employee/per form times 100 employees or  8500 rows in the db table over the course of one year .

Is this possible to do with DBXL using one button click?

Hi Doreen,

I'm not sure I follow.

DBXL is probably querying SQL data from a table, and that info is displayed in the form.

Some other data is typed in and the form submitted to DBXL. That becomes a new XML document in DBXL.

That document can be opened and resubmitted (to overwrite) or a new one can be submitted.

I don't really understand the question about being able to submit 8500 rows in one button click. Sorry! Can you elaborate?

Sorry, I know it's confusing to me too but my SME wants me to alter the insert to DB part so that a report can be easily filtered for later use. I will explain how it works now, and how I want it to work, next. 

Currently this is how the form works:

1) On form open DBXL retrieves from SQL view, employee info to fill header info, and also retrieves a SQL table to retrieve statements that populate the table rows (85 rows get populated with statements) .

2) User clicks a radio button control to right of each row or statement. 

3) The value of the radio button checked (1, 2, 3 , 4 or 5) is passed to a field 

4) On button click, DBXL inserts (or overwrites if existing)  one line of all form information into another table, all the employee header info and all statements and their resulting radio button values that were selected.

5) the table in the database receives one row of values from each form for : DocID (1), EmpName (Mark), EmpID(123), Statement 1 value, Result1 value, Statement 2 value, Result 2 value, Statement 3 value, Result 3 value, and so on...

How I would like this to work is change step 5 to be:

5) On button click DBXL inserts multiple rows to table in database like this:

row 1: DocID (1), EmpName (Mark) EmpID (123), statement 1 value, result 1 value

row 2: DocID (1), EmpName (Mark), EmpID (123), statement 2 value, result 2 value

row 3: DocID (1), EmpName (Mark), EmpID (123), statement 3 value, result 3 value and so on...

I understand. The main obstacle in achieving this is that your data schema is flat (statement1, result1, statement2, result2, etc) and not repeating.

With a repeating (data driven) structure (screenshot attached) you could point the root node of the DB mapping to the repeating group2 node. This would result in the DB mapping you are describing in step 5: one database row for each statement/result combination. 

In this data driven schema:

• my:group2[1]/my:statement = statement1
• my:group2[1]/my:result = result1
• my:group2[2]/my:statement = statement2
• and so on.

XML to XML might be an option as well, but it seems to me like the desired mapping (step 5) would requires a change to your form's schema.

Is this simply a matter of creating a repeating table a populating it and DBXL takes care of the rest or is there more to it than that?  Do you have an example (in IP 2007) demonstrating this so I can see how it works?  I will revise my form to use a repeating table to see what questions pop up.

Another caveat to my project is my SME has requested that we pre-populate the database table first then use DBXL to update the table.  Can DBXL do that?  So in my example, step 5, the multiple rows would already exist in database table but without the result values.  When the form is submitted to Sharepoint and database, the result values will update in the existing rows.  Is this possible?

In this project I am creating the InfoPath form programmatically, pre-populating form with employee header information from SQL view, and then uploading the form to SharePoint library AFTER database is pre-poulated.  In this scenario I could retreive a unique record ID and insert it into a form field on creation to tell DBXL which row to update -- is that possible with DBXL?

Hi Doreen,

DBXL is not typically used in this fashion (updating existing SQL data). However, DBXL could be used that way. It's not a scenario we encourage or recommend (we frankly try to avoid it), because of its complexity.

Since you are dealing with such advanced scenarios, I have opened a support ticket to get this request into the support queue. I'll ask Patrick to follow up with you to go over these scenarios and discuss how Qdabra or DBXL can help.

Thanks.