InfoPath Dev

Hello,

I am relatively new to using infopath. Here is what I am trying to accomplish:

I have a database with a table of Part Numbers and related information such as description, revision, etc. The part numbers are keyed with a unique ID and the part number values themselves cannot repeat (database field rule). This is a Document/Revision Control database.

I have an Infopath request form that is setup as a document workflow. It has a repeating table for part numbers and revision description, etc.

What I want is the ability for the repeating table to:

1. Add new records to the part number table that do not exist.

2. Update exisiting records, only modifying certain fields such as revision description, etc.

Setup for 1 is easy and done.

But, when I try to add a part number that already exists, I get an error upon submit that tells me that I cannot add it because it would violate the database rule of reating values for the part number field.

Any advice?

Hi JodyG,

The old values aren't associated with an InfoPath form so InfoPath won't delete them when you try to edit. It just tries to add a new one which conflicts. I recommend adding a column to identify records submitted from InfoPath and then modify your primary key to include both your existing ID column and this new one. When you save, then you are setting that column to 1 since it's coming from InfoPath. Then the constraint will work.

Best practice here is to not tie your form directly to SQL because a) you won't be able to use it in browser forms (due to authentication issues with double hop from browser to IPFS to SQL), b) when the data source or database schemas change it's hard to migrate old XMLs (may not be an issue if you aren't saving forms), and c) data type support in SQL is a subset of what InfoPath supports. The best practice is to use a Web service on top of your SQL database. We have an out-of-box one you can install and configure. It will save you time as you scale your application and it is inexpensive (free trial available). It's called DBXL.

Best of luck!

Thank you Patrick.

Can you explain what this actually looks like in both terms of the part number db table and the form repeating table...

 Here is an example of the db form table:

RequestTable (for the general form info such as due date, project info and who the form gets routed to for approval):

[RequestID] [Requestor] [DueDate] ...

PartNumbersTable:

[P/NID] [RequestID] [PartNo] [Description] [Type] [Rev] [NextRev] [RevDesc] [Matl] ....

All ID fields are unique keys and [PartNo] is indexed with no duplicates allowed.

The repeating table is completely based on the PartNumbersTable, with exception of course the ID fields.

As you can see, it is pretty straight forward for adding records...no real problem there. The only problem is inputting data that already exists but some fields need updating such as RevDesc for existing records that are being revised.

I am trying to avoid creating another form for updating records. I tried creating another replicate table, but that doesn't update the main PartNumbersTable which. I have looked into using a query field for PartNo, but that cannot be in a repeating table.

I also understand your suggestions regarding best practices, however, I will not be using a browser enabled form, we are not svaing forms. This will strictly be a very small scale interoffice workflow just to manage documentation.

With that said, I am looking for a very easy and quick solution with minimal impact to the user.

Understood.

In SQL Management Studio, in table design mode, I believe you can select two fields (rows) and right click to make primary.

For specific implementation questions, we'd be happy to provide an hour of paid support. You may need an hour or two of an expert's time to work out the details. If interested, just go to Qdabra.com->Consulting->Support.

Good luck,

Thanks again Patrick. For clarity, I am just connected to an Access 2007 Database and have no real knowlege of manipulating SQL databases or dealing with SQL Management Studio. I really am looking for simple and easy and something that does not cost much more time or money. The PartNumbersTable that I am trying to deal with is used by other tables in the database. I cannot modify the key or modify the rules of the PartNo field for database integrity. I am merely adding a method of inputting and updating data, and giving it an email based workflow.