InfoPath Dev

I'm sure this has been answered before, but I guess my search isn't giving me what I need to know.

Is there a method that exists in DBXL that allows a form to retrieve data from existing SQL tables, then conversely update the data if it changes?  For example, we have a SQL table of existing IT employees with various personal data already gathered.  I'd like to retrieve the existing data by user ID (the table key), allow various edits, then update the table data when submitted back to DBXL. 

 Is this even possible?  I'm looking for rules based, as I'd like to keep from having to use an admin deployed solution, but am not totally against it.

Thanks.

Hi Tom,

To map the XML to SQL, DBXL keeps track of the primary keys. The problem is that those primary keys don't exist until you submit it once from InfoPath (via DBXL).

So, there are two techniques for doing what you want to do:

1. "Pre-Batch": Convert your SQL data to XML using XLS to XML tool
• This will automatically create mapping IDs for all data recordsets, i,e, subsequent opens will open the XML and update the data fine
• But, you'll end up with duplicate data that won't correspond to your existing (unless you remove constraints, add a separate primary key column and delete existing recordsets)
2. "On-Demand": Pre-populate form from SQL using QueryDB and submit as a new XML
• Map to a separate table and use a view to union the two tables
• Or, map to the same with an additional column that has the DBXL mapping primary key
• You could also trigger updates to the old table rows when a new value is written, but I would only do that if you have constraints on the old tables that need to be retained
• Subsequent opens should open the saved XML. So the form should query the table see if the DBXL column is set and if so, load the XML and use qRules SwapDom to insert it 

Both techniques work and we have customers using them.

The reasons for the complexity are that the primary data store for DBXL is XML. We specifically choose this because that preserves data integrity (whereas converting to SQL does not). XML is a richer structure than SQL. Of course, the XML is just stored as a column in SQL, but if we map out to SQL we lose the ability to do a loose coupling. I have blogged the reasons for this here: http://www.infopathdev.com/blogs/patrick/archive/2008/07/09/using-dbxl-with-existing-databases.aspx.

Thanks Patrick.  I'm using the on demand method, with lots of help from Hilary's previous blog on this topic.  It works really well.

Related to that, I'm wondering if you could help me with an issue.  I found your sample paging example, and it looks like it's a pretty simple and elegant solution.  What I'm wondering, is there a formula or function I can use to tell the row position of a fairly large table?  In your example you are using the item number of each entry in the repeating table to control what's displayed in each page.  The contents of the table I'm reading isn't autonumbering, so how can I tell what range is currently being used?

Hope this makes some sense and thanks.

Patrick, I think I have a preliminary method to deal with the issue in the previous post, but I'm having trouble loading the contents of the secondary data source call into a table in the main data source.  I'm using CopyTable, and I've posted a question here, http://www.infopathdev.com/forums/p/27028/93845.aspx#93845.  Using your paging method, I'm thinking I can load a row number attribute in the table with count(preceding-sibling::group2) + 1, where group2 is the repeating node in the main data source.

Will this RowNumber attribute work with the paging example you posted this summer?