InfoPath Dev

I have an InfoPath 2010 form with qRules that submits via  SubmitToDBXL to DBXL 2.6. Ernesto had guided me in setting DBXL up to map the form to a SharePoint library. That functionality works great.

I’ve since added a database mapping to the form in DBXL. When I submit the form the data is not appearing in the mapped database. I am not seeing any errors.

Other than creating the database mapping I made no changes in DBXL or in the form. Am I missing a step or setting? Attached is a screen capture of the database map and form submit actions.

 After submitting, does the form show in the Documents tab?

If you click Reshred, does an error come up?

The document does show up in the document count.

Reshreding throws errors on four of the six documents (attached). The other two documents do populate data into the SQL Database.

So the complication is how to set up keys. Ignoring, for the moment, the fact that I am dealing with legacy data from another system that this form is replacing....

I am looking to write the form data to SQL, then query DBXL to obtain the SQL record ID, specifically 'fundingID', and have that saved in the form in the SharePoint library. Having DBXL write the form to the SharePoint library made part of this redundant for this form, but I'll need to do this for other forms that are using qRules to write to SharePoint lists in addition to saving the form to a SharePoint library.

We may need to schedule a web session.

Thanks for checking. The problem is that the key is blank, and SQL won't accept a blank value for the key.

Where is fundingID coming from? Is it a value calculated in the form, or does the user type it in?

It sounds like the mapping is set up correctly, but there is bad data in the forms, namely, the blank key. Just make sure that the key is not blank before the user can submit to DBXL, such as disabling the submit button if the fundingID is blank.

The intent is that fundingID will come from SQL Server generating an auto numbered identity & primary key value. I was not aware that DBXL had to be provided with a key value. The plan was to import the legacy data into the SQL database, including the legacy IDs, then set the column as an identity column/primary key/auto number.

Some background: Some of the legacy data are inactive records- they are essentially read only for business analysis and should never have to be exposed as InfoPath forms. The rest of the legacy data are active records- they will have to be opened in InfoPath to be updated. All of the legacy data includes record IDs that need to be preserved, with new records picking up where they left off. This is somewhat easy; create a table with no key or identity columns that matches the structure of the legacy data, write in the legacy data, then edit the column with the legacy ID to be a key and identity column- SQL Server's auto numbering picks up where the legacy data left off (and doesn't mind if there are gaps in the legacy data's numbering).

If I have to provide DBXL with a key, then I can use the formID value (not mapped because I didn't think I needed it in the database). formID is a concatenation of a time stamp and user ID. But I won't have formID values for my active legacy data- unless that can be generated with the ExcelToInfoPath tool.

Is the fundingID column in SQL defined as an autoincrement identity key? If so, then it should not be mapped in DBXL.

It would be very helpful to have a manual for the product. All I have been able to find are tutorials that make assumptions. For example, the purchased product comes with two PDF files; one on how to install the license and one that barely covers installation of the product. It is assumed one knows to go to www.infopathdev.com (note not www.qdabra.com where the product was purchased) and search for additional documentation and sort through the many different versions of product documentation.

Q: When mapping to database tables, does DBXL require a column for its sole use in each table being mapped so that DBXL may generate its own key identity value in order to maintain the relationship with the corresponding copy of the data in DBXL's internal database? If so, what are the requirements for this column (e.g. integer, not designated as an identity column, designated as a primary key)?

Q: Does DBXL support NULL values for mapped fields?

Q: What is the technical definition of "shred", "shredding", and "reshredding"? Here in Washington, DC shredding means destruction of documents.

Q: In the DBXL Database Accelerator Administration Tool (DAAT) under a document type's database configuration database map, what is the purpose of the Key column? The field help in DAAT states a database identity should be used, but you state an auto increment identity key should NOT be mapped, and therefore not used.

Hi-

I'm sorry you've encounted issues with our DBXL documentation.

From the product page on Qdabra.com, we offer a list of available documents: http://www.qdabra.com/en/products/docs/DBXL26.aspx

We also offer installation instructions, a Getting Started guide and other documents from the default.htm page which comes up after installation.

We also offer free DBXL training every few months to help DBXL license owners.

Let us know how we can improve these going forward.

--

In the example database mapping shown in the Getting Started document, we show how to map the DBXL::DocID into a SQL column, and this serves as the key. That's the easiest scenario possible. Since every SQL table requires a key, then let's allow DBXL to control that. Module 2 of the DBXL training also covers this scenario.

In that same Module 2 of the DBXL training, in the section "Add a child table in SQL", we show how to set up a SQL autoincrement key, and we do not map the DBXL::DocID as the key.

The document "How to create database mappings in DBXL" also covers both of these scenarios. It has a section called "When to use the key field in database mapping".

--

Now to address your specific questions:

DC Forms:

Q: When mapping to database tables, does DBXL require a column for its sole use in each table being mapped so that DBXL may generate its own key identity value in order to maintain the relationship with the corresponding copy of the data in DBXL's internal database? If so, what are the requirements for this column (e.g. integer, not designated as an identity column, designated as a primary key)?

No. A key is necessary, but the key can be defined:

(a) in the SQL table (such as an autoincrement identity column), or

(b) in the DBXL database mapping.

In scenario (a), DBXL does not maintain the key (SQL does).

DC Forms:

Q: Does DBXL support NULL values for mapped fields?

This is not up to DBXL; it's up to the design of the SQL table. When designing the SQL table, you can determine which columns allow NULLs.

A column used as a key can never be NULL.

DC Forms:

Q: What is the technical definition of "shred", "shredding", and "reshredding"? Here in Washington, DC shredding means destruction of documents.

When you reshred, DBXL removes the data in SQL and immediately regenerates it using the latest data available in the XML forms.

DC Forms:

Q: In the DBXL Database Accelerator Administration Tool (DAAT) under a document type's database configuration database map, what is the purpose of the Key column? The field help in DAAT states a database identity should be used, but you state an auto increment identity key should NOT be mapped, and therefore not used.

Every interaction with a SQL table requires a key. Otherwise, the tool interacting with SQL won't be able to identify one record from the other. The key keeps the records unique.

Therefore, the interaction between DBXL and SQL requires a key, so that DBXL can uniquely identify the records stored in SQL. The key can be defined in SQL (autoincrement) or it can be mapped using the key checkbox in the Database mappign tab. It has to be one or the other.

Hope this helps.